Re: Nested join support
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 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 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 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: > >> > >>"EXCEPT" ... > >> > >> Should this syntax be supported? > >> > >> It looks like this syntax is supported by at least postgres and sqlserver. > >> > >> Thanks! >
Re: Nested join support
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 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 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: >> >>"EXCEPT" ... >> >> Should this syntax be supported? >> >> It looks like this syntax is supported by at least postgres and sqlserver. >> >> Thanks!
Re: [MINOR]
I like the [MINOR] prefix because it makes it easy to identify simple commits (via grep or ctrl+f), the same way [CALCITE-1234] makes it easy to find commits related to [CALCITE-1234]. I also like that it maintains the "[...]" styling at the beginning of the commit message. Neither of these reasons is strong enough for me to say I oppose, just some minor (heh) counter-arguments. -Tanner On Tue, Jan 2, 2024 at 1:05 PM Julian Hyde wrote: > Ralph Waldo Emerson once wrote: “A foolish consistency is the > hobgoblin of little minds, adored by little statesmen and philosophers > and divines." > > That said, people tend to bring conventions from other projects to > Calcite, and we end up with chaos. By which I mean, lots of > self-expression, but no standards, and therefore commit messages that > have lower information content, and more work for the release manager > coercing them into a consistent change log. > > In Calcite we have not used '[MINOR]' as a prefix to minor commits. If > it is minor, it doesn't need a jira case, and doesn't need a prefix. > But a few commits with [MINOR] crept in, starting about a year ago. > Once or twice, I asked people to remove them, but the PRs had already > been merged. > > Any objections if I add a lint rule to fail the build if the commit > message contains [MINOR]? > > While I'm there, any other standards we should enforce? > > Julian >
FilterableTable query planner question
Hello, I'm making a FilterableTable with LDAP as a backend. I noticed in the FilterableTable method: public Enumerable scan(DataContext root, List filters) The filters list is empty if the sql where clause is checking the same field in all tables. ie. select test1.field test2.field from test1 full outer join test2 on test1.id = test2.id where test1.field = 'myval' or test2.field = 'myval'; When I do an EXPLAIN PLAN FOR select ... I notice the BindableTableScan filters array is empty with both: where test1.field = 'myval' or test2.field = 'myval'; and it has the filter if I remove one: where test1.field = 'myval' Is there an example I could be pointed to help understand the query planner here, and hopefully write my implementation such that the filters show up as expected for each table? Thank you! Eric
Re: Nested join support
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 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: > > "EXCEPT" ... > > Should this syntax be supported? > > It looks like this syntax is supported by at least postgres and sqlserver. > > Thanks!
[MINOR]
Ralph Waldo Emerson once wrote: “A foolish consistency is the hobgoblin of little minds, adored by little statesmen and philosophers and divines." That said, people tend to bring conventions from other projects to Calcite, and we end up with chaos. By which I mean, lots of self-expression, but no standards, and therefore commit messages that have lower information content, and more work for the release manager coercing them into a consistent change log. In Calcite we have not used '[MINOR]' as a prefix to minor commits. If it is minor, it doesn't need a jira case, and doesn't need a prefix. But a few commits with [MINOR] crept in, starting about a year ago. Once or twice, I asked people to remove them, but the PRs had already been merged. Any objections if I add a lint rule to fail the build if the commit message contains [MINOR]? While I'm there, any other standards we should enforce? Julian
Nested join support
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: "EXCEPT" ... Should this syntax be supported? It looks like this syntax is supported by at least postgres and sqlserver. Thanks!
