core/src/test/resources/sql/misc.iq

# misc.iq - Miscellaneous queries
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
!use post
!set outputformat mysql

# [CALCITE-7060] Enable dumping high-level plans in quidem tests
SELECT * FROM EMP WHERE DEPTNO > 20;
SELECT "EMP"."ENAME", "EMP"."DEPTNO", "EMP"."GENDER"
FROM "POST"."EMP" AS "EMP"
WHERE "EMP"."DEPTNO" > 20
!explain-validated-on Calcite

# [CALCITE-6751] Reduction of CAST from string to interval is incorrect
SELECT TIME '10:00:00' + CAST('1' AS INTERVAL SECOND);
+----------+
| EXPR$0   |
+----------+
| 10:00:01 |
+----------+
(1 row)

!ok

# Due to CALCITE-6752 the following test crashes:
# SELECT TIME '10:00:00' + CAST('1.1' AS INTERVAL SECOND);
# +------------+
# | EXPR$0     |
# +------------+
# | 11:00:00.1 |
# +------------+
# (1 row)
#
# !ok

SELECT TIME '10:00:00' + CAST('1' AS INTERVAL HOUR);
+----------+
| EXPR$0   |
+----------+
| 11:00:00 |
+----------+
(1 row)

!ok

SELECT TIME '10:00:00' + CAST('1' AS INTERVAL MINUTE);
+----------+
| EXPR$0   |
+----------+
| 10:01:00 |
+----------+
(1 row)

!ok

SELECT UUID '123e4567-e89b-12d3-a456-426655440000';
+--------------------------------------+
| EXPR$0                               |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
+--------------------------------------+
(1 row)

!ok

SELECT CAST('123e4567-e89b-12d3-a456-426655440000' AS UUID);
+--------------------------------------+
| EXPR$0                               |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
+--------------------------------------+
(1 row)

!ok

SELECT CAST('123e4567e89b12d3a456426655440000' AS UUID);
java.lang.IllegalArgumentException: Invalid UUID string: 123e4567e89b12d3a456426655440000
!error

SELECT CAST(UUID '123e4567-e89b-12d3-a456-426655440000' AS VARCHAR);
+--------------------------------------+
| EXPR$0                               |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
+--------------------------------------+
(1 row)

!ok

SELECT CAST(UUID '123e4567-e89b-12d3-a456-426655440000' AS VARBINARY);
+----------------------------------+
| EXPR$0                           |
+----------------------------------+
| 123e4567e89b12d3a456426655440000 |
+----------------------------------+
(1 row)

!ok

SELECT CAST(x'123e4567e89b12d3a456426655440000' AS UUID);
+--------------------------------------+
| EXPR$0                               |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426655440000 |
+--------------------------------------+
(1 row)

!ok

SELECT CAST(x'00' AS UUID);
java.lang.IllegalArgumentException: Need at least 16 bytes for UUID
!error

SELECT UUID '123e4567-e89b-12d3-a456-426655440000' = '123e4567-e89b-12d3-a456-426655440000';
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

SELECT CAST(NULL AS UUID);
+--------+
| EXPR$0 |
+--------+
|        |
+--------+
(1 row)

!ok

SELECT UUID NULL;
java.sql.SQLException: Error while executing SQL "SELECT UUID NULL": parse failed: Incorrect syntax near the keyword 'UUID' at line 1, column 8.
!error

# Compare varchar to number (uses an implicit cast from VARCHAR to INTEGER)
SELECT '1' > 0 AS C;
+------+
| C    |
+------+
| true |
+------+
(1 row)

!ok

# [CALCITE-6735] Type coercion for comparisons does not coerce ROW types
# These are compared in the same way as '1' > 0
SELECT ROW('1') > ROW(0) AS C;
+------+
| C    |
+------+
| true |
+------+
(1 row)

!ok

# [CALCITE-6735] Type coercion for comparisons does not coerce ROW types
# These are compared in the same way as x'10' > 0, which throws
SELECT ROW(x'10') > ROW(0) AS C;
java.sql.SQLException: Error while executing SQL "SELECT ROW(x'10') > ROW(0) AS C": From line 1, column 11 to line 1, column 26: Cannot apply '>' to arguments of type '<ROW(BINARY(1) EXPR$0)> > <ROW(INTEGER EXPR$0)>'.
!error

# [CALCITE-6733] Type inferred by coercion for comparisons with decimal is too narrow
SELECT ASCII('8') >= ABS(1.1806236821);
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

# [CALCITE-356] Allow column references of the form schema.table.column
select "hr"."emps"."empid"
from "hr"."emps";
+-------+
| empid |
+-------+
|   100 |
|   110 |
|   150 |
|   200 |
+-------+
(4 rows)

!ok

# [CALCITE-881] Allow schema.table.column references in GROUP BY
select "hr"."emps"."empid", count(*) as c
from "hr"."emps"
group by "hr"."emps"."empid";
+-------+---+
| empid | C |
+-------+---+
|   100 | 1 |
|   110 | 1 |
|   150 | 1 |
|   200 | 1 |
+-------+---+
(4 rows)

!ok

select distinct "hr"."emps"."empid" + 1 as e
from "hr"."emps"
group by "hr"."emps"."empid";
+-----+
| E   |
+-----+
| 101 |
| 111 |
| 151 |
| 201 |
+-----+
(4 rows)

!ok

# [CALCITE-4258] SqlToRelConverter: SELECT 1 IS [NOT] DISTINCT FROM NULL fails with AssertionError
SELECT 1 IS DISTINCT FROM NULL;
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

# [CALCITE-4258] SqlToRelConverter: SELECT 1 IS [NOT] DISTINCT FROM NULL fails with AssertionError
SELECT 1 IS NOT DISTINCT FROM NULL;
+--------+
| EXPR$0 |
+--------+
| false  |
+--------+
(1 row)

!ok

# Case-sensitive errors
select empid from "hr"."emps";
Column 'EMPID' not found in any table; did you mean 'empid'?
!error

select empid from "hr".emps;
Object 'EMPS' not found within 'hr'; did you mean 'emps'?
!error

select empid from hr.emps;
Object 'HR' not found; did you mean 'hr'?
!error

select empid from bad_schema.bad_table;
Object 'BAD_SCHEMA' not found
!error

select empid from bad_cat.bad_schema.bad_table;
Object 'BAD_CAT' not found
!error

select empid from "catalog".bad_schema.bad_table;
Object 'catalog' not found
!error

select empid from catalog.bad_schema.bad_table;
Object 'CATALOG' not found
!error

select empid from catalog.HR.bad_table;
Object 'CATALOG' not found
!error

select empid from catalog."hr".bad_table;
Object 'CATALOG' not found
!error

select empid from catalog."hr".emp;
Object 'CATALOG' not found
!error

select empid from HR.bad_table;
Object 'HR' not found; did you mean 'hr'?
!error

select empid from "HR".bad_table;
Object 'HR' not found; did you mean 'hr'?
!error

select empid from HR."emps";
Object 'HR' not found; did you mean 'hr'?
!error

select empid from "hr".bad_table;
Object 'BAD_TABLE' not found within 'hr'
!error

select empid from "hr".emps;
Object 'EMPS' not found within 'hr'; did you mean 'emps'?
!error

select empid from "hr";
Object 'hr' not found
!error

# [CALCITE-307] CAST(timestamp AS DATE) gives ClassCastException
# Based on [DRILL-1051]
with data(c_row, c_timestamp) as (select * from (values
    (1, TIMESTAMP '1997-01-02 03:04:05'),
    (2, TIMESTAMP '1997-01-02 00:00:00'),
    (3, TIMESTAMP '2001-09-22 18:19:20'),
    (4, TIMESTAMP '1997-02-10 17:32:01'),
    (5, TIMESTAMP '1997-02-10 17:32:00'),
    (6, TIMESTAMP '1997-02-11 17:32:01'),
    (7, TIMESTAMP '1997-02-12 17:32:01'),
    (8, TIMESTAMP '1997-02-13 17:32:01'),
    (9, TIMESTAMP '1997-02-14 17:32:01'),
    (10, TIMESTAMP '1997-02-15 17:32:01'),
    (11, TIMESTAMP '1997-02-16 17:32:01'),
    (13, TIMESTAMP '0097-02-16 17:32:01'),
    (14, TIMESTAMP '0597-02-16 17:32:01'),
    (15, TIMESTAMP '1097-02-16 17:32:01'),
    (16, TIMESTAMP '1697-02-16 17:32:01'),
    (17, TIMESTAMP '1797-02-16 17:32:01'),
    (18, TIMESTAMP '1897-02-16 17:32:01'),
    (19, TIMESTAMP '1997-02-16 17:32:01'),
    (20, TIMESTAMP '2097-02-16 17:32:01'),
    (21, TIMESTAMP '1996-02-28 17:32:01'),
    (22, TIMESTAMP '1996-02-29 17:32:01'),
    (23, TIMESTAMP '1996-03-01 17:32:01')))
select cast(c_timestamp as varchar(20)), cast(c_timestamp as date) from data where c_row <> 12;
+---------------------+------------+
| EXPR$0              | EXPR$1     |
+---------------------+------------+
| 1997-01-02 03:04:05 | 1997-01-02 |
| 1997-01-02 00:00:00 | 1997-01-02 |
| 2001-09-22 18:19:20 | 2001-09-22 |
| 1997-02-10 17:32:01 | 1997-02-10 |
| 1997-02-10 17:32:00 | 1997-02-10 |
| 1997-02-11 17:32:01 | 1997-02-11 |
| 1997-02-12 17:32:01 | 1997-02-12 |
| 1997-02-13 17:32:01 | 1997-02-13 |
| 1997-02-14 17:32:01 | 1997-02-14 |
| 1997-02-15 17:32:01 | 1997-02-15 |
| 1997-02-16 17:32:01 | 1997-02-16 |
| 1697-02-16 17:32:01 | 1697-02-16 |
| 1797-02-16 17:32:01 | 1797-02-16 |
| 1897-02-16 17:32:01 | 1897-02-16 |
| 1997-02-16 17:32:01 | 1997-02-16 |
| 2097-02-16 17:32:01 | 2097-02-16 |
| 1996-02-28 17:32:01 | 1996-02-28 |
| 1996-02-29 17:32:01 | 1996-02-29 |
| 1996-03-01 17:32:01 | 1996-03-01 |
| 0097-02-16 17:32:01 | 0097-02-16 |
| 0597-02-16 17:32:01 | 0597-02-16 |
| 1097-02-16 17:32:01 | 1097-02-16 |
+---------------------+------------+
(22 rows)

!ok

# [DRILL-1149]
select *, upper("name")
from "hr"."emps";
+-------+--------+-----------+---------+------------+-----------+
| empid | deptno | name      | salary  | commission | EXPR$5    |
+-------+--------+-----------+---------+------------+-----------+
|   100 |     10 | Bill      | 10000.0 |       1000 | BILL      |
|   110 |     10 | Theodore  | 11500.0 |        250 | THEODORE  |
|   150 |     10 | Sebastian |  7000.0 |            | SEBASTIAN |
|   200 |     20 | Eric      |  8000.0 |        500 | ERIC      |
+-------+--------+-----------+---------+------------+-----------+
(4 rows)

!ok

# [DRILL-1199] Order by nested inside a where clause fails
# (Not that it's right, but Tableau does it.)
select * from (select * from "hr"."emps" order by "empid") where (0=1);
+-------+--------+------+--------+------------+
| empid | deptno | name | salary | commission |
+-------+--------+------+--------+------------+
+-------+--------+------+--------+------------+
(0 rows)

!ok

# [DRILL-1842] Tableau, again
select count(distinct "salary") as c
from "hr"."emps"
join "hr"."depts" on "emps"."deptno" = "depts"."deptno"
having count(1) > 0;
+---+
| C |
+---+
| 3 |
+---+
(1 row)

!ok

# [CALCITE-340] SqlToRelConverter fails with complex join condition
select e."deptno", d."deptno"
from "hr"."emps" as e
join "hr"."depts" as d
on ( e."deptno" + 1 - 1 = d."deptno" + 2 - 2  and e."deptno" + 10 - 10 = d."deptno" + 20 - 20);
+--------+--------+
| deptno | deptno |
+--------+--------+
|     10 |     10 |
|     10 |     10 |
|     10 |     10 |
+--------+--------+
(3 rows)

!ok

# [CALCITE-340] SqlToRelConverter fails with complex join condition. Switch LHS and RHS.
select e."deptno", d."deptno"
from "hr"."emps" as e
join "hr"."depts" as d
on ( d."deptno" + 2 - 2 = e."deptno" + 1 - 1  and d."deptno" + 20 - 20 = e."deptno" + 10 - 10);
+--------+--------+
| deptno | deptno |
+--------+--------+
|     10 |     10 |
|     10 |     10 |
|     10 |     10 |
+--------+--------+
(3 rows)

!ok

# [CALCITE-340] SqlToRelConverter fails with complex join condition. Switch LHS and RHS.
select e."deptno", d."deptno"
from "hr"."emps" as e
join "hr"."depts" as d
on ( d."deptno" + 2 - 2 = e."deptno" + 1 - 1  and e."deptno" + 10 - 10 = d."deptno" + 20 - 20);
+--------+--------+
| deptno | deptno |
+--------+--------+
|     10 |     10 |
|     10 |     10 |
|     10 |     10 |
+--------+--------+
(3 rows)

!ok

# [CALCITE-377] Mixed equi and non-equi join
select e."empid", d."name", e."name"
from "hr"."emps" as e
join "hr"."depts" as d
on e."deptno" = d."deptno"
and e."name" <> d."name";
+-------+-------+-----------+
| empid | name  | name      |
+-------+-------+-----------+
|   100 | Sales | Bill      |
|   110 | Sales | Theodore  |
|   150 | Sales | Sebastian |
+-------+-------+-----------+
(3 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t4], name0=[$t2])
  EnumerableMergeJoin(condition=[AND(=($1, $3), <>(CAST($2):VARCHAR, CAST($4):VARCHAR))], joinType=[inner])
    EnumerableSort(sort0=[$1], dir0=[ASC])
      EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
        EnumerableTableScan(table=[[hr, emps]])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])
        EnumerableTableScan(table=[[hr, depts]])
!plan

# Same query, expressed using WHERE.
select e."empid", d."name", e."name"
from "hr"."emps" as e,
  "hr"."depts" as d
where e."deptno" = d."deptno"
and e."name" <> d."name";
+-------+-------+-----------+
| empid | name  | name      |
+-------+-------+-----------+
|   100 | Sales | Bill      |
|   110 | Sales | Theodore  |
|   150 | Sales | Sebastian |
+-------+-------+-----------+
(3 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t4], name0=[$t2])
  EnumerableMergeJoin(condition=[AND(=($1, $3), <>(CAST($2):VARCHAR, CAST($4):VARCHAR))], joinType=[inner])
    EnumerableSort(sort0=[$1], dir0=[ASC])
      EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
        EnumerableTableScan(table=[[hr, emps]])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])
        EnumerableTableScan(table=[[hr, depts]])
!plan

# Un-correlated EXISTS
select "deptno" from "hr"."depts"
where exists (select 1 from "hr"."emps");
+--------+
| deptno |
+--------+
|     10 |
|     30 |
|     40 |
+--------+
(3 rows)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
  EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
      EnumerableTableScan(table=[[hr, depts]])
    EnumerableAggregate(group=[{0}])
      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], i=[$t5])
        EnumerableTableScan(table=[[hr, emps]])
!plan
!}
!if (use_new_decorr) {
EnumerableNestedLoopJoin(condition=[true], joinType=[semi])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5])
    EnumerableTableScan(table=[[hr, emps]])
!plan
!}

# Un-correlated NOT EXISTS
select "deptno" from "hr"."depts"
where not exists (select 1 from "hr"."emps");
+--------+
| deptno |
+--------+
+--------+
(0 rows)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], deptno=[$t0], $condition=[$t2])
  EnumerableNestedLoopJoin(condition=[true], joinType=[left])
    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
      EnumerableTableScan(table=[[hr, depts]])
    EnumerableAggregate(group=[{0}])
      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], i=[$t5])
        EnumerableTableScan(table=[[hr, emps]])
!plan
!}
!if (use_new_decorr) {
EnumerableNestedLoopJoin(condition=[true], joinType=[anti])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5])
    EnumerableTableScan(table=[[hr, emps]])
!plan
!}

# Un-correlated EXISTS (table empty)
select "deptno" from "hr"."depts"
where exists (select 1 from "hr"."emps" where "empid" < 0);
+--------+
| deptno |
+--------+
+--------+
(0 rows)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t0])
  EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
      EnumerableTableScan(table=[[hr, depts]])
    EnumerableAggregate(group=[{0}])
      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[0], expr#8=[<($t6, $t7)], i=[$t5], $condition=[$t8])
        EnumerableTableScan(table=[[hr, emps]])
!plan
!}
!if (use_new_decorr) {
EnumerableNestedLoopJoin(condition=[true], joinType=[semi])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[0], expr#7=[<($t5, $t6)], empid=[$t0], $condition=[$t7])
    EnumerableTableScan(table=[[hr, emps]])
!plan
!}

# Un-correlated NOT EXISTS (table empty)
select "deptno" from "hr"."depts"
where not exists (select 1 from "hr"."emps" where "empid" < 0);
+--------+
| deptno |
+--------+
|     10 |
|     30 |
|     40 |
+--------+
(3 rows)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t1)], deptno=[$t0], $condition=[$t2])
  EnumerableNestedLoopJoin(condition=[true], joinType=[left])
    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
      EnumerableTableScan(table=[[hr, depts]])
    EnumerableAggregate(group=[{0}])
      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[CAST($t0):INTEGER NOT NULL], expr#7=[0], expr#8=[<($t6, $t7)], i=[$t5], $condition=[$t8])
        EnumerableTableScan(table=[[hr, emps]])
!plan
!}
!if (use_new_decorr) {
EnumerableNestedLoopJoin(condition=[true], joinType=[anti])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[0], expr#7=[<($t5, $t6)], empid=[$t0], $condition=[$t7])
    EnumerableTableScan(table=[[hr, emps]])
!plan
!}

# EXISTS
select * from "hr"."emps"
where exists (
  select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
+-------+--------+-----------+---------+------------+
| empid | deptno | name      | salary  | commission |
+-------+--------+-----------+---------+------------+
|   100 |     10 | Bill      | 10000.0 |       1000 |
|   110 |     10 | Theodore  | 11500.0 |        250 |
|   150 |     10 | Sebastian |  7000.0 |            |
+-------+--------+-----------+---------+------------+
(3 rows)

!ok
!if (use_old_decorr) {
EnumerableHashJoin(condition=[=($1, $5)], joinType=[semi])
  EnumerableTableScan(table=[[hr, emps]])
  EnumerableTableScan(table=[[hr, depts]])
!plan
!}
!if (use_new_decorr) {
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($1, $5)], joinType=[semi])
  EnumerableTableScan(table=[[hr, emps]])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
!plan
!}

# NOT EXISTS
# Right results, but it would be better if the plan used EnumerableCorrelateRel; see [CALCITE-374]
select * from "hr"."emps"
where not exists (
  select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
+-------+--------+------+--------+------------+
| empid | deptno | name | salary | commission |
+-------+--------+------+--------+------------+
|   200 |     20 | Eric | 8000.0 |        500 |
+-------+--------+------+--------+------------+
(1 row)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NULL($t6)], proj#0..4=[{exprs}], $condition=[$t7])
  EnumerableMergeJoin(condition=[=($1, $5)], joinType=[left])
    EnumerableSort(sort0=[$1], dir0=[ASC])
      EnumerableTableScan(table=[[hr, emps]])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
        EnumerableAggregate(group=[{0}])
          EnumerableTableScan(table=[[hr, depts]])
!plan
!}
!if (use_new_decorr) {
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($1, $5)], joinType=[anti])
  EnumerableTableScan(table=[[hr, emps]])
  EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
    EnumerableTableScan(table=[[hr, depts]])
!plan
!}

# NOT EXISTS .. OR NOT EXISTS
# Right results, but it would be better if the plan used EnumerableCorrelateRel; see [CALCITE-374]
select * from "hr"."emps"
where not exists (
  select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno")
or not exists (
  select 1 from "hr"."depts" where "depts"."deptno" + 90 = "emps"."empid");
+-------+--------+-----------+---------+------------+
| empid | deptno | name      | salary  | commission |
+-------+--------+-----------+---------+------------+
|   110 |     10 | Theodore  | 11500.0 |        250 |
|   150 |     10 | Sebastian |  7000.0 |            |
|   200 |     20 | Eric      |  8000.0 |        500 |
+-------+--------+-----------+---------+------------+
(3 rows)

!ok
!if (use_old_decorr) {
EnumerableCalc(expr#0..8=[{inputs}], expr#9=[IS NULL($t5)], expr#10=[IS NULL($t8)], expr#11=[OR($t9, $t10)], proj#0..4=[{exprs}], $condition=[$t11])
  EnumerableMergeJoin(condition=[=($6, $7)], joinType=[left])
    EnumerableSort(sort0=[$6], dir0=[ASC])
      EnumerableCalc(expr#0..6=[{inputs}], expr#7=[CAST($t0):INTEGER NOT NULL], proj#0..4=[{exprs}], i=[$t6], empid0=[$t7])
        EnumerableMergeJoin(condition=[=($1, $5)], joinType=[left])
          EnumerableSort(sort0=[$1], dir0=[ASC])
            EnumerableTableScan(table=[[hr, emps]])
          EnumerableSort(sort0=[$0], dir0=[ASC])
            EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
              EnumerableAggregate(group=[{0}])
                EnumerableTableScan(table=[[hr, depts]])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
        EnumerableAggregate(group=[{0}])
          EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], $f4=[$t5])
            EnumerableTableScan(table=[[hr, depts]])
!plan
!}
!if (use_new_decorr) {
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[NOT($t5)], expr#8=[NOT($t6)], expr#9=[OR($t7, $t8)], proj#0..4=[{exprs}], $condition=[$t9])
  EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($0, $7)], joinType=[left_mark])
    EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($1, $5)], joinType=[left_mark])
      EnumerableTableScan(table=[[hr, emps]])
      EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
        EnumerableTableScan(table=[[hr, depts]])
    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t2], empid=[$t0])
      EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])
        EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}])
          EnumerableAggregate(group=[{0}])
            EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($1, $2)], joinType=[left_mark])
              EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
                EnumerableTableScan(table=[[hr, emps]])
              EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
                EnumerableTableScan(table=[[hr, depts]])
        EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], deptno=[$t0], $f1=[$t5])
          EnumerableTableScan(table=[[hr, depts]])
!plan
!}

# Left join to a relation with one row is recognized as a trivial semi-join
# and eliminated.
select e."deptno"
from "hr"."emps" as e
left join (select count(*) from "hr"."depts") on true;
+--------+
| deptno |
+--------+
|     10 |
|     10 |
|     10 |
|     20 |
+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], deptno=[$t1])
  EnumerableTableScan(table=[[hr, emps]])
!plan

# Filter combined with an OR filter.
select * from (
  select * from "hr"."emps" as e
  where e."deptno" < 30) as e
where e."deptno" > 10 or e."name" = 'Sebastian';
+-------+--------+-----------+--------+------------+
| empid | deptno | name      | salary | commission |
+-------+--------+-----------+--------+------------+
|   150 |     10 | Sebastian | 7000.0 |            |
|   200 |     20 | Eric      | 8000.0 |        500 |
+-------+--------+-----------+--------+------------+
(2 rows)

!ok

# Filter combined with an AND filter. Test case for
# [CALCITE-389] MergeFilterRule should flatten AND condition
select * from (
  select * from "hr"."emps" as e
  where e."deptno" < 30) as e
where e."deptno" >= 10 and e."name" = 'Sebastian';
+-------+--------+-----------+--------+------------+
| empid | deptno | name      | salary | commission |
+-------+--------+-----------+--------+------------+
|   150 |     10 | Sebastian | 7000.0 |            |
+-------+--------+-----------+--------+------------+
(1 row)

!ok

# [CALCITE-393] If no fields are projected from a table, field trimmer should
# project a dummy expression
select 1 from "hr"."emps";
+--------+
| EXPR$0 |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1], EXPR$0=[$t5])
  EnumerableTableScan(table=[[hr, emps]])
!plan

# [CALCITE-393] for table scan under join
select count(*) as c from "hr"."emps", "hr"."depts";
+----+
| C  |
+----+
| 12 |
+----+
(1 row)

!ok
EnumerableAggregate(group=[{}], C=[COUNT()])
  EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
    EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5])
      EnumerableTableScan(table=[[hr, emps]])
    EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], DUMMY=[$t4])
      EnumerableTableScan(table=[[hr, depts]])
!plan

# [CALCITE-345] AssertionError in RexToLixTranslator comparing to date literal
!use catchall
select count(*) as c from "everyTypes" where "sqlDate" = DATE '1970-01-01';
+---+
| C |
+---+
| 1 |
+---+
(1 row)

!ok
select count(*) as c from "everyTypes" where "sqlDate" = DATE '1971-02-03';
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok
select count(*) as c from "everyTypes" where "sqlDate" > DATE '1970-01-01';
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok
select count(*) as c from "everyTypes" where "sqlTime" = TIME '01:23:45';
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok
select count(*) as c from "everyTypes" where "sqlTimestamp" = TIMESTAMP '1970-01-01 01:23:45';
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok
select count(*) as c from "everyTypes" where "utilDate" = TIMESTAMP '1970-01-01 01:23:45';
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok

# [CALCITE-715] Add PERIOD type constructor and period operators (CONTAINS, PRECEDES, etc.)
select period("sqlDate", INTERVAL '1' YEAR) as p
from "everyTypes";
+------------+
| P          |
+------------+
| {0, 12}    |
| {null, 12} |
+------------+
(2 rows)

!ok

!use foodmart

select count(*) as c
from "customer"
where period ("birthdate", DATE '1970-02-05') contains DATE '1964-01-01';
EnumerableAggregate(group=[{}], C=[COUNT()])
  EnumerableCalc(expr#0..28=[{inputs}], expr#29=[1964-01-01], expr#30=[<=($t16, $t29)], proj#0..28=[{exprs}], $condition=[$t30])
    EnumerableTableScan(table=[[foodmart2, customer]])
!plan
+------+
| C    |
+------+
| 7918 |
+------+
(1 row)

!ok

# [CALCITE-346] Add commutative join rule
#
# 3-way join that does not require bushy join.  Best plan is: sales_fact_1997 as
# left-most leaf, then customer (with filter), then product.
select *
from "sales_fact_1997" as s
  join "customer" as c on s."customer_id" = c."customer_id"
  join "product" as p on s."product_id" = p."product_id"
where c."city" = 'San Francisco';
EnumerableMergeJoin(condition=[=($0, $38)], joinType=[inner])
  EnumerableSort(sort0=[$0], dir0=[ASC])
    EnumerableMergeJoin(condition=[=($2, $8)], joinType=[inner])
      EnumerableSort(sort0=[$2], dir0=[ASC])
        EnumerableTableScan(table=[[foodmart2, sales_fact_1997]])
      EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco':VARCHAR(30)], expr#30=[=($t9, $t29)], proj#0..28=[{exprs}], $condition=[$t30])
        EnumerableTableScan(table=[[foodmart2, customer]])
  EnumerableTableScan(table=[[foodmart2, product]])
!plan

# 4-way join whose optimal plan requires bushy join.
#
# In the plan, note that filters on customer.city and product_department are
# pushed down. And the plan is a bushy join, with sub-joins (product_class,
# product) and (sales_fact_1997, customer).  However, scan(sales_fact_1997)
# should be left-most leaf, but is not because CommutativeJoinRule is currently
# disabled.
!use foodmart
select *
from "sales_fact_1997" as s
  join "customer" as c on s."customer_id" = c."customer_id"
  join "product" as p on s."product_id" = p."product_id"
  join "product_class" as pc on p."product_class_id" = pc."product_class_id"
where c."city" = 'San Francisco'
 and pc."product_department" = 'Snacks';
EnumerableCalc(expr#0..56=[{inputs}], product_id0=[$t20], time_id=[$t21], customer_id=[$t22], promotion_id=[$t23], store_id=[$t24], store_sales=[$t25], store_cost=[$t26], unit_sales=[$t27], customer_id0=[$t28], account_num=[$t29], lname=[$t30], fname=[$t31], mi=[$t32], address1=[$t33], address2=[$t34], address3=[$t35], address4=[$t36], city=[$t37], state_province=[$t38], postal_code=[$t39], country=[$t40], customer_region_id=[$t41], phone1=[$t42], phone2=[$t43], birthdate=[$t44], marital_status=[$t45], yearly_income=[$t46], gender=[$t47], total_children=[$t48], num_children_at_home=[$t49], education=[$t50], date_accnt_opened=[$t51], member_card=[$t52], occupation=[$t53], houseowner=[$t54], num_cars_owned=[$t55], fullname=[$t56], product_class_id0=[$t5], product_id=[$t6], brand_name=[$t7], product_name=[$t8], SKU=[$t9], SRP=[$t10], gross_weight=[$t11], net_weight=[$t12], recyclable_package=[$t13], low_fat=[$t14], units_per_case=[$t15], cases_per_pallet=[$t16], shelf_width=[$t17], shelf_height=[$t18], shelf_depth=[$t19], product_class_id=[$t0], product_subcategory=[$t1], product_category=[$t2], product_department=[$t3], product_family=[$t4])
  EnumerableHashJoin(condition=[=($6, $20)], joinType=[inner])
    EnumerableHashJoin(condition=[=($0, $5)], joinType=[inner])
      EnumerableCalc(expr#0..4=[{inputs}], expr#5=['Snacks':VARCHAR(30)], expr#6=[=($t3, $t5)], proj#0..4=[{exprs}], $condition=[$t6])
        EnumerableTableScan(table=[[foodmart2, product_class]])
      EnumerableTableScan(table=[[foodmart2, product]])
    EnumerableMergeJoin(condition=[=($2, $8)], joinType=[inner])
      EnumerableSort(sort0=[$2], dir0=[ASC])
        EnumerableTableScan(table=[[foodmart2, sales_fact_1997]])
      EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco':VARCHAR(30)], expr#30=[=($t9, $t29)], proj#0..28=[{exprs}], $condition=[$t30])
        EnumerableTableScan(table=[[foodmart2, customer]])
!plan

# Check that when filters are merged, duplicate conditions are eliminated.
select * from (
  select * from "days"
  where "day" = 1)
where "day" = 1;
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[=($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])
  EnumerableTableScan(table=[[foodmart2, days]])
!plan

# [HIVE-5873] Semi-join to count sub-query
# [CALCITE-365] AssertionError while translating query with WITH and correlated sub-query
with parts (PNum, OrderOnHand)
     as (select * from (values (3, 6), (10, 1), (8, 0)) as t(PNum, OrderOnHand)),
  supply (PNum, Qty)
     as (select * from (values (3, 4), (3, 2), (10, 1)))
select pnum
from parts p
where orderOnHand
     in (select count(*) from supply s
          where s.pnum = p.pnum);
+------+
| PNUM |
+------+
|    8 |
|   10 |
+------+
(2 rows)

!ok

# [HIVE-7362]
# Just checking that HAVING-EXISTS works.
with src (key, "value")
  as (select * from (values (1, 'a'), (2, 'z')) as t(key, "value"))
select b.key, count(*) as c
from src b
group by b.key
having exists
  (select a.key
  from src a
  where a.key = b.key and a."value" > 'val_9');
+-----+---+
| KEY | C |
+-----+---+
|   2 | 1 |
+-----+---+
(1 row)

!ok

# Having with correlation anded with normal condition.
with src (key, "value")
  as (select * from (values (1, 'a'), (2, 'z')) as t(key, "value"))
select b.key, count(*) as c
from src b
group by b.key
having exists
  (select a.key
  from src a
  where a.key = b.key and a."value" > 'val_9') and b.key > 0;
+-----+---+
| KEY | C |
+-----+---+
|   2 | 1 |
+-----+---+
(1 row)

!ok

# [CALCITE-6007] Sub-query that contains WITH and has no alias generates invalid SQL after expansion
SELECT a, b
FROM (
  WITH sub AS (SELECT 1 AS a, 2 AS b)
  SELECT * FROM sub)
WHERE a IS NOT NULL;
+---+---+
| A | B |
+---+---+
| 1 | 2 |
+---+---+
(1 row)

!ok

# Similar pattern to previous, without WITH
SELECT a, b
FROM (SELECT 1 AS a, 2 AS b)
WHERE a IS NOT NULL;
+---+---+
| A | B |
+---+---+
| 1 | 2 |
+---+---+
(1 row)

!ok

# [CALCITE-411] Duplicate aliases
select 1 as a, 2 as a from (values (true));
+---+---+
| A | A |
+---+---+
| 1 | 2 |
+---+---+
(1 row)

!ok

select "day", "day" from "days" where "day" < 3;
+-----+-----+
| day | day |
+-----+-----+
|   1 |   1 |
|   2 |   2 |
+-----+-----+
(2 rows)

!ok

# [DERBY-5313] CASE expression in GROUP BY clause
select case when a=1 then 1 else 2 end
from "days" t1(a,x) join "days" t2(b,x) on a=b
group by case when a=1 then 1 else 2 end;
+--------+
| EXPR$0 |
+--------+
|      1 |
|      2 |
+--------+
(2 rows)

!ok

# [DERBY-4450] GROUP BY in an IN-sub-query inside HAVING clause whose select
# list is subset of group by columns.
select sum("day") from "days" group by "week_day" having "week_day" in (
  select "week_day" from "days" group by "week_day", "day");
+--------+
| EXPR$0 |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
+--------+
(7 rows)

!ok

# [DERBY-4701] Aggregate function on a GROUP BY column also present in a HAVING
# clause
SELECT MAX("day") as m, COUNT(T."week_day") AS c
FROM "days" T
GROUP BY T."week_day"
HAVING COUNT(T."week_day") = 1;
+---+---+
| M | C |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
+---+---+
(7 rows)

!ok

# [DERBY-3616] Combinations of DISTINCT and GROUP BY
!use post
select distinct gender from emp group by gender;
+--------+
| GENDER |
+--------+
| F      |
| M      |
+--------+
(2 rows)

!ok
select distinct gender from emp group by gender, deptno;
+--------+
| GENDER |
+--------+
| F      |
| M      |
+--------+
(2 rows)

!ok
select gender, deptno from emp;
+--------+--------+
| GENDER | DEPTNO |
+--------+--------+
| F      |     10 |
| F      |     30 |
| F      |     30 |
| F      |     50 |
| F      |     60 |
| F      |        |
| M      |     10 |
| M      |     20 |
| M      |     50 |
+--------+--------+
(9 rows)

!ok
select distinct gender, deptno from emp group by gender, deptno, ename;
+--------+--------+
| GENDER | DEPTNO |
+--------+--------+
| F      |     10 |
| F      |     30 |
| F      |     50 |
| F      |     60 |
| F      |        |
| M      |     10 |
| M      |     20 |
| M      |     50 |
+--------+--------+
(8 rows)

!ok
select distinct gender, deptno from emp group by gender, deptno;
+--------+--------+
| GENDER | DEPTNO |
+--------+--------+
| F      |     10 |
| F      |     30 |
| F      |     50 |
| F      |     60 |
| F      |        |
| M      |     10 |
| M      |     20 |
| M      |     50 |
+--------+--------+
(8 rows)

!ok
select distinct gender, deptno from emp group by gender, ename, deptno;
+--------+--------+
| GENDER | DEPTNO |
+--------+--------+
| F      |     10 |
| F      |     30 |
| F      |     50 |
| F      |     60 |
| F      |        |
| M      |     10 |
| M      |     20 |
| M      |     50 |
+--------+--------+
(8 rows)

!ok
select distinct gender, sum(deptno) as s from emp group by gender, deptno;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 50 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(7 rows)

!ok
select gender, sum(deptno) as s from emp group by gender, deptno;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 50 |
| F      | 60 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(8 rows)

!ok
select gender, sum(deptno) as s from emp group by gender, ename;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 30 |
| F      | 30 |
| F      | 50 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(9 rows)

!ok
select distinct gender, sum(deptno) as s from emp group by gender, ename;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 30 |
| F      | 50 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(8 rows)

!ok
select gender, sum(deptno) as s from emp group by gender, deptno, ename;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 30 |
| F      | 30 |
| F      | 50 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(9 rows)

!ok
select distinct gender, sum(deptno) as s from emp group by gender, deptno, ename;
+--------+----+
| GENDER | S  |
+--------+----+
| F      | 10 |
| F      | 30 |
| F      | 50 |
| F      | 60 |
| F      |    |
| M      | 10 |
| M      | 20 |
| M      | 50 |
+--------+----+
(8 rows)

!ok
select distinct gender, sum(deptno) as s from emp group by gender;
+--------+-----+
| GENDER | S   |
+--------+-----+
| F      | 180 |
| M      |  80 |
+--------+-----+
(2 rows)

!ok

select distinct gender, deptno from emp group by gender;
Expression 'DEPTNO' is not being grouped
!error

select distinct gender, deptno from emp group by gender, ename;
Expression 'DEPTNO' is not being grouped
!error

select distinct gender, deptno, sum(deptno) as s from emp group by gender;
Expression 'DEPTNO' is not being grouped
!error

!use scott

# In Quidem's "scott" schema, we allow CREATE VIEW
create view v as
  select * from (values (1, 'a'), (2, 'b')) as t (x, y);
(0 rows modified)

!update

select count(*) as c
from v;
+---+
| C |
+---+
| 2 |
+---+
(1 row)

!ok

# ORDER BY expression with SELECT DISTINCT
select distinct deptno, job
from "scott".emp
order by substring(job from 2 for 1), -deptno;
+--------+-----------+
| DEPTNO | JOB       |
+--------+-----------+
|     30 | SALESMAN  |
|     30 | MANAGER   |
|     20 | MANAGER   |
|     10 | MANAGER   |
|     30 | CLERK     |
|     20 | CLERK     |
|     10 | CLERK     |
|     20 | ANALYST   |
|     10 | PRESIDENT |
+--------+-----------+
(9 rows)

!ok

# [CALCITE-2180] Invalid code generated for negative of byte and short values
select -deptno as d
from "scott".dept;
+-----+
| D   |
+-----+
| -40 |
| -30 |
| -20 |
| -10 |
+-----+
(4 rows)

!ok

# [CALCITE-2099] Incorrect code generated for UNION
select count(*) as c from "scott".emp group by deptno
union
select count(*) as c from "scott".emp group by deptno;
+---+
| C |
+---+
| 3 |
| 5 |
| 6 |
+---+
(3 rows)

!ok

# Similar
select count(*) as c from "scott".emp group by job
union all
select count(*) as c from "scott".dept group by deptno;
+---+
| C |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 3 |
| 4 |
| 4 |
+---+
(9 rows)

!ok

# [CALCITE-1864] Allow NULL literal as argument
select count(*) as c from "scott".emp where empno > null;
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok

# [CALCITE-613] Implicitly convert strings in comparisons
select * from "scott".emp where hiredate < '1981-01-02';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
(1 row)

!ok
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1981-01-02], expr#9=[<($t4, $t8)], proj#0..7=[{exprs}], $condition=[$t9])
  EnumerableTableScan(table=[[scott, EMP]])
!plan
select * from "scott".emp where '1981-01-02' > hiredate;
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
(1 row)

!ok
select * from "scott".emp where hiredate between '1981-01-02' and '1981-06-01';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-02-04 | 2975.00 |        |     20 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-01-05 | 2850.00 |        |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
(4 rows)

!ok
select * from "scott".emp where hiredate > '1986-01-02';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 |      |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 |      |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
(2 rows)

!ok
select * from "scott".emp where '1986-01-02' < hiredate;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 |      |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 |      |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
(2 rows)

!ok
select * from "scott".emp where '1986-' || '01-02' < hiredate;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 |      |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 |      |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
(2 rows)

!ok
select * from "scott".emp where sal < '1100';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20 |
|  7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 |      |     30 |
+-------+-------+-------+------+------------+--------+------+--------+
(2 rows)

!ok
select * from "scott".emp where empno in ('7369', '7876');
+-------+-------+-------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 |  800.00 |      |     20 |
|  7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 |      |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
(2 rows)

!ok
select * from "scott".emp where empno between '7500' and '07600';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-02-04 | 2975.00 |        |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
(2 rows)

!ok

# BETWEEN follows the PostgreSQL style to coerce STRING operand to type of the other
# NUMERIC operands, see TypeCoercionImpl#commonTypeForBinaryComparison.
select * from "scott".emp where deptno between '7369' and '7876';
Caused by: java.lang.NumberFormatException: Value out of range. Value:"7369" Radix:10
!error

select * from "scott".emp where '7369' between empno and '7876';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
(1 row)

!ok

# [CALCITE-546] Allow table, column and field called "*"
# See [DRILL-3859], [DRILL-3860].
SELECT * FROM (VALUES (0, 0)) AS T(A, "*");
+---+---+
| A | * |
+---+---+
| 0 | 0 |
+---+---+
(1 row)

!ok

SELECT a FROM (VALUES (0, 0)) AS T(A, "*");
+---+
| A |
+---+
| 0 |
+---+
(1 row)

!ok

SELECT b FROM (VALUES (0, 0)) AS T(A, "*");
Column 'B' not found in any table
!error

# See [DRILL-3860].
SELECT "a" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
+---+
| a |
+---+
| 1 |
+---+
(1 row)

!ok

SELECT "A" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
+---+
| A |
+---+
| 2 |
+---+
(1 row)

!ok

SELECT "." FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
+---+
| . |
+---+
| 3 |
+---+
(1 row)

!ok

SELECT "*" FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
+---+
| * |
+---+
| 4 |
+---+
(1 row)

!ok

SELECT * FROM (VALUES (1, 2, 3, 4)) AS T("a", "A", ".", "*");
+---+---+---+---+
| a | A | . | * |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
(1 row)

!ok

# Implicit ROW
select deptno, (empno, deptno) as r
from "scott".emp;
+--------+------------+
| DEPTNO | R          |
+--------+------------+
|     10 | {7782, 10} |
|     10 | {7839, 10} |
|     10 | {7934, 10} |
|     20 | {7369, 20} |
|     20 | {7566, 20} |
|     20 | {7788, 20} |
|     20 | {7876, 20} |
|     20 | {7902, 20} |
|     30 | {7499, 30} |
|     30 | {7521, 30} |
|     30 | {7654, 30} |
|     30 | {7698, 30} |
|     30 | {7844, 30} |
|     30 | {7900, 30} |
+--------+------------+
(14 rows)

!ok

# Explicit ROW
select deptno, row (empno, deptno) as r
from "scott".emp;
+--------+------------+
| DEPTNO | R          |
+--------+------------+
|     10 | {7782, 10} |
|     10 | {7839, 10} |
|     10 | {7934, 10} |
|     20 | {7369, 20} |
|     20 | {7566, 20} |
|     20 | {7788, 20} |
|     20 | {7876, 20} |
|     20 | {7902, 20} |
|     30 | {7499, 30} |
|     30 | {7521, 30} |
|     30 | {7654, 30} |
|     30 | {7698, 30} |
|     30 | {7844, 30} |
|     30 | {7900, 30} |
+--------+------------+
(14 rows)

!ok

# [CALCITE-5960] CAST failed if SqlTypeFamily of targetType is NULL
# Cast row
SELECT cast(row(1, 2) as row(a integer, b tinyint)) as r;
+--------+
| R      |
+--------+
| {1, 2} |
+--------+
(1 row)

!ok

# [CALCITE-877] Allow ROW as argument to COLLECT
select deptno, collect(r) as empnos
from (select deptno, (empno, deptno) as r
  from "scott".emp)
group by deptno;
+--------+--------------------------------------------------------------------------+
| DEPTNO | EMPNOS                                                                   |
+--------+--------------------------------------------------------------------------+
|     10 | [{7782, 10}, {7839, 10}, {7934, 10}]                                     |
|     20 | [{7369, 20}, {7566, 20}, {7788, 20}, {7876, 20}, {7902, 20}]             |
|     30 | [{7499, 30}, {7521, 30}, {7654, 30}, {7698, 30}, {7844, 30}, {7900, 30}] |
+--------+--------------------------------------------------------------------------+
(3 rows)

!ok

# [CALCITE-922] Value of INTERVAL literal
select deptno * interval '2' day as d2,
 deptno * interval -'3' hour as h3,
 deptno * interval -'-4' hour as h4,
 deptno * interval -'4:30' hour to minute as h4_5,
 deptno * interval -'-1-3' year to month as y1_25
from "scott".dept;
+-----+------+------+---------+--------+
| D2  | H3   | H4   | H4_5    | Y1_25  |
+-----+------+------+---------+--------+
| +20 | -30  | +40  | -45:00  | +12-06 |
| +40 | -60  | +80  | -90:00  | +25-00 |
| +60 | -90  | +120 | -135:00 | +37-06 |
| +80 | -120 | +160 | -180:00 | +50-00 |
+-----+------+------+---------+--------+
(4 rows)

!ok

# [CALCITE-4091] Interval expressions
select empno, mgr, date '1970-01-01' + interval empno day as d,
  timestamp '1970-01-01 00:00:00' + interval (mgr / 100) minute as ts
from "scott".emp
order by empno;
+-------+------+------------+---------------------+
| EMPNO | MGR  | D          | TS                  |
+-------+------+------------+---------------------+
|  7369 | 7902 | 1990-03-06 | 1970-01-01 01:19:00 |
|  7499 | 7698 | 1990-07-14 | 1970-01-01 01:16:00 |
|  7521 | 7698 | 1990-08-05 | 1970-01-01 01:16:00 |
|  7566 | 7839 | 1990-09-19 | 1970-01-01 01:18:00 |
|  7654 | 7698 | 1990-12-16 | 1970-01-01 01:16:00 |
|  7698 | 7839 | 1991-01-29 | 1970-01-01 01:18:00 |
|  7782 | 7839 | 1991-04-23 | 1970-01-01 01:18:00 |
|  7788 | 7566 | 1991-04-29 | 1970-01-01 01:15:00 |
|  7839 |      | 1991-06-19 |                     |
|  7844 | 7698 | 1991-06-24 | 1970-01-01 01:16:00 |
|  7876 | 7788 | 1991-07-26 | 1970-01-01 01:17:00 |
|  7900 | 7698 | 1991-08-19 | 1970-01-01 01:16:00 |
|  7902 | 7566 | 1991-08-21 | 1970-01-01 01:15:00 |
|  7934 | 7782 | 1991-09-22 | 1970-01-01 01:17:00 |
+-------+------+------------+---------------------+
(14 rows)

!ok

# [CALCITE-6581] INTERVAL with WEEK and QUARTER
select timestamp '1970-01-01' + interval '2' week as w,
  timestamp '1970-01-01 00:00:00' + interval '2' quarter as q;
+---------------------+---------------------+
| W                   | Q                   |
+---------------------+---------------------+
| 1970-01-15 00:00:00 | 1970-07-01 00:00:00 |
+---------------------+---------------------+
(1 row)

!ok

# [CALCITE-1486] Invalid "Invalid literal" error for complex expression
select 8388608/(60+27.39);
+-------------------+
| EXPR$0            |
+-------------------+
| 95990.47945989244 |
+-------------------+
(1 row)

!ok

# [CALCITE-1120] Support SELECT without FROM
select 2 + 2;
+--------+
| EXPR$0 |
+--------+
|      4 |
+--------+
(1 row)

!ok

select 2 + 2 as a, 'b' as b;
+---+---+
| A | B |
+---+---+
| 4 | b |
+---+---+
(1 row)

!ok

select distinct 2 as a, 'b' as b;
+---+---+
| A | B |
+---+---+
| 2 | b |
+---+---+
(1 row)

!ok

select distinct 2 as a, 'b' as b
order by 2;
+---+---+
| A | B |
+---+---+
| 2 | b |
+---+---+
(1 row)

!ok

select distinct 2 as a, 'b' as b
union all
select 3, 'c';
+---+---+
| A | B |
+---+---+
| 2 | b |
| 3 | c |
+---+---+
(2 rows)

!ok

# Composite UNION
select deptno, deptno from "scott".emp
union
select deptno, deptno from "scott".dept;
+--------+--------+
| DEPTNO | DEPTNO |
+--------+--------+
|     10 |     10 |
|     20 |     20 |
|     30 |     30 |
|     40 |     40 |
+--------+--------+
(4 rows)

!ok

# Composite MINUS
select deptno, deptno from "scott".emp
except
select deptno, deptno from "scott".dept where deptno = 10;
+--------+--------+
| DEPTNO | DEPTNO |
+--------+--------+
|     20 |     20 |
|     30 |     30 |
+--------+--------+
(2 rows)

!ok

select * from (
  select 2 as a, 3 as b)
where a < b;
+---+---+
| A | B |
+---+---+
| 2 | 3 |
+---+---+
(1 row)

!ok

select * from (
  select distinct 2 as a, 3 as b)
where a > b;
+---+---+
| A | B |
+---+---+
+---+---+
(0 rows)

!ok

select count(*) as c from "scott".dept where exists (select 1);
+---+
| C |
+---+
| 4 |
+---+
(1 row)

!ok

# INTERSECT
select deptno from "scott".emp
intersect
select deptno from "scott".dept;
+--------+
| DEPTNO |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
(3 rows)

!ok

# INTERSECT DISTINCT means the same as INTERSECT
select deptno from "scott".emp
intersect distinct
select deptno from "scott".dept;
+--------+
| DEPTNO |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
(3 rows)

!ok

# INTERSECT can return NULL values
select comm from "scott".emp where mgr = 7698
intersect
select comm from "scott".emp where deptno = 10;
+------+
| COMM |
+------+
|      |
+------+
(1 row)

!ok

# As above, composite
select job, comm from "scott".emp where mgr = 7698
intersect
select job, comm from "scott".emp where deptno = 10;
+-------+------+
| JOB   | COMM |
+-------+------+
| CLERK |      |
+-------+------+
(1 row)

!ok

# Make sure that IN does not compare the shorter of two strings.
# If it did, 'MARTIN' would be emitted.
select ename, deptno
from "scott".emp
where ename in ('MARTINGALE', 'WARD');
+-------+--------+
| ENAME | DEPTNO |
+-------+--------+
| WARD  |     30 |
+-------+--------+
(1 row)

!ok

# [DRILL-4906]
select (case when (true) then 1 end) from (values(1));
+--------+
| EXPR$0 |
+--------+
|      1 |
+--------+
(1 row)

!ok
EXPR$0 INTEGER(10)
!type

# RAND_INTEGER with seed
select i, rand_integer(1, 5) as r
from (values 1, 2, 3, 4, 5) as t(i);
+---+---+
| I | R |
+---+---+
| 1 | 0 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+---+---+
(5 rows)

!ok

# Same query, should yield same results
select i, rand_integer(1, 5) as r
from (values 1, 2, 3, 4, 5) as t(i);
+---+---+
| I | R |
+---+---+
| 1 | 0 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+---+---+
(5 rows)

!ok

# Same query with different seed
select i, rand_integer(-1, 5) as r
from (values 1, 2, 3, 4, 5) as t(i);
+---+---+
| I | R |
+---+---+
| 1 | 3 |
| 2 | 0 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
+---+---+
(5 rows)

!ok

# Same query with different bound
select i, rand_integer(-1, 2) as r
from (values 1, 2, 3, 4, 5) as t(i);
+---+---+
| I | R |
+---+---+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
+---+---+
(5 rows)

!ok

# RAND with seed
select i, rand(-1) as r
from (values 1, 2, 3, 4, 5) as t(i);
+---+---------------------+
| I | R                   |
+---+---------------------+
| 1 | 0.03305388522187047 |
| 2 |  0.6573104025344794 |
| 3 |  0.7450920948729041 |
| 4 |  0.6624972807480889 |
| 5 |  0.5532616835728703 |
+---+---------------------+
(5 rows)

!ok

# PI function
values pi;
+-------------------+
| PI                |
+-------------------+
| 3.141592653589793 |
+-------------------+
(1 row)

!ok

values pi();
+-------------------+
| EXPR$0            |
+-------------------+
| 3.141592653589793 |
+-------------------+
(1 row)

!ok

# DEGREES function
values (degrees(pi), degrees(-pi / 2));
+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
|  180.0 |  -90.0 |
+--------+--------+
(1 row)

!ok

# [CALCITE-1592] SqlToRelConverter throws UnsupportedOperationException if query has NOT ... NOT IN
select * from "scott".emp where not (ename not in ('Fred'));
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----+-----+----------+-----+------+--------+
+-------+-------+-----+-----+----------+-----+------+--------+
(0 rows)

!ok

select count(*) as c from "scott".emp where not (ename in ('Fred'));
+----+
| C  |
+----+
| 14 |
+----+
(1 row)

!ok

# [CALCITE-1638] Simplify "$x = $x" to "$x is not null"
select count(*) as c from "scott".emp where deptno > deptno;
+---+
| C |
+---+
| 0 |
+---+
(1 row)

!ok
EnumerableAggregate(group=[{}], C=[COUNT()])
  EnumerableValues(tuples=[[]])
!plan

# [CALCITE-1659] Simplifying CAST('YYYY-MM-DD hh:mm:ss.SSS' as TIMESTAMP)
# should round the sub-second fraction
select TIMESTAMP '2016-02-26 19:06:00.123456789',
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(0)),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(1)),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(2)),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(3)),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(6)),
  CAST('2016-02-26 19:06:00.123' as TIMESTAMP(9));
+-------------------------+---------------------+---------------------+-----------------------+------------------------+-------------------------+-------------------------+-------------------------+
| EXPR$0                  | EXPR$1              | EXPR$2              | EXPR$3                | EXPR$4                 | EXPR$5                  | EXPR$6                  | EXPR$7                  |
+-------------------------+---------------------+---------------------+-----------------------+------------------------+-------------------------+-------------------------+-------------------------+
| 2016-02-26 19:06:00.123 | 2016-02-26 19:06:00 | 2016-02-26 19:06:00 | 2016-02-26 19:06:00.1 | 2016-02-26 19:06:00.12 | 2016-02-26 19:06:00.123 | 2016-02-26 19:06:00.123 | 2016-02-26 19:06:00.123 |
+-------------------------+---------------------+---------------------+-----------------------+------------------------+-------------------------+-------------------------+-------------------------+
(1 row)

!ok
EnumerableValues(tuples=[[{ 2016-02-26 19:06:00.123, 2016-02-26 19:06:00, 2016-02-26 19:06:00, 2016-02-26 19:06:00.1, 2016-02-26 19:06:00.12, 2016-02-26 19:06:00.123, 2016-02-26 19:06:00.123, 2016-02-26 19:06:00.123 }]])
!plan

# [CALCITE-1664] CAST('<string>' as TIMESTAMP) adds part of sub-second fraction to the value
select
  TIMESTAMP '2016-02-26 19:06:00.12345678',
  CAST('2016-02-26 19:06:00.12345678' as TIMESTAMP),
  TIMESTAMPDIFF(SECOND,
    TIMESTAMP '2016-02-26 19:06:00.123456789',
    CAST('2016-02-26 19:06:00.123456789' as TIMESTAMP));
+-------------------------+---------------------+--------+
| EXPR$0                  | EXPR$1              | EXPR$2 |
+-------------------------+---------------------+--------+
| 2016-02-26 19:06:00.123 | 2016-02-26 19:06:00 |      0 |
+-------------------------+---------------------+--------+
(1 row)

!ok

# TIMESTAMPDIFF with 'flag' literal as time unit argument
SELECT TIMESTAMPDIFF(quarter, TIMESTAMP '2008-12-25', TIMESTAMP '2008-09-25');
+--------+
| EXPR$0 |
+--------+
|     -1 |
+--------+
(1 row)

! ok

# ARRAY and MULTISET
select array[1,2] as a from (values (1));
+--------+
| A      |
+--------+
| [1, 2] |
+--------+
(1 row)

!ok

select array[1,null,2] as a from (values (1));
+--------------+
| A            |
+--------------+
| [1, null, 2] |
+--------------+
(1 row)

!ok

values array['a',null,'bcd'],
  array['efgh'];
+------------------+
| EXPR$0           |
+------------------+
| [a  , null, bcd] |
| [efgh]           |
+------------------+
(2 rows)

!ok

select multiset[1,null,2,2-1] as m from (values (1));
+-----------------+
| M               |
+-----------------+
| [1, 1, null, 2] |
+-----------------+
(1 row)

!ok

!use catchall

# [CALCITE-1054] NPE caused by wrong code generation for Timestamp fields
select "sqlTimestamp" as T
from "everyTypes"
where "sqlTimestamp" >= {ts '1969-01-01 00:00:00'}
and "sqlTimestamp" < {ts '1998-01-01 00:00:00'};
+---------------------+
| T                   |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
(1 row)

!ok

# [CALCITE-1188] NullPointerException in EXTRACT with WHERE ... IN clause if field has null value
select "sqlTimestamp" T
from "everyTypes"
where extract(YEAR from "sqlTimestamp") IN (1969, 1970);
+---------------------+
| T                   |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
(1 row)

!ok

!use scott

# [CALCITE-2032] Error when implicitly converting character literal to date literal
select *
from "scott".emp
WHERE hiredate = '1980-12-17';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
(1 row)

!ok

select *
from "scott".emp
WHERE hiredate >= '1980-12-17'
AND deptno = 20;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 |      |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 |      |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 |      |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 |      |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 |      |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
(5 rows)

!ok

# [CALCITE-2002] DISTINCT applied to VALUES returns wrong result
SELECT DISTINCT T.B
FROM (VALUES (1, 'X'),(2, 'Y'),(3, 'X'),(4, 'X')) AS T(A, B);
+---+
| B |
+---+
| X |
| Y |
+---+
(2 rows)

!ok

# [CALCITE-2183] Implement RelSubset.copy
select *
from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)
where false;
+---+---+
| X | Y |
+---+---+
+---+---+
(0 rows)

!ok

# [CALCITE-2447] POWER, ATAN2 functions fail with NoSuchMethodException
values power(0.5, 2);
+--------+
| EXPR$0 |
+--------+
|   0.25 |
+--------+
(1 row)

!ok

# [CALCITE-2447] POWER, ATAN2 functions fail with NoSuchMethodException
values atan2(0.5, 2);
+---------------------+
| EXPR$0              |
+---------------------+
| 0.24497866312686414 |
+---------------------+
(1 row)

!ok

# [CALCITE-1167] OVERLAPS should match even if operands are in (high, low) order
values ((date '1999-12-01', date '2001-12-31') overlaps (date '2001-01-01' , date '2002-11-11'));
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

values ((date '2001-12-31', date '1999-12-01') overlaps (date '2001-01-01' , date '2002-11-11'));
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

values ((date '2001-12-31', date '1999-12-01') overlaps (date '2002-11-11', date '2001-01-01'));
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

values ((date '2001-12-31', date '1999-12-01') overlaps (date '2002-01-01', date '2002-11-11'));
+--------+
| EXPR$0 |
+--------+
| false  |
+--------+
(1 row)

!ok

# Sub-query returns a MAP, column is renamed, and enclosing query references the map.
select mycol['b'] as x
from (select map['a', false, 'b', true] from (values (2))) as t(mycol);
+------+
| X    |
+------+
| true |
+------+
(1 row)

!ok

# JSON
values json_exists('{"foo":"bar"}', 'strict $.foo' false on error);
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

# [CALCITE-2908] Implement SQL LAST_DAY function
with data(c_date, c_timestamp) as (select * from (values
    (DATE'1965-01-10', TIMESTAMP '1965-01-10 20:20:20'),
    (DATE'2019-01-01', TIMESTAMP '2019-01-01 18:19:20'),
    (DATE'2019-02-20', TIMESTAMP '2019-02-20 00:00:00'),
    (DATE'2019-02-28', TIMESTAMP '2019-02-28 03:04:05'),
    (DATE'2019-03-02', TIMESTAMP '2019-03-02 15:10:05'),
    (DATE'2019-06-28', TIMESTAMP '2019-06-28 17:32:01'),
    (DATE'2019-12-12', TIMESTAMP '2019-12-12 12:12:01')))
select last_day(c_date), last_day(c_timestamp) from data;
+------------+------------+
| EXPR$0     | EXPR$1     |
+------------+------------+
| 1965-01-31 | 1965-01-31 |
| 2019-01-31 | 2019-01-31 |
| 2019-02-28 | 2019-02-28 |
| 2019-02-28 | 2019-02-28 |
| 2019-03-31 | 2019-03-31 |
| 2019-06-30 | 2019-06-30 |
| 2019-12-31 | 2019-12-31 |
+------------+------------+
(7 rows)

!ok

# [CALCITE-3142] An NPE when rounding a nullable numeric
SELECT ROUND(CAST((X/Y) AS NUMERIC), 2)
FROM (VALUES (1, 2), (NULLIF(5, 5), NULLIF(5, 5))) A(X, Y);
+--------+
| EXPR$0 |
+--------+
|   0.00 |
|        |
+--------+
(2 rows)

!ok

# [CALCITE-3143]Dividing NULLIF clause may cause Division by zero error
SELECT CASE WHEN "Z" < 77 AND "Z" > 0 THEN 99 ELSE 88 END
FROM (
  SELECT SUM("X") / NULLIF(SUM(0),0) AS Z
  FROM (VALUES (1.1, 2.5), (4.51, 32.5)) A(X, Y)
  GROUP BY "Y");
+--------+
| EXPR$0 |
+--------+
|     88 |
|     88 |
+--------+
(2 rows)

!ok

# [CALCITE-3150] NPE in UPPER when repeated and combine with LIKE
SELECT "NAME"
FROM (VALUES ('Bill'), NULLIF('x', 'x'), ('Eric')) A(NAME)
WHERE UPPER("NAME") LIKE 'B%' AND UPPER("NAME") LIKE '%L';
+------+
| NAME |
+------+
| Bill |
+------+
(1 row)

!ok

# [CALCITE-3717] Query fails with "division by zero" exception
SELECT
  CASE WHEN A=0 THEN (B+C+D)*1.0
  WHEN B=0 THEN 1.0/A+(C+D)*1.0
  WHEN C=0 THEN 1.0/A+1.0/B+D*1.0
  WHEN D=0 THEN 1.0/A+1.0/B+1.0/C
  ELSE 1.0/A+1.0/B+1.0/C+1.0/D
  END AS V
FROM (VALUES (0, 2, 4, 8),
   (1, 0, 4, 8),
   (1, 2, 0, 8),
   (1, 2, 4, 0),
   (0, 0, 0, 0),
   (1, 2, 4, 8),
   (CAST(null as int), CAST(null as int), CAST(null as int), CAST(null as int))) AS T(A,B,C,D) order by V;
+-------------+
| V           |
+-------------+
|        0E-8 |
|  1.75000000 |
|  1.87500000 |
|  9.50000000 |
| 13.00000000 |
| 14.00000000 |
|             |
+-------------+
(7 rows)

!ok

# TIMESTAMP literals without a time part are OK.
SELECT TIMESTAMP '1969-07-20' AS ts;
+---------------------+
| TS                  |
+---------------------+
| 1969-07-20 00:00:00 |
+---------------------+
(1 row)

!ok

# Short TIMESTAMP literals are equivalent to long TIMESTAMP literals
SELECT TIMESTAMP '1969-07-20' + i AS ts
FROM (VALUES (INTERVAL '1' DAY)) AS t (i)
GROUP BY TIMESTAMP '1969-07-20 00:00:00' + i;
+---------------------+
| TS                  |
+---------------------+
| 1969-07-21 00:00:00 |
+---------------------+
(1 row)

!ok

# [CALCITE-5870] Allow literals like DECIMAL '12.3' (consistent with Postgres)
# Test a decimal value between decimal logic for range checking.
select 12.3 between decimal '5.6' and decimal '17.8';
+--------+
| EXPR$0 |
+--------+
| true   |
+--------+
(1 row)

!ok

# Test the decimal 'xx' + a decimal constant value case
select decimal'12.3' + 5.6;
+--------+
| EXPR$0 |
+--------+
|   17.9 |
+--------+
(1 row)

!ok

# [CALCITE-5570] Support nested map type for SqlDataTypeSpec
SELECT
  cast(x as map<int, int>),
  cast(y as map<int, varchar array>),
  cast(z as map<varchar  multiset, map<int, int>>)
FROM (
  SELECT
    map[1, 2, 3, 4] as x,
    map[1, array['a', 'b'], 2, array['c', 'd']] as y,
    map[multiset['A'], map[1,2], multiset['B'], map[3, 4]] as z
);
+------------+----------------------+------------------------+
| EXPR$0     | EXPR$1               | EXPR$2                 |
+------------+----------------------+------------------------+
| {1=2, 3=4} | {1=[a, b], 2=[c, d]} | {[A]={1=2}, [B]={3=4}} |
+------------+----------------------+------------------------+
(1 row)

!ok

# [CALCITE-6040] The operand type inference of SqlMapValueConstructor is incorrect
SELECT
  map['foo', null],
  map[null, 'foo'],
  map[1, 'foo', 2, null],
  map[1, null, 2, 'foo'];
+------------+------------+-----------------+-----------------+
| EXPR$0     | EXPR$1     | EXPR$2          | EXPR$3          |
+------------+------------+-----------------+-----------------+
| {foo=null} | {null=foo} | {1=foo, 2=null} | {1=null, 2=foo} |
+------------+------------+-----------------+-----------------+
(1 row)

!ok

!use post

# [CALCITE-5863] Incorrect validation with range and multiple order by columns
SELECT sum("salary")
OVER (ORDER BY "salary", "deptno" RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM "hr"."emps";
+---------+
| EXPR$0  |
+---------+
| 11500.0 |
| 21500.0 |
| 29500.0 |
| 36500.0 |
+---------+
(4 rows)

!ok

# [CALCITE-6566] JDBC adapter should generate PI function with parentheses in most dialects

!use scott-mysql

# PI function
values pi;
No match found for function signature PI()
!error

values pi();
+-------------------+
| EXPR$0            |
+-------------------+
| 3.141592653589793 |
+-------------------+
(1 row)

!ok

!use scott

# PI function
values pi;
+-------------------+
| PI                |
+-------------------+
| 3.141592653589793 |
+-------------------+
(1 row)

!ok

values pi();
+-------------------+
| EXPR$0            |
+-------------------+
| 3.141592653589793 |
+-------------------+
(1 row)

!ok

!use scott-babel
# Tests for https://issues.apache.org/jira/browse/CALCITE-6939
# [CALCITE-6939] Add support for Lateral Column Alias
# These need the BABEL conformance level.

select empno as x, x as y from emp;
+------+------+
| X    | Y    |
+------+------+
| 7369 | 7369 |
| 7499 | 7499 |
| 7521 | 7521 |
| 7566 | 7566 |
| 7654 | 7654 |
| 7698 | 7698 |
| 7782 | 7782 |
| 7788 | 7788 |
| 7839 | 7839 |
| 7844 | 7844 |
| 7876 | 7876 |
| 7900 | 7900 |
| 7902 | 7902 |
| 7934 | 7934 |
+------+------+
(14 rows)

!ok

select empno as x, x+1 as y from emp
group by empno, empno+1;
+------+------+
| X    | Y    |
+------+------+
| 7369 | 7370 |
| 7499 | 7500 |
| 7521 | 7522 |
| 7566 | 7567 |
| 7654 | 7655 |
| 7698 | 7699 |
| 7782 | 7783 |
| 7788 | 7789 |
| 7839 | 7840 |
| 7844 | 7845 |
| 7876 | 7877 |
| 7900 | 7901 |
| 7902 | 7903 |
| 7934 | 7935 |
+------+------+
(14 rows)

!ok

# group by lateral aliases introduced
select empno as x, x+1 as y from emp
group by x, y;
+------+------+
| X    | Y    |
+------+------+
| 7369 | 7370 |
| 7499 | 7500 |
| 7521 | 7522 |
| 7566 | 7567 |
| 7654 | 7655 |
| 7698 | 7699 |
| 7782 | 7783 |
| 7788 | 7789 |
| 7839 | 7840 |
| 7844 | 7845 |
| 7876 | 7877 |
| 7900 | 7901 |
| 7902 | 7903 |
| 7934 | 7935 |
+------+------+
(14 rows)

!ok

# group by a mix of expressions and aliases
select empno as x, x+1 as y from emp
group by empno, y;
+------+------+
| X    | Y    |
+------+------+
| 7369 | 7370 |
| 7499 | 7500 |
| 7521 | 7522 |
| 7566 | 7567 |
| 7654 | 7655 |
| 7698 | 7699 |
| 7782 | 7783 |
| 7788 | 7789 |
| 7839 | 7840 |
| 7844 | 7845 |
| 7876 | 7877 |
| 7900 | 7901 |
| 7902 | 7903 |
| 7934 | 7935 |
+------+------+
(14 rows)

!ok

# having with lateral aliases
select empno as x, x+1 as y from emp
group by x, y
having x > 7800;
+------+------+
| X    | Y    |
+------+------+
| 7839 | 7840 |
| 7844 | 7845 |
| 7876 | 7877 |
| 7900 | 7901 |
| 7902 | 7903 |
| 7934 | 7935 |
+------+------+
(6 rows)

!ok

# expression using new alias used in group by, and also used laterally
select empno + deptno as z, count(z) as w, w+1 as x from emp
group by z;
+------+---+---+
| Z    | W | X |
+------+---+---+
| 7389 | 1 | 2 |
| 7529 | 1 | 2 |
| 7551 | 1 | 2 |
| 7586 | 1 | 2 |
| 7684 | 1 | 2 |
| 7728 | 1 | 2 |
| 7792 | 1 | 2 |
| 7808 | 1 | 2 |
| 7849 | 1 | 2 |
| 7874 | 1 | 2 |
| 7896 | 1 | 2 |
| 7922 | 1 | 2 |
| 7930 | 1 | 2 |
| 7944 | 1 | 2 |
+------+---+---+
(14 rows)

!ok

# [CALCITE-7001] Cast of malformed literal to TIMESTAMP WITH LOCAL TIME ZONE
select cast ('malformed' AS TIMESTAMP WITH LOCAL TIME ZONE);
Illegal TIMESTAMP WITH LOCAL TIME ZONE literal 'malformed'
!error

# [CALCITE-7001] Cast of malformed literal to TIMESTAMP WITH LOCAL TIME ZONE
select cast ('0000-01-01 00:00:00' AS TIMESTAMP WITH LOCAL TIME ZONE);
Illegal TIMESTAMP WITH LOCAL TIME ZONE literal '0000-01-01 00:00:00'
!error

select TIMESTAMP WITH LOCAL TIME ZONE 'malformed';
From line 1, column 8 to line 1, column 49: Illegal TIMESTAMP WITH LOCAL TIME ZONE literal 'malformed'
!error

select TIMESTAMP WITH LOCAL TIME ZONE '0000-01-01 00:00:00';
From line 1, column 8 to line 1, column 59: Illegal TIMESTAMP WITH LOCAL TIME ZONE literal '0000-01-01 00:00:00'
!error

# End misc.iq