Обсуждение: VALUES ROW(...)

Hi!

PostgreSQL does not accept the following standard conforming statement:

   VALUES ROW(1,2), ROW(3,4)

There is a comment about this in the source code [0]:

/*
* We should allow ROW '(' expr_list ')' too, but that seems to require
* making VALUES a fully reserved word, which will probably break more apps
* than allowing the noise-word is worth.
*/

The latest release of MySQL (8.0.19) introduced table value constructors (VALUES), but **requires** the keyword ROW
[1].Of the 9 systems I tested, only MySQL and H2 accept ROW in VALUES [2]. 

Is it worth re-visiting this decision in order to improve standard conformance and MySQL (and H2) compability?

-markus

Refs:
[0] src/backend/parser/gram.y -
https://github.com/postgres/postgres/blob/30012a04a6c8127397a8ab71e160d9c7e7fbe874/src/backend/parser/gram.y#L11893
[1] https://dev.mysql.com/doc/refman/8.0/en/values.html
[2] Not supporting it: Db2 11.5, MariaDB 10.4, Oracle 19c, SQL Server 2019, SQLite 3.30.0, Derby 10.15.1.3.
    Some results published here: https://modern-sql.com/feature/values#compatibility

Markus Winand <markus.winand@winand.at> writes:
> PostgreSQL does not accept the following standard conforming statement:
>    VALUES ROW(1,2), ROW(3,4)
> There is a comment about this in the source code [0]:

> /*
> * We should allow ROW '(' expr_list ')' too, but that seems to require
> * making VALUES a fully reserved word, which will probably break more apps
> * than allowing the noise-word is worth.
> */

> The latest release of MySQL (8.0.19) introduced table value constructors (VALUES), but **requires** the keyword ROW
[1].Of the 9 systems I tested, only MySQL and H2 accept ROW in VALUES [2]. 

> Is it worth re-visiting this decision in order to improve standard conformance and MySQL (and H2) compability?

I'd still say that making VALUES fully reserved is a bridge too far:
you will make many more people unhappy from that than you make happy
because we can now spell this more than one way.  (I'm somewhat
guessing that some people are using "values" as a column or table name,
but I doubt that that's an unreasonable guess.)

If you want to see some movement on this, look into whether we can
find a way to allow it without that.  I don't recall exactly what
the stumbling block is there, but maybe there's a way around it.

            regards, tom lane