[jira] [Commented] (CALCITE-3155) AssertionError in RelToSqlConverter when visiting empty Values node

["Jin Xing (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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)