Ambiguous syntax when using scalar subquery without parentheses in JSON_OBJECT

[lukaseder]

This works as expected:

select json_object(
  key 'a' value (select 1), 
  key 'b' value (select 2)
)

And produces:

{"a":1,"b":2}

Curiously, this works as well:

select json_object(
  key 'a' value (select 1), 
  key 'b' value select 2
)

It seems the parentheses around the scalar subquery of the last key/value of a JSON_OBJECT are optional. This, however, fails due to the ambiguity of the , operator:

select json_object(
  key 'a' value select 1, 
  key 'b' value select 2
)

The error being:

SQL Error [42001] [42001]: Syntax error in SQL statement "select json_object(\000d\000a  key 'a' value select 1, \000d\000a  [*]key 'b' value select 2\000d\000a)"; expected "*, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
select json_object(
  key 'a' value select 1, 
  key 'b' value select 2
) [42001-210]

There seems to be a bug either in the second or in the third query. I tend to think that the second query is wrong, and the scalar subquery without parentheses should be rejected, but then again, this works:

select select select 1 = select 1

So it doesn't seem to be a simple fix.

[katzyn]

From my point of view H2 should disallow subqueries without parentheses at least in STRICT and some other compatibility modes to avoid such surprises.

We can't disallow them in LEGACY mode because many applications may use them due to various reasons.

Should they work in REGULAR mode is an open question. This error-prone non-standard syntax is actually documented in H2, but it obviously cannot be fixed for all cases. If SQL warnings will be implemented, usage of such syntax should produce a warning, I think. It also should be removed from documentation.

[lukaseder]

This error-prone non-standard syntax is actually documented in H2, but it obliviously cannot be fixed for all cases

I think it also cannot be documented for all cases, there must be so many edge cases like this one here, where the "outer syntax" creates ambiguities...

If SQL warnings will be implemented, usage of such syntax should produce a warning, I think. It also should be removed from documentation.

Agreed.