-
Notifications
You must be signed in to change notification settings - Fork 1.3k
Closed
Description
According to the SQL standard, a "qualified join" (i.e. a join with ON or USING clause) should be parsed as follows:
<qualified join> ::=
{ <table reference> | <partitioned join table> }
[ <join type> ] JOIN
{ <table reference> | <partitioned join table> }
<join specification>
Where
<table reference> ::=
<table factor>
| <joined table>
<table factor> ::=
<table primary> [ <sample clause> ]
<table primary> ::=
<table or query name>
| ...
| <parenthesized joined table>
As you can see, the right side of the JOIN operator is a <table reference>, which might itself be a <joined table>, and thus a <qualified join>, or a <parenthesized joined table>
This means, the following syntax is valid (and works, e.g. in SQL Server, resulting in a single record (1, 1, 1)) but is rejected by H2. The following syntax is putting a <parenthesized joined table> at the right side of X join:
select
X.A,
Y.B,
Z.C
from (
select 1 A
) X
join (
(
select 1 B
) Y
join (
select 1 C
) Z
on Z.C = Y.B
)
on Y.B = X.AError message:
Syntax error in SQL statement "SELECT X.A, Y.B, Z.C FROM ( SELECT 1 A ) X JOIN ( ( SELECT 1 B ) Y[*] JOIN ( SELECT 1 C ) Z ON Z.C = Y.B ) ON Y.B = X.A "; expected "UNION, MINUS, EXCEPT, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, FOR, )"; ```
This works, putting a <table factor> at the right side of X join:
select
X.A,
Y.B,
Z.C
from (
select 1 A
) X
join
(
select 1 B
) Y
join (
select 1 C
) Z
on Z.C = Y.B
on Y.B = X.AMetadata
Metadata
Assignees
Labels
No labels