[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[
https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17008857#comment-17008857
]
Jin Xing commented on CALCITE-3155:
---
Hi [~danny0405] and [~MusbahELFIL]
PR-1286 add a test cases like below
{code:java}
@Test
public void testUnionAllWithNoOperands() {
String query = "select A.\"department_id\" "
+ "from \"foodmart\".\"employee\" A "
+ " where A.\"department_id\" = ( select min( A.\"department_id\") from
\"foodmart\".\"department\" B where 1=2 )";
final String expected = "SELECT \"employee\".\"department_id\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "INNER JOIN (SELECT \"t1\".\"department_id\" AS \"department_id0\","
+ " MIN(\"t1\".\"department_id\")\n"
+ "FROM (SELECT *\nFROM (VALUES (NULL, NULL))"
+ " AS \"t\" (\"department_id\", \"department_description\")"
+ "\nWHERE 1 = 0) AS \"t\","
+ "\n(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\""
+ "\nGROUP BY \"department_id\") AS \"t1\""
+ "\nGROUP BY \"t1\".\"department_id\") AS \"t3\" "
+ "ON \"employee\".\"department_id\" = \"t3\".\"department_id0\""
+ " AND \"employee\".\"department_id\" = MIN(\"t1\".\"department_id\")";
sql(query).ok(expected);
}
{code}
Seems the *expected* sql is not executable with join condition
*employee.department_id= MIN(t1department_id)*
If I run below sql:
{code:java}
select * from test t1 join test t2 on t1.b=min(t2.b);
{code}
Postgre will complain
{code:java}
ERROR: aggregate functions are not allowed in JOIN conditions
{code}
Mysql will complain
{code:java}
Invalid use of group function
{code}
> AssertionError in RelToSqlConverter when visiting empty Values node
> ---
>
> Key: CALCITE-3155
> URL: https://issues.apache.org/jira/browse/CALCITE-3155
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Musbah EL FIL
>Assignee: Danny Chen
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Given the query:
> *select A."department_id" from "foodmart"."employee" A where
> A."department_id" = ( select min( A."department_id") from
> "foodmart"."department" B where 1=2 )*
> The conversion from SqlNode to RelNode is correct, however when going from
> RelNode to SqlNode, an exception was being thrown.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[
https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883461#comment-16883461
]
Danny Chan commented on CALCITE-3155:
-
[~julianhyde] If the whole `values` statement is special enough to have
limitation for some dialects, maybe we should add another control flag in
SqlDialect like supportsValues ? Instead of using values, maybe we could use
subquery instead:
{code:sql}
select * from (
select null as c0, null as c1
) t1 where 1=0
{code}
> AssertionError in RelToSqlConverter when visiting empty Values node
> ---
>
> Key: CALCITE-3155
> URL: https://issues.apache.org/jira/browse/CALCITE-3155
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Musbah EL FIL
>Assignee: Danny Chan
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Given the query:
> *select A."department_id" from "foodmart"."employee" A where
> A."department_id" = ( select min( A."department_id") from
> "foodmart"."department" B where 1=2 )*
> The conversion from SqlNode to RelNode is correct, however when going from
> RelNode to SqlNode, an exception was being thrown.
>
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[
https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883437#comment-16883437
]
Julian Hyde commented on CALCITE-3155:
--
Partly my bad - I had overridden {{supportsAliasedValues}} to false in my
dialect object, so the code was assuming that my dialect was Oracle and has a
DUAL table.
But when I fix my dialect, I discover that VALUES unparsing does not work - and
never has - for MySQL. For MySQL we need to use SELECT-without-FROM, because it
does not allow VALUES in a query.
> AssertionError in RelToSqlConverter when visiting empty Values node
> ---
>
> Key: CALCITE-3155
> URL: https://issues.apache.org/jira/browse/CALCITE-3155
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Musbah EL FIL
>Assignee: Danny Chan
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Given the query:
> *select A."department_id" from "foodmart"."employee" A where
> A."department_id" = ( select min( A."department_id") from
> "foodmart"."department" B where 1=2 )*
> The conversion from SqlNode to RelNode is correct, however when going from
> RelNode to SqlNode, an exception was being thrown.
>
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[ https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883324#comment-16883324 ] Musbah EL FIL commented on CALCITE-3155: I am not sure that this problem is due to my change: * The query which depends on DUAL was added to a branch in the code that was already using DUAL. It would generate a "SELECT NULL as X ... from DUAL where false" * The commit was modified to account for the cases where the dialect supports aliased values. It would generate a query of the form "Select * from VALUES(NULL, NULL ...) where 1=0" So in summary, my changes should not output a query similar to the one that is failing. A stack trace of the exception would help to pinpoint the problem even further. > AssertionError in RelToSqlConverter when visiting empty Values node > --- > > Key: CALCITE-3155 > URL: https://issues.apache.org/jira/browse/CALCITE-3155 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Musbah EL FIL >Assignee: Danny Chan >Priority: Minor > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 1h 10m > Remaining Estimate: 0h > > Given the query: > *select A."department_id" from "foodmart"."employee" A where > A."department_id" = ( select min( A."department_id") from > "foodmart"."department" B where 1=2 )* > The conversion from SqlNode to RelNode is correct, however when going from > RelNode to SqlNode, an exception was being thrown. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[
https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883295#comment-16883295
]
Julian Hyde commented on CALCITE-3155:
--
As I feared, this has broken stuff, because not all databases have a DUAL
table. I now get an error when running against MySQL.
{noformat}
java.lang.RuntimeException: while executing SQL: SELECT
NULL AS `users.name`
FROM `DUAL`
WHERE 1 = 0, (conn=5) Table 'looker_test.dual' doesn't exist
at ...
Caused by: java.sql.SQLSyntaxErrorException: (conn=5) Table 'looker_test.dual'
doesn't exist
{noformat}
> AssertionError in RelToSqlConverter when visiting empty Values node
> ---
>
> Key: CALCITE-3155
> URL: https://issues.apache.org/jira/browse/CALCITE-3155
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Musbah EL FIL
>Assignee: Danny Chan
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Given the query:
> *select A."department_id" from "foodmart"."employee" A where
> A."department_id" = ( select min( A."department_id") from
> "foodmart"."department" B where 1=2 )*
> The conversion from SqlNode to RelNode is correct, however when going from
> RelNode to SqlNode, an exception was being thrown.
>
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[ https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16875953#comment-16875953 ] Musbah EL FIL commented on CALCITE-3155: Thank you all for your reactivity! > AssertionError in RelToSqlConverter when visiting empty Values node > --- > > Key: CALCITE-3155 > URL: https://issues.apache.org/jira/browse/CALCITE-3155 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Musbah EL FIL >Assignee: Danny Chan >Priority: Minor > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 1h 10m > Remaining Estimate: 0h > > Given the query: > *select A."department_id" from "foodmart"."employee" A where > A."department_id" = ( select min( A."department_id") from > "foodmart"."department" B where 1=2 )* > The conversion from SqlNode to RelNode is correct, however when going from > RelNode to SqlNode, an exception was being thrown. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[ https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16875840#comment-16875840 ] Musbah EL FIL commented on CALCITE-3155: Indeed. The fix was applied to the part of the code that was already using the DUAL table. I have update the PR with an additional commit to account for the non-Oracle case. In such cases, i am building query of the form "*select * from ( VALUES(null, null, ..., null) ) as T(C1, C2 ... Cn) where 1=0* ". The reason for choosing "_1=0_" is that "_where false_" is not supported on SQL Server. And we need to build a sub-query that returns an empty result set since we are trying to convert to an SQL string VALUES with no tuples. > AssertionError in RelToSqlConverter when visiting empty Values node > --- > > Key: CALCITE-3155 > URL: https://issues.apache.org/jira/browse/CALCITE-3155 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Musbah EL FIL >Priority: Minor > Labels: pull-request-available > Time Spent: 1h > Remaining Estimate: 0h > > Given the query: > *select A."department_id" from "foodmart"."employee" A where > A."department_id" = ( select min( A."department_id") from > "foodmart"."department" B where 1=2 )* > The conversion from SqlNode to RelNode is correct, however when going from > RelNode to SqlNode, an exception was being thrown. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node
[ https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16875628#comment-16875628 ] Julian Hyde commented on CALCITE-3155: -- With its use of the DUAL table, the fix is far too dependent on oracle. I would like to see this problem solved for several databases simultaneously. I think it would lead to better code structure. > AssertionError in RelToSqlConverter when visiting empty Values node > --- > > Key: CALCITE-3155 > URL: https://issues.apache.org/jira/browse/CALCITE-3155 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Musbah EL FIL >Priority: Minor > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Given the query: > *select A."department_id" from "foodmart"."employee" A where > A."department_id" = ( select min( A."department_id") from > "foodmart"."department" B where 1=2 )* > The conversion from SqlNode to RelNode is correct, however when going from > RelNode to SqlNode, an exception was being thrown. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
