Search code examples
sqlfirebird

Subselect in double parentheses leads to multiple rows in singleton select error

I ran into a strange behavior in Firebird (Firebird 4 and Firebird 2.5 as well) using the following example query:

select * from rdb$relations 
where rdb$relation_id in ((select rdb$relation_id from rdb$relations))

Upon execution I get the following error:

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

When I replace the double parentheses with a single pair

select * from rdb$relations 
where rdb$relation_id in (select rdb$relation_id from rdb$relations)

then the query is executed as expected. I played around with some other examples found out that I run into this error only if the subquery delivers more than one result record.

My actual problem is that the real SQL statement is generated by Spring Data Criteria API within the scope of a Spring Boot application. So I don't have means to control the number of parentheses generated.

In my opinion the number of parentheses should not have any effect on the semantic of the given or similar SQL statements.

Solution

  • The problem is that there are two forms of IN (...):

    • IN <table subquery>
    • IN (<value-expression> [{, <value-expression>} ...]).

    One of the <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.

    And although in theory both productions could be valid (at least, since Firebird 5.0 added support for parenthesized query expressions), the parser picks one path (for the <scalar subquery>).