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

[mrotteveel]

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 <row value expression> productions is <scalar subquery>. Both <table subquery> and <scalar subquery> resolve to <subquery>, which resolves to (<query expression>), hence the error if it doesn't produce a singleton scalar value.

Syntax Rule 1 (resolving the ambiguity):

1. If <in value list> consists of a single <row value expression>, then that <row value expression> shall not be a <scalar subquery>.

   NOTE 337 — This Syntax Rule resolves an ambiguity in which <in predicate value> might be interpreted either as a <table subquery> or as a <scalar subquery>. The ambiguity is resolved by adopting the interpretation that the <in predicate value> will be interpreted as a <table subquery>.

[mrotteveel]

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

[mrotteveel]

The handling in Firebird is unrelated to parenthesized sub-queries, so I also backported this to Firebird 3 and Firebird 4.