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
