I'll replace the VALUES subqueries with tables and indent to make the
intended operator precedence clearer:
SELECT *
FROM customer
LEFT OUTER JOIN orders
INNER JOIN lineitem
ON o_orderkey = l_orderkey
ON c_custkey = o_custkey
Since 1.31 [1] we support parenthesized joins, like this:
SELECT *
FROM customer AS c
LEFT OUTER JOIN (orders AS o
INNER JOIN lineitem AS l
ON o.orderkey = l.orderkey)
ON c.custkey = o.custkey
But I believe the thing before the ON keyword must be a table
reference, not a join term. For your query to be valid, you need to
convert a join term such as 'x join y on condition' into a table
reference by enclosing it in parentheses.
Should we support the version without parentheses? My inclination is
no. The precedence/associativity of the JOIN operator are not clear
enough without parentheses; ambiguity is bad for users (cryptic error
messages, and obscure parser bugs) and bad for performance (parsers
have to backtrack). But if you can show that the SQL standard allows
joins without parentheses we should reconsider.
Julian
[1] https://issues.apache.org/jira/browse/CALCITE-35
On Tue, Jan 2, 2024 at 3:36 PM Sean Broeder <s...@dremio.com.invalid> wrote:
>
> Thank you for looking at this Julian. To be clear, I’m asking if this
> statement should be supported or not. I’m not suggesting that it should be.
>
> Hopefully this formatting is easier to read:
>
> SELECT *
> FROM (
> VALUES (1,
> 'John')) AS "customer"(c_custkey,
> c_name)
> LEFT OUTER JOIN (
> VALUES(100,
> 1)) AS "orders"(o_orderkey,
> o_custkey)
> INNER JOIN (
> VALUES (100,
> 'Random item')) AS "lineitem"(l_orderkey,
> l_itemname) ON o_orderkey =
> l_orderkey ON c_custkey = o_custkey
>
>
> > On Jan 2, 2024, at 1:07 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> > What is 'this syntax' you think we should support? (Your query is
> > poorly formatted, so I can't see what pattern in it is confusing the
> > parser.)
> >
> > On Tue, Jan 2, 2024 at 11:46 AM Sean Broeder <sbroe...@apache.org> wrote:
> >>
> >> It looks like Calcite doesn't support the query
> >>
> >> select * from (values (1, 'John')) as
> >>
> >> "customer"(c_custkey, c_name)
> >>
> >> left outer join (values(100, 1)) as "orders"(o_orderkey, o_custkey)
> >>
> >> inner join (values (100, 'Random item')) as "lineitem"(l_orderkey,
> >> l_itemname) on o_orderkey = l_orderkey
> >>
> >> on c_custkey = o_custkey
> >> I'm seeing a parser error
> >> on c_custkey = o_custkey": parse failed: Encountered "on" at line 5, column
> >> 1.
> >> Was expecting one of:
> >> <EOF>
> >> "EXCEPT" ...
> >>
> >> Should this syntax be supported?
> >>
> >> It looks like this syntax is supported by at least postgres and sqlserver.
> >>
> >> Thanks!
>
