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

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

# The ArrayTable "days" is sorted by "day", so plan must not contain sort
select * from "days" order by "day";
!verify
EnumerableTableScan(table=[[foodmart2, days]])
!plan

# The ArrayTable "days" is sorted by "day", so the plan does not sort, only applies limit
select * from "days" order by "day" limit 2;
+-----+----------+
| day | week_day |
+-----+----------+
|   1 | Sunday   |
|   2 | Monday   |
+-----+----------+
(2 rows)

!ok
EnumerableLimit(fetch=[2])
  EnumerableTableScan(table=[[foodmart2, days]])
!plan

# The ArrayTable "days" is sorted by "day", so the plan must not contain Sort
select * from "days" where "day" between 2 and 4 order by "day";
+-----+-----------+
| day | week_day  |
+-----+-----------+
|   2 | Monday    |
|   3 | Tuesday   |
|   4 | Wednesday |
+-----+-----------+
(3 rows)

!ok
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[Sarg[[2..4]]], expr#3=[SEARCH($t0, $t2)], proj#0..1=[{exprs}], $condition=[$t3])
  EnumerableTableScan(table=[[foodmart2, days]])
!plan

# [CALCITE-970] Default collation of NULL values
# Nulls high, i.e. first if DESC
select "store_id", "grocery_sqft" from "store"
where "store_id" < 3
order by 2 DESC;
+----------+--------------+
| store_id | grocery_sqft |
+----------+--------------+
|        0 |              |
|        2 |        22271 |
|        1 |        17475 |
+----------+--------------+
(3 rows)

!ok

# Nulls high, i.e. first if DESC, composite sort
select "store_id", "grocery_sqft" from "store"
where "store_id" < 3
order by "florist", 2 DESC;
+----------+--------------+
| store_id | grocery_sqft |
+----------+--------------+
|        0 |              |
|        2 |        22271 |
|        1 |        17475 |
+----------+--------------+
(3 rows)

!ok

# Nulls high, i.e. last if ASC
select "store_id", "grocery_sqft" from "store"
where "store_id" < 3
order by 2;
+----------+--------------+
| store_id | grocery_sqft |
+----------+--------------+
|        1 |        17475 |
|        2 |        22271 |
|        0 |              |
+----------+--------------+
(3 rows)

!ok

# [CALCITE-969] Composite EnumerableSort with DESC wrongly sorts NULL values low
# Nulls high, i.e. last if ASC, composite sort
select "store_id", "grocery_sqft" from "store"
where "store_id" < 3
order by "florist", 2;
+----------+--------------+
| store_id | grocery_sqft |
+----------+--------------+
|        1 |        17475 |
|        2 |        22271 |
|        0 |              |
+----------+--------------+
(3 rows)

!ok

!use scott

# [CALCITE-2102] Ignore duplicate ORDER BY keys
select *
from "scott".DEPT
order by deptno desc, dname, deptno;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |
+--------+------------+----------+
(4 rows)

!ok
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[ASC])
  EnumerableTableScan(table=[[scott, DEPT]])
!plan

!use post

# [CALCITE-603] WITH ... ORDER BY cannot find table
with e as (select "empid" as empid from "hr"."emps" where "empid" < 120)
select * from e as e1, e as e2 order by e1.empid + e2.empid, e1.empid;
+-------+--------+
| EMPID | EMPID0 |
+-------+--------+
|   100 |    100 |
|   100 |    110 |
|   110 |    100 |
|   110 |    110 |
+-------+--------+
(4 rows)

!ok

# WITH ... LIMIT
with e as (select "empid" as empid from "hr"."emps" where "empid" < 200)
select * from e where empid > 100 limit 5;
+-------+
| EMPID |
+-------+
|   150 |
|   110 |
+-------+
(2 rows)

!ok

# [CALCITE-1015] OFFSET 0 causes AssertionError
select * from "hr"."emps" offset 0;
+-------+--------+-----------+---------+------------+
| empid | deptno | name      | salary  | commission |
+-------+--------+-----------+---------+------------+
|   100 |     10 | Bill      | 10000.0 |       1000 |
|   110 |     10 | Theodore  | 11500.0 |        250 |
|   150 |     10 | Sebastian |  7000.0 |            |
|   200 |     20 | Eric      |  8000.0 |        500 |
+-------+--------+-----------+---------+------------+
(4 rows)

!ok
EnumerableTableScan(table=[[hr, emps]])
!plan

# [CALCITE-634] Allow ORDER BY aggregate function in SELECT DISTINCT, provided
# that it occurs in SELECT clause
select distinct "deptno", count(*) as c
from "hr"."emps"
group by "deptno"
order by count(*) desc;
+--------+---+
| deptno | C |
+--------+---+
|     10 | 3 |
|     20 | 1 |
+--------+---+
(2 rows)

!ok

select distinct count("empid") as c
from "hr"."emps"
group by "empid"
order by 1;
+---+
| C |
+---+
| 1 |
+---+
(1 row)

!ok

# End sort.iq