IN predicate incorrectly handles single parenthesized subquery as IN-list, instead of table subquery

Currently IN with a parenthesized subquery is incorrect handled as an IN-list with a single value, instead of a table subquery.

The following use of IN with a subquery will work fine:

select *
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_ID in (
  select RDB$CHARACTER_SET_ID
  from RDB$COLLATIONS
)

However, adding another set of parentheses in the IN, will make the parser choose the IN-list alternative instead of the IN-subquery route.

As a result, using

select *
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_ID in ((
  select RDB$CHARACTER_SET_ID
  from RDB$COLLATIONS
))

will result in error

multiple rows in singleton select [SQLState:21000, ISC error code:335544652]

This is essentially an ambiguity in the syntax BNF of 8.4 \<in predicate> of ISO 9075-2:2023, but the SQL standard has an explicit Syntax Rule to address this ambiguity.

BNF syntax (ambiguous)

<in predicate> ::=
  <row value predicand> <in predicate part 2>

<in predicate part 2> ::=
  [ NOT ] IN <in predicate value>

<in predicate value> ::=
    <table subquery>
  | <left paren> <in value list> <right paren>

<in value list> ::=
  <row value expression> [ { <comma> <row value expression> }... ]

One of the \ productions is \. Both \

and \ resolve to \, which resolves to (\), hence the error if it doesn't produce a singleton scalar value.

Syntax Rule 1 (resolving the ambiguity):

1) If \ consists of a single \, then that \ shall not be a \.

NOTE 337 — This Syntax Rule resolves an ambiguity in which \ might be interpreted either as a \

or as a \. The ambiguity is resolved by adopting the interpretation that the \ will be interpreted as a \

mrotteveel commented 3 months ago

Given support for parenthesized query expressions was introduced in Firebird 5.0, this should only be fixed in Firebird 5.0 and/or higher.

FirebirdSQL / firebird

IN predicate incorrectly handles single parenthesized subquery as IN-list, instead of table subquery #8182