h2database / h2database/h2database
Ambiguous syntax when using scalar subquery without parentheses in JSON_OBJECT
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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