[
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)
