h2database / h2database/h2database

Ambiguous syntax when using scalar subquery without parentheses in JSON_OBJECT

Open
#3,449 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Java
Stars
4.6k
Forks
1.3k
Avg merge
1d 2h
Merged PRs (30d)
9

Description

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.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the three JSON_OBJECT queries and the nested SELECT example in H2 to compare which forms the SQL parser accepts. Review the parser entry points for JSON_OBJECT key/value expressions and define whether unparenthesized scalar subqueries should be accepted or rejected; done requires consistent handling of the ambiguous comma syntax and regression coverage for these examples.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.