You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/12/13 23:01:29 UTC
[3/7] calcite git commit: [CALCITE-1022] Rename ".oq" Quidem files to
".iq"
http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/join.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
new file mode 100644
index 0000000..357ddc5
--- /dev/null
+++ b/core/src/test/resources/sql/join.iq
@@ -0,0 +1,288 @@
+# join.iq - Join query tests
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use post
+!set outputformat mysql
+
+# OR is a theta join
+select *
+from emp
+join dept
+on emp.deptno = dept.deptno or emp.ename = dept.dname;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Alice | 30 | F | 30 | Engineering |
+| Bob | 10 | M | 10 | Sales |
+| Eric | 20 | M | 20 | Marketing |
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
++-------+--------+--------+---------+-------------+
+(5 rows)
+
+!ok
+
+# As an INNER join, it can be executed as an equi-join followed by a filter
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t1, $t3)], expr#6=[CAST($t0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], expr#7=[=($t6, $t4)], expr#8=[OR($t5, $t7)], proj#0..4=[{exprs}], $condition=[$t8])
+ EnumerableJoin(condition=[true], joinType=[inner])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
+!plan
+
+# Now the same, but LEFT join
+select *
+from emp
+left join dept
+on emp.deptno = dept.deptno or emp.ename = dept.dname;
++-------+--------+--------+---------+-------------+
+| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
++-------+--------+--------+---------+-------------+
+| Adam | 50 | M | | |
+| Alice | 30 | F | 30 | Engineering |
+| Bob | 10 | M | 10 | Sales |
+| Eric | 20 | M | 20 | Marketing |
+| Eve | 50 | F | | |
+| Grace | 60 | F | | |
+| Jane | 10 | F | 10 | Sales |
+| Susan | 30 | F | 30 | Engineering |
+| Wilma | | F | | |
++-------+--------+--------+---------+-------------+
+(9 rows)
+
+!ok
+
+# Cannot be decomposed into an equi-join; plan uses EnumerableThetaJoin
+EnumerableThetaJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, $4))], joinType=[left])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
+!plan
+
+!use scott
+
+# Push aggregate through join
+select distinct dept.deptno, emp.deptno
+from "scott".emp join "scott".dept using (deptno);
++--------+--------+
+| DEPTNO | DEPTNO |
++--------+--------+
+| 10 | 10 |
+| 20 | 20 |
+| 30 | 30 |
++--------+--------+
+(3 rows)
+
+!ok
+EnumerableAggregate(group=[{0, 2}])
+ EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+select distinct dept.deptno
+from "scott".emp join "scott".dept using (deptno);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
+| 30 |
++--------+
+(3 rows)
+
+!ok
+EnumerableAggregate(group=[{0}])
+ EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# [CALCITE-676] AssertionError in GROUPING SETS query
+select emp.deptno as e, dept.deptno as d
+from "scott".emp join "scott".dept using (deptno)
+group by cube(emp.deptno, dept.deptno);
++----+----+
+| E | D |
++----+----+
+| 10 | 10 |
+| 10 | |
+| 20 | 20 |
+| 20 | |
+| 30 | 30 |
+| 30 | |
+| | 10 |
+| | 20 |
+| | 30 |
+| | |
++----+----+
+(10 rows)
+
+!ok
+
+# [CALCITE-688] splitCondition does not behave correctly
+# when one side of the condition references columns from
+# different inputs
+select distinct emp1.deptno, emp3.ename
+from "scott".emp emp1 join "scott".emp emp2 on (emp1.deptno = emp2.deptno)
+join "scott".emp emp3 on (emp1.deptno + emp2.deptno = emp3.deptno + 10);
++--------+--------+
+| DEPTNO | ENAME |
++--------+--------+
+| 10 | CLARK |
+| 10 | KING |
+| 10 | MILLER |
+| 20 | ALLEN |
+| 20 | BLAKE |
+| 20 | JAMES |
+| 20 | MARTIN |
+| 20 | TURNER |
+| 20 | WARD |
++--------+--------+
+(9 rows)
+
+!ok
+
+EnumerableCalc(expr#0..1=[{inputs}], DEPTNO0=[$t1], ENAME=[$t0])
+ EnumerableAggregate(group=[{1, 16}])
+ EnumerableJoin(condition=[=($8, $25)], joinType=[inner])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], proj#0..7=[{exprs}], $f8=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..15=[{inputs}], expr#16=[+($t7, $t15)], expr#17=[CAST($t16):INTEGER], proj#0..15=[{exprs}], $f16=[$t17])
+ EnumerableJoin(condition=[=($7, $15)], joinType=[inner])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+
+
+# [CALCITE-457] Non-ansi join should push condition with expression into join
+select e.deptno, d.deptno
+from "scott".dept D , "scott".emp E
+where e.deptno + 10 = d.deptno * 2;
++--------+--------+
+| DEPTNO | DEPTNO |
++--------+--------+
+| 10 | 10 |
+| 10 | 10 |
+| 10 | 10 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
+| 30 | 20 |
++--------+--------+
+(9 rows)
+
+!ok
+EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t3], DEPTNO0=[$t0])
+ EnumerableJoin(condition=[=($1, $4)], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[*($t0, $t3)], DEPTNO=[$t0], $f1=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], EMPNO=[$t0], DEPTNO=[$t7], $f2=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+### [CALCITE-801] NullPointerException using USING on table alias with column aliases
+select *
+from (values (100, 'Bill', 1),
+ (200, 'Eric', 1),
+ (150, 'Sebastian', 3)) as e(empid, name, deptno)
+join (values (1, 'LeaderShip'),
+ (2, 'TestGroup'),
+ (3, 'Development')) as d(deptno, name)
+using (deptno);
++-------+-----------+--------+---------+-------------+
+| EMPID | NAME | DEPTNO | DEPTNO0 | NAME0 |
++-------+-----------+--------+---------+-------------+
+| 100 | Bill | 1 | 1 | LeaderShip |
+| 150 | Sebastian | 3 | 3 | Development |
+| 200 | Eric | 1 | 1 | LeaderShip |
++-------+-----------+--------+---------+-------------+
+(3 rows)
+
+!ok
+
+### [CALCITE-1018] SortJoinTransposeRule not firing due to getMaxRowCount(RelSubset) returning null
+select * from (select * from "scott".emp) e left join (
+ select * from "scott".dept d) using (deptno)
+order by empno limit 10;
++-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO0 | DNAME | LOC |
++-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | 20 | RESEARCH | DALLAS |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | 30 | SALES | CHICAGO |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
++-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
+(10 rows)
+
+!ok
+EnumerableLimit(fetch=[10])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableJoin(condition=[=($7, $8)], joinType=[left])
+ EnumerableLimit(fetch=[10])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# End join.iq
http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/join.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/join.oq b/core/src/test/resources/sql/join.oq
deleted file mode 100644
index 9cd9e2c..0000000
--- a/core/src/test/resources/sql/join.oq
+++ /dev/null
@@ -1,288 +0,0 @@
-# join.oq - Join query tests
-#
-# Licensed to the Apache Software Foundation (ASF) under one or more
-# contributor license agreements. See the NOTICE file distributed with
-# this work for additional information regarding copyright ownership.
-# The ASF licenses this file to you under the Apache License, Version 2.0
-# (the "License"); you may not use this file except in compliance with
-# the License. You may obtain a copy of the License at
-#
-# http://www.apache.org/licenses/LICENSE-2.0
-#
-# Unless required by applicable law or agreed to in writing, software
-# distributed under the License is distributed on an "AS IS" BASIS,
-# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-# See the License for the specific language governing permissions and
-# limitations under the License.
-#
-!use post
-!set outputformat mysql
-
-# OR is a theta join
-select *
-from emp
-join dept
-on emp.deptno = dept.deptno or emp.ename = dept.dname;
-+-------+--------+--------+---------+-------------+
-| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
-+-------+--------+--------+---------+-------------+
-| Alice | 30 | F | 30 | Engineering |
-| Bob | 10 | M | 10 | Sales |
-| Eric | 20 | M | 20 | Marketing |
-| Jane | 10 | F | 10 | Sales |
-| Susan | 30 | F | 30 | Engineering |
-+-------+--------+--------+---------+-------------+
-(5 rows)
-
-!ok
-
-# As an INNER join, it can be executed as an equi-join followed by a filter
-EnumerableCalc(expr#0..4=[{inputs}], expr#5=[=($t1, $t3)], expr#6=[CAST($t0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], expr#7=[=($t6, $t4)], expr#8=[OR($t5, $t7)], proj#0..4=[{exprs}], $condition=[$t8])
- EnumerableJoin(condition=[true], joinType=[inner])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
-!plan
-
-# Now the same, but LEFT join
-select *
-from emp
-left join dept
-on emp.deptno = dept.deptno or emp.ename = dept.dname;
-+-------+--------+--------+---------+-------------+
-| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME |
-+-------+--------+--------+---------+-------------+
-| Adam | 50 | M | | |
-| Alice | 30 | F | 30 | Engineering |
-| Bob | 10 | M | 10 | Sales |
-| Eric | 20 | M | 20 | Marketing |
-| Eve | 50 | F | | |
-| Grace | 60 | F | | |
-| Jane | 10 | F | 10 | Sales |
-| Susan | 30 | F | 30 | Engineering |
-| Wilma | | F | | |
-+-------+--------+--------+---------+-------------+
-(9 rows)
-
-!ok
-
-# Cannot be decomposed into an equi-join; plan uses EnumerableThetaJoin
-EnumerableThetaJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, $4))], joinType=[left])
- EnumerableUnion(all=[true])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3])
- EnumerableValues(tuples=[[{ 0 }]])
- EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]])
-!plan
-
-!use scott
-
-# Push aggregate through join
-select distinct dept.deptno, emp.deptno
-from "scott".emp join "scott".dept using (deptno);
-+--------+--------+
-| DEPTNO | DEPTNO |
-+--------+--------+
-| 10 | 10 |
-| 20 | 20 |
-| 30 | 30 |
-+--------+--------+
-(3 rows)
-
-!ok
-EnumerableAggregate(group=[{0, 2}])
- EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-select distinct dept.deptno
-from "scott".emp join "scott".dept using (deptno);
-+--------+
-| DEPTNO |
-+--------+
-| 10 |
-| 20 |
-| 30 |
-+--------+
-(3 rows)
-
-!ok
-EnumerableAggregate(group=[{0}])
- EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-# [CALCITE-676] AssertionError in GROUPING SETS query
-select emp.deptno as e, dept.deptno as d
-from "scott".emp join "scott".dept using (deptno)
-group by cube(emp.deptno, dept.deptno);
-+----+----+
-| E | D |
-+----+----+
-| 10 | 10 |
-| 10 | |
-| 20 | 20 |
-| 20 | |
-| 30 | 30 |
-| 30 | |
-| | 10 |
-| | 20 |
-| | 30 |
-| | |
-+----+----+
-(10 rows)
-
-!ok
-
-# [CALCITE-688] splitCondition does not behave correctly
-# when one side of the condition references columns from
-# different inputs
-select distinct emp1.deptno, emp3.ename
-from "scott".emp emp1 join "scott".emp emp2 on (emp1.deptno = emp2.deptno)
-join "scott".emp emp3 on (emp1.deptno + emp2.deptno = emp3.deptno + 10);
-+--------+--------+
-| DEPTNO | ENAME |
-+--------+--------+
-| 10 | CLARK |
-| 10 | KING |
-| 10 | MILLER |
-| 20 | ALLEN |
-| 20 | BLAKE |
-| 20 | JAMES |
-| 20 | MARTIN |
-| 20 | TURNER |
-| 20 | WARD |
-+--------+--------+
-(9 rows)
-
-!ok
-
-EnumerableCalc(expr#0..1=[{inputs}], DEPTNO0=[$t1], ENAME=[$t0])
- EnumerableAggregate(group=[{1, 16}])
- EnumerableJoin(condition=[=($8, $25)], joinType=[inner])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], proj#0..7=[{exprs}], $f8=[$t9])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..15=[{inputs}], expr#16=[+($t7, $t15)], expr#17=[CAST($t16):INTEGER], proj#0..15=[{exprs}], $f16=[$t17])
- EnumerableJoin(condition=[=($7, $15)], joinType=[inner])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-
-
-# [CALCITE-457] Non-ansi join should push condition with expression into join
-select e.deptno, d.deptno
-from "scott".dept D , "scott".emp E
-where e.deptno + 10 = d.deptno * 2;
-+--------+--------+
-| DEPTNO | DEPTNO |
-+--------+--------+
-| 10 | 10 |
-| 10 | 10 |
-| 10 | 10 |
-| 30 | 20 |
-| 30 | 20 |
-| 30 | 20 |
-| 30 | 20 |
-| 30 | 20 |
-| 30 | 20 |
-+--------+--------+
-(9 rows)
-
-!ok
-EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t3], DEPTNO0=[$t0])
- EnumerableJoin(condition=[=($1, $4)], joinType=[inner])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[*($t0, $t3)], DEPTNO=[$t0], $f1=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], EMPNO=[$t0], DEPTNO=[$t7], $f2=[$t9])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-### [CALCITE-801] NullPointerException using USING on table alias with column aliases
-select *
-from (values (100, 'Bill', 1),
- (200, 'Eric', 1),
- (150, 'Sebastian', 3)) as e(empid, name, deptno)
-join (values (1, 'LeaderShip'),
- (2, 'TestGroup'),
- (3, 'Development')) as d(deptno, name)
-using (deptno);
-+-------+-----------+--------+---------+-------------+
-| EMPID | NAME | DEPTNO | DEPTNO0 | NAME0 |
-+-------+-----------+--------+---------+-------------+
-| 100 | Bill | 1 | 1 | LeaderShip |
-| 150 | Sebastian | 3 | 3 | Development |
-| 200 | Eric | 1 | 1 | LeaderShip |
-+-------+-----------+--------+---------+-------------+
-(3 rows)
-
-!ok
-
-### [CALCITE-1018] SortJoinTransposeRule not firing due to getMaxRowCount(RelSubset) returning null
-select * from (select * from "scott".emp) e left join (
- select * from "scott".dept d) using (deptno)
-order by empno limit 10;
-+-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
-| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO0 | DNAME | LOC |
-+-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
-| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | 20 | RESEARCH | DALLAS |
-| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
-| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
-| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS |
-| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
-| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | 30 | SALES | CHICAGO |
-| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK |
-| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS |
-| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK |
-| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
-+-------+--------+-----------+------+------------+---------+---------+--------+---------+------------+----------+
-(10 rows)
-
-!ok
-EnumerableLimit(fetch=[10])
- EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableJoin(condition=[=($7, $8)], joinType=[left])
- EnumerableLimit(fetch=[10])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableTableScan(table=[[scott, DEPT]])
-!plan
-
-# End join.oq
http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/misc.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
new file mode 100644
index 0000000..10d69fd
--- /dev/null
+++ b/core/src/test/resources/sql/misc.iq
@@ -0,0 +1,1111 @@
+# misc.iq - Miscellaneous queries
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use post
+!set outputformat mysql
+
+# [CALCITE-356] Allow column references of the form schema.table.column
+select "hr"."emps"."empid"
+from "hr"."emps";
++-------+
+| empid |
++-------+
+| 100 |
+| 110 |
+| 150 |
+| 200 |
++-------+
+(4 rows)
+
+!ok
+
+# [CALCITE-881] Allow schema.table.column references in GROUP BY
+select "hr"."emps"."empid", count(*) as c
+from "hr"."emps"
+group by "hr"."emps"."empid";
++-------+---+
+| empid | C |
++-------+---+
+| 100 | 1 |
+| 110 | 1 |
+| 150 | 1 |
+| 200 | 1 |
++-------+---+
+(4 rows)
+
+!ok
+
+select distinct "hr"."emps"."empid" + 1 as e
+from "hr"."emps"
+group by "hr"."emps"."empid";
++-----+
+| E |
++-----+
+| 101 |
+| 111 |
+| 151 |
+| 201 |
++-----+
+(4 rows)
+
+!ok
+
+# [CALCITE-307] CAST(timestamp AS DATE) gives ClassCastException
+# Based on [DRILL-1051]
+with data(c_row, c_timestamp) as (select * from (values
+ (1, TIMESTAMP '1997-01-02 03:04:05'),
+ (2, TIMESTAMP '1997-01-02 00:00:00'),
+ (3, TIMESTAMP '2001-09-22 18:19:20'),
+ (4, TIMESTAMP '1997-02-10 17:32:01'),
+ (5, TIMESTAMP '1997-02-10 17:32:00'),
+ (6, TIMESTAMP '1997-02-11 17:32:01'),
+ (7, TIMESTAMP '1997-02-12 17:32:01'),
+ (8, TIMESTAMP '1997-02-13 17:32:01'),
+ (9, TIMESTAMP '1997-02-14 17:32:01'),
+ (10, TIMESTAMP '1997-02-15 17:32:01'),
+ (11, TIMESTAMP '1997-02-16 17:32:01'),
+ (13, TIMESTAMP '0097-02-16 17:32:01'),
+ (14, TIMESTAMP '0597-02-16 17:32:01'),
+ (15, TIMESTAMP '1097-02-16 17:32:01'),
+ (16, TIMESTAMP '1697-02-16 17:32:01'),
+ (17, TIMESTAMP '1797-02-16 17:32:01'),
+ (18, TIMESTAMP '1897-02-16 17:32:01'),
+ (19, TIMESTAMP '1997-02-16 17:32:01'),
+ (20, TIMESTAMP '2097-02-16 17:32:01'),
+ (21, TIMESTAMP '1996-02-28 17:32:01'),
+ (22, TIMESTAMP '1996-02-29 17:32:01'),
+ (23, TIMESTAMP '1996-03-01 17:32:01')))
+select cast(c_timestamp as varchar(20)), cast(c_timestamp as date) from data where c_row <> 12;
+
++---------------------+------------+
+| EXPR$0 | EXPR$1 |
++---------------------+------------+
+| 1997-01-02 03:04:05 | 1997-01-02 |
+| 1997-01-02 00:00:00 | 1997-01-02 |
+| 2001-09-22 18:19:20 | 2001-09-22 |
+| 1997-02-10 17:32:01 | 1997-02-10 |
+| 1997-02-10 17:32:00 | 1997-02-10 |
+| 1997-02-11 17:32:01 | 1997-02-11 |
+| 1997-02-12 17:32:01 | 1997-02-12 |
+| 1997-02-13 17:32:01 | 1997-02-13 |
+| 1997-02-14 17:32:01 | 1997-02-14 |
+| 1997-02-15 17:32:01 | 1997-02-15 |
+| 1997-02-16 17:32:01 | 1997-02-16 |
+| 0097-02-14 17:32:01 | 0097-02-14 |
+| 0597-02-18 17:32:01 | 0597-02-18 |
+| 1097-02-22 17:32:01 | 1097-02-22 |
+| 1697-02-16 17:32:01 | 1697-02-16 |
+| 1797-02-16 17:32:01 | 1797-02-16 |
+| 1897-02-16 17:32:01 | 1897-02-16 |
+| 1997-02-16 17:32:01 | 1997-02-16 |
+| 2097-02-16 17:32:01 | 2097-02-16 |
+| 1996-02-28 17:32:01 | 1996-02-28 |
+| 1996-02-29 17:32:01 | 1996-02-29 |
+| 1996-03-01 17:32:01 | 1996-03-01 |
++---------------------+------------+
+(22 rows)
+
+!ok
+
+# [DRILL-1149]
+select *, upper("name")
+from "hr"."emps";
++-------+--------+-----------+---------+------------+-----------+
+| empid | deptno | name | salary | commission | EXPR$5 |
++-------+--------+-----------+---------+------------+-----------+
+| 100 | 10 | Bill | 10000.0 | 1000 | BILL |
+| 110 | 10 | Theodore | 11500.0 | 250 | THEODORE |
+| 150 | 10 | Sebastian | 7000.0 | | SEBASTIAN |
+| 200 | 20 | Eric | 8000.0 | 500 | ERIC |
++-------+--------+-----------+---------+------------+-----------+
+(4 rows)
+
+!ok
+
+# [DRILL-1199] Order by nested inside a where clause fails
+# (Not that it's right, but Tableau does it.)
+select * from (select * from "hr"."emps" order by "empid") where (0=1);
++-------+--------+------+--------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+------+--------+------------+
++-------+--------+------+--------+------------+
+(0 rows)
+
+!ok
+
+# [DRILL-1842] Tableau, again
+select count(distinct "salary") as c
+from "hr"."emps"
+join "hr"."depts" on "emps"."deptno" = "depts"."deptno"
+having count(1) > 0;
++---+
+| C |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+# [CALCITE-340] SqlToRelConverter fails with complex join condition
+select e."deptno", d."deptno"
+from "hr"."emps" as e
+join "hr"."depts" as d
+on ( e."deptno" + 1 - 1 = d."deptno" + 2 - 2 and e."deptno" + 10 - 10 = d."deptno" + 20 - 20);
++--------+--------+
+| deptno | deptno |
++--------+--------+
+| 10 | 10 |
+| 10 | 10 |
+| 10 | 10 |
++--------+--------+
+(3 rows)
+
+!ok
+
+# [CALCITE-340] SqlToRelConverter fails with complex join condition. Switch LHS and RHS.
+select e."deptno", d."deptno"
+from "hr"."emps" as e
+join "hr"."depts" as d
+on ( d."deptno" + 2 - 2 = e."deptno" + 1 - 1 and d."deptno" + 20 - 20 = e."deptno" + 10 - 10);
++--------+--------+
+| deptno | deptno |
++--------+--------+
+| 10 | 10 |
+| 10 | 10 |
+| 10 | 10 |
++--------+--------+
+(3 rows)
+
+!ok
+
+# [CALCITE-340] SqlToRelConverter fails with complex join condition. Switch LHS and RHS.
+select e."deptno", d."deptno"
+from "hr"."emps" as e
+join "hr"."depts" as d
+on ( d."deptno" + 2 - 2 = e."deptno" + 1 - 1 and e."deptno" + 10 - 10 = d."deptno" + 20 - 20);
++--------+--------+
+| deptno | deptno |
++--------+--------+
+| 10 | 10 |
+| 10 | 10 |
+| 10 | 10 |
++--------+--------+
+(3 rows)
+
+!ok
+
+# [CALCITE-377] Mixed equi and non-equi join
+select e."empid", d."name", e."name"
+from "hr"."emps" as e
+join "hr"."depts" as d
+on e."deptno" = d."deptno"
+and e."name" <> d."name";
++-------+-------+-----------+
+| empid | name | name |
++-------+-------+-----------+
+| 100 | Sales | Bill |
+| 110 | Sales | Theodore |
+| 150 | Sales | Sebastian |
++-------+-------+-----------+
+(3 rows)
+
+!ok
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7])
+ EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
+ EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# Same query, expressed using WHERE.
+select e."empid", d."name", e."name"
+from "hr"."emps" as e,
+ "hr"."depts" as d
+where e."deptno" = d."deptno"
+and e."name" <> d."name";
++-------+-------+-----------+
+| empid | name | name |
++-------+-------+-----------+
+| 100 | Sales | Bill |
+| 110 | Sales | Theodore |
+| 150 | Sales | Sebastian |
++-------+-------+-----------+
+(3 rows)
+
+!ok
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7])
+ EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
+ EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# EXISTS
+select * from "hr"."emps"
+where exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
++-------+--------+-----------+---------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+---------+------------+
+| 100 | 10 | Bill | 10000.0 | 1000 |
+| 110 | 10 | Theodore | 11500.0 | 250 |
+| 150 | 10 | Sebastian | 7000.0 | |
++-------+--------+-----------+---------+------------+
+(3 rows)
+
+!ok
+EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], deptno0=[$t0], $f0=[$t5])
+ EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# NOT EXISTS
+# Right results, but it would be better if the plan used EnumerableSemiJoinRel; see [CALCITE-374]
+select * from "hr"."emps"
+where not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
++-------+--------+------+--------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+------+--------+------------+
+| 200 | 20 | Eric | 8000.0 | 500 |
++-------+--------+------+--------+------------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t6)], expr#8=[NOT($t7)], proj#0..4=[{exprs}], $condition=[$t8])
+ EnumerableJoin(condition=[=($1, $5)], joinType=[left])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+ EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# NOT EXISTS .. OR NOT EXISTS
+# Right results, but it would be better if the plan used EnumerableSemiJoinRel; see [CALCITE-374]
+select * from "hr"."emps"
+where not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno")
+or not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" + 90 = "emps"."empid");
+
++-------+--------+-----------+---------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+---------+------------+
+| 110 | 10 | Theodore | 11500.0 | 250 |
+| 150 | 10 | Sebastian | 7000.0 | |
+| 200 | 20 | Eric | 8000.0 | 500 |
++-------+--------+-----------+---------+------------+
+(3 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], expr#9=[NOT($t8)], expr#10=[IS NOT NULL($t7)], expr#11=[NOT($t10)], expr#12=[OR($t9, $t11)], proj#0..4=[{exprs}], $condition=[$t12])
+ EnumerableJoin(condition=[=($0, $6)], joinType=[left])
+ EnumerableCalc(expr#0..6=[{inputs}], proj#0..4=[{exprs}], $f0=[$t6])
+ EnumerableJoin(condition=[=($1, $5)], joinType=[left])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+ EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableTableScan(table=[[hr, depts]])
+ EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[true], $f0=[$t7], empid=[$t0])
+ EnumerableJoin(condition=[=($1, $6)], joinType=[inner])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}])
+ EnumerableAggregate(group=[{0}])
+ EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+ EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[hr, emps]])
+ EnumerableTableScan(table=[[hr, depts]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], proj#0..3=[{exprs}], $f4=[$t5])
+ EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# Filter combined with an OR filter.
+select * from (
+ select * from "hr"."emps" as e
+ where e."deptno" < 30) as e
+where e."deptno" > 10 or e."name" = 'Sebastian';
++-------+--------+-----------+--------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+--------+------------+
+| 150 | 10 | Sebastian | 7000.0 | |
+| 200 | 20 | Eric | 8000.0 | 500 |
++-------+--------+-----------+--------+------------+
+(2 rows)
+
+!ok
+
+# Filter combined with an AND filter. Test case for
+# [CALCITE-389] MergeFilterRule should flatten AND condition
+select * from (
+ select * from "hr"."emps" as e
+ where e."deptno" < 30) as e
+where e."deptno" >= 10 and e."name" = 'Sebastian';
++-------+--------+-----------+--------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+--------+------------+
+| 150 | 10 | Sebastian | 7000.0 | |
++-------+--------+-----------+--------+------------+
+(1 row)
+
+!ok
+
+# [CALCITE-393] If no fields are projected from a table, field trimmer should
+# project a dummy expression
+select 1 from "hr"."emps";
++--------+
+| EXPR$0 |
++--------+
+| 1 |
+| 1 |
+| 1 |
+| 1 |
++--------+
+(4 rows)
+
+!ok
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1], EXPR$0=[$t5])
+ EnumerableTableScan(table=[[hr, emps]])
+!plan
+
+# [CALCITE-393] for table scan under join
+select count(*) as c from "hr"."emps", "hr"."depts";
++----+
+| C |
++----+
+| 12 |
++----+
+(1 row)
+
+!ok
+EnumerableAggregate(group=[{}], C=[COUNT()])
+ EnumerableJoin(condition=[true], joinType=[inner])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], DUMMY=[$t4])
+ EnumerableTableScan(table=[[hr, depts]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5])
+ EnumerableTableScan(table=[[hr, emps]])
+!plan
+
+# [CALCITE-345] AssertionError in RexToLixTranslator comparing to date literal
+!use catchall
+select count(*) as c from "everyTypes" where "sqlDate" = DATE '1970-01-01';
++---+
+| C |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+select count(*) as c from "everyTypes" where "sqlDate" = DATE '1971-02-03';
++---+
+| C |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+select count(*) as c from "everyTypes" where "sqlDate" > DATE '1970-01-01';
++---+
+| C |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+select count(*) as c from "everyTypes" where "sqlTime" = TIME '01:23:45';
++---+
+| C |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+select count(*) as c from "everyTypes" where "sqlTimestamp" = TIMESTAMP '1970-01-01 01:23:45';
++---+
+| C |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+select count(*) as c from "everyTypes" where "utilDate" = TIMESTAMP '1970-01-01 01:23:45';
++---+
+| C |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+
+# [CALCITE-346] Add commutative join rule
+#
+# 3-way join that does not require bushy join. Best plan is: sales_fact_1997 as
+# left-most leaf, then customer (with filter), then product.
+!use foodmart
+select *
+from "sales_fact_1997" as s
+ join "customer" as c using ("customer_id")
+ join "product" as p using ("product_id")
+where c."city" = 'San Francisco';
+EnumerableJoin(condition=[=($0, $38)], joinType=[inner])
+ EnumerableJoin(condition=[=($2, $8)], joinType=[inner])
+ EnumerableTableScan(table=[[foodmart2, sales_fact_1997]])
+ EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco'], expr#30=[=($t9, $t29)], proj#0..28=[{exprs}], $condition=[$t30])
+ EnumerableTableScan(table=[[foodmart2, customer]])
+ EnumerableTableScan(table=[[foodmart2, product]])
+!plan
+
+# 4-way join whose optimal plan requires bushy join.
+#
+# In the plan, note that filters on customer.city and product_department are
+# pushed down. And the plan is a bushy join, with sub-joins (product_class,
+# product) and (sales_fact_1997, customer). However, scan(sales_fact_1997)
+# should be left-most leaf, but is not because CommutativeJoinRule is currently
+# disabled.
+!use foodmart
+select *
+from "sales_fact_1997" as s
+ join "customer" as c using ("customer_id")
+ join "product" as p using ("product_id")
+ join "product_class" as pc using ("product_class_id")
+where c."city" = 'San Francisco'
+ and pc."product_department" = 'Snacks';
+EnumerableCalc(expr#0..56=[{inputs}], product_id0=[$t20], time_id=[$t21], customer_id=[$t22], promotion_id=[$t23], store_id=[$t24], store_sales=[$t25], store_cost=[$t26], unit_sales=[$t27], customer_id0=[$t28], account_num=[$t29], lname=[$t30], fname=[$t31], mi=[$t32], address1=[$t33], address2=[$t34], address3=[$t35], address4=[$t36], city=[$t37], state_province=[$t38], postal_code=[$t39], country=[$t40], customer_region_id=[$t41], phone1=[$t42], phone2=[$t43], birthdate=[$t44], marital_status=[$t45], yearly_income=[$t46], gender=[$t47], total_children=[$t48], num_children_at_home=[$t49], education=[$t50], date_accnt_opened=[$t51], member_card=[$t52], occupation=[$t53], houseowner=[$t54], num_cars_owned=[$t55], fullname=[$t56], product_class_id0=[$t5], product_id=[$t6], brand_name=[$t7], product_name=[$t8], SKU=[$t9], SRP=[$t10], gross_weight=[$t11], net_weight=[$t12], recyclable_package=[$t13], low_fat=[$t14], units_per_case=[$t15], cases_per_pallet=[$t16], shelf_width=[$t17], she
lf_height=[$t18], shelf_depth=[$t19], product_class_id=[$t0], product_subcategory=[$t1], product_category=[$t2], product_department=[$t3], product_family=[$t4])
+ EnumerableJoin(condition=[=($6, $20)], joinType=[inner])
+ EnumerableJoin(condition=[=($0, $5)], joinType=[inner])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=['Snacks'], expr#6=[=($t3, $t5)], proj#0..4=[{exprs}], $condition=[$t6])
+ EnumerableTableScan(table=[[foodmart2, product_class]])
+ EnumerableTableScan(table=[[foodmart2, product]])
+ EnumerableJoin(condition=[=($2, $8)], joinType=[inner])
+ EnumerableTableScan(table=[[foodmart2, sales_fact_1997]])
+ EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco'], expr#30=[=($t9, $t29)], proj#0..28=[{exprs}], $condition=[$t30])
+ EnumerableTableScan(table=[[foodmart2, customer]])
+!plan
+
+# Check that when filters are merged, duplicate conditions are eliminated.
+select * from (
+ select * from "days"
+ where "day" = 1)
+where "day" = 1;
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[=($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])
+ EnumerableTableScan(table=[[foodmart2, days]])
+!plan
+
+# [HIVE-5873] Semi-join to count subquery
+# [CALCITE-365] AssertionError while translating query with WITH and correlated sub-query
+!if (false) {
+with parts (PNum, OrderOnHand)
+ as (select * from (values (3, 6), (10, 1), (8, 0)) as t(PNum, OrderOnHand)),
+ supply (PNum, Qty)
+ as (select * from (values (3, 4), (3, 2), (10, 1)))
+select pnum
+from parts p
+where orderOnHand
+ in (select count(*) from supply s
+ where s.pnum = p.pnum);
++------+
+| PNUM |
++------+
+| 8 |
++------+
+(1 row)
+
+!ok
+!}
+
+# [HIVE-7362]
+# Just checking that HAVING-EXISTS works.
+with src (key, "value")
+ as (select * from (values (1, 'a'), (2, 'z')) as t(key, "value"))
+select b.key, count(*) as c
+from src b
+group by b.key
+having exists
+ (select a.key
+ from src a
+ where a.key = b.key and a."value" > 'val_9');
++-----+---+
+| KEY | C |
++-----+---+
+| 2 | 1 |
++-----+---+
+(1 row)
+
+!ok
+
+# [CALCITE-411] Duplicate aliases
+select 1 as a, 2 as a from (values (true));
++---+---+
+| A | A |
++---+---+
+| 1 | 2 |
++---+---+
+(1 row)
+
+!ok
+
+select "day", "day" from "days" where "day" < 3;
++-----+-----+
+| day | day |
++-----+-----+
+| 1 | 1 |
+| 2 | 2 |
++-----+-----+
+(2 rows)
+
+!ok
+
+# [DERBY-5313] CASE expression in GROUP BY clause
+select case when a=1 then 1 else 2 end
+from "days" t1(a,x) join "days" t2(b,x) on a=b
+group by case when a=1 then 1 else 2 end;
++--------+
+| EXPR$0 |
++--------+
+| 1 |
+| 2 |
++--------+
+(2 rows)
+
+!ok
+
+# [DERBY-4450] GROUP BY in an IN-subquery inside HAVING clause whose select list
+# is subset of group by columns.
+select sum("day") from "days" group by "week_day" having "week_day" in (
+ select "week_day" from "days" group by "week_day", "day");
++--------+
+| EXPR$0 |
++--------+
+| 1 |
+| 2 |
+| 3 |
+| 4 |
+| 5 |
+| 6 |
+| 7 |
++--------+
+(7 rows)
+
+!ok
+
+# [DERBY-4701] Aggregate function on a GROUP BY column also present in a HAVING
+# clause
+SELECT MAX("day") as m, COUNT(T."week_day") AS c
+FROM "days" T
+GROUP BY T."week_day"
+HAVING COUNT(T."week_day") = 1;
++---+---+
+| M | C |
++---+---+
+| 1 | 1 |
+| 2 | 1 |
+| 3 | 1 |
+| 4 | 1 |
+| 5 | 1 |
+| 6 | 1 |
+| 7 | 1 |
++---+---+
+(7 rows)
+
+!ok
+
+# [DERBY-3616] Combinations of DISTINCT and GROUP BY
+!use post
+select distinct gender from emp group by gender;
++--------+
+| GENDER |
++--------+
+| F |
+| M |
++--------+
+(2 rows)
+
+!ok
+select distinct gender from emp group by gender, deptno;
++--------+
+| GENDER |
++--------+
+| F |
+| M |
++--------+
+(2 rows)
+
+!ok
+select gender, deptno from emp;
++--------+--------+
+| GENDER | DEPTNO |
++--------+--------+
+| F | 10 |
+| F | 30 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+--------+
+(9 rows)
+
+!ok
+select distinct gender, deptno from emp group by gender, deptno, ename;
++--------+--------+
+| GENDER | DEPTNO |
++--------+--------+
+| F | 10 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+--------+
+(8 rows)
+
+!ok
+select distinct gender, deptno from emp group by gender, deptno;
++--------+--------+
+| GENDER | DEPTNO |
++--------+--------+
+| F | 10 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+--------+
+(8 rows)
+
+!ok
+select distinct gender, deptno from emp group by gender, ename, deptno;
++--------+--------+
+| GENDER | DEPTNO |
++--------+--------+
+| F | 10 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+--------+
+(8 rows)
+
+!ok
+select distinct gender, sum(deptno) as s from emp group by gender, deptno;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(7 rows)
+
+!ok
+select gender, sum(deptno) as s from emp group by gender, deptno;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 50 |
+| F | 60 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(8 rows)
+
+!ok
+select gender, sum(deptno) as s from emp group by gender, ename;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 30 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(9 rows)
+
+!ok
+select distinct gender, sum(deptno) as s from emp group by gender, ename;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(8 rows)
+
+!ok
+select gender, sum(deptno) as s from emp group by gender, deptno, ename;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 30 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(9 rows)
+
+!ok
+select distinct gender, sum(deptno) as s from emp group by gender, deptno, ename;
++--------+----+
+| GENDER | S |
++--------+----+
+| F | 10 |
+| F | 30 |
+| F | 50 |
+| F | 60 |
+| F | |
+| M | 10 |
+| M | 20 |
+| M | 50 |
++--------+----+
+(8 rows)
+
+!ok
+select distinct gender, sum(deptno) as s from emp group by gender;
++--------+-----+
+| GENDER | S |
++--------+-----+
+| F | 180 |
+| M | 80 |
++--------+-----+
+(2 rows)
+
+!ok
+
+select distinct gender, deptno from emp group by gender;
+Expression 'DEPTNO' is not being grouped
+!error
+
+select distinct gender, deptno from emp group by gender, ename;
+Expression 'DEPTNO' is not being grouped
+!error
+
+select distinct gender, deptno, sum(deptno) as s from emp group by gender;
+Expression 'DEPTNO' is not being grouped
+!error
+
+!use scott
+
+# [CALCITE-613] Implicitly convert strings in comparisons
+select * from "scott".emp where hiredate < '1981-01-02';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=['1981-01-02'], expr#9=[CAST($t8):DATE NOT NULL], expr#10=[<($t4, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+select * from "scott".emp where '1981-01-02' > hiredate;
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+select * from "scott".emp where hiredate between '1981-01-02' and '1981-06-01';
++-------+-------+----------+------+------------+---------+--------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+----------+------+------------+---------+--------+--------+
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
++-------+-------+----------+------+------------+---------+--------+--------+
+(4 rows)
+
+!ok
+select * from "scott".emp where hiredate > '1986-01-02';
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where '1986-01-02' < hiredate;
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where '1986-' || '01-02' < hiredate;
++-------+-------+---------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+---------+------+------------+---------+------+--------+
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+---------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where sal < '1100';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
++-------+-------+-------+------+------------+--------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where empno in ('7369', '7876');
++-------+-------+-------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+---------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
++-------+-------+-------+------+------------+---------+------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where empno between '7500' and '07600';
++-------+-------+----------+------+------------+---------+--------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+----------+------+------------+---------+--------+--------+
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
++-------+-------+----------+------+------------+---------+--------+--------+
+(2 rows)
+
+!ok
+select * from "scott".emp where deptno between '7369' and '7876';
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+select * from "scott".emp where '7369' between empno and '7876';
++-------+-------+-------+------+------------+--------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-------+------+------------+--------+------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
++-------+-------+-------+------+------------+--------+------+--------+
+(1 row)
+
+!ok
+
+# [CALCITE-546] Allow table, column and field called "*"
+# See [DRILL-3859], [DRILL-3860].
+SELECT * FROM (VALUES (0, 0)) AS T(A, "*");
++---+---+
+| A | * |
++---+---+
+| 0 | 0 |
++---+---+
+(1 row)
+
+!ok
+
+SELECT a FROM (VALUES (0, 0)) AS T(A, "*");
++---+
+| A |
++---+
+| 0 |
++---+
+(1 row)
+
+!ok
+
+SELECT b FROM (VALUES (0, 0)) AS T(A, "*");
+Column 'B' not found in any table
+!error
+
+# See [DRILL-3860].
+SELECT "a" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
++---+
+| a |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+
+SELECT "A" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
++---+
+| A |
++---+
+| 2 |
++---+
+(1 row)
+
+!ok
+
+SELECT "." FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
++---+
+| . |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+SELECT "*" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
++---+
+| * |
++---+
+| 4 |
++---+
+(1 row)
+
+!ok
+
+SELECT * FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
++---+---+---+---+
+| a | A | . | * |
++---+---+---+---+
+| 1 | 2 | 3 | 4 |
++---+---+---+---+
+(1 row)
+
+!ok
+
+# Implicit ROW
+select deptno, (empno, deptno) as r
+from "scott".emp;
++--------+------------+
+| DEPTNO | R |
++--------+------------+
+| 10 | {7782, 10} |
+| 10 | {7839, 10} |
+| 10 | {7934, 10} |
+| 20 | {7369, 20} |
+| 20 | {7566, 20} |
+| 20 | {7788, 20} |
+| 20 | {7876, 20} |
+| 20 | {7902, 20} |
+| 30 | {7499, 30} |
+| 30 | {7521, 30} |
+| 30 | {7654, 30} |
+| 30 | {7698, 30} |
+| 30 | {7844, 30} |
+| 30 | {7900, 30} |
++--------+------------+
+(14 rows)
+
+!ok
+
+# Explicit ROW
+select deptno, row (empno, deptno) as r
+from "scott".emp;
+ROW expression encountered in illegal context
+!error
+
+# [CALCITE-877] Allow ROW as argument to COLLECT
+select deptno, collect(r) as empnos
+from (select deptno, (empno, deptno) as r
+ from "scott".emp)
+group by deptno;
++--------+--------------------------------------------------------------------------+
+| DEPTNO | EMPNOS |
++--------+--------------------------------------------------------------------------+
+| 10 | [{7782, 10}, {7839, 10}, {7934, 10}] |
+| 20 | [{7369, 20}, {7566, 20}, {7788, 20}, {7876, 20}, {7902, 20}] |
+| 30 | [{7499, 30}, {7521, 30}, {7654, 30}, {7698, 30}, {7844, 30}, {7900, 30}] |
++--------+--------------------------------------------------------------------------+
+(3 rows)
+
+!ok
+
+# [CALCITE-922] Value of INTERVAL literal
+select deptno * interval '2' day as d2,
+ deptno * interval -'3' hour as h3,
+ deptno * interval -'-4' hour as h4,
+ deptno * interval -'4:30' hour to minute as h4_5,
+ deptno * interval -'-1-3' year to month as y1_25
+from "scott".dept;
++-----+------+------+---------+--------+
+| D2 | H3 | H4 | H4_5 | Y1_25 |
++-----+------+------+---------+--------+
+| +20 | -30 | +40 | -45:00 | +12-06 |
+| +40 | -60 | +80 | -90:00 | +25-00 |
+| +60 | -90 | +120 | -135:00 | +37-06 |
+| +80 | -120 | +160 | -180:00 | +50-00 |
++-----+------+------+---------+--------+
+(4 rows)
+
+!ok
+
+# End misc.iq