Subquery as Element of Nested SQL List

user_16028966 0
Viewed 298

TLDR

Is there a dialect of SQL that not only (1) permits nested lists, but also (2) permits an SQL subquery be a valid element of such a list?

SELECT *
FROM tuples
WHERE (dim_1, dim_2) IN ((1, 2), (SELECT DISTINCT x, y FROM coords LIMIT 2))

Background

Sorry to post an academic question due to my narrow domain knowledge, rather than a diagnostic question due to code errors. I post this question here because I have been unable to find an answer online: most search results deal with nested subqueries, and any others deal with nested lists containing scalar elements.

I am writing an R function insert_params() to dynamically create an SQL list from an arbitrary R list() (via DBI::sqlInterpolate() to prevent injection), with optional recursion (or alternatively flattening via unlist()) for nested lists. I have in mind a situation like

SELECT *
FROM my_table
WHERE (field_1, field_2) IN (('bobby', 'tables'), ('strawberry', 'fields'), (SELECT 0 WHERE 0 = 1))

where insert_params() is designed to (optionally) insert an empty subquery as a syntactically valid representation in SQL (as opposed to ()) of an empty list() from R.

While I am working for the moment with Microsoft SQL Server, which does not seem to permit nested SQL lists, I don't want insert_params() to limit users working with other SQL dialects, so I want to build in the flexibility to handle nested lists.

Now insert_params() does indeed produce an SQL string like the one above, as intended. However, I want to ensure this string is syntactically valid in those SQL dialects that do permit nested lists; otherwise, this functionality is pointless.

Thanks for your help!

Edit:

I'd also like to know if such dialects (if they exist) and queries pose issues for ANSI compliance. I don't know much about ANSI compliance, but insert_params() does use a DBI::ANSI object in tandem with DBI::sqlInterpolate(). Per R documentation for DBI::ANSI:

Description

A dummy DBI connector that simulates ANSI-SQL compliance

subquery sql-injection r nested sql
2 Answers

No. You can handle this in one of two ways. I would recommend:

WHERE (dim_1, dim_2) IN ((1, 2)) OR
      (dim_1, dim_2) IN (SELECT x, y FROM coords LIMIT 2))

Or:

WHERE (dim_1, dim_2) IN ( (SELECT x, y FROM coords LIMIT 2) 
                          UNION ALL
                          (SELECT 1, 2)
                        )

Notes:

  • SELECT DISTINCT is not needed with IN.
  • LIMIT is not standard SQL. The generic SQL would be FETCH FIRST 2 ROWS ONLY.
  • Not all databases support SELECT with no FROM, so the second solution would need to be tweaked in those databases.
user_11440350

Oracle has the feature, for example, this is a valid Oracle SQL query

SELECT * 
FROM mytable
WHERE (a, b) IN (((select max(a) from mytable), (select max(b) from mytable)), (9,5));

Note subqueries at the IN list of tuples.

db<>fiddle

and this is valid query too

SELECT * 
FROM mytable
WHERE (a, b) IN (
    select a+1, b+1 from mytable union all
    select 9,5 from dual union all
    select (select max(a) from mytable), (select max(b) from mytable) from dual)
user_62199790
Related