[CALCITE-1022] Rename ".oq" Quidem files to ".iq"
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/8c2bc8f1 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/8c2bc8f1 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/8c2bc8f1 Branch: refs/heads/master Commit: 8c2bc8f16b7b26fed7a53aa06f940341dea18219 Parents: 84b55ef Author: Julian Hyde <[email protected]> Authored: Sat Dec 12 10:35:22 2015 -0800 Committer: Julian Hyde <[email protected]> Committed: Sat Dec 12 13:41:05 2015 -0800 ---------------------------------------------------------------------- .../calcite/sql/fun/SqlGroupIdFunction.java | 2 +- .../calcite/sql/fun/SqlGroupingFunction.java | 2 +- .../calcite/sql/fun/SqlGroupingIdFunction.java | 2 +- .../java/org/apache/calcite/test/JdbcTest.java | 33 +- core/src/test/resources/sql/agg.iq | 1347 ++++++++++++++++++ core/src/test/resources/sql/agg.oq | 1347 ------------------ core/src/test/resources/sql/conditions.iq | 261 ++++ core/src/test/resources/sql/conditions.oq | 259 ---- core/src/test/resources/sql/dummy.iq | 23 + core/src/test/resources/sql/dummy.oq | 23 - core/src/test/resources/sql/join.iq | 288 ++++ core/src/test/resources/sql/join.oq | 288 ---- core/src/test/resources/sql/misc.iq | 1111 +++++++++++++++ core/src/test/resources/sql/misc.oq | 1111 --------------- core/src/test/resources/sql/outer.iq | 349 +++++ core/src/test/resources/sql/outer.oq | 349 ----- core/src/test/resources/sql/scalar.iq | 216 +++ core/src/test/resources/sql/scalar.oq | 216 --- core/src/test/resources/sql/sequence.iq | 79 + core/src/test/resources/sql/sequence.oq | 79 - core/src/test/resources/sql/sort.iq | 179 +++ core/src/test/resources/sql/sort.oq | 179 --- core/src/test/resources/sql/subquery.iq | 278 ++++ core/src/test/resources/sql/subquery.oq | 278 ---- core/src/test/resources/sql/winagg.iq | 323 +++++ core/src/test/resources/sql/winagg.oq | 323 ----- 26 files changed, 4474 insertions(+), 4471 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupIdFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupIdFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupIdFunction.java index f72a159..7b61703 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupIdFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupIdFunction.java @@ -27,7 +27,7 @@ import org.apache.calcite.sql.type.ReturnTypes; * <p>This function is not defined in the SQL standard; our implementation is * consistent with Oracle. * - * <p>Some examples are in {@code agg.oq}. + * <p>Some examples are in {@code agg.iq}. */ class SqlGroupIdFunction extends SqlAbstractGroupFunction { public SqlGroupIdFunction() { http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java index c3dae08..16e75e0 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingFunction.java @@ -26,7 +26,7 @@ import org.apache.calcite.sql.type.ReturnTypes; * * <p>This function is defined in the SQL standard. * - * <p>Some examples are in {@code agg.oq}. + * <p>Some examples are in {@code agg.iq}. */ class SqlGroupingFunction extends SqlAbstractGroupFunction { public SqlGroupingFunction() { http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java index 6470db4..a4276b4 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlGroupingIdFunction.java @@ -27,7 +27,7 @@ import org.apache.calcite.sql.type.ReturnTypes; * <p>This function is not defined in the SQL standard; our implementation is * consistent with Oracle. * - * <p>Some examples are in {@code agg.oq}. + * <p>Some examples are in {@code agg.iq}. */ class SqlGroupingIdFunction extends SqlAbstractGroupFunction { public SqlGroupingIdFunction() { http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index 391729d..9d2597f 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -3058,7 +3058,8 @@ public class JdbcTest { .query("select \"store_id\", \"grocery_sqft\" from \"store\"\n" + "where \"store_id\" < 10\n" + "order by 1 fetch first 5 rows only") - .explainContains("PLAN=EnumerableCalc(expr#0..23=[{inputs}], store_id=[$t0], grocery_sqft=[$t16])\n" + .explainContains("" + + "PLAN=EnumerableCalc(expr#0..23=[{inputs}], store_id=[$t0], grocery_sqft=[$t16])\n" + " EnumerableLimit(fetch=[5])\n" + " EnumerableCalc(expr#0..23=[{inputs}], expr#24=[10], expr#25=[<($t0, $t24)], proj#0..23=[{exprs}], $condition=[$t25])\n" + " EnumerableTableScan(table=[[foodmart2, store]])\n") @@ -4307,7 +4308,7 @@ public class JdbcTest { /** Tests CALCITE-980: different flavors of boolean logic */ @Test public void testBooleansInWhere() throws Exception { - checkRun("sql/conditions.oq"); + checkRun("sql/conditions.iq"); } /** Tests CALCITE-980: different flavors of boolean logic */ @@ -4613,25 +4614,25 @@ public class JdbcTest { /** Runs the dummy script, which is checked in empty but which you may * use as scratch space during development. */ - // Do not add '@Ignore'; just remember not to commit changes to dummy.oq + // Do not add '@Ignore'; just remember not to commit changes to dummy.iq @Test public void testRunDummy() throws Exception { - checkRun("sql/dummy.oq"); + checkRun("sql/dummy.iq"); } @Test public void testRunAgg() throws Exception { - checkRun("sql/agg.oq"); + checkRun("sql/agg.iq"); } @Test public void testRunJoin() throws Exception { - checkRun("sql/join.oq"); + checkRun("sql/join.iq"); } @Test public void testRunOuter() throws Exception { - checkRun("sql/outer.oq"); + checkRun("sql/outer.iq"); } @Test public void testRunWinAgg() throws Exception { - checkRun("sql/winagg.oq"); + checkRun("sql/winagg.iq"); } @Test public void testRunMisc() throws Exception { @@ -4641,35 +4642,35 @@ public class JdbcTest { // Oracle as the JDBC data source. return; } - checkRun("sql/misc.oq"); + checkRun("sql/misc.iq"); } @Test public void testRunSequence() throws Exception { - checkRun("sql/sequence.oq"); + checkRun("sql/sequence.iq"); } @Test public void testRunSort() throws Exception { - checkRun("sql/sort.oq"); + checkRun("sql/sort.iq"); } @Test public void testRunScalar() throws Exception { - checkRun("sql/scalar.oq"); + checkRun("sql/scalar.iq"); } @Test public void testRunSubquery() throws Exception { - checkRun("sql/subquery.oq"); + checkRun("sql/subquery.iq"); } private void checkRun(String path) throws Exception { final File inFile; final File outFile; if (path.startsWith("/")) { - // e.g. path = "/tmp/foo.oq" + // e.g. path = "/tmp/foo.iq" inFile = new File(path); outFile = new File(path + ".out"); } else { - // e.g. path = "sql/outer.oq" - // inUrl = "file:/home/fred/calcite/core/target/test-classes/sql/outer.oq" + // e.g. path = "sql/outer.iq" + // inUrl = "file:/home/fred/calcite/core/target/test-classes/sql/outer.iq" final URL inUrl = JdbcTest.class.getResource("/" + path); String x = inUrl.getFile(); assert x.endsWith(path); http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/agg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq new file mode 100644 index 0000000..8e5770e --- /dev/null +++ b/core/src/test/resources/sql/agg.iq @@ -0,0 +1,1347 @@ +# agg.iq - Aggregate functions +# +# 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 + +# count(*) returns number of rows in table +select count(ename) as c from emp; ++---+ +| C | ++---+ +| 9 | ++---+ +(1 row) + +!ok + +# count of not-nullable column same as count(*) +select count(ename) as c from emp; ++---+ +| C | ++---+ +| 9 | ++---+ +(1 row) + +!ok + +# count of nullable column +select count(deptno) as c from emp; ++---+ +| C | ++---+ +| 8 | ++---+ +(1 row) + +!ok + +# composite count +select count(deptno, ename, 1, deptno) as c from emp; ++---+ +| C | ++---+ +| 8 | ++---+ +(1 row) + +!ok + +select city, gender as c from emps; ++---------------+---+ +| CITY | C | ++---------------+---+ +| Vancouver | F | +| San Francisco | M | +| | | +| Vancouver | M | +| | F | ++---------------+---+ +(5 rows) + +!ok + +# SELECT DISTINCT includes fully and partially null rows +select distinct city, gender from emps; ++---------------+--------+ +| CITY | GENDER | ++---------------+--------+ +| | | +| Vancouver | M | +| | F | +| San Francisco | M | +| Vancouver | F | ++---------------+--------+ +(5 rows) + +!ok + +# COUNT excludes fully or partially null rows +select count(city, gender) as c from emps; ++---+ +| C | ++---+ +| 3 | ++---+ +(1 row) + +!ok + +# COUNT-DISTINCT excludes fully or partially null rows +select count(distinct city, gender) as c from emps; ++---+ +| C | ++---+ +| 3 | ++---+ +(1 row) + +!ok + +select distinct mod(deptno, 20) as m, gender as c from emps; ++----+---+ +| M | C | ++----+---+ +| 0 | F | +| 10 | | +| 0 | M | ++----+---+ +(3 rows) + +!ok + +# Partially null row (10, NULL) is excluded from count. +select count(distinct mod(deptno, 20), gender) as c from emps; ++---+ +| C | ++---+ +| 2 | ++---+ +(1 row) + +!ok + +select count(mod(deptno, 20), gender) as c from emps; ++---+ +| C | ++---+ +| 4 | ++---+ +(1 row) + +!ok + +# Nulls in GROUP BY +select x = 1 as x1, count(*) as c +from (values 0, 1, 2, cast(null as integer)) as t(x) +group by x = 1; +X1 BOOLEAN(1) +C BIGINT(19) NOT NULL +!type ++-------+---+ +| X1 | C | ++-------+---+ +| false | 2 | +| true | 1 | +| | 1 | ++-------+---+ +(3 rows) + +!ok + +# Basic GROUPING SETS +select deptno, count(*) as c from emps group by grouping sets ((), (deptno)); ++--------+---+ +| DEPTNO | C | ++--------+---+ +| 10 | 1 | +| 20 | 2 | +| 40 | 2 | +| | 5 | ++--------+---+ +(4 rows) + +!ok + +# GROUPING SETS on expression +select deptno + 1, count(*) as c from emps group by grouping sets ((), (deptno + 1)); ++--------+---+ +| EXPR$0 | C | ++--------+---+ +| 11 | 1 | +| 21 | 2 | +| 41 | 2 | +| | 5 | ++--------+---+ +(4 rows) + +!ok + +# CUBE +select deptno + 1, count(*) as c from emp group by cube(deptno, gender); ++--------+---+ +| EXPR$0 | C | ++--------+---+ +| 11 | 1 | +| 11 | 1 | +| 11 | 2 | +| 21 | 1 | +| 21 | 1 | +| 31 | 2 | +| 31 | 2 | +| 51 | 1 | +| 51 | 1 | +| 51 | 2 | +| 61 | 1 | +| 61 | 1 | +| | 1 | +| | 1 | +| | 3 | +| | 6 | +| | 9 | ++--------+---+ +(17 rows) + +!ok + +# ROLLUP on 1 column +select deptno + 1, count(*) as c +from emp +group by rollup(deptno); ++--------+---+ +| EXPR$0 | C | ++--------+---+ +| 11 | 2 | +| 21 | 1 | +| 31 | 2 | +| 51 | 2 | +| 61 | 1 | +| | 1 | +| | 9 | ++--------+---+ +(7 rows) + +!ok + +# ROLLUP on 2 columns; project columns in different order +select gender, deptno + 1, count(*) as c +from emp +group by rollup(deptno, gender); ++--------+--------+---+ +| GENDER | EXPR$1 | C | ++--------+--------+---+ +| M | 21 | 1 | +| F | 11 | 1 | +| F | 31 | 2 | +| F | 51 | 1 | +| F | 61 | 1 | +| F | | 1 | +| M | 11 | 1 | +| M | 51 | 1 | +| | 11 | 2 | +| | 21 | 1 | +| | 31 | 2 | +| | 51 | 2 | +| | 61 | 1 | +| | | 1 | +| | | 9 | ++--------+--------+---+ +(15 rows) + +!ok + +# ROLLUP on column with nulls +# Note the two rows with NULL key (one represents ALL) +select gender, count(*) as c +from emp +group by rollup(gender); ++--------+---+ +| GENDER | C | ++--------+---+ +| F | 6 | +| M | 3 | +| | 9 | ++--------+---+ +(3 rows) + +!ok + +# ROLLUP plus ORDER BY +select gender, count(*) as c +from emp +group by rollup(gender) +order by c desc; ++--------+---+ +| GENDER | C | ++--------+---+ +| | 9 | +| F | 6 | +| M | 3 | ++--------+---+ +(3 rows) + +!ok + +# ROLLUP cartesian product +select deptno, count(*) as c +from emp +group by rollup(deptno), rollup(gender); ++--------+---+ +| DEPTNO | C | ++--------+---+ +| 10 | 1 | +| 10 | 1 | +| 20 | 1 | +| 20 | 1 | +| | 1 | +| 10 | 2 | +| 30 | 2 | +| 30 | 2 | +| 50 | 1 | +| 50 | 1 | +| 50 | 2 | +| 60 | 1 | +| 60 | 1 | +| | 1 | +| | 3 | +| | 6 | +| | 9 | ++--------+---+ +(17 rows) + +!ok + +# ROLLUP cartesian product of with tuple with expression +select deptno / 2 + 1 as half1, count(*) as c +from emp +group by rollup(deptno / 2, gender), rollup(substring(ename FROM 1 FOR 1)); ++-------+---+ +| HALF1 | C | ++-------+---+ +| 11 | 1 | +| 11 | 1 | +| 11 | 1 | +| 11 | 1 | +| 16 | 1 | +| 16 | 1 | +| 16 | 1 | +| 16 | 1 | +| 16 | 2 | +| 16 | 2 | +| 26 | 1 | +| 26 | 1 | +| 26 | 1 | +| 26 | 1 | +| 26 | 1 | +| 26 | 1 | +| 26 | 2 | +| 31 | 1 | +| 31 | 1 | +| 31 | 1 | +| 31 | 1 | +| 6 | 1 | +| 6 | 1 | +| 6 | 1 | +| 6 | 1 | +| 6 | 1 | +| 6 | 1 | +| 6 | 2 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 1 | +| | 2 | +| | 2 | +| | 9 | ++-------+---+ +(40 rows) + +!ok + +# ROLLUP with HAVING +select deptno + 1 as d1, count(*) as c +from emp +group by rollup(deptno) +having count(*) > 3; ++----+---+ +| D1 | C | ++----+---+ +| | 9 | ++----+---+ +(1 row) + +!ok + +# CUBE and DISTINCT +select distinct count(*) from emp group by cube(deptno, gender); ++--------+ +| EXPR$0 | ++--------+ +| 1 | +| 2 | +| 3 | +| 6 | +| 9 | ++--------+ +(5 rows) + +!ok + +# CUBE and JOIN +select e.deptno, e.gender, min(e.ename) as min_name +from emp as e join dept as d using (deptno) +group by cube(e.deptno, d.deptno, e.gender) +having count(*) > 2 or gender = 'M' and e.deptno = 10; ++--------+--------+----------+ +| DEPTNO | GENDER | MIN_NAME | ++--------+--------+----------+ +| 10 | M | Bob | +| 10 | M | Bob | +| | F | Alice | +| | | Alice | ++--------+--------+----------+ +(4 rows) + +!ok + +# GROUPING in SELECT clause of GROUP BY query +select count(*) as c, grouping(deptno) as g +from emp +group by deptno; ++---+---+ +| C | G | ++---+---+ +| 1 | 1 | +| 1 | 1 | +| 1 | 1 | +| 2 | 1 | +| 2 | 1 | +| 2 | 1 | ++---+---+ +(6 rows) + +!ok + +# GROUPING, GROUP_ID, GROUPING_ID in SELECT clause of GROUP BY query +select count(*) as c, + grouping(deptno) as g, + group_id() as gid, + grouping_id(deptno) as gd, + grouping_id(gender) as gg, + grouping_id(gender, deptno) as ggd, + grouping_id(gender, deptno) as gdg +from emp +group by deptno, gender; ++---+---+-----+----+----+-----+-----+ +| C | G | GID | GD | GG | GGD | GDG | ++---+---+-----+----+----+-----+-----+ +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 1 | 1 | 3 | 1 | 1 | 3 | 3 | +| 2 | 1 | 3 | 1 | 1 | 3 | 3 | ++---+---+-----+----+----+-----+-----+ +(8 rows) + +!ok + +# GROUPING in ORDER BY clause +select count(*) as c +from emp +group by deptno +order by grouping(deptno); ++---+ +| C | ++---+ +| 1 | +| 2 | +| 1 | +| 2 | +| 1 | +| 2 | ++---+ +(6 rows) + +!ok + +# Duplicate argument to GROUPING_ID. +select deptno, gender, grouping_id(deptno, gender, deptno), count(*) as c +from emp +where deptno = 10 +group by rollup(gender, deptno); ++--------+--------+--------+---+ +| DEPTNO | GENDER | EXPR$2 | C | ++--------+--------+--------+---+ +| 10 | F | 0 | 1 | +| 10 | M | 0 | 1 | +| | F | 5 | 1 | +| | M | 5 | 1 | +| | | 7 | 2 | ++--------+--------+--------+---+ +(5 rows) + +!ok + +# GROUPING in SELECT clause of ROLLUP query +select count(*) as c, deptno, grouping(deptno) as g +from emp +group by rollup(deptno); ++---+--------+---+ +| C | DEPTNO | G | ++---+--------+---+ +| 1 | 20 | 0 | +| 1 | 60 | 0 | +| 1 | | 0 | +| 2 | 10 | 0 | +| 2 | 30 | 0 | +| 2 | 50 | 0 | +| 9 | | 1 | ++---+--------+---+ +(7 rows) + +!ok + +# GROUPING, GROUPING_ID and GROUP_ID +select deptno, gender, grouping(deptno) gd, grouping(gender) gg, + grouping_id(deptno, gender) dg, grouping_id(gender, deptno) gd, + group_id() gid, count(*) c +from emp group by cube(deptno, gender); ++--------+--------+----+----+----+----+-----+---+ +| DEPTNO | GENDER | GD | GG | DG | GD | GID | C | ++--------+--------+----+----+----+----+-----+---+ +| 10 | F | 0 | 0 | 0 | 0 | 0 | 1 | +| 10 | M | 0 | 0 | 0 | 0 | 0 | 1 | +| 10 | | 0 | 1 | 1 | 2 | 1 | 2 | +| 20 | M | 0 | 0 | 0 | 0 | 0 | 1 | +| 20 | | 0 | 1 | 1 | 2 | 1 | 1 | +| 30 | F | 0 | 0 | 0 | 0 | 0 | 2 | +| 30 | | 0 | 1 | 1 | 2 | 1 | 2 | +| 50 | F | 0 | 0 | 0 | 0 | 0 | 1 | +| 50 | M | 0 | 0 | 0 | 0 | 0 | 1 | +| 50 | | 0 | 1 | 1 | 2 | 1 | 2 | +| 60 | F | 0 | 0 | 0 | 0 | 0 | 1 | +| 60 | | 0 | 1 | 1 | 2 | 1 | 1 | +| | F | 0 | 0 | 0 | 0 | 0 | 1 | +| | F | 1 | 0 | 2 | 1 | 2 | 6 | +| | M | 1 | 0 | 2 | 1 | 2 | 3 | +| | | 0 | 1 | 1 | 2 | 1 | 1 | +| | | 1 | 1 | 3 | 3 | 3 | 9 | ++--------+--------+----+----+----+----+-----+---+ +(17 rows) + +!ok + +!use scott + +# [KYLIN-751] Max on negative double values is not working +# [CALCITE-735] Primitive.DOUBLE.min should be large and negative +select max(v) as x, min(v) as n +from (values cast(-86.4 as double), cast(-100 as double)) as t(v); ++-------+--------+ +| X | N | ++-------+--------+ +| -86.4 | -100.0 | ++-------+--------+ +(1 row) + +!ok + +select max(v) as x, min(v) as n +from (values cast(-86.4 as double), cast(-100 as double), cast(2 as double)) as t(v); ++-----+--------+ +| X | N | ++-----+--------+ +| 2.0 | -100.0 | ++-----+--------+ +(1 row) + +!ok + +select max(v) as x, min(v) as n +from (values cast(-86.4 as float), cast(-100 as float)) as t(v); ++-------+--------+ +| X | N | ++-------+--------+ +| -86.4 | -100.0 | ++-------+--------+ +(1 row) + +!ok + +# COLLECT +select deptno, collect(empno) as empnos +from "scott".emp +group by deptno; ++--------+--------------------------------------+ +| DEPTNO | EMPNOS | ++--------+--------------------------------------+ +| 10 | [7782, 7839, 7934] | +| 20 | [7369, 7566, 7788, 7876, 7902] | +| 30 | [7499, 7521, 7654, 7698, 7844, 7900] | ++--------+--------------------------------------+ +(3 rows) + +!ok + +# COLLECT DISTINCT +# Disabled in JDK 1.7 because order of values is different +!if (jdk18) { +select deptno, collect(distinct job) as jobs +from "scott".emp +group by deptno; ++--------+-----------------------------+ +| DEPTNO | JOBS | ++--------+-----------------------------+ +| 10 | [MANAGER, CLERK, PRESIDENT] | +| 20 | [CLERK, ANALYST, MANAGER] | +| 30 | [SALESMAN, MANAGER, CLERK] | ++--------+-----------------------------+ +(3 rows) + +!ok +!} + +# COLLECT ... FILTER +select deptno, collect(empno) filter (where empno < 7550) as empnos +from "scott".emp +group by deptno; ++--------+--------------+ +| DEPTNO | EMPNOS | ++--------+--------------+ +| 10 | [] | +| 20 | [7369] | +| 30 | [7499, 7521] | ++--------+--------------+ +(3 rows) + +!ok + +# Aggregate FILTER +select deptno, + sum(sal) filter (where job = 'CLERK') c_sal, + sum(sal) filter (where job = 'CLERK' and deptno > 10) c10_sal, + max(sal) filter (where job = 'CLERK') as max_c, + min(sal) filter (where job = 'CLERK') as min_c, + max(sal) filter (where job = 'CLERK') + - min(sal) filter (where job = 'CLERK') as range_c, + max(sal) filter (where job = 'SALESMAN') + - min(sal) filter (where job = 'SALESMAN') as range_m +from "scott".emp +group by deptno; ++--------+---------+---------+---------+---------+---------+---------+ +| DEPTNO | C_SAL | C10_SAL | MAX_C | MIN_C | RANGE_C | RANGE_M | ++--------+---------+---------+---------+---------+---------+---------+ +| 10 | 1300.00 | | 1300.00 | 1300.00 | 0.00 | | +| 20 | 1900.00 | 1900.00 | 1100.00 | 800.00 | 300.00 | | +| 30 | 950.00 | 950.00 | 950.00 | 950.00 | 0.00 | 350.00 | ++--------+---------+---------+---------+---------+---------+---------+ +(3 rows) + +!ok + +# Aggregate FILTER on condition in GROUP BY +select deptno, + sum(sal) filter (where deptno = 10) sal_10 +from "scott".emp +group by deptno; +DEPTNO TINYINT(3) +SAL_10 DECIMAL(7, 2) +!type ++--------+---------+ +| DEPTNO | SAL_10 | ++--------+---------+ +| 10 | 8750.00 | +| 20 | | +| 30 | | ++--------+---------+ +(3 rows) + +!ok + +# Aggregate FILTER with HAVING +select deptno +from "scott".emp +group by deptno +having sum(sal) filter (where job = 'CLERK') > 1000; ++--------+ +| DEPTNO | ++--------+ +| 10 | +| 20 | ++--------+ +(2 rows) + +!ok + +# Aggregate FILTER with ORDER BY +select deptno +from "scott".emp +group by deptno +order by sum(sal) filter (where job = 'CLERK'); ++--------+ +| DEPTNO | ++--------+ +| 30 | +| 10 | +| 20 | ++--------+ +(3 rows) + +!ok + +# Aggregate FILTER with JOIN +select dept.deptno, + sum(sal) filter (where 1 < 2) as s, + sum(sal) as s1, + count(*) filter (where emp.ename < dept.dname) as c +from "scott".emp +join "scott".dept using (deptno) +group by dept.deptno; ++--------+----------+----------+---+ +| DEPTNO | S | S1 | C | ++--------+----------+----------+---+ +| 10 | 8750.00 | 8750.00 | 0 | +| 20 | 10875.00 | 10875.00 | 3 | +| 30 | 9400.00 | 9400.00 | 4 | ++--------+----------+----------+---+ +(3 rows) + +!ok + +# Aggregate FILTER with DISTINCT +select deptno, + count(distinct job) as cdj +from "scott".emp +group by deptno; ++--------+-----+ +| DEPTNO | CDJ | ++--------+-----+ +| 10 | 3 | +| 20 | 3 | +| 30 | 3 | ++--------+-----+ +(3 rows) + +!ok + +select deptno, + count(distinct job) filter (where job <> 'SALESMAN') as cdj +from "scott".emp +group by deptno; ++--------+-----+ +| DEPTNO | CDJ | ++--------+-----+ +| 10 | 3 | +| 20 | 3 | +| 30 | 2 | ++--------+-----+ +(3 rows) + +!ok + +# Multiple distinct count +select deptno, + count(distinct job) as j, count(distinct mgr) as m +from "scott".emp +group by deptno; ++--------+---+---+ +| DEPTNO | J | M | ++--------+---+---+ +| 10 | 3 | 2 | +| 20 | 3 | 4 | +| 30 | 3 | 2 | ++--------+---+---+ +(3 rows) + +!ok + +# Multiple distinct count and non-distinct aggregates +select deptno, + count(distinct job) as dj, + count(job) as j, + count(distinct mgr) as m, + sum(sal) as s +from "scott".emp +group by deptno; ++--------+----+---+---+----------+ +| DEPTNO | DJ | J | M | S | ++--------+----+---+---+----------+ +| 10 | 3 | 3 | 2 | 8750.00 | +| 20 | 3 | 5 | 4 | 10875.00 | +| 30 | 3 | 6 | 2 | 9400.00 | ++--------+----+---+---+----------+ +(3 rows) + +!ok + +# Multiple distinct count and non-distinct aggregates, no GROUP BY +select count(distinct job) as dj, + count(job) as j, + count(distinct mgr) as m, + sum(sal) as s +from "scott".emp; ++----+----+---+----------+ +| DJ | J | M | S | ++----+----+---+----------+ +| 5 | 14 | 6 | 29025.00 | ++----+----+---+----------+ +(1 row) + +!ok + +# [CALCITE-280] BigDecimal underflow +# Previously threw "java.lang.ArithmeticException: Non-terminating decimal +# expansion; no exact representable decimal result" +select avg(comm) as a, count(comm) as c from "scott".emp where empno < 7844; ++-------------------+---+ +| A | C | ++-------------------+---+ +| 733.3333333333333 | 3 | ++-------------------+---+ +(1 row) + +!ok + +# [CALCITE-846] Push aggregate with FILTER through UNION ALL +select deptno, count(*) filter (where job = 'CLERK') as cf, count(*) as c +from ( + select * from "scott".emp where deptno < 20 + union all + select * from "scott".emp where deptno > 20) +group by deptno; ++--------+----+---+ +| DEPTNO | CF | C | ++--------+----+---+ +| 10 | 1 | 3 | +| 30 | 1 | 6 | ++--------+----+---+ +(2 rows) + +!ok +EnumerableAggregate(group=[{0}], CF=[COUNT() FILTER $1], C=[COUNT()]) + EnumerableCalc(expr#0..1=[{inputs}], expr#2=['CLERK'], expr#3=[=($t0, $t2)], DEPTNO=[$t1], $f1=[$t3]) + EnumerableUnion(all=[true]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[<($t7, $t8)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t9]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[>($t7, $t8)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t9]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# [CALCITE-751] Aggregate join transpose +select count(*) +from "scott".emp join "scott".dept using (deptno); ++--------+ +| EXPR$0 | ++--------+ +| 14 | ++--------+ +(1 row) + +!ok +EnumerableAggregate(group=[{}], EXPR$0=[COUNT()]) + 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 + +# Push sum: splits into sum * count +select sum(sal) +from "scott".emp join "scott".dept using (deptno); ++----------+ +| EXPR$0 | ++----------+ +| 29025.00 | ++----------+ +(1 row) + +!ok +EnumerableAggregate(group=[{}], EXPR$0=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum; no aggregate needed after join +select sum(sal) +from "scott".emp join "scott".dept using (deptno) +group by emp.deptno, dept.deptno; ++----------+ +| EXPR$0 | ++----------+ +| 10875.00 | +| 8750.00 | +| 9400.00 | ++----------+ +(3 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t2]) + EnumerableAggregate(group=[{0, 3}], EXPR$0=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum; group by only one of the join keys +select sum(sal) +from "scott".emp join "scott".dept using (deptno) +group by emp.deptno; ++----------+ +| EXPR$0 | ++----------+ +| 10875.00 | +| 8750.00 | +| 9400.00 | ++----------+ +(3 rows) + +!ok +EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1]) + EnumerableAggregate(group=[{3}], EXPR$0=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push min; Join-Aggregate is optimized to SemiJoin +select min(sal) +from "scott".emp join "scott".dept using (deptno) +group by emp.deptno; ++---------+ +| EXPR$0 | ++---------+ +| 1300.00 | +| 800.00 | +| 950.00 | ++---------+ +(3 rows) + +!ok +EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1]) + EnumerableAggregate(group=[{3}], EXPR$0=[MIN($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum and count +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno); ++----+----------+ +| C | S | ++----+----------+ +| 14 | 29025.00 | ++----+----------+ +(1 row) + +!ok +EnumerableAggregate(group=[{}], C=[COUNT()], S=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum and count, group by join key +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno) group by emp.deptno; ++---+----------+ +| C | S | ++---+----------+ +| 3 | 8750.00 | +| 5 | 10875.00 | +| 6 | 9400.00 | ++---+----------+ +(3 rows) + +!ok +# No aggregate on top, because output of join is unique +EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) + EnumerableAggregate(group=[{3}], C=[COUNT()], S=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum and count, group by join key plus another column +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno) group by emp.job, dept.deptno; ++---+---------+ +| C | S | ++---+---------+ +| 1 | 1300.00 | +| 1 | 2450.00 | +| 1 | 2850.00 | +| 1 | 2975.00 | +| 1 | 5000.00 | +| 1 | 950.00 | +| 2 | 1900.00 | +| 2 | 6000.00 | +| 4 | 5600.00 | ++---+---------+ +(9 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], C=[$t2], S=[$t3]) + EnumerableAggregate(group=[{0, 2}], C=[COUNT()], S=[SUM($3)]) + EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum and count, group by non-join column +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno) group by emp.job; ++---+---------+ +| C | S | ++---+---------+ +| 1 | 5000.00 | +| 2 | 6000.00 | +| 3 | 8275.00 | +| 4 | 4150.00 | +| 4 | 5600.00 | ++---+---------+ +(5 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) + EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($3)]) + EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push count and sum, group by superset of join key +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno) group by emp.job, dept.deptno; ++---+---------+ +| C | S | ++---+---------+ +| 1 | 5000.00 | +| 2 | 6000.00 | +| 4 | 5600.00 | +| 1 | 1300.00 | +| 1 | 2450.00 | +| 1 | 2850.00 | +| 1 | 2975.00 | +| 1 | 950.00 | +| 2 | 1900.00 | ++---+---------+ +(9 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], C=[$t2], S=[$t3]) + EnumerableAggregate(group=[{0, 2}], C=[COUNT()], S=[SUM($3)]) + EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push count and sum, group by a column being aggregated +select count(*) as c, sum(sal) as s +from "scott".emp join "scott".dept using (deptno) group by emp.sal; ++---+---------+ +| C | S | ++---+---------+ +| 1 | 5000.00 | +| 2 | 6000.00 | +| 1 | 1100.00 | +| 1 | 1300.00 | +| 1 | 1500.00 | +| 1 | 1600.00 | +| 1 | 2450.00 | +| 1 | 2850.00 | +| 1 | 2975.00 | +| 1 | 800.00 | +| 1 | 950.00 | +| 2 | 2500.00 | ++---+---------+ +(12 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) + EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($2)]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Push sum, self-join, returning one row with a null value +select sum(e.sal) as s +from "scott".emp e join "scott".emp m on e.mgr = e.empno; ++---+ +| S | ++---+ +| | ++---+ +(1 row) + +!ok + +# Push sum, self-join +select sum(e.sal) as s +from "scott".emp e join "scott".emp m on e.mgr = m.empno; ++----------+ +| S | ++----------+ +| 24025.00 | ++----------+ +(1 row) + +!ok + +# Push sum, self-join, cartesian product over nullable and non-nullable columns +select sum(e.sal) as ss, count(e.sal) as cs, count(e.mgr) as cm +from "scott".emp e +join "scott".emp m on e.deptno = m.deptno +group by e.deptno, m.deptno; ++----------+----+----+ +| SS | CS | CM | ++----------+----+----+ +| 26250.00 | 9 | 6 | +| 54375.00 | 25 | 25 | +| 56400.00 | 36 | 36 | ++----------+----+----+ +(3 rows) + +!ok + +# Push sum, self-join, aggregate by column on "many" side +select sum(e.sal) as s +from "scott".emp e join "scott".emp m on e.mgr = m.empno +group by m.empno; ++---------+ +| S | ++---------+ +| 1100.00 | +| 1300.00 | +| 6000.00 | +| 6550.00 | +| 800.00 | +| 8275.00 | ++---------+ +(6 rows) + +!ok + +# Push sum, self-join, aggregate by column on "one" side. +# Note inflated totals due to cartesian product. +select sum(m.sal) as s +from "scott".emp e join "scott".emp m on e.mgr = m.empno +group by m.empno; ++----------+ +| S | ++----------+ +| 14250.00 | +| 15000.00 | +| 2450.00 | +| 3000.00 | +| 3000.00 | +| 5950.00 | ++----------+ +(6 rows) + +!ok + +# Collation of LogicalAggregate ([CALCITE-783] and [CALCITE-822]) +select sum(x) as sum_cnt, + count(distinct y) as cnt_dist +from + ( + select + count(*) as x, + t1.job as y, + t1.deptno as z + from + "scott".emp t1 + group by t1.job, t1.deptno + order by t1.job, t1.deptno +) sq(x,y,z) +group by z +order by sum_cnt; + ++---------+----------+ +| SUM_CNT | CNT_DIST | ++---------+----------+ +| 3 | 3 | +| 5 | 3 | +| 6 | 3 | ++---------+----------+ +(3 rows) + +!ok + +# [CALCITE-938] Aggregate row count +select empno, d.deptno +from "scott".emp +join (select distinct deptno from "scott".dept) d +using (deptno); ++-------+--------+ +| EMPNO | DEPTNO | ++-------+--------+ +| 7369 | 20 | +| 7499 | 30 | +| 7521 | 30 | +| 7566 | 20 | +| 7654 | 30 | +| 7698 | 30 | +| 7782 | 10 | +| 7788 | 20 | +| 7839 | 10 | +| 7844 | 30 | +| 7876 | 20 | +| 7900 | 30 | +| 7902 | 20 | +| 7934 | 10 | ++-------+--------+ +(14 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], EMPNO=[$t1], DEPTNO=[$t0]) + 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-729] IndexOutOfBoundsException in ROLLUP query on JDBC data source +!use jdbc_scott +select deptno, job, count(*) as c +from jdbc_scott.emp +group by rollup (deptno, job) +order by 1, 2; ++--------+-----------+----+ +| DEPTNO | JOB | C | ++--------+-----------+----+ +| 10 | CLERK | 1 | +| 10 | MANAGER | 1 | +| 10 | PRESIDENT | 1 | +| 10 | | 3 | +| 20 | ANALYST | 2 | +| 20 | CLERK | 2 | +| 20 | MANAGER | 1 | +| 20 | | 5 | +| 30 | CLERK | 1 | +| 30 | MANAGER | 1 | +| 30 | SALESMAN | 4 | +| 30 | | 6 | +| | | 14 | ++--------+-----------+----+ +(13 rows) + +!ok + +# [CALCITE-799] Incorrect result for "HAVING count(*) > 1" +select d.deptno, min(e.empid) as empid +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) +on e.deptno = d.deptno +group by d.deptno +having count(*) > 1; ++--------+-------+ +| DEPTNO | EMPID | ++--------+-------+ +| 1 | 100 | ++--------+-------+ +(1 row) + +!ok + +# Same, using USING (combining [CALCITE-799] and [CALCITE-801]) +select d.deptno, min(e.empid) as empid +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) +group by d.deptno +having count(*) > 1; ++--------+-------+ +| DEPTNO | EMPID | ++--------+-------+ +| 1 | 100 | ++--------+-------+ +(1 row) + +!ok + +# [CALCITE-886] System functions in the GROUP BY clause +# Calls to system functions do not have "()", which may confuse the validator. +select CURRENT_USER as CUSER +from jdbc_scott.emp +group by CURRENT_USER; ++-------+ +| CUSER | ++-------+ +| SCOTT | ++-------+ +(1 row) + +!ok + +# [CALCITE-886] System functions in the GROUP BY clause +# System function inside a GROUPING SETS. +select CURRENT_USER as CUSER +from jdbc_scott.emp +group by grouping sets(CURRENT_USER); ++-------+ +| CUSER | ++-------+ +| SCOTT | ++-------+ +(1 row) + +!ok + +# End agg.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/agg.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.oq b/core/src/test/resources/sql/agg.oq deleted file mode 100644 index 2dea732..0000000 --- a/core/src/test/resources/sql/agg.oq +++ /dev/null @@ -1,1347 +0,0 @@ -# agg.oq - Aggregate functions -# -# 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 - -# count(*) returns number of rows in table -select count(ename) as c from emp; -+---+ -| C | -+---+ -| 9 | -+---+ -(1 row) - -!ok - -# count of not-nullable column same as count(*) -select count(ename) as c from emp; -+---+ -| C | -+---+ -| 9 | -+---+ -(1 row) - -!ok - -# count of nullable column -select count(deptno) as c from emp; -+---+ -| C | -+---+ -| 8 | -+---+ -(1 row) - -!ok - -# composite count -select count(deptno, ename, 1, deptno) as c from emp; -+---+ -| C | -+---+ -| 8 | -+---+ -(1 row) - -!ok - -select city, gender as c from emps; -+---------------+---+ -| CITY | C | -+---------------+---+ -| Vancouver | F | -| San Francisco | M | -| | | -| Vancouver | M | -| | F | -+---------------+---+ -(5 rows) - -!ok - -# SELECT DISTINCT includes fully and partially null rows -select distinct city, gender from emps; -+---------------+--------+ -| CITY | GENDER | -+---------------+--------+ -| | | -| Vancouver | M | -| | F | -| San Francisco | M | -| Vancouver | F | -+---------------+--------+ -(5 rows) - -!ok - -# COUNT excludes fully or partially null rows -select count(city, gender) as c from emps; -+---+ -| C | -+---+ -| 3 | -+---+ -(1 row) - -!ok - -# COUNT-DISTINCT excludes fully or partially null rows -select count(distinct city, gender) as c from emps; -+---+ -| C | -+---+ -| 3 | -+---+ -(1 row) - -!ok - -select distinct mod(deptno, 20) as m, gender as c from emps; -+----+---+ -| M | C | -+----+---+ -| 0 | F | -| 10 | | -| 0 | M | -+----+---+ -(3 rows) - -!ok - -# Partially null row (10, NULL) is excluded from count. -select count(distinct mod(deptno, 20), gender) as c from emps; -+---+ -| C | -+---+ -| 2 | -+---+ -(1 row) - -!ok - -select count(mod(deptno, 20), gender) as c from emps; -+---+ -| C | -+---+ -| 4 | -+---+ -(1 row) - -!ok - -# Nulls in GROUP BY -select x = 1 as x1, count(*) as c -from (values 0, 1, 2, cast(null as integer)) as t(x) -group by x = 1; -X1 BOOLEAN(1) -C BIGINT(19) NOT NULL -!type -+-------+---+ -| X1 | C | -+-------+---+ -| false | 2 | -| true | 1 | -| | 1 | -+-------+---+ -(3 rows) - -!ok - -# Basic GROUPING SETS -select deptno, count(*) as c from emps group by grouping sets ((), (deptno)); -+--------+---+ -| DEPTNO | C | -+--------+---+ -| 10 | 1 | -| 20 | 2 | -| 40 | 2 | -| | 5 | -+--------+---+ -(4 rows) - -!ok - -# GROUPING SETS on expression -select deptno + 1, count(*) as c from emps group by grouping sets ((), (deptno + 1)); -+--------+---+ -| EXPR$0 | C | -+--------+---+ -| 11 | 1 | -| 21 | 2 | -| 41 | 2 | -| | 5 | -+--------+---+ -(4 rows) - -!ok - -# CUBE -select deptno + 1, count(*) as c from emp group by cube(deptno, gender); -+--------+---+ -| EXPR$0 | C | -+--------+---+ -| 11 | 1 | -| 11 | 1 | -| 11 | 2 | -| 21 | 1 | -| 21 | 1 | -| 31 | 2 | -| 31 | 2 | -| 51 | 1 | -| 51 | 1 | -| 51 | 2 | -| 61 | 1 | -| 61 | 1 | -| | 1 | -| | 1 | -| | 3 | -| | 6 | -| | 9 | -+--------+---+ -(17 rows) - -!ok - -# ROLLUP on 1 column -select deptno + 1, count(*) as c -from emp -group by rollup(deptno); -+--------+---+ -| EXPR$0 | C | -+--------+---+ -| 11 | 2 | -| 21 | 1 | -| 31 | 2 | -| 51 | 2 | -| 61 | 1 | -| | 1 | -| | 9 | -+--------+---+ -(7 rows) - -!ok - -# ROLLUP on 2 columns; project columns in different order -select gender, deptno + 1, count(*) as c -from emp -group by rollup(deptno, gender); -+--------+--------+---+ -| GENDER | EXPR$1 | C | -+--------+--------+---+ -| M | 21 | 1 | -| F | 11 | 1 | -| F | 31 | 2 | -| F | 51 | 1 | -| F | 61 | 1 | -| F | | 1 | -| M | 11 | 1 | -| M | 51 | 1 | -| | 11 | 2 | -| | 21 | 1 | -| | 31 | 2 | -| | 51 | 2 | -| | 61 | 1 | -| | | 1 | -| | | 9 | -+--------+--------+---+ -(15 rows) - -!ok - -# ROLLUP on column with nulls -# Note the two rows with NULL key (one represents ALL) -select gender, count(*) as c -from emp -group by rollup(gender); -+--------+---+ -| GENDER | C | -+--------+---+ -| F | 6 | -| M | 3 | -| | 9 | -+--------+---+ -(3 rows) - -!ok - -# ROLLUP plus ORDER BY -select gender, count(*) as c -from emp -group by rollup(gender) -order by c desc; -+--------+---+ -| GENDER | C | -+--------+---+ -| | 9 | -| F | 6 | -| M | 3 | -+--------+---+ -(3 rows) - -!ok - -# ROLLUP cartesian product -select deptno, count(*) as c -from emp -group by rollup(deptno), rollup(gender); -+--------+---+ -| DEPTNO | C | -+--------+---+ -| 10 | 1 | -| 10 | 1 | -| 20 | 1 | -| 20 | 1 | -| | 1 | -| 10 | 2 | -| 30 | 2 | -| 30 | 2 | -| 50 | 1 | -| 50 | 1 | -| 50 | 2 | -| 60 | 1 | -| 60 | 1 | -| | 1 | -| | 3 | -| | 6 | -| | 9 | -+--------+---+ -(17 rows) - -!ok - -# ROLLUP cartesian product of with tuple with expression -select deptno / 2 + 1 as half1, count(*) as c -from emp -group by rollup(deptno / 2, gender), rollup(substring(ename FROM 1 FOR 1)); -+-------+---+ -| HALF1 | C | -+-------+---+ -| 11 | 1 | -| 11 | 1 | -| 11 | 1 | -| 11 | 1 | -| 16 | 1 | -| 16 | 1 | -| 16 | 1 | -| 16 | 1 | -| 16 | 2 | -| 16 | 2 | -| 26 | 1 | -| 26 | 1 | -| 26 | 1 | -| 26 | 1 | -| 26 | 1 | -| 26 | 1 | -| 26 | 2 | -| 31 | 1 | -| 31 | 1 | -| 31 | 1 | -| 31 | 1 | -| 6 | 1 | -| 6 | 1 | -| 6 | 1 | -| 6 | 1 | -| 6 | 1 | -| 6 | 1 | -| 6 | 2 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 1 | -| | 2 | -| | 2 | -| | 9 | -+-------+---+ -(40 rows) - -!ok - -# ROLLUP with HAVING -select deptno + 1 as d1, count(*) as c -from emp -group by rollup(deptno) -having count(*) > 3; -+----+---+ -| D1 | C | -+----+---+ -| | 9 | -+----+---+ -(1 row) - -!ok - -# CUBE and DISTINCT -select distinct count(*) from emp group by cube(deptno, gender); -+--------+ -| EXPR$0 | -+--------+ -| 1 | -| 2 | -| 3 | -| 6 | -| 9 | -+--------+ -(5 rows) - -!ok - -# CUBE and JOIN -select e.deptno, e.gender, min(e.ename) as min_name -from emp as e join dept as d using (deptno) -group by cube(e.deptno, d.deptno, e.gender) -having count(*) > 2 or gender = 'M' and e.deptno = 10; -+--------+--------+----------+ -| DEPTNO | GENDER | MIN_NAME | -+--------+--------+----------+ -| 10 | M | Bob | -| 10 | M | Bob | -| | F | Alice | -| | | Alice | -+--------+--------+----------+ -(4 rows) - -!ok - -# GROUPING in SELECT clause of GROUP BY query -select count(*) as c, grouping(deptno) as g -from emp -group by deptno; -+---+---+ -| C | G | -+---+---+ -| 1 | 1 | -| 1 | 1 | -| 1 | 1 | -| 2 | 1 | -| 2 | 1 | -| 2 | 1 | -+---+---+ -(6 rows) - -!ok - -# GROUPING, GROUP_ID, GROUPING_ID in SELECT clause of GROUP BY query -select count(*) as c, - grouping(deptno) as g, - group_id() as gid, - grouping_id(deptno) as gd, - grouping_id(gender) as gg, - grouping_id(gender, deptno) as ggd, - grouping_id(gender, deptno) as gdg -from emp -group by deptno, gender; -+---+---+-----+----+----+-----+-----+ -| C | G | GID | GD | GG | GGD | GDG | -+---+---+-----+----+----+-----+-----+ -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 1 | 1 | 3 | 1 | 1 | 3 | 3 | -| 2 | 1 | 3 | 1 | 1 | 3 | 3 | -+---+---+-----+----+----+-----+-----+ -(8 rows) - -!ok - -# GROUPING in ORDER BY clause -select count(*) as c -from emp -group by deptno -order by grouping(deptno); -+---+ -| C | -+---+ -| 1 | -| 2 | -| 1 | -| 2 | -| 1 | -| 2 | -+---+ -(6 rows) - -!ok - -# Duplicate argument to GROUPING_ID. -select deptno, gender, grouping_id(deptno, gender, deptno), count(*) as c -from emp -where deptno = 10 -group by rollup(gender, deptno); -+--------+--------+--------+---+ -| DEPTNO | GENDER | EXPR$2 | C | -+--------+--------+--------+---+ -| 10 | F | 0 | 1 | -| 10 | M | 0 | 1 | -| | F | 5 | 1 | -| | M | 5 | 1 | -| | | 7 | 2 | -+--------+--------+--------+---+ -(5 rows) - -!ok - -# GROUPING in SELECT clause of ROLLUP query -select count(*) as c, deptno, grouping(deptno) as g -from emp -group by rollup(deptno); -+---+--------+---+ -| C | DEPTNO | G | -+---+--------+---+ -| 1 | 20 | 0 | -| 1 | 60 | 0 | -| 1 | | 0 | -| 2 | 10 | 0 | -| 2 | 30 | 0 | -| 2 | 50 | 0 | -| 9 | | 1 | -+---+--------+---+ -(7 rows) - -!ok - -# GROUPING, GROUPING_ID and GROUP_ID -select deptno, gender, grouping(deptno) gd, grouping(gender) gg, - grouping_id(deptno, gender) dg, grouping_id(gender, deptno) gd, - group_id() gid, count(*) c -from emp group by cube(deptno, gender); -+--------+--------+----+----+----+----+-----+---+ -| DEPTNO | GENDER | GD | GG | DG | GD | GID | C | -+--------+--------+----+----+----+----+-----+---+ -| 10 | F | 0 | 0 | 0 | 0 | 0 | 1 | -| 10 | M | 0 | 0 | 0 | 0 | 0 | 1 | -| 10 | | 0 | 1 | 1 | 2 | 1 | 2 | -| 20 | M | 0 | 0 | 0 | 0 | 0 | 1 | -| 20 | | 0 | 1 | 1 | 2 | 1 | 1 | -| 30 | F | 0 | 0 | 0 | 0 | 0 | 2 | -| 30 | | 0 | 1 | 1 | 2 | 1 | 2 | -| 50 | F | 0 | 0 | 0 | 0 | 0 | 1 | -| 50 | M | 0 | 0 | 0 | 0 | 0 | 1 | -| 50 | | 0 | 1 | 1 | 2 | 1 | 2 | -| 60 | F | 0 | 0 | 0 | 0 | 0 | 1 | -| 60 | | 0 | 1 | 1 | 2 | 1 | 1 | -| | F | 0 | 0 | 0 | 0 | 0 | 1 | -| | F | 1 | 0 | 2 | 1 | 2 | 6 | -| | M | 1 | 0 | 2 | 1 | 2 | 3 | -| | | 0 | 1 | 1 | 2 | 1 | 1 | -| | | 1 | 1 | 3 | 3 | 3 | 9 | -+--------+--------+----+----+----+----+-----+---+ -(17 rows) - -!ok - -!use scott - -# [KYLIN-751] Max on negative double values is not working -# [CALCITE-735] Primitive.DOUBLE.min should be large and negative -select max(v) as x, min(v) as n -from (values cast(-86.4 as double), cast(-100 as double)) as t(v); -+-------+--------+ -| X | N | -+-------+--------+ -| -86.4 | -100.0 | -+-------+--------+ -(1 row) - -!ok - -select max(v) as x, min(v) as n -from (values cast(-86.4 as double), cast(-100 as double), cast(2 as double)) as t(v); -+-----+--------+ -| X | N | -+-----+--------+ -| 2.0 | -100.0 | -+-----+--------+ -(1 row) - -!ok - -select max(v) as x, min(v) as n -from (values cast(-86.4 as float), cast(-100 as float)) as t(v); -+-------+--------+ -| X | N | -+-------+--------+ -| -86.4 | -100.0 | -+-------+--------+ -(1 row) - -!ok - -# COLLECT -select deptno, collect(empno) as empnos -from "scott".emp -group by deptno; -+--------+--------------------------------------+ -| DEPTNO | EMPNOS | -+--------+--------------------------------------+ -| 10 | [7782, 7839, 7934] | -| 20 | [7369, 7566, 7788, 7876, 7902] | -| 30 | [7499, 7521, 7654, 7698, 7844, 7900] | -+--------+--------------------------------------+ -(3 rows) - -!ok - -# COLLECT DISTINCT -# Disabled in JDK 1.7 because order of values is different -!if (jdk18) { -select deptno, collect(distinct job) as jobs -from "scott".emp -group by deptno; -+--------+-----------------------------+ -| DEPTNO | JOBS | -+--------+-----------------------------+ -| 10 | [MANAGER, CLERK, PRESIDENT] | -| 20 | [CLERK, ANALYST, MANAGER] | -| 30 | [SALESMAN, MANAGER, CLERK] | -+--------+-----------------------------+ -(3 rows) - -!ok -!} - -# COLLECT ... FILTER -select deptno, collect(empno) filter (where empno < 7550) as empnos -from "scott".emp -group by deptno; -+--------+--------------+ -| DEPTNO | EMPNOS | -+--------+--------------+ -| 10 | [] | -| 20 | [7369] | -| 30 | [7499, 7521] | -+--------+--------------+ -(3 rows) - -!ok - -# Aggregate FILTER -select deptno, - sum(sal) filter (where job = 'CLERK') c_sal, - sum(sal) filter (where job = 'CLERK' and deptno > 10) c10_sal, - max(sal) filter (where job = 'CLERK') as max_c, - min(sal) filter (where job = 'CLERK') as min_c, - max(sal) filter (where job = 'CLERK') - - min(sal) filter (where job = 'CLERK') as range_c, - max(sal) filter (where job = 'SALESMAN') - - min(sal) filter (where job = 'SALESMAN') as range_m -from "scott".emp -group by deptno; -+--------+---------+---------+---------+---------+---------+---------+ -| DEPTNO | C_SAL | C10_SAL | MAX_C | MIN_C | RANGE_C | RANGE_M | -+--------+---------+---------+---------+---------+---------+---------+ -| 10 | 1300.00 | | 1300.00 | 1300.00 | 0.00 | | -| 20 | 1900.00 | 1900.00 | 1100.00 | 800.00 | 300.00 | | -| 30 | 950.00 | 950.00 | 950.00 | 950.00 | 0.00 | 350.00 | -+--------+---------+---------+---------+---------+---------+---------+ -(3 rows) - -!ok - -# Aggregate FILTER on condition in GROUP BY -select deptno, - sum(sal) filter (where deptno = 10) sal_10 -from "scott".emp -group by deptno; -DEPTNO TINYINT(3) -SAL_10 DECIMAL(7, 2) -!type -+--------+---------+ -| DEPTNO | SAL_10 | -+--------+---------+ -| 10 | 8750.00 | -| 20 | | -| 30 | | -+--------+---------+ -(3 rows) - -!ok - -# Aggregate FILTER with HAVING -select deptno -from "scott".emp -group by deptno -having sum(sal) filter (where job = 'CLERK') > 1000; -+--------+ -| DEPTNO | -+--------+ -| 10 | -| 20 | -+--------+ -(2 rows) - -!ok - -# Aggregate FILTER with ORDER BY -select deptno -from "scott".emp -group by deptno -order by sum(sal) filter (where job = 'CLERK'); -+--------+ -| DEPTNO | -+--------+ -| 30 | -| 10 | -| 20 | -+--------+ -(3 rows) - -!ok - -# Aggregate FILTER with JOIN -select dept.deptno, - sum(sal) filter (where 1 < 2) as s, - sum(sal) as s1, - count(*) filter (where emp.ename < dept.dname) as c -from "scott".emp -join "scott".dept using (deptno) -group by dept.deptno; -+--------+----------+----------+---+ -| DEPTNO | S | S1 | C | -+--------+----------+----------+---+ -| 10 | 8750.00 | 8750.00 | 0 | -| 20 | 10875.00 | 10875.00 | 3 | -| 30 | 9400.00 | 9400.00 | 4 | -+--------+----------+----------+---+ -(3 rows) - -!ok - -# Aggregate FILTER with DISTINCT -select deptno, - count(distinct job) as cdj -from "scott".emp -group by deptno; -+--------+-----+ -| DEPTNO | CDJ | -+--------+-----+ -| 10 | 3 | -| 20 | 3 | -| 30 | 3 | -+--------+-----+ -(3 rows) - -!ok - -select deptno, - count(distinct job) filter (where job <> 'SALESMAN') as cdj -from "scott".emp -group by deptno; -+--------+-----+ -| DEPTNO | CDJ | -+--------+-----+ -| 10 | 3 | -| 20 | 3 | -| 30 | 2 | -+--------+-----+ -(3 rows) - -!ok - -# Multiple distinct count -select deptno, - count(distinct job) as j, count(distinct mgr) as m -from "scott".emp -group by deptno; -+--------+---+---+ -| DEPTNO | J | M | -+--------+---+---+ -| 10 | 3 | 2 | -| 20 | 3 | 4 | -| 30 | 3 | 2 | -+--------+---+---+ -(3 rows) - -!ok - -# Multiple distinct count and non-distinct aggregates -select deptno, - count(distinct job) as dj, - count(job) as j, - count(distinct mgr) as m, - sum(sal) as s -from "scott".emp -group by deptno; -+--------+----+---+---+----------+ -| DEPTNO | DJ | J | M | S | -+--------+----+---+---+----------+ -| 10 | 3 | 3 | 2 | 8750.00 | -| 20 | 3 | 5 | 4 | 10875.00 | -| 30 | 3 | 6 | 2 | 9400.00 | -+--------+----+---+---+----------+ -(3 rows) - -!ok - -# Multiple distinct count and non-distinct aggregates, no GROUP BY -select count(distinct job) as dj, - count(job) as j, - count(distinct mgr) as m, - sum(sal) as s -from "scott".emp; -+----+----+---+----------+ -| DJ | J | M | S | -+----+----+---+----------+ -| 5 | 14 | 6 | 29025.00 | -+----+----+---+----------+ -(1 row) - -!ok - -# [CALCITE-280] BigDecimal underflow -# Previously threw "java.lang.ArithmeticException: Non-terminating decimal -# expansion; no exact representable decimal result" -select avg(comm) as a, count(comm) as c from "scott".emp where empno < 7844; -+-------------------+---+ -| A | C | -+-------------------+---+ -| 733.3333333333333 | 3 | -+-------------------+---+ -(1 row) - -!ok - -# [CALCITE-846] Push aggregate with FILTER through UNION ALL -select deptno, count(*) filter (where job = 'CLERK') as cf, count(*) as c -from ( - select * from "scott".emp where deptno < 20 - union all - select * from "scott".emp where deptno > 20) -group by deptno; -+--------+----+---+ -| DEPTNO | CF | C | -+--------+----+---+ -| 10 | 1 | 3 | -| 30 | 1 | 6 | -+--------+----+---+ -(2 rows) - -!ok -EnumerableAggregate(group=[{0}], CF=[COUNT() FILTER $1], C=[COUNT()]) - EnumerableCalc(expr#0..1=[{inputs}], expr#2=['CLERK'], expr#3=[=($t0, $t2)], DEPTNO=[$t1], $f1=[$t3]) - EnumerableUnion(all=[true]) - EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[<($t7, $t8)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t9]) - EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..7=[{inputs}], expr#8=[20], expr#9=[>($t7, $t8)], JOB=[$t2], DEPTNO=[$t7], $condition=[$t9]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# [CALCITE-751] Aggregate join transpose -select count(*) -from "scott".emp join "scott".dept using (deptno); -+--------+ -| EXPR$0 | -+--------+ -| 14 | -+--------+ -(1 row) - -!ok -EnumerableAggregate(group=[{}], EXPR$0=[COUNT()]) - 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 - -# Push sum: splits into sum * count -select sum(sal) -from "scott".emp join "scott".dept using (deptno); -+----------+ -| EXPR$0 | -+----------+ -| 29025.00 | -+----------+ -(1 row) - -!ok -EnumerableAggregate(group=[{}], EXPR$0=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum; no aggregate needed after join -select sum(sal) -from "scott".emp join "scott".dept using (deptno) -group by emp.deptno, dept.deptno; -+----------+ -| EXPR$0 | -+----------+ -| 10875.00 | -| 8750.00 | -| 9400.00 | -+----------+ -(3 rows) - -!ok -EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t2]) - EnumerableAggregate(group=[{0, 3}], EXPR$0=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum; group by only one of the join keys -select sum(sal) -from "scott".emp join "scott".dept using (deptno) -group by emp.deptno; -+----------+ -| EXPR$0 | -+----------+ -| 10875.00 | -| 8750.00 | -| 9400.00 | -+----------+ -(3 rows) - -!ok -EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1]) - EnumerableAggregate(group=[{3}], EXPR$0=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push min; Join-Aggregate is optimized to SemiJoin -select min(sal) -from "scott".emp join "scott".dept using (deptno) -group by emp.deptno; -+---------+ -| EXPR$0 | -+---------+ -| 1300.00 | -| 800.00 | -| 950.00 | -+---------+ -(3 rows) - -!ok -EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1]) - EnumerableAggregate(group=[{3}], EXPR$0=[MIN($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum and count -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno); -+----+----------+ -| C | S | -+----+----------+ -| 14 | 29025.00 | -+----+----------+ -(1 row) - -!ok -EnumerableAggregate(group=[{}], C=[COUNT()], S=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum and count, group by join key -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno) group by emp.deptno; -+---+----------+ -| C | S | -+---+----------+ -| 3 | 8750.00 | -| 5 | 10875.00 | -| 6 | 9400.00 | -+---+----------+ -(3 rows) - -!ok -# No aggregate on top, because output of join is unique -EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) - EnumerableAggregate(group=[{3}], C=[COUNT()], S=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum and count, group by join key plus another column -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno) group by emp.job, dept.deptno; -+---+---------+ -| C | S | -+---+---------+ -| 1 | 1300.00 | -| 1 | 2450.00 | -| 1 | 2850.00 | -| 1 | 2975.00 | -| 1 | 5000.00 | -| 1 | 950.00 | -| 2 | 1900.00 | -| 2 | 6000.00 | -| 4 | 5600.00 | -+---+---------+ -(9 rows) - -!ok -EnumerableCalc(expr#0..3=[{inputs}], C=[$t2], S=[$t3]) - EnumerableAggregate(group=[{0, 2}], C=[COUNT()], S=[SUM($3)]) - EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum and count, group by non-join column -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno) group by emp.job; -+---+---------+ -| C | S | -+---+---------+ -| 1 | 5000.00 | -| 2 | 6000.00 | -| 3 | 8275.00 | -| 4 | 4150.00 | -| 4 | 5600.00 | -+---+---------+ -(5 rows) - -!ok -EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) - EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($3)]) - EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push count and sum, group by superset of join key -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno) group by emp.job, dept.deptno; -+---+---------+ -| C | S | -+---+---------+ -| 1 | 5000.00 | -| 2 | 6000.00 | -| 4 | 5600.00 | -| 1 | 1300.00 | -| 1 | 2450.00 | -| 1 | 2850.00 | -| 1 | 2975.00 | -| 1 | 950.00 | -| 2 | 1900.00 | -+---+---------+ -(9 rows) - -!ok -EnumerableCalc(expr#0..3=[{inputs}], C=[$t2], S=[$t3]) - EnumerableAggregate(group=[{0, 2}], C=[COUNT()], S=[SUM($3)]) - EnumerableJoin(condition=[=($0, $4)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push count and sum, group by a column being aggregated -select count(*) as c, sum(sal) as s -from "scott".emp join "scott".dept using (deptno) group by emp.sal; -+---+---------+ -| C | S | -+---+---------+ -| 1 | 5000.00 | -| 2 | 6000.00 | -| 1 | 1100.00 | -| 1 | 1300.00 | -| 1 | 1500.00 | -| 1 | 1600.00 | -| 1 | 2450.00 | -| 1 | 2850.00 | -| 1 | 2975.00 | -| 1 | 800.00 | -| 1 | 950.00 | -| 2 | 2500.00 | -+---+---------+ -(12 rows) - -!ok -EnumerableCalc(expr#0..2=[{inputs}], C=[$t1], S=[$t2]) - EnumerableAggregate(group=[{2}], C=[COUNT()], S=[SUM($2)]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) -!plan - -# Push sum, self-join, returning one row with a null value -select sum(e.sal) as s -from "scott".emp e join "scott".emp m on e.mgr = e.empno; -+---+ -| S | -+---+ -| | -+---+ -(1 row) - -!ok - -# Push sum, self-join -select sum(e.sal) as s -from "scott".emp e join "scott".emp m on e.mgr = m.empno; -+----------+ -| S | -+----------+ -| 24025.00 | -+----------+ -(1 row) - -!ok - -# Push sum, self-join, cartesian product over nullable and non-nullable columns -select sum(e.sal) as ss, count(e.sal) as cs, count(e.mgr) as cm -from "scott".emp e -join "scott".emp m on e.deptno = m.deptno -group by e.deptno, m.deptno; -+----------+----+----+ -| SS | CS | CM | -+----------+----+----+ -| 26250.00 | 9 | 6 | -| 54375.00 | 25 | 25 | -| 56400.00 | 36 | 36 | -+----------+----+----+ -(3 rows) - -!ok - -# Push sum, self-join, aggregate by column on "many" side -select sum(e.sal) as s -from "scott".emp e join "scott".emp m on e.mgr = m.empno -group by m.empno; -+---------+ -| S | -+---------+ -| 1100.00 | -| 1300.00 | -| 6000.00 | -| 6550.00 | -| 800.00 | -| 8275.00 | -+---------+ -(6 rows) - -!ok - -# Push sum, self-join, aggregate by column on "one" side. -# Note inflated totals due to cartesian product. -select sum(m.sal) as s -from "scott".emp e join "scott".emp m on e.mgr = m.empno -group by m.empno; -+----------+ -| S | -+----------+ -| 14250.00 | -| 15000.00 | -| 2450.00 | -| 3000.00 | -| 3000.00 | -| 5950.00 | -+----------+ -(6 rows) - -!ok - -# Collation of LogicalAggregate ([CALCITE-783] and [CALCITE-822]) -select sum(x) as sum_cnt, - count(distinct y) as cnt_dist -from - ( - select - count(*) as x, - t1.job as y, - t1.deptno as z - from - "scott".emp t1 - group by t1.job, t1.deptno - order by t1.job, t1.deptno -) sq(x,y,z) -group by z -order by sum_cnt; - -+---------+----------+ -| SUM_CNT | CNT_DIST | -+---------+----------+ -| 3 | 3 | -| 5 | 3 | -| 6 | 3 | -+---------+----------+ -(3 rows) - -!ok - -# [CALCITE-938] Aggregate row count -select empno, d.deptno -from "scott".emp -join (select distinct deptno from "scott".dept) d -using (deptno); -+-------+--------+ -| EMPNO | DEPTNO | -+-------+--------+ -| 7369 | 20 | -| 7499 | 30 | -| 7521 | 30 | -| 7566 | 20 | -| 7654 | 30 | -| 7698 | 30 | -| 7782 | 10 | -| 7788 | 20 | -| 7839 | 10 | -| 7844 | 30 | -| 7876 | 20 | -| 7900 | 30 | -| 7902 | 20 | -| 7934 | 10 | -+-------+--------+ -(14 rows) - -!ok -EnumerableCalc(expr#0..2=[{inputs}], EMPNO=[$t1], DEPTNO=[$t0]) - 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-729] IndexOutOfBoundsException in ROLLUP query on JDBC data source -!use jdbc_scott -select deptno, job, count(*) as c -from jdbc_scott.emp -group by rollup (deptno, job) -order by 1, 2; -+--------+-----------+----+ -| DEPTNO | JOB | C | -+--------+-----------+----+ -| 10 | CLERK | 1 | -| 10 | MANAGER | 1 | -| 10 | PRESIDENT | 1 | -| 10 | | 3 | -| 20 | ANALYST | 2 | -| 20 | CLERK | 2 | -| 20 | MANAGER | 1 | -| 20 | | 5 | -| 30 | CLERK | 1 | -| 30 | MANAGER | 1 | -| 30 | SALESMAN | 4 | -| 30 | | 6 | -| | | 14 | -+--------+-----------+----+ -(13 rows) - -!ok - -# [CALCITE-799] Incorrect result for "HAVING count(*) > 1" -select d.deptno, min(e.empid) as empid -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) -on e.deptno = d.deptno -group by d.deptno -having count(*) > 1; -+--------+-------+ -| DEPTNO | EMPID | -+--------+-------+ -| 1 | 100 | -+--------+-------+ -(1 row) - -!ok - -# Same, using USING (combining [CALCITE-799] and [CALCITE-801]) -select d.deptno, min(e.empid) as empid -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) -group by d.deptno -having count(*) > 1; -+--------+-------+ -| DEPTNO | EMPID | -+--------+-------+ -| 1 | 100 | -+--------+-------+ -(1 row) - -!ok - -# [CALCITE-886] System functions in the GROUP BY clause -# Calls to system functions do not have "()", which may confuse the validator. -select CURRENT_USER as CUSER -from jdbc_scott.emp -group by CURRENT_USER; -+-------+ -| CUSER | -+-------+ -| SCOTT | -+-------+ -(1 row) - -!ok - -# [CALCITE-886] System functions in the GROUP BY clause -# System function inside a GROUPING SETS. -select CURRENT_USER as CUSER -from jdbc_scott.emp -group by grouping sets(CURRENT_USER); -+-------+ -| CUSER | -+-------+ -| SCOTT | -+-------+ -(1 row) - -!ok - -# End agg.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/conditions.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/conditions.iq b/core/src/test/resources/sql/conditions.iq new file mode 100644 index 0000000..bc565af --- /dev/null +++ b/core/src/test/resources/sql/conditions.iq @@ -0,0 +1,261 @@ +# conditions.iq - conditions +# +# 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 catchall +!set outputformat mysql + +# OR test + +with tmp(a, b) as ( + values (1, 1), (1, 0), (1, cast(null as int)) + , (0, 1), (0, 0), (0, cast(null as int)) + , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) +select * + from tmp + where a = 1 or b = 1 + order by 1, 2; + ++---+---+ +| A | B | ++---+---+ +| 0 | 1 | +| 1 | 0 | +| 1 | 1 | +| 1 | | +| | 1 | ++---+---+ +(5 rows) + +!ok + +with tmp(a, b) as ( + values (1, 1), (1, 0), (1, cast(null as int)) + , (0, 1), (0, 0), (0, cast(null as int)) + , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) +select * + from tmp + where not (a = 1 or b = 1) + order by 1, 2; + ++---+---+ +| A | B | ++---+---+ +| 0 | 0 | ++---+---+ +(1 row) + +!ok + +# AND test + +with tmp(a, b) as ( + values (1, 1), (1, 0), (1, cast(null as int)) + , (0, 1), (0, 0), (0, cast(null as int)) + , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) +select * + from tmp + where a = 1 AND b = 1 + order by 1, 2; + ++---+---+ +| A | B | ++---+---+ +| 1 | 1 | ++---+---+ +(1 row) + +!ok + +with tmp(a, b) as ( + values (1, 1), (1, 0), (1, cast(null as int)) + , (0, 1), (0, 0), (0, cast(null as int)) + , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) +select * + from tmp + where not (a = 1 AND b = 1) + order by 1, 2; + ++---+---+ +| A | B | ++---+---+ +| 0 | 0 | +| 0 | 1 | +| 0 | | +| 1 | 0 | +| | 0 | ++---+---+ +(5 rows) + +!ok + +# Test cases for CALCITE-980 + +select "value" from "nullables" a where "value" = 'A' or "value" = 'B' order by 1; + ++-------+ +| value | ++-------+ +| A | +| B | ++-------+ +(2 rows) + +!ok + +select "value" from "nullables" a where not ("value" = 'A' or "value" = 'B') order by 1; + ++-------+ +| value | ++-------+ +| C | ++-------+ +(1 row) + +!ok + +select "value" from "nullables" a where not (not ("value" = 'A' or "value" = 'B')) order by 1; + ++-------+ +| value | ++-------+ +| A | +| B | ++-------+ +(2 rows) + +!ok + +select "value" from "nullables" a where "value" = 'A' and "value" = 'B' order by 1; + ++-------+ +| value | ++-------+ ++-------+ +(0 rows) + +!ok + +select "value" from "nullables" a where not ("value" = 'A' and "value" = 'B') order by 1; + ++-------+ +| value | ++-------+ +| A | +| B | +| C | ++-------+ +(3 rows) + +!ok + +select "value" from "nullables" a where not (not ("value" = 'A' and "value" = 'B')) order by 1; + ++-------+ +| value | ++-------+ ++-------+ +(0 rows) + +!ok + +select "value" from "nullables" a + where case when not ("value" = 'A' or "value" = 'B') then 1 else 0 end = 1 + order by 1; + ++-------+ +| value | ++-------+ +| C | ++-------+ +(1 row) + +!ok + +select "value" from "nullables" a + where + case when not ("value"='A' or "value"='B') + then + case when ("value"='A' or "value"='B') then 1 else 2 end + else 0 + end = 2 + order by 1; + ++-------+ +| value | ++-------+ +| C | ++-------+ +(1 row) + +!ok + +select "value" from "nullables" a + where + case when not ("value"='A' or "value"='B') + then + case when not /* <--diff from above */ ("value"='A' or "value"='B') then 1 else 2 end + else 0 + end = 1 /* <- diff from above*/ + order by 1; + ++-------+ +| value | ++-------+ +| C | ++-------+ +(1 row) + +!ok + +select "value" from "nullables" a + where + case when not ("value"='A' or "value"='B') + then + case when not ("value"='A' or "value"='B') then 1 else 2 end + else 0 + end = 0 /* <- diff from above*/ + order by 1; + ++-------+ +| value | ++-------+ +| A | +| B | +| | ++-------+ +(3 rows) + +!ok + +select "value" from "nullables" a + where + case when not ("value"='A' or "value"='B') + then + case when not ("value"='A' or "value"='B') then 1 else 2 end + else 0 + end = 2 /* <- diff from above*/ + order by 1; + ++-------+ +| value | ++-------+ ++-------+ +(0 rows) + +!ok + +# End conditions.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/conditions.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/conditions.oq b/core/src/test/resources/sql/conditions.oq deleted file mode 100644 index f7d7f51..0000000 --- a/core/src/test/resources/sql/conditions.oq +++ /dev/null @@ -1,259 +0,0 @@ -# conditions.oq - conditions -# -# 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 catchall -!set outputformat mysql - -# OR test - -with tmp(a, b) as ( - values (1, 1), (1, 0), (1, cast(null as int)) - , (0, 1), (0, 0), (0, cast(null as int)) - , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) -select * - from tmp - where a = 1 or b = 1 - order by 1, 2; - -+---+---+ -| A | B | -+---+---+ -| 0 | 1 | -| 1 | 0 | -| 1 | 1 | -| 1 | | -| | 1 | -+---+---+ -(5 rows) - -!ok - -with tmp(a, b) as ( - values (1, 1), (1, 0), (1, cast(null as int)) - , (0, 1), (0, 0), (0, cast(null as int)) - , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) -select * - from tmp - where not (a = 1 or b = 1) - order by 1, 2; - -+---+---+ -| A | B | -+---+---+ -| 0 | 0 | -+---+---+ -(1 row) - -!ok - -# AND test - -with tmp(a, b) as ( - values (1, 1), (1, 0), (1, cast(null as int)) - , (0, 1), (0, 0), (0, cast(null as int)) - , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) -select * - from tmp - where a = 1 AND b = 1 - order by 1, 2; - -+---+---+ -| A | B | -+---+---+ -| 1 | 1 | -+---+---+ -(1 row) - -!ok - -with tmp(a, b) as ( - values (1, 1), (1, 0), (1, cast(null as int)) - , (0, 1), (0, 0), (0, cast(null as int)) - , (cast(null as int), 1), (cast(null as int), 0), (cast(null as int), cast(null as int))) -select * - from tmp - where not (a = 1 AND b = 1) - order by 1, 2; - -+---+---+ -| A | B | -+---+---+ -| 0 | 0 | -| 0 | 1 | -| 0 | | -| 1 | 0 | -| | 0 | -+---+---+ -(5 rows) - -!ok - -# Test cases for CALCITE-980 - -select "value" from "nullables" a where "value" = 'A' or "value" = 'B' order by 1; - -+-------+ -| value | -+-------+ -| A | -| B | -+-------+ -(2 rows) - -!ok - -select "value" from "nullables" a where not ("value" = 'A' or "value" = 'B') order by 1; - -+-------+ -| value | -+-------+ -| C | -+-------+ -(1 row) - -!ok - -select "value" from "nullables" a where not (not ("value" = 'A' or "value" = 'B')) order by 1; - -+-------+ -| value | -+-------+ -| A | -| B | -+-------+ -(2 rows) - -!ok - -select "value" from "nullables" a where "value" = 'A' and "value" = 'B' order by 1; - -+-------+ -| value | -+-------+ -+-------+ -(0 rows) - -!ok - -select "value" from "nullables" a where not ("value" = 'A' and "value" = 'B') order by 1; - -+-------+ -| value | -+-------+ -| A | -| B | -| C | -+-------+ -(3 rows) - -!ok - -select "value" from "nullables" a where not (not ("value" = 'A' and "value" = 'B')) order by 1; - -+-------+ -| value | -+-------+ -+-------+ -(0 rows) - -!ok - -select "value" from "nullables" a - where case when not ("value" = 'A' or "value" = 'B') then 1 else 0 end = 1 - order by 1; - -+-------+ -| value | -+-------+ -| C | -+-------+ -(1 row) - -!ok - -select "value" from "nullables" a - where - case when not ("value"='A' or "value"='B') - then - case when ("value"='A' or "value"='B') then 1 else 2 end - else 0 - end = 2 - order by 1; - -+-------+ -| value | -+-------+ -| C | -+-------+ -(1 row) - -!ok - -select "value" from "nullables" a - where - case when not ("value"='A' or "value"='B') - then - case when not /* <--diff from above */ ("value"='A' or "value"='B') then 1 else 2 end - else 0 - end = 1 /* <- diff from above*/ - order by 1; - -+-------+ -| value | -+-------+ -| C | -+-------+ -(1 row) - -!ok - -select "value" from "nullables" a - where - case when not ("value"='A' or "value"='B') - then - case when not ("value"='A' or "value"='B') then 1 else 2 end - else 0 - end = 0 /* <- diff from above*/ - order by 1; - -+-------+ -| value | -+-------+ -| A | -| B | -| | -+-------+ -(3 rows) - -!ok - -select "value" from "nullables" a - where - case when not ("value"='A' or "value"='B') - then - case when not ("value"='A' or "value"='B') then 1 else 2 end - else 0 - end = 2 /* <- diff from above*/ - order by 1; - -+-------+ -| value | -+-------+ -+-------+ -(0 rows) - -!ok http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/dummy.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/dummy.iq b/core/src/test/resources/sql/dummy.iq new file mode 100644 index 0000000..166e1b0 --- /dev/null +++ b/core/src/test/resources/sql/dummy.iq @@ -0,0 +1,23 @@ +# dummy.iq - Scratch space +# +# 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 +values 1; +EXPR$0 +1 +!ok +# End dummy.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/dummy.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/dummy.oq b/core/src/test/resources/sql/dummy.oq deleted file mode 100644 index 10c37d5..0000000 --- a/core/src/test/resources/sql/dummy.oq +++ /dev/null @@ -1,23 +0,0 @@ -# dummy.oq - Scratch space -# -# 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 -values 1; -EXPR$0 -1 -!ok -# End dummy.oq
