Possible syntax mismatch for json_object in MySQL compatibility mode

[bazoocaze]

Hello,

I'm using H2 for integration tests on a daily basis, and I think I found a syntax mismatch in MySQL compatibility mode (MODE=MySQL) for the json_object function.

MySQL version: 5.7 and 8.0
H2 version: 2.1.210 and 2.1.214

Exemple 1:

select json_object('first_name', 'John', 'last_name', 'Doe');

MySQL Result: {"last_name": "Doe", "first_name": "John"}
H2 Result: not works (Syntax error in SQL statement)

Exemple 2:

select json_object('first_name': 'John', 'last_name': 'Doe');

MySQL Result: not works (bad SQL grammar)
H2 Result: {"first_name":"John","last_name":"Doe"}

Exemple 3:

select json_object(key 'first_name' value 'John', key 'last_name' value 'Doe');

MySQL Result: not works (bad SQL grammar)
H2 Result: {"first_name":"John","last_name":"Doe"}

https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-object

Is it possible (or is it intended to) to have a compatible json_object syntaxe for MySQL compatibility mode?

[katzyn]

JSON_OBJECT function is a part of the SQL Standard. H2 accepts both forms from the Standard.

If MySQL doesn't accept them, please ask developers of MySQL about support of standard syntax.

[katzyn]

You can submit a PR, of course, but this non-portable syntax should only be allowed in MySQL (and possibly MariaDB, if this DBMS allows it too) compatibility modes. It must be rejected in all other modes to avoid its accidental misuse.

[andreitokar]

It seems, that rule of thumb here is: we are trying not to implement compatibility for bugs and deficiencies of other databases.
If MySQL does not support some standard feature, why do you want H2 to do the same? Unlikely that you would you use such syntax in your application, if it is targeted to MySQL, and you just test with H2. H2 can not be used as SQL correctness validator for database XYZ.

[bazoocaze]

Sorry, I thought that MySQL compatibility mode was meant to adapt mismatching features between a specif DB engine and H2.
Ok, so please tell me in advance if H2 is not welcoming such patches, so I do not take any effort on this.
Thank you for your time.

[katzyn]

You may submit a pull request where

1. In MySQL and MariaDB compatibility modes both standard an their own syntax will be accepted.
2. In all other compatibility modes only standard syntax will be accepted.

But please find existing or fill new issues in their bugtrackers about missing support for standard syntax and post links to these issues here.
https://bugs.mysql.com/
https://jira.mariadb.org/projects/MDEV/issues

It will be even better to contribute support of standard-compliant JSON_OBJECT syntax to these database systems instead of introduction of non-standard syntax in H2.

[bazoocaze]

Non-standard syntax reported for MariaDB project

https://jira.mariadb.org/browse/MDEV-29161

[bazoocaze]

FYI:

The guys at MariaDB accepted the claim on the colon ':' separator syntax 🎉
Could you please point where in the ISO SQL standard is the syntax for KEY/VALUE keywords as found on H2?

you and H2 guys are right, the standard syntax is with a colon ':', not with a comma ','. I agree that we should support the standard syntax. As for key/value variant — I wasn't able to find it in the standard. Can you point out where the 2016 standard says that? Part, section, subsection, etc?

[katzyn]

I answered to your question here:
#3579 (comment)