is it a bug?

· 788 words · 4 minute read

[2023-02-01 Edit: I somehow navigated the standard wrongly, so I edited this blog post. Thanks Lukas fot pointing me out where my mistake was.]

Last week-end, I found this tweet:

My take was that Postgres was right and conforming to the SQL Standard whereas the other RDBMS were wrong, but that’s faith, not proof.

So, I gathered my courage and deep dived into the SQL Standard to find formal proof.

The problem 🔗

It’s not that I don’t trust people on the Internet, but I always double check what I find there. So, what is the problem?

laetitia=# select (select 1,2) = (select 1,2);
ERROR:  subquery must return only one column
LINE 1: select (select 1,2) = (select 1,2);
               ^

Just to be sure, I tried using an explicit row constructor (as I mentioned in the Twitter thread):

laetitia=# select row(1,2) = row(1,2);
 ?column? 
 ----------
  t
  (1 row)

So, it seemed I was not so wrong when I assumed that in this case you need an explicit row constructor.

Using Browsable SQL grammar 🔗

I could have used the pdf of the SQL standard, but as we now have a browsable version of the SQL-2016 standard, it was much easier to use it. You’ll find this browsable grammar here.

Where do we start? 🔗

So, the starting point is:

<direct SQL statement>.

So that we have:

<direct SQL statement> ::=
  <directly executable statement>  <semicolon>  ::=

  <directly executable statement> ; ::=

   <direct SQL data statement>  ; ::=

   <direct select statement: multiple rows>  ; ::=

   <cursor specification>  ; ::=

   <query expression>  ; ::=

   <query expression body>  ; ::=

   <query term>  ; :: =

   <simple table>  ; ::=

   <query specification>  ; ::=

   SELECT <select list>  ; ::=

   SELECT <select sublist>  ; ::=

   SELECT <derived column>  ; ::=

   SELECT <value expression>  ; ::=

   SELECT <boolean value expression>  ; ::=

   SELECT <boolean term>  ; ::=

   SELECT <boolean factor>  ; ::=

   SELECT <boolean test>  ; ::=

   SELECT <boolean primary>  ; ::=

   SELECT <predicate>  ; ::=

   SELECT <comparison predicate>  ; ::=

   SELECT <row value predicand>  <comparison predicate part 2>  ; ::=

   SELECT <row value predicand>  <comp op>  <row value predicand>  ; ::=

   SELECT <row value predicand>  <equals operator>  <row value predicand>  ; ::=

   SELECT <row value predicand>  = <row value predicand>  ; ::=

   SELECT <row value constructor predicand>  = <row value constructor predicand>  ; ::=

   SELECT <explicit row value constructor>  = <explicit row value constructor>  ; ::=

   SELECT <row subquery>  = <row subquery>  ; ::=

   SELECT <subquery>  = <subquery>  ; ::=

   SELECT <left paren>  <query expression>  <right paren>  ; ::=

   SELECT ( <query expression>  ) ;

   SELECT <left paren>  <query expression>  <right paren>  = <left paren>  <query expression>  <right paren>  ; ::=

   SELECT ( <query expression>  ) = ( <query expression>  );

So, it seems that Lukas was wrong as (select 1,2) = (select 1,2) is not a query expression and even though it was a query expression, it should be between parenthesis. Just for the sake of proof, I tried the following on Postgres.


laetitia=# select ((select 1,2) = (select 1,2));
ERROR:  subquery must return only one column
LINE 1: select ((select 1,2) = (select 1,2));
^

So, Luke was right and I was wrong. Still, I’m very happy to have learned something new today! Next step: patching the parser?

What about using the row operator? 🔗

You can follow the path of using a row operator from that point:

   SELECT <explicit row value constructor>  = <explicit row value constructor>  ; ::=

   SELECT ROW <left paren>  <row value constructor element>  <right paren>  = <left paren>  <row value constructor element>  <right paren>  ; ::=

   SELECT ROW ( <row value constructor element>  ) = ROW ( <row value constructor element>  ) ; ::=

   SELECT ROW ( <value expression> ) = ROW ( <value expression>  ) ;

## Am I wrong?

Of course, I could be wrong and if you find a path in the SQL Standard where you end up with select (select 1,2) = (select 1,2); being a valid query, please let me know your complete navigation path through the SQL grammar, so I can understand how it works.

I was wrong 🔗

Yes, I was wrong and I have no shame to admit it. It’s the better way to learn and remember something. Navigating the standard can be tricky and we can get lost very fast. Next time, I’ll double and triple check my navigation path!

So, Postgres is not compliant to this particular Standard SQL syntax. I think it should be! I’m not sure my skills are enough to write this patch, but I can at least try!

PostgreSQL SQL Standard