Repository: calcite Updated Branches: refs/heads/master 84b55ef58 -> 5197a7147
http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/sort.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sort.oq b/core/src/test/resources/sql/sort.oq deleted file mode 100644 index be30ac4..0000000 --- a/core/src/test/resources/sql/sort.oq +++ /dev/null @@ -1,179 +0,0 @@ -# sort.oq - Sorting and collation -# -# 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 foodmart -!set outputformat mysql - -# The ArrayTable "days" is sorted by "day", so plan must not contain sort -select * from "days" order by "day"; -!verify -EnumerableTableScan(table=[[foodmart2, days]]) -!plan - -# The ArrayTable "days" is sorted by "day", so the plan does not sort, only applies limit -select * from "days" order by "day" limit 2; -+-----+----------+ -| day | week_day | -+-----+----------+ -| 1 | Sunday | -| 2 | Monday | -+-----+----------+ -(2 rows) - -!ok -EnumerableLimit(fetch=[2]) - EnumerableTableScan(table=[[foodmart2, days]]) -!plan - -# The ArrayTable "days" is sorted by "day", so the plan must not contain Sort -select * from "days" where "day" between 2 and 4 order by "day"; -+-----+-----------+ -| day | week_day | -+-----+-----------+ -| 2 | Monday | -| 3 | Tuesday | -| 4 | Wednesday | -+-----+-----------+ -(3 rows) - -!ok -EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[>=($t0, $t2)], expr#4=[4], expr#5=[<=($t0, $t4)], expr#6=[AND($t3, $t5)], proj#0..1=[{exprs}], $condition=[$t6]) - EnumerableTableScan(table=[[foodmart2, days]]) -!plan - -# [CALCITE-970] Default collation of NULL values -# Nulls high, i.e. first if DESC -select "store_id", "grocery_sqft" from "store" -where "store_id" < 3 -order by 2 DESC; -+----------+--------------+ -| store_id | grocery_sqft | -+----------+--------------+ -| 0 | | -| 2 | 22271 | -| 1 | 17475 | -+----------+--------------+ -(3 rows) - -!ok - -# Nulls high, i.e. first if DESC, composite sort -select "store_id", "grocery_sqft" from "store" -where "store_id" < 3 -order by "florist", 2 DESC; -+----------+--------------+ -| store_id | grocery_sqft | -+----------+--------------+ -| 0 | | -| 2 | 22271 | -| 1 | 17475 | -+----------+--------------+ -(3 rows) - -!ok - -# Nulls high, i.e. last if ASC -select "store_id", "grocery_sqft" from "store" -where "store_id" < 3 -order by 2; -+----------+--------------+ -| store_id | grocery_sqft | -+----------+--------------+ -| 1 | 17475 | -| 2 | 22271 | -| 0 | | -+----------+--------------+ -(3 rows) - -!ok - -# [CALCITE-969] Composite EnumerableSort with DESC wrongly sorts NULL values low -# Nulls high, i.e. last if ASC, composite sort -select "store_id", "grocery_sqft" from "store" -where "store_id" < 3 -order by "florist", 2; -+----------+--------------+ -| store_id | grocery_sqft | -+----------+--------------+ -| 1 | 17475 | -| 2 | 22271 | -| 0 | | -+----------+--------------+ -(3 rows) - -!ok - -!use post - -# [CALCITE-603] WITH ... ORDER BY cannot find table -with e as (select "empid" as empid from "hr"."emps" where "empid" < 120) -select * from e as e1, e as e2 order by e1.empid + e2.empid, e1.empid; -+-------+--------+ -| EMPID | EMPID0 | -+-------+--------+ -| 100 | 100 | -| 100 | 110 | -| 110 | 100 | -| 110 | 110 | -+-------+--------+ -(4 rows) - -!ok - -# WITH ... LIMIT -with e as (select "empid" as empid from "hr"."emps" where "empid" < 200) -select * from e where empid > 100 limit 5; -+-------+ -| EMPID | -+-------+ -| 150 | -| 110 | -+-------+ -(2 rows) - -!ok - -# [CALCITE-634] Allow ORDER BY aggregate function in SELECT DISTINCT, provided -# that it occurs in SELECT clause -select distinct "deptno", count(*) as c -from "hr"."emps" -group by "deptno" -order by count(*) desc; -+--------+---+ -| deptno | C | -+--------+---+ -| 10 | 3 | -| 20 | 1 | -+--------+---+ -(2 rows) - -!ok - -select distinct count("empid") as c -from "hr"."emps" -group by "empid" -order by 1; -+---+ -| C | -+---+ -| 1 | -+---+ -(1 row) - -!ok - -# End sort.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/subquery.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/subquery.iq b/core/src/test/resources/sql/subquery.iq new file mode 100644 index 0000000..b69b669 --- /dev/null +++ b/core/src/test/resources/sql/subquery.iq @@ -0,0 +1,278 @@ +# subquery.iq - Queries involving IN and EXISTS sub-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 psql + +# [CALCITE-373] +# the following should return no rows, because the IN list has a null. +# for details on this: see HIVE-784, Dayal's paper from VLDB-87 +with +t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)), +t2(x) as (select * from (values 1,case when 1 = 1 then null else 3 end)) +select * +from t1 +where t1.x not in (select t2.x from t2); + X +--- +(0 rows) + +!ok +EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t1, $t6)], expr#8=[false], expr#9=[IS NOT NULL($t5)], expr#10=[true], expr#11=[IS NULL($t3)], expr#12=[null], expr#13=[<($t2, $t1)], expr#14=[CASE($t7, $t8, $t9, $t10, $t11, $t12, $t13, $t12, $t8)], expr#15=[NOT($t14)], X=[$t0], $condition=[$t15]) + EnumerableJoin(condition=[=($3, $4)], joinType=[left]) + EnumerableCalc(expr#0..2=[{inputs}], $f0=[$t2], $f1=[$t0], $f2=[$t1], $f3=[$t2]) + EnumerableJoin(condition=[true], joinType=[inner]) + EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) + EnumerableUnion(all=[true]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableUnion(all=[true]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableAggregate(group=[{0}], agg#0=[MIN($1)]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}]) + EnumerableUnion(all=[true]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) + EnumerableValues(tuples=[[{ 0 }]]) +!plan + +# Use of case is to get around issue with directly specifying null in values +# list. Postgres gives 0 rows. +with +t1(x) as (select * from (values (1),(2),(case when 1 = 1 then null else 3 end)) as t1), +t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t2) +select * +from t1 +where t1.x not in (select t2.x from t2); + + X +--- +(0 rows) + +!ok + +# RHS has a mixture of NULL and NOT NULL keys +select * from dept where deptno not in (select deptno from emp); + DEPTNO | DNAME +--------+------- +(0 rows) + +!ok +select deptno, deptno in (select deptno from emp) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | true + 20 | true + 30 | true + 40 | null +(4 rows) + +!ok +select deptno, deptno not in (select deptno from emp) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | false + 20 | false + 30 | false + 40 | null +(4 rows) + +!ok + +# RHS has only NULL keys +select * from dept where deptno not in (select deptno from emp where deptno is null); + DEPTNO | DNAME +--------+------- +(0 rows) + +!ok +select deptno, deptno in (select deptno from emp where deptno is null) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | null + 20 | null + 30 | null + 40 | null +(4 rows) + +!ok +select deptno, deptno not in (select deptno from emp where deptno is null) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | null + 20 | null + 30 | null + 40 | null +(4 rows) + +!ok + +# RHS has only NOT NULL keys +select * from dept where deptno not in (select deptno from emp where deptno is not null); + DEPTNO | DNAME +--------+------------- + 40 | Empty +(1 row) + +!ok +select deptno, deptno in (select deptno from emp where deptno is not null) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | true + 20 | true + 30 | true + 40 | false +(4 rows) + +!ok +select deptno, deptno not in (select deptno from emp where deptno is not null) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | false + 20 | false + 30 | false + 40 | true +(4 rows) + +!ok + +# RHS has no rows +# Even 'NULL NOT IN ...' is TRUE. +select * from dept where deptno not in (select deptno from emp where false); + DEPTNO | DNAME +--------+------------- + 10 | Sales + 20 | Marketing + 30 | Engineering + 40 | Empty +(4 rows) + +!ok +select deptno, deptno in (select deptno from emp where false) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | false + 20 | false + 30 | false + 40 | false +(4 rows) + +!ok +select deptno, deptno not in (select deptno from emp where false) from dept; + DEPTNO | EXPR$1 +--------+-------- + 10 | true + 20 | true + 30 | true + 40 | true +(4 rows) + +!ok + +# Multiple IN, connected by OR +select * from dept +where deptno in (select deptno from emp where gender = 'F') +or deptno in (select deptno from emp where gender = 'M'); + DEPTNO | DNAME +--------+------------- + 30 | Engineering + 10 | Sales + 20 | Marketing +(3 rows) + +!ok + +# Mix IN and EXISTS +select * from dept +where deptno in (select deptno from emp where gender = 'F') +or exists (select 99, 101 from emp where gender = 'X'); + DEPTNO | DNAME +--------+------------- + 30 | Engineering + 10 | Sales +(2 rows) + +!ok + +# Composite key +select * from dept +where (deptno, deptno) in (select deptno * 2 - deptno, deptno from emp where gender = 'F'); + +# Composite key, part literal +select * from emp +where (gender, deptno) in (select gender, 10 from emp where gender = 'F'); + ENAME | DEPTNO | GENDER +-------+--------+-------- + Jane | 10 | F +(1 row) + +!ok + +!use scott + +# [CALCITE-694] Scan HAVING clause for sub-queries and IN-lists +SELECT count(*) AS c +FROM "scott".emp +GROUP BY emp.deptno +HAVING sum(case when emp.empno in (7369, 7839, 7902) then emp.sal else 0 end) + BETWEEN 5000.0 AND 10000.0; + C +--- + 3 +(1 row) + +!ok + +# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING +# clause gives AssertionError +SELECT emp.deptno +FROM "scott".emp +GROUP BY emp.deptno +HAVING max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp); + DEPTNO +-------- + 10 + 20 + 30 +(3 rows) + +!ok + +# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING +# clause gives AssertionError +SELECT emp.deptno, + max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp) as bbbb +FROM "scott".emp +GROUP BY emp.deptno; + DEPTNO | BBBB +--------+------ + 10 | true + 20 | true + 30 | true +(3 rows) + +!ok + +# End subquery.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/subquery.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/subquery.oq b/core/src/test/resources/sql/subquery.oq deleted file mode 100644 index 9db95d3..0000000 --- a/core/src/test/resources/sql/subquery.oq +++ /dev/null @@ -1,278 +0,0 @@ -# subquery.oq - Queries involving IN and EXISTS sub-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 psql - -# [CALCITE-373] -# the following should return no rows, because the IN list has a null. -# for details on this: see HIVE-784, Dayal's paper from VLDB-87 -with -t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)), -t2(x) as (select * from (values 1,case when 1 = 1 then null else 3 end)) -select * -from t1 -where t1.x not in (select t2.x from t2); - X ---- -(0 rows) - -!ok -EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t1, $t6)], expr#8=[false], expr#9=[IS NOT NULL($t5)], expr#10=[true], expr#11=[IS NULL($t3)], expr#12=[null], expr#13=[<($t2, $t1)], expr#14=[CASE($t7, $t8, $t9, $t10, $t11, $t12, $t13, $t12, $t8)], expr#15=[NOT($t14)], X=[$t0], $condition=[$t15]) - EnumerableJoin(condition=[=($3, $4)], joinType=[left]) - EnumerableCalc(expr#0..2=[{inputs}], $f0=[$t2], $f1=[$t0], $f2=[$t1], $f3=[$t2]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) - EnumerableUnion(all=[true]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableUnion(all=[true]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableAggregate(group=[{0}], agg#0=[MIN($1)]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}]) - EnumerableUnion(all=[true]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5]) - EnumerableValues(tuples=[[{ 0 }]]) -!plan - -# Use of case is to get around issue with directly specifying null in values -# list. Postgres gives 0 rows. -with -t1(x) as (select * from (values (1),(2),(case when 1 = 1 then null else 3 end)) as t1), -t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t2) -select * -from t1 -where t1.x not in (select t2.x from t2); - - X ---- -(0 rows) - -!ok - -# RHS has a mixture of NULL and NOT NULL keys -select * from dept where deptno not in (select deptno from emp); - DEPTNO | DNAME ---------+------- -(0 rows) - -!ok -select deptno, deptno in (select deptno from emp) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | true - 20 | true - 30 | true - 40 | null -(4 rows) - -!ok -select deptno, deptno not in (select deptno from emp) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | false - 20 | false - 30 | false - 40 | null -(4 rows) - -!ok - -# RHS has only NULL keys -select * from dept where deptno not in (select deptno from emp where deptno is null); - DEPTNO | DNAME ---------+------- -(0 rows) - -!ok -select deptno, deptno in (select deptno from emp where deptno is null) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | null - 20 | null - 30 | null - 40 | null -(4 rows) - -!ok -select deptno, deptno not in (select deptno from emp where deptno is null) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | null - 20 | null - 30 | null - 40 | null -(4 rows) - -!ok - -# RHS has only NOT NULL keys -select * from dept where deptno not in (select deptno from emp where deptno is not null); - DEPTNO | DNAME ---------+------------- - 40 | Empty -(1 row) - -!ok -select deptno, deptno in (select deptno from emp where deptno is not null) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | true - 20 | true - 30 | true - 40 | false -(4 rows) - -!ok -select deptno, deptno not in (select deptno from emp where deptno is not null) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | false - 20 | false - 30 | false - 40 | true -(4 rows) - -!ok - -# RHS has no rows -# Even 'NULL NOT IN ...' is TRUE. -select * from dept where deptno not in (select deptno from emp where false); - DEPTNO | DNAME ---------+------------- - 10 | Sales - 20 | Marketing - 30 | Engineering - 40 | Empty -(4 rows) - -!ok -select deptno, deptno in (select deptno from emp where false) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | false - 20 | false - 30 | false - 40 | false -(4 rows) - -!ok -select deptno, deptno not in (select deptno from emp where false) from dept; - DEPTNO | EXPR$1 ---------+-------- - 10 | true - 20 | true - 30 | true - 40 | true -(4 rows) - -!ok - -# Multiple IN, connected by OR -select * from dept -where deptno in (select deptno from emp where gender = 'F') -or deptno in (select deptno from emp where gender = 'M'); - DEPTNO | DNAME ---------+------------- - 30 | Engineering - 10 | Sales - 20 | Marketing -(3 rows) - -!ok - -# Mix IN and EXISTS -select * from dept -where deptno in (select deptno from emp where gender = 'F') -or exists (select 99, 101 from emp where gender = 'X'); - DEPTNO | DNAME ---------+------------- - 30 | Engineering - 10 | Sales -(2 rows) - -!ok - -# Composite key -select * from dept -where (deptno, deptno) in (select deptno * 2 - deptno, deptno from emp where gender = 'F'); - -# Composite key, part literal -select * from emp -where (gender, deptno) in (select gender, 10 from emp where gender = 'F'); - ENAME | DEPTNO | GENDER --------+--------+-------- - Jane | 10 | F -(1 row) - -!ok - -!use scott - -# [CALCITE-694] Scan HAVING clause for sub-queries and IN-lists -SELECT count(*) AS c -FROM "scott".emp -GROUP BY emp.deptno -HAVING sum(case when emp.empno in (7369, 7839, 7902) then emp.sal else 0 end) - BETWEEN 5000.0 AND 10000.0; - C ---- - 3 -(1 row) - -!ok - -# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING -# clause gives AssertionError -SELECT emp.deptno -FROM "scott".emp -GROUP BY emp.deptno -HAVING max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp); - DEPTNO --------- - 10 - 20 - 30 -(3 rows) - -!ok - -# [CALCITE-716] Scalar sub-query and aggregate function in SELECT or HAVING -# clause gives AssertionError -SELECT emp.deptno, - max(emp.empno) > (SELECT min(emp.empno) FROM "scott".emp) as bbbb -FROM "scott".emp -GROUP BY emp.deptno; - DEPTNO | BBBB ---------+------ - 10 | true - 20 | true - 30 | true -(3 rows) - -!ok - -# End subquery.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/winagg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/winagg.iq b/core/src/test/resources/sql/winagg.iq new file mode 100644 index 0000000..f7c6f54 --- /dev/null +++ b/core/src/test/resources/sql/winagg.iq @@ -0,0 +1,323 @@ +# winagg.iq - Unit tests for windowed aggregation, verified on Postgres +# +# 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 psql + +# Multiple window functions sharing a single window +select count(*) over(partition by gender order by ename) as count1, + count(*) over(partition by deptno order by ename) as count2, + sum(deptno) over(partition by gender order by ename) as sum1, + sum(deptno) over(partition by deptno order by ename) as sum2 +from emp +order by sum1, sum2; + COUNT1 | COUNT2 | SUM1 | SUM2 +--------+--------+------+------ + 1 | 1 | 30 | 30 + 1 | 1 | 50 | 50 + 2 | 1 | 60 | 10 + 3 | 1 | 80 | 20 + 2 | 2 | 80 | 100 + 3 | 1 | 140 | 60 + 4 | 2 | 150 | 20 + 5 | 2 | 180 | 60 + 6 | 1 | 180 | +(9 rows) + +!ok + +!if (false) { +select *, first_value(deptno) over () from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | 10 + Bob | 10 | M | 10 + Eric | 20 | M | 10 + Susan | 30 | F | 10 + Alice | 30 | F | 10 + Adam | 50 | M | 10 + Eve | 50 | F | 10 + Grace | 60 | F | 10 +(8 rows) + +!ok +!} +!if (false) { +select *, first_value(ename) over () from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | Jane + Bob | 10 | M | Jane + Eric | 20 | M | Jane + Susan | 30 | F | Jane + Alice | 30 | F | Jane + Adam | 50 | M | Jane + Eve | 50 | F | Jane + Grace | 60 | F | Jane +(8 rows) + +!ok +!} +!if (false) { +select *, first_value(ename) over (partition by deptno) from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | Jane + Bob | 10 | M | Jane + Eric | 20 | M | Eric + Susan | 30 | F | Susan + Alice | 30 | F | Susan + Adam | 50 | M | Adam + Eve | 50 | F | Adam + Grace | 60 | F | Grace +(8 rows) + +!ok +!} +!if (false) { +select *, first_value(ename) over (partition by deptno range current row) from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | Jane + Bob | 10 | M | Jane + Eric | 20 | M | Eric + Susan | 30 | F | Susan + Alice | 30 | F | Susan + Adam | 50 | M | Adam + Eve | 50 | F | Adam + Grace | 60 | F | Grace +(8 rows) + +!ok +!} +!if (false) { +select *, first_value(ename) over (partition by deptno range unbounded preceding) from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | Jane + Bob | 10 | M | Jane + Eric | 20 | M | Eric + Susan | 30 | F | Susan + Alice | 30 | F | Susan + Adam | 50 | M | Adam + Eve | 50 | F | Adam + Grace | 60 | F | Grace +(8 rows) + +!ok +!} +!if (false) { +select *, first_value(ename) over (partition by deptno order by gender range unbounded preceding) from emp; + ename | deptno | gender | first_value +-------+--------+--------+------------- + Jane | 10 | F | Jane + Bob | 10 | M | Jane + Eric | 20 | M | Eric + Alice | 30 | F | Alice + Susan | 30 | F | Alice + Eve | 50 | F | Eve + Adam | 50 | M | Eve + Grace | 60 | F | Grace +(8 rows) + +!ok +!} +# Calcite does not yet generate tied ranks +!if (false) { +select *, count(*) over (order by deptno) as c from emp; + ENAME | DEPTNO | GENDER | C +-------+--------+--------+--- + Jane | 10 | F | 2 + Bob | 10 | M | 2 + Eric | 20 | M | 3 + Susan | 30 | F | 5 + Alice | 30 | F | 5 + Adam | 50 | M | 7 + Eve | 50 | F | 7 + Grace | 60 | F | 8 +(8 rows) + +!ok +!} +select *, rank() over (order by deptno) as c from emp; + ENAME | DEPTNO | GENDER | C +-------+--------+--------+--- + Jane | 10 | F | 1 + Bob | 10 | M | 1 + Eric | 20 | M | 3 + Susan | 30 | F | 4 + Alice | 30 | F | 4 + Adam | 50 | M | 6 + Eve | 50 | F | 6 + Grace | 60 | F | 8 + Wilma | | F | 9 +(9 rows) + +!ok + +# Calcite does not yet generate tied ranks +select *, dense_rank() over (order by deptno) as c from emp; + ENAME | DEPTNO | GENDER | C +-------+--------+--------+--- + Jane | 10 | F | 1 + Bob | 10 | M | 1 + Eric | 20 | M | 2 + Susan | 30 | F | 3 + Alice | 30 | F | 3 + Adam | 50 | M | 4 + Eve | 50 | F | 4 + Grace | 60 | F | 5 + Wilma | | F | 6 +(9 rows) + +!ok + +# [CALCITE-806] ROW_NUMBER should emit distinct values +# +# We only run this test under JDK 1.8 because the results are +# non-deterministic and are different (but still correct) on +# JDK 1.7 and other platforms. +!if (jdk18) { +select *, + row_number() over (order by deptno) as r1, + row_number() over (partition by deptno order by gender desc) as r2, + row_number() over (partition by deptno order by gender) as r3, + row_number() over (partition by gender) as r4, + row_number() over () as r +from emp; + ENAME | DEPTNO | GENDER | R1 | R2 | R3 | R4 | R +-------+--------+--------+----+----+----+----+--- + Wilma | | F | 9 | 1 | 1 | 1 | 1 + Eve | 50 | F | 7 | 2 | 1 | 2 | 2 + Jane | 10 | F | 1 | 2 | 1 | 3 | 3 + Grace | 60 | F | 8 | 1 | 1 | 4 | 4 + Susan | 30 | F | 4 | 1 | 1 | 5 | 5 + Alice | 30 | F | 5 | 2 | 2 | 6 | 6 + Adam | 50 | M | 6 | 1 | 2 | 1 | 7 + Eric | 20 | M | 3 | 1 | 1 | 2 | 8 + Bob | 10 | M | 2 | 1 | 2 | 3 | 9 +(9 rows) + +!ok +!} + +# As above, ROW_NUMBER without explicit ORDER BY +select deptno, + ename, + row_number() over (partition by deptno) as r +from emp +where gender = 'F'; + DEPTNO | ENAME | R +--------+-------+--- + 10 | Jane | 1 + 30 | Alice | 2 + 30 | Susan | 1 + 50 | Eve | 1 + 60 | Grace | 1 + | Wilma | 1 +(6 rows) + +!ok + +!if (false) { +select *, count(*) over (order by deptno), first_value(ename) over (order by deptno rows 2 following) from emp; + ERROR: frame starting from following row cannot end with current row + LINE 1: ...o), first_value(ename) over (order by deptno rows 2 followin... +!ok +!} + +# Without ORDER BY +select *, count(*) over (partition by deptno) as c from emp; + ENAME | DEPTNO | GENDER | C +-------+--------+--------+--- + Adam | 50 | M | 2 + Alice | 30 | F | 2 + Bob | 10 | M | 2 + Eric | 20 | M | 1 + Eve | 50 | F | 2 + Grace | 60 | F | 1 + Jane | 10 | F | 2 + Susan | 30 | F | 2 + Wilma | | F | 1 +(9 rows) + +!ok + +# No ORDER BY, windows defined in WINDOW clause. +select deptno, gender, min(gender) over w1 as a, min(gender) over w2 as d +from emp +window w1 as (), + w2 as (partition by deptno); + DEPTNO | GENDER | A | D +--------+--------+---+--- + 10 | F | F | F + 10 | M | F | F + 20 | M | F | M + 30 | F | F | F + 30 | F | F | F + 50 | F | F | F + 50 | M | F | F + 60 | F | F | F + | F | F | F +(9 rows) + +!ok + +# Composite COUNT. +select deptno, gender, count(gender, deptno) over w1 as a +from emp +window w1 as (); + DEPTNO | GENDER | A +--------+--------+--- + 10 | F | 8 + 10 | M | 8 + 20 | M | 8 + 30 | F | 8 + 30 | F | 8 + 50 | F | 8 + 50 | M | 8 + 60 | F | 8 + | F | 8 +(9 rows) + +!ok + +# Window Aggregate and group-by. +!set outputformat mysql +select min(deptno) as x, rank() over (order by ename) as y, + max(ename) over (partition by deptno) as z +from emp +group by deptno, ename; + ++----+---+-------+ +| X | Y | Z | ++----+---+-------+ +| | 9 | Wilma | +| 50 | 1 | Eve | +| 50 | 5 | Eve | +| 20 | 4 | Eric | +| 10 | 3 | Jane | +| 10 | 7 | Jane | +| 60 | 6 | Grace | +| 30 | 2 | Susan | +| 30 | 8 | Susan | ++----+---+-------+ +(9 rows) + +!ok + +# End winagg.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/winagg.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/winagg.oq b/core/src/test/resources/sql/winagg.oq deleted file mode 100644 index 44bc186..0000000 --- a/core/src/test/resources/sql/winagg.oq +++ /dev/null @@ -1,323 +0,0 @@ -# winagg.oq - Unit tests for windowed aggregation, verified on Postgres -# -# 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 psql - -# Multiple window functions sharing a single window -select count(*) over(partition by gender order by ename) as count1, - count(*) over(partition by deptno order by ename) as count2, - sum(deptno) over(partition by gender order by ename) as sum1, - sum(deptno) over(partition by deptno order by ename) as sum2 -from emp -order by sum1, sum2; - COUNT1 | COUNT2 | SUM1 | SUM2 ---------+--------+------+------ - 1 | 1 | 30 | 30 - 1 | 1 | 50 | 50 - 2 | 1 | 60 | 10 - 3 | 1 | 80 | 20 - 2 | 2 | 80 | 100 - 3 | 1 | 140 | 60 - 4 | 2 | 150 | 20 - 5 | 2 | 180 | 60 - 6 | 1 | 180 | -(9 rows) - -!ok - -!if (false) { -select *, first_value(deptno) over () from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | 10 - Bob | 10 | M | 10 - Eric | 20 | M | 10 - Susan | 30 | F | 10 - Alice | 30 | F | 10 - Adam | 50 | M | 10 - Eve | 50 | F | 10 - Grace | 60 | F | 10 -(8 rows) - -!ok -!} -!if (false) { -select *, first_value(ename) over () from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | Jane - Bob | 10 | M | Jane - Eric | 20 | M | Jane - Susan | 30 | F | Jane - Alice | 30 | F | Jane - Adam | 50 | M | Jane - Eve | 50 | F | Jane - Grace | 60 | F | Jane -(8 rows) - -!ok -!} -!if (false) { -select *, first_value(ename) over (partition by deptno) from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | Jane - Bob | 10 | M | Jane - Eric | 20 | M | Eric - Susan | 30 | F | Susan - Alice | 30 | F | Susan - Adam | 50 | M | Adam - Eve | 50 | F | Adam - Grace | 60 | F | Grace -(8 rows) - -!ok -!} -!if (false) { -select *, first_value(ename) over (partition by deptno range current row) from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | Jane - Bob | 10 | M | Jane - Eric | 20 | M | Eric - Susan | 30 | F | Susan - Alice | 30 | F | Susan - Adam | 50 | M | Adam - Eve | 50 | F | Adam - Grace | 60 | F | Grace -(8 rows) - -!ok -!} -!if (false) { -select *, first_value(ename) over (partition by deptno range unbounded preceding) from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | Jane - Bob | 10 | M | Jane - Eric | 20 | M | Eric - Susan | 30 | F | Susan - Alice | 30 | F | Susan - Adam | 50 | M | Adam - Eve | 50 | F | Adam - Grace | 60 | F | Grace -(8 rows) - -!ok -!} -!if (false) { -select *, first_value(ename) over (partition by deptno order by gender range unbounded preceding) from emp; - ename | deptno | gender | first_value --------+--------+--------+------------- - Jane | 10 | F | Jane - Bob | 10 | M | Jane - Eric | 20 | M | Eric - Alice | 30 | F | Alice - Susan | 30 | F | Alice - Eve | 50 | F | Eve - Adam | 50 | M | Eve - Grace | 60 | F | Grace -(8 rows) - -!ok -!} -# Calcite does not yet generate tied ranks -!if (false) { -select *, count(*) over (order by deptno) as c from emp; - ENAME | DEPTNO | GENDER | C --------+--------+--------+--- - Jane | 10 | F | 2 - Bob | 10 | M | 2 - Eric | 20 | M | 3 - Susan | 30 | F | 5 - Alice | 30 | F | 5 - Adam | 50 | M | 7 - Eve | 50 | F | 7 - Grace | 60 | F | 8 -(8 rows) - -!ok -!} -select *, rank() over (order by deptno) as c from emp; - ENAME | DEPTNO | GENDER | C --------+--------+--------+--- - Jane | 10 | F | 1 - Bob | 10 | M | 1 - Eric | 20 | M | 3 - Susan | 30 | F | 4 - Alice | 30 | F | 4 - Adam | 50 | M | 6 - Eve | 50 | F | 6 - Grace | 60 | F | 8 - Wilma | | F | 9 -(9 rows) - -!ok - -# Calcite does not yet generate tied ranks -select *, dense_rank() over (order by deptno) as c from emp; - ENAME | DEPTNO | GENDER | C --------+--------+--------+--- - Jane | 10 | F | 1 - Bob | 10 | M | 1 - Eric | 20 | M | 2 - Susan | 30 | F | 3 - Alice | 30 | F | 3 - Adam | 50 | M | 4 - Eve | 50 | F | 4 - Grace | 60 | F | 5 - Wilma | | F | 6 -(9 rows) - -!ok - -# [CALCITE-806] ROW_NUMBER should emit distinct values -# -# We only run this test under JDK 1.8 because the results are -# non-deterministic and are different (but still correct) on -# JDK 1.7 and other platforms. -!if (jdk18) { -select *, - row_number() over (order by deptno) as r1, - row_number() over (partition by deptno order by gender desc) as r2, - row_number() over (partition by deptno order by gender) as r3, - row_number() over (partition by gender) as r4, - row_number() over () as r -from emp; - ENAME | DEPTNO | GENDER | R1 | R2 | R3 | R4 | R --------+--------+--------+----+----+----+----+--- - Wilma | | F | 9 | 1 | 1 | 1 | 1 - Eve | 50 | F | 7 | 2 | 1 | 2 | 2 - Jane | 10 | F | 1 | 2 | 1 | 3 | 3 - Grace | 60 | F | 8 | 1 | 1 | 4 | 4 - Susan | 30 | F | 4 | 1 | 1 | 5 | 5 - Alice | 30 | F | 5 | 2 | 2 | 6 | 6 - Adam | 50 | M | 6 | 1 | 2 | 1 | 7 - Eric | 20 | M | 3 | 1 | 1 | 2 | 8 - Bob | 10 | M | 2 | 1 | 2 | 3 | 9 -(9 rows) - -!ok -!} - -# As above, ROW_NUMBER without explicit ORDER BY -select deptno, - ename, - row_number() over (partition by deptno) as r -from emp -where gender = 'F'; - DEPTNO | ENAME | R ---------+-------+--- - 10 | Jane | 1 - 30 | Alice | 2 - 30 | Susan | 1 - 50 | Eve | 1 - 60 | Grace | 1 - | Wilma | 1 -(6 rows) - -!ok - -!if (false) { -select *, count(*) over (order by deptno), first_value(ename) over (order by deptno rows 2 following) from emp; - ERROR: frame starting from following row cannot end with current row - LINE 1: ...o), first_value(ename) over (order by deptno rows 2 followin... -!ok -!} - -# Without ORDER BY -select *, count(*) over (partition by deptno) as c from emp; - ENAME | DEPTNO | GENDER | C --------+--------+--------+--- - Adam | 50 | M | 2 - Alice | 30 | F | 2 - Bob | 10 | M | 2 - Eric | 20 | M | 1 - Eve | 50 | F | 2 - Grace | 60 | F | 1 - Jane | 10 | F | 2 - Susan | 30 | F | 2 - Wilma | | F | 1 -(9 rows) - -!ok - -# No ORDER BY, windows defined in WINDOW clause. -select deptno, gender, min(gender) over w1 as a, min(gender) over w2 as d -from emp -window w1 as (), - w2 as (partition by deptno); - DEPTNO | GENDER | A | D ---------+--------+---+--- - 10 | F | F | F - 10 | M | F | F - 20 | M | F | M - 30 | F | F | F - 30 | F | F | F - 50 | F | F | F - 50 | M | F | F - 60 | F | F | F - | F | F | F -(9 rows) - -!ok - -# Composite COUNT. -select deptno, gender, count(gender, deptno) over w1 as a -from emp -window w1 as (); - DEPTNO | GENDER | A ---------+--------+--- - 10 | F | 8 - 10 | M | 8 - 20 | M | 8 - 30 | F | 8 - 30 | F | 8 - 50 | F | 8 - 50 | M | 8 - 60 | F | 8 - | F | 8 -(9 rows) - -!ok - -# Window Aggregate and group-by. -!set outputformat mysql -select min(deptno) as x, rank() over (order by ename) as y, - max(ename) over (partition by deptno) as z -from emp -group by deptno, ename; - -+----+---+-------+ -| X | Y | Z | -+----+---+-------+ -| | 9 | Wilma | -| 50 | 1 | Eve | -| 50 | 5 | Eve | -| 20 | 4 | Eric | -| 10 | 3 | Jane | -| 10 | 7 | Jane | -| 60 | 6 | Grace | -| 30 | 2 | Susan | -| 30 | 8 | Susan | -+----+---+-------+ -(9 rows) - -!ok - -# End winagg.oq
