You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/12/13 23:01:30 UTC
[4/7] calcite git commit: [CALCITE-1022] Rename ".oq" Quidem files to
".iq"
[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 <jh...@apache.org>
Authored: Sat Dec 12 10:35:22 2015 -0800
Committer: Julian Hyde <jh...@apache.org>
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