Skip to content

H2 does not correctly parse <parenthesized joined table> #492

@lukaseder

Description

@lukaseder

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.A

Error 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.A

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions