http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/misc.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq deleted file mode 100644 index aa55654..0000000 --- a/core/src/test/resources/sql/misc.oq +++ /dev/null @@ -1,1111 +0,0 @@ -# misc.oq - 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-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-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 | -| 0097-02-14 17:32:01 | 0097-02-14 | -| 0597-02-18 17:32:01 | 0597-02-18 | -| 1097-02-22 17:32:01 | 1097-02-22 | -| 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 | -+---------------------+------------+ -(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}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7]) - EnumerableJoin(condition=[=($1, $3)], joinType=[inner]) - EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}]) - EnumerableTableScan(table=[[hr, emps]]) - 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}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7]) - EnumerableJoin(condition=[=($1, $3)], joinType=[inner]) - EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}]) - EnumerableTableScan(table=[[hr, depts]]) -!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 -EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], deptno0=[$t0], $f0=[$t5]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableTableScan(table=[[hr, depts]]) -!plan - -# NOT EXISTS -# Right results, but it would be better if the plan used EnumerableSemiJoinRel; 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 -EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t6)], expr#8=[NOT($t7)], proj#0..4=[{exprs}], $condition=[$t8]) - EnumerableJoin(condition=[=($1, $5)], joinType=[left]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableTableScan(table=[[hr, depts]]) -!plan - -# NOT EXISTS .. OR NOT EXISTS -# Right results, but it would be better if the plan used EnumerableSemiJoinRel; 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 -EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], expr#9=[NOT($t8)], expr#10=[IS NOT NULL($t7)], expr#11=[NOT($t10)], expr#12=[OR($t9, $t11)], proj#0..4=[{exprs}], $condition=[$t12]) - EnumerableJoin(condition=[=($0, $6)], joinType=[left]) - EnumerableCalc(expr#0..6=[{inputs}], proj#0..4=[{exprs}], $f0=[$t6]) - EnumerableJoin(condition=[=($1, $5)], joinType=[left]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableTableScan(table=[[hr, depts]]) - EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..6=[{inputs}], expr#7=[true], $f0=[$t7], empid=[$t0]) - EnumerableJoin(condition=[=($1, $6)], joinType=[inner]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}]) - EnumerableAggregate(group=[{0}]) - EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableTableScan(table=[[hr, depts]]) - EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], proj#0..3=[{exprs}], $f4=[$t5]) - EnumerableTableScan(table=[[hr, depts]]) -!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()]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableCalc(expr#0..3=[{inputs}], expr#4=[0], DUMMY=[$t4]) - EnumerableTableScan(table=[[hr, depts]]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], DUMMY=[$t5]) - EnumerableTableScan(table=[[hr, emps]]) -!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-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. -!use foodmart -select * -from "sales_fact_1997" as s - join "customer" as c using ("customer_id") - join "product" as p using ("product_id") -where c."city" = 'San Francisco'; -EnumerableJoin(condition=[=($0, $38)], joinType=[inner]) - EnumerableJoin(condition=[=($2, $8)], joinType=[inner]) - EnumerableTableScan(table=[[foodmart2, sales_fact_1997]]) - EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco'], 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 using ("customer_id") - join "product" as p using ("product_id") - join "product_class" as pc using ("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], she lf_height=[$t18], shelf_depth=[$t19], product_class_id=[$t0], product_subcategory=[$t1], product_category=[$t2], product_department=[$t3], product_family=[$t4]) - EnumerableJoin(condition=[=($6, $20)], joinType=[inner]) - EnumerableJoin(condition=[=($0, $5)], joinType=[inner]) - EnumerableCalc(expr#0..4=[{inputs}], expr#5=['Snacks'], expr#6=[=($t3, $t5)], proj#0..4=[{exprs}], $condition=[$t6]) - EnumerableTableScan(table=[[foodmart2, product_class]]) - EnumerableTableScan(table=[[foodmart2, product]]) - EnumerableJoin(condition=[=($2, $8)], joinType=[inner]) - EnumerableTableScan(table=[[foodmart2, sales_fact_1997]]) - EnumerableCalc(expr#0..28=[{inputs}], expr#29=['San Francisco'], 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 subquery -# [CALCITE-365] AssertionError while translating query with WITH and correlated sub-query -!if (false) { -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 | -+------+ -(1 row) - -!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 - -# [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-subquery 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 - -# [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=[CAST($t8):DATE NOT NULL], expr#10=[<($t4, $t9)], proj#0..7=[{exprs}], $condition=[$t10]) - 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 -select * from "scott".emp where deptno between '7369' and '7876'; -+-------+-------+-----+-----+----------+-----+------+--------+ -| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | -+-------+-------+-----+-----+----------+-----+------+--------+ -+-------+-------+-----+-----+----------+-----+------+--------+ -(0 rows) - -!ok -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; -ROW expression encountered in illegal context -!error - -# [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 - -# End misc.oq
http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/outer.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/outer.iq b/core/src/test/resources/sql/outer.iq new file mode 100644 index 0000000..af8e259 --- /dev/null +++ b/core/src/test/resources/sql/outer.iq @@ -0,0 +1,349 @@ +# outer.iq - Various kinds of outer join +# +# 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 + +select * from emp; ++-------+--------+--------+ +| ENAME | DEPTNO | GENDER | ++-------+--------+--------+ +| Jane | 10 | F | +| Bob | 10 | M | +| Eric | 20 | M | +| Susan | 30 | F | +| Alice | 30 | F | +| Adam | 50 | M | +| Eve | 50 | F | +| Grace | 60 | F | +| Wilma | | F | ++-------+--------+--------+ +(9 rows) + +!ok +select * from emp join dept on emp.deptno = dept.deptno; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Bob | 10 | M | 10 | Sales | +| Eric | 20 | M | 20 | Marketing | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | ++-------+--------+--------+---------+-------------+ +(5 rows) + +!ok + +# The following test is disabled, because we cannot handle non-equi-join. +# Following it are the results from MySQL. +!if (false) { +select * from emp join dept on emp.deptno = dept.deptno and emp.gender = 'F'; + ename | deptno | gender | deptno | dname +-------+--------+--------+--------+------------- + Jane | 10 | F | 10 | Sales + Susan | 30 | F | 30 | Engineering + Alice | 30 | F | 30 | Engineering + +!ok +!} + +select * from emp join dept on emp.deptno = dept.deptno where emp.gender = 'F'; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | ++-------+--------+--------+---------+-------------+ +(3 rows) + +!ok + +select * from (select * from emp where gender ='F') as emp join dept on emp.deptno = dept.deptno; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | ++-------+--------+--------+---------+-------------+ +(3 rows) + +!ok + +# The following test is disabled, because we cannot handle non-equi-join. +# Following it are the results from MySQL. +!if (false) { +select * from emp left join dept on emp.deptno = dept.deptno and emp.gender = 'F'; + ename | deptno | gender | deptno | dname +-------+--------+--------+--------+------------- + Jane | 10 | F | 10 | Sales + Susan | 30 | F | 30 | Engineering + Alice | 30 | F | 30 | Engineering + Bob | 10 | M | NULL | NULL + Eric | 20 | M | NULL | NULL + Adam | 50 | M | NULL | NULL + Eve | 50 | F | NULL | NULL + Grace | 60 | F | NULL | NULL +!ok +!} + +select * from emp left join dept on emp.deptno = dept.deptno where emp.gender = 'F'; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| Eve | 50 | F | | | +| Grace | 60 | F | | | +| Wilma | | F | | | ++-------+--------+--------+---------+-------------+ +(6 rows) + +!ok + +select * from (select * from emp where gender ='F') as emp left join dept on emp.deptno = dept.deptno; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| Eve | 50 | F | | | +| Grace | 60 | F | | | +| Wilma | | F | | | ++-------+--------+--------+---------+-------------+ +(6 rows) + +!ok + +# The following test is disabled, because we cannot handle non-equi-join. +# Following it are the results from MySQL. +!if (false) { +select * from emp right join dept on emp.deptno = dept.deptno and emp.gender = 'F'; ++-------+--------+--------+--------+-------------+ +| ename | deptno | gender | deptno | dname | ++-------+--------+--------+--------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| NULL | NULL | NULL | 20 | Marketing | +| NULL | NULL | NULL | 40 | Empty | ++-------+--------+--------+--------+-------------+ +!ok +!} + +select * from emp right join dept on emp.deptno = dept.deptno where emp.gender = 'F'; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | ++-------+--------+--------+---------+-------------+ +(3 rows) + +!ok + +select * from (select * from emp where gender ='F') as emp right join dept on emp.deptno = dept.deptno; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| | | | 20 | Marketing | +| | | | 40 | Empty | ++-------+--------+--------+---------+-------------+ +(5 rows) + +!ok + +!if (false) { +select * from emp full join dept on emp.deptno = dept.deptno and emp.gender = 'F'; + ename | deptno | gender | deptno | dname +-------+--------+--------+--------+------------- + Jane | 10 | F | 10 | Sales + | | | 20 | Marketing + Alice | 30 | F | 30 | Engineering + Susan | 30 | F | 30 | Engineering + | | | 40 | Empty + Wilma | | F | | + Eric | 20 | M | | + Bob | 10 | M | | + Eve | 50 | F | | + Adam | 50 | M | | + Grace | 60 | F | | +(11 rows) + +!ok +!} + + +select * from emp full join dept on emp.deptno = dept.deptno where emp.gender = 'F'; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| Eve | 50 | F | | | +| Grace | 60 | F | | | +| Wilma | | F | | | ++-------+--------+--------+---------+-------------+ +(6 rows) + +!ok + +select * from (select * from emp where gender ='F') as emp full join dept on emp.deptno = dept.deptno; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| Eve | 50 | F | | | +| Grace | 60 | F | | | +| Wilma | | F | | | +| | | | 20 | Marketing | +| | | | 40 | Empty | ++-------+--------+--------+---------+-------------+ +(8 rows) + +!ok + +# same as above, but expressed as a theta-join +select * from (select * from emp where gender ='F') as emp full join dept on emp.deptno - dept.deptno = 0; ++-------+--------+--------+---------+-------------+ +| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | ++-------+--------+--------+---------+-------------+ +| Jane | 10 | F | 10 | Sales | +| Susan | 30 | F | 30 | Engineering | +| Alice | 30 | F | 30 | Engineering | +| Eve | 50 | F | | | +| Grace | 60 | F | | | +| Wilma | | F | | | +| | | | 20 | Marketing | +| | | | 40 | Empty | ++-------+--------+--------+---------+-------------+ +(8 rows) + +!ok +EnumerableThetaJoin(condition=[=(-($1, $3), 0)], joinType=[full]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)], proj#0..2=[{exprs}], $condition=[$t4]) + EnumerableUnion(all=[true]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) + EnumerableValues(tuples=[[{ 0 }]]) + EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]]) +!plan + +# [CALCITE-554] Outer join over NULL keys generates wrong result +with t1(x) as (select * from (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)), + t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t(x)) +select t1.x from t1 left join t2 on t1.x = t2.x; ++---+ +| X | ++---+ +| 1 | +| 2 | +| | ++---+ +(3 rows) + +!ok + +# Equivalent query, using CAST, and skipping unnecessary aliases +# (Postgres doesn't like the missing alias, or the missing parentheses.) +with t1(x) as (select * from (values 1, 2, cast(null as integer))), + t2(x) as (select * from (values 1, cast(null as integer))) +select t1.x from t1 left join t2 on t1.x = t2.x; ++---+ +| X | ++---+ +| 1 | +| 2 | +| | ++---+ +(3 rows) + +!ok + +# Similar query, projecting left and right key columns +with t1(x) as (select * from (values (1), (2), (cast(null as integer))) as t), + t2(x) as (select * from (values (1), (cast(null as integer))) as t) +select t1.x, t2.x from t1 left join t2 on t1.x = t2.x; ++---+---+ +| X | X | ++---+---+ +| 1 | 1 | +| 2 | | +| | | ++---+---+ +(3 rows) + +!ok + +# Similar, with 2 columns on each side projecting both columns +with t1(x, y) as (select * from (values (1, 10), (2, 20), (cast(null as integer), 30)) as t), + t2(x, y) as (select * from (values (1, 100), (cast(null as integer), 200)) as t) +select * from t1 left join t2 on t1.x = t2.x; ++---+----+----+-----+ +| X | Y | X0 | Y0 | ++---+----+----+-----+ +| 1 | 10 | 1 | 100 | +| 2 | 20 | | | +| | 30 | | | ++---+----+----+-----+ +(3 rows) + +!ok + +# Similar, full join +with t1(x, y) as (select * from (values (1, 10), (2, 20), (cast(null as integer), 30)) as t), + t2(x, y) as (select * from (values (1,100), (cast(null as integer), 200)) as t) +select * from t1 full join t2 on t1.x = t2.x; ++---+----+----+-----+ +| X | Y | X0 | Y0 | ++---+----+----+-----+ +| 1 | 10 | 1 | 100 | +| 2 | 20 | | | +| | 30 | | | +| | | | 200 | ++---+----+----+-----+ +(4 rows) + +!ok + +# End outer.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/outer.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/outer.oq b/core/src/test/resources/sql/outer.oq deleted file mode 100644 index 5d08047..0000000 --- a/core/src/test/resources/sql/outer.oq +++ /dev/null @@ -1,349 +0,0 @@ -# outer.oq - Various kinds of outer join -# -# 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 - -select * from emp; -+-------+--------+--------+ -| ENAME | DEPTNO | GENDER | -+-------+--------+--------+ -| Jane | 10 | F | -| Bob | 10 | M | -| Eric | 20 | M | -| Susan | 30 | F | -| Alice | 30 | F | -| Adam | 50 | M | -| Eve | 50 | F | -| Grace | 60 | F | -| Wilma | | F | -+-------+--------+--------+ -(9 rows) - -!ok -select * from emp join dept on emp.deptno = dept.deptno; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Bob | 10 | M | 10 | Sales | -| Eric | 20 | M | 20 | Marketing | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -+-------+--------+--------+---------+-------------+ -(5 rows) - -!ok - -# The following test is disabled, because we cannot handle non-equi-join. -# Following it are the results from MySQL. -!if (false) { -select * from emp join dept on emp.deptno = dept.deptno and emp.gender = 'F'; - ename | deptno | gender | deptno | dname --------+--------+--------+--------+------------- - Jane | 10 | F | 10 | Sales - Susan | 30 | F | 30 | Engineering - Alice | 30 | F | 30 | Engineering - -!ok -!} - -select * from emp join dept on emp.deptno = dept.deptno where emp.gender = 'F'; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -+-------+--------+--------+---------+-------------+ -(3 rows) - -!ok - -select * from (select * from emp where gender ='F') as emp join dept on emp.deptno = dept.deptno; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -+-------+--------+--------+---------+-------------+ -(3 rows) - -!ok - -# The following test is disabled, because we cannot handle non-equi-join. -# Following it are the results from MySQL. -!if (false) { -select * from emp left join dept on emp.deptno = dept.deptno and emp.gender = 'F'; - ename | deptno | gender | deptno | dname --------+--------+--------+--------+------------- - Jane | 10 | F | 10 | Sales - Susan | 30 | F | 30 | Engineering - Alice | 30 | F | 30 | Engineering - Bob | 10 | M | NULL | NULL - Eric | 20 | M | NULL | NULL - Adam | 50 | M | NULL | NULL - Eve | 50 | F | NULL | NULL - Grace | 60 | F | NULL | NULL -!ok -!} - -select * from emp left join dept on emp.deptno = dept.deptno where emp.gender = 'F'; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| Eve | 50 | F | | | -| Grace | 60 | F | | | -| Wilma | | F | | | -+-------+--------+--------+---------+-------------+ -(6 rows) - -!ok - -select * from (select * from emp where gender ='F') as emp left join dept on emp.deptno = dept.deptno; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| Eve | 50 | F | | | -| Grace | 60 | F | | | -| Wilma | | F | | | -+-------+--------+--------+---------+-------------+ -(6 rows) - -!ok - -# The following test is disabled, because we cannot handle non-equi-join. -# Following it are the results from MySQL. -!if (false) { -select * from emp right join dept on emp.deptno = dept.deptno and emp.gender = 'F'; -+-------+--------+--------+--------+-------------+ -| ename | deptno | gender | deptno | dname | -+-------+--------+--------+--------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| NULL | NULL | NULL | 20 | Marketing | -| NULL | NULL | NULL | 40 | Empty | -+-------+--------+--------+--------+-------------+ -!ok -!} - -select * from emp right join dept on emp.deptno = dept.deptno where emp.gender = 'F'; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -+-------+--------+--------+---------+-------------+ -(3 rows) - -!ok - -select * from (select * from emp where gender ='F') as emp right join dept on emp.deptno = dept.deptno; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| | | | 20 | Marketing | -| | | | 40 | Empty | -+-------+--------+--------+---------+-------------+ -(5 rows) - -!ok - -!if (false) { -select * from emp full join dept on emp.deptno = dept.deptno and emp.gender = 'F'; - ename | deptno | gender | deptno | dname --------+--------+--------+--------+------------- - Jane | 10 | F | 10 | Sales - | | | 20 | Marketing - Alice | 30 | F | 30 | Engineering - Susan | 30 | F | 30 | Engineering - | | | 40 | Empty - Wilma | | F | | - Eric | 20 | M | | - Bob | 10 | M | | - Eve | 50 | F | | - Adam | 50 | M | | - Grace | 60 | F | | -(11 rows) - -!ok -!} - - -select * from emp full join dept on emp.deptno = dept.deptno where emp.gender = 'F'; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| Eve | 50 | F | | | -| Grace | 60 | F | | | -| Wilma | | F | | | -+-------+--------+--------+---------+-------------+ -(6 rows) - -!ok - -select * from (select * from emp where gender ='F') as emp full join dept on emp.deptno = dept.deptno; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| Eve | 50 | F | | | -| Grace | 60 | F | | | -| Wilma | | F | | | -| | | | 20 | Marketing | -| | | | 40 | Empty | -+-------+--------+--------+---------+-------------+ -(8 rows) - -!ok - -# same as above, but expressed as a theta-join -select * from (select * from emp where gender ='F') as emp full join dept on emp.deptno - dept.deptno = 0; -+-------+--------+--------+---------+-------------+ -| ENAME | DEPTNO | GENDER | DEPTNO0 | DNAME | -+-------+--------+--------+---------+-------------+ -| Jane | 10 | F | 10 | Sales | -| Susan | 30 | F | 30 | Engineering | -| Alice | 30 | F | 30 | Engineering | -| Eve | 50 | F | | | -| Grace | 60 | F | | | -| Wilma | | F | | | -| | | | 20 | Marketing | -| | | | 40 | Empty | -+-------+--------+--------+---------+-------------+ -(8 rows) - -!ok -EnumerableThetaJoin(condition=[=(-($1, $3), 0)], joinType=[full]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)], proj#0..2=[{exprs}], $condition=[$t4]) - EnumerableUnion(all=[true]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Jane'], expr#2=[10], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Bob'], expr#2=[10], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Eric'], expr#2=[20], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Susan'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Alice'], expr#2=[30], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Adam'], expr#2=[50], expr#3=['M'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Eve'], expr#2=[50], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Grace'], expr#2=[60], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableCalc(expr#0=[{inputs}], expr#1=['Wilma'], expr#2=[null], expr#3=['F'], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) - EnumerableValues(tuples=[[{ 0 }]]) - EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, { 30, 'Engineering' }, { 40, 'Empty ' }]]) -!plan - -# [CALCITE-554] Outer join over NULL keys generates wrong result -with t1(x) as (select * from (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)), - t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t(x)) -select t1.x from t1 left join t2 on t1.x = t2.x; -+---+ -| X | -+---+ -| 1 | -| 2 | -| | -+---+ -(3 rows) - -!ok - -# Equivalent query, using CAST, and skipping unnecessary aliases -# (Postgres doesn't like the missing alias, or the missing parentheses.) -with t1(x) as (select * from (values 1, 2, cast(null as integer))), - t2(x) as (select * from (values 1, cast(null as integer))) -select t1.x from t1 left join t2 on t1.x = t2.x; -+---+ -| X | -+---+ -| 1 | -| 2 | -| | -+---+ -(3 rows) - -!ok - -# Similar query, projecting left and right key columns -with t1(x) as (select * from (values (1), (2), (cast(null as integer))) as t), - t2(x) as (select * from (values (1), (cast(null as integer))) as t) -select t1.x, t2.x from t1 left join t2 on t1.x = t2.x; -+---+---+ -| X | X | -+---+---+ -| 1 | 1 | -| 2 | | -| | | -+---+---+ -(3 rows) - -!ok - -# Similar, with 2 columns on each side projecting both columns -with t1(x, y) as (select * from (values (1, 10), (2, 20), (cast(null as integer), 30)) as t), - t2(x, y) as (select * from (values (1, 100), (cast(null as integer), 200)) as t) -select * from t1 left join t2 on t1.x = t2.x; -+---+----+----+-----+ -| X | Y | X0 | Y0 | -+---+----+----+-----+ -| 1 | 10 | 1 | 100 | -| 2 | 20 | | | -| | 30 | | | -+---+----+----+-----+ -(3 rows) - -!ok - -# Similar, full join -with t1(x, y) as (select * from (values (1, 10), (2, 20), (cast(null as integer), 30)) as t), - t2(x, y) as (select * from (values (1,100), (cast(null as integer), 200)) as t) -select * from t1 full join t2 on t1.x = t2.x; -+---+----+----+-----+ -| X | Y | X0 | Y0 | -+---+----+----+-----+ -| 1 | 10 | 1 | 100 | -| 2 | 20 | | | -| | 30 | | | -| | | | 200 | -+---+----+----+-----+ -(4 rows) - -!ok - -# End outer.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/scalar.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/scalar.iq b/core/src/test/resources/sql/scalar.iq new file mode 100644 index 0000000..283f5c3 --- /dev/null +++ b/core/src/test/resources/sql/scalar.iq @@ -0,0 +1,216 @@ +# scalar.iq - Scalar sub-queries +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to you under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +!set outputformat mysql +!use scott + +select deptno, (select min(empno) from "scott".emp where deptno = dept.deptno) as x from "scott".dept; ++--------+------+ +| DEPTNO | X | ++--------+------+ +| 10 | 7782 | +| 20 | 7369 | +| 30 | 7499 | +| 40 | | ++--------+------+ +(4 rows) + +!ok + +select deptno, (select count(*) from "scott".emp where deptno = dept.deptno) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | 3 | +| 20 | 5 | +| 30 | 6 | +| 40 | 0 | ++--------+---+ +(4 rows) + +!ok + +select deptno, (select count(*) from "scott".emp where deptno = dept.deptno group by deptno) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | 3 | +| 20 | 5 | +| 30 | 6 | +| 40 | | ++--------+---+ +(4 rows) + +!ok + +# cast necessary to prevent overflow +select deptno, (select sum(cast(empno as int)) from "scott".emp where deptno = dept.deptno group by deptno) as x from "scott".dept; ++--------+-------+ +| DEPTNO | X | ++--------+-------+ +| 10 | 23555 | +| 20 | 38501 | +| 30 | 46116 | +| 40 | | ++--------+-------+ +(4 rows) + +!ok + +select deptno, (select count(*) from "scott".emp where 1 = 0) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | 0 | +| 20 | 0 | +| 30 | 0 | +| 40 | 0 | ++--------+---+ +(4 rows) + +!ok + +select deptno, (select count(*) from "scott".emp where 1 = 0 group by ()) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | 0 | +| 20 | 0 | +| 30 | 0 | +| 40 | 0 | ++--------+---+ +(4 rows) + +!ok + +select deptno, (select sum(empno) from "scott".emp where 1 = 0) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | | +| 20 | | +| 30 | | +| 40 | | ++--------+---+ +(4 rows) + +!ok + +select deptno, (select sum(empno) from "scott".emp where 1 = 0 group by ()) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | | +| 20 | | +| 30 | | +| 40 | | ++--------+---+ +(4 rows) + +!ok + +# [CALCITE-709] Errors with LIMIT inside scalar sub-query +!if (false) { +select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 1) as x from "scott".dept; ++--------+----------------------+ +| DEPTNO | X | ++--------+----------------------+ +| 10 | 23555 | +| 20 | 38501 | +| 30 | 46116 | +| 40 | null | ++--------+----------------------+ +(4 rows) + +!ok +!} + +# [CALCITE-709] Errors with LIMIT inside scalar sub-query +!if (false) { +select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept; ++--------+----------------------+ +| DEPTNO | X | ++--------+----------------------+ +| 10 | 23555 | +| 20 | 38501 | +| 30 | 46116 | +| 40 | null | ++--------+----------------------+ +(4 rows) + +!ok +!} + +# [CALCITE-709] Errors with LIMIT inside scalar sub-query +!if (false) { +select deptno, (select deptno from "scott".emp where deptno = dept.deptno limit 1) as x from "scott".dept; ++--------+------+ +| DEPTNO | X | ++--------+------+ +| 10 | 10 | +| 20 | 20 | +| 30 | 30 | +| 40 | null | ++--------+------+ +(4 rows) + +!ok +!} + +select deptno, (select deptno from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept; ++--------+---+ +| DEPTNO | X | ++--------+---+ +| 10 | | +| 20 | | +| 30 | | +| 40 | | ++--------+---+ +(4 rows) + +!ok + +# [CALCITE-709] Errors with LIMIT inside scalar sub-query +!if (false) { +select deptno, (select empno from "scott".emp where deptno = dept.deptno order by empno limit 1) as x from "scott".dept; ++--------+--------+ +| DEPTNO | X | ++--------+--------+ +| 10 | 7369 | +| 20 | 7369 | +| 30 | 7369 | +| 40 | 7369 | ++--------+--------+ +(4 rows) + +!ok +!} + +select deptno, (select empno from "scott".emp order by empno limit 1) as x from "scott".dept; ++--------+------+ +| DEPTNO | X | ++--------+------+ +| 10 | 7369 | +| 20 | 7369 | +| 30 | 7369 | +| 40 | 7369 | ++--------+------+ +(4 rows) + +!ok + +# End scalar.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/scalar.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/scalar.oq b/core/src/test/resources/sql/scalar.oq deleted file mode 100644 index 103df29..0000000 --- a/core/src/test/resources/sql/scalar.oq +++ /dev/null @@ -1,216 +0,0 @@ -# scalar.oq - Scalar sub-queries -# -# Licensed to the Apache Software Foundation (ASF) under one or more -# contributor license agreements. See the NOTICE file distributed with -# this work for additional information regarding copyright ownership. -# The ASF licenses this file to you under the Apache License, Version 2.0 -# (the "License"); you may not use this file except in compliance with -# the License. You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -!set outputformat mysql -!use scott - -select deptno, (select min(empno) from "scott".emp where deptno = dept.deptno) as x from "scott".dept; -+--------+------+ -| DEPTNO | X | -+--------+------+ -| 10 | 7782 | -| 20 | 7369 | -| 30 | 7499 | -| 40 | | -+--------+------+ -(4 rows) - -!ok - -select deptno, (select count(*) from "scott".emp where deptno = dept.deptno) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | 3 | -| 20 | 5 | -| 30 | 6 | -| 40 | 0 | -+--------+---+ -(4 rows) - -!ok - -select deptno, (select count(*) from "scott".emp where deptno = dept.deptno group by deptno) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | 3 | -| 20 | 5 | -| 30 | 6 | -| 40 | | -+--------+---+ -(4 rows) - -!ok - -# cast necessary to prevent overflow -select deptno, (select sum(cast(empno as int)) from "scott".emp where deptno = dept.deptno group by deptno) as x from "scott".dept; -+--------+-------+ -| DEPTNO | X | -+--------+-------+ -| 10 | 23555 | -| 20 | 38501 | -| 30 | 46116 | -| 40 | | -+--------+-------+ -(4 rows) - -!ok - -select deptno, (select count(*) from "scott".emp where 1 = 0) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | 0 | -| 20 | 0 | -| 30 | 0 | -| 40 | 0 | -+--------+---+ -(4 rows) - -!ok - -select deptno, (select count(*) from "scott".emp where 1 = 0 group by ()) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | 0 | -| 20 | 0 | -| 30 | 0 | -| 40 | 0 | -+--------+---+ -(4 rows) - -!ok - -select deptno, (select sum(empno) from "scott".emp where 1 = 0) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | | -| 20 | | -| 30 | | -| 40 | | -+--------+---+ -(4 rows) - -!ok - -select deptno, (select sum(empno) from "scott".emp where 1 = 0 group by ()) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | | -| 20 | | -| 30 | | -| 40 | | -+--------+---+ -(4 rows) - -!ok - -# [CALCITE-709] Errors with LIMIT inside scalar sub-query -!if (false) { -select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 1) as x from "scott".dept; -+--------+----------------------+ -| DEPTNO | X | -+--------+----------------------+ -| 10 | 23555 | -| 20 | 38501 | -| 30 | 46116 | -| 40 | null | -+--------+----------------------+ -(4 rows) - -!ok -!} - -# [CALCITE-709] Errors with LIMIT inside scalar sub-query -!if (false) { -select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept; -+--------+----------------------+ -| DEPTNO | X | -+--------+----------------------+ -| 10 | 23555 | -| 20 | 38501 | -| 30 | 46116 | -| 40 | null | -+--------+----------------------+ -(4 rows) - -!ok -!} - -# [CALCITE-709] Errors with LIMIT inside scalar sub-query -!if (false) { -select deptno, (select deptno from "scott".emp where deptno = dept.deptno limit 1) as x from "scott".dept; -+--------+------+ -| DEPTNO | X | -+--------+------+ -| 10 | 10 | -| 20 | 20 | -| 30 | 30 | -| 40 | null | -+--------+------+ -(4 rows) - -!ok -!} - -select deptno, (select deptno from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept; -+--------+---+ -| DEPTNO | X | -+--------+---+ -| 10 | | -| 20 | | -| 30 | | -| 40 | | -+--------+---+ -(4 rows) - -!ok - -# [CALCITE-709] Errors with LIMIT inside scalar sub-query -!if (false) { -select deptno, (select empno from "scott".emp where deptno = dept.deptno order by empno limit 1) as x from "scott".dept; -+--------+--------+ -| DEPTNO | X | -+--------+--------+ -| 10 | 7369 | -| 20 | 7369 | -| 30 | 7369 | -| 40 | 7369 | -+--------+--------+ -(4 rows) - -!ok -!} - -select deptno, (select empno from "scott".emp order by empno limit 1) as x from "scott".dept; -+--------+------+ -| DEPTNO | X | -+--------+------+ -| 10 | 7369 | -| 20 | 7369 | -| 30 | 7369 | -| 40 | 7369 | -+--------+------+ -(4 rows) - -!ok - -# End scalar.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/sequence.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sequence.iq b/core/src/test/resources/sql/sequence.iq new file mode 100644 index 0000000..be79eaa --- /dev/null +++ b/core/src/test/resources/sql/sequence.iq @@ -0,0 +1,79 @@ +# sequence.iq - Sequences +# +# 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 seq +!set outputformat mysql + +select next value for "my_seq" as c from (values 1, 2); ++---+ +| C | ++---+ +| 1 | +| 2 | ++---+ +(2 rows) + +!ok +select current value for "my_seq" as c from (values 1, 2); ++---+ +| C | ++---+ +| 2 | +| 2 | ++---+ +(2 rows) + +!ok + +select next value for "my_seq" as c from (values 1, 2); +C BIGINT(19) NOT NULL +!type + +# Qualified with schema name +select next value for "s"."my_seq" as c from (values 1, 2); +C BIGINT(19) NOT NULL +!type + +select next value for "unknown_seq" as c from (values 1, 2); +From line 1, column 23 to line 1, column 35: Table 'unknown_seq' not found +!error + +# Qualified with bad schema name +select next value for "unknown_schema"."my_seq" as c from (values 1, 2); +From line 1, column 23 to line 1, column 47: Table 'unknown_schema.my_seq' not found +!error + +# Table found, but not a sequence +select next value for "metadata".tables as c from (values 1, 2); +From line 1, column 23 to line 1, column 39: Table 'metadata.TABLES' is not a sequence +!error + +# Sequences appear in the catalog as tables of type 'SEQUENCE' +select * from "metadata".tables; ++----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ +| tableCat | tableSchem | tableName | tableType | remarks | typeCat | typeSchem | typeName | selfReferencingColName | refGeneration | ++----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ +| | metadata | COLUMNS | SYSTEM_TABLE | | | | | | | +| | metadata | TABLES | SYSTEM_TABLE | | | | | | | +| | s | my_seq | SEQUENCE | | | | | | | ++----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ +(3 rows) + +!ok + +# End sequence.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/sequence.oq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sequence.oq b/core/src/test/resources/sql/sequence.oq deleted file mode 100644 index 4f338de..0000000 --- a/core/src/test/resources/sql/sequence.oq +++ /dev/null @@ -1,79 +0,0 @@ -# sequence.oq - Sequences -# -# 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 seq -!set outputformat mysql - -select next value for "my_seq" as c from (values 1, 2); -+---+ -| C | -+---+ -| 1 | -| 2 | -+---+ -(2 rows) - -!ok -select current value for "my_seq" as c from (values 1, 2); -+---+ -| C | -+---+ -| 2 | -| 2 | -+---+ -(2 rows) - -!ok - -select next value for "my_seq" as c from (values 1, 2); -C BIGINT(19) NOT NULL -!type - -# Qualified with schema name -select next value for "s"."my_seq" as c from (values 1, 2); -C BIGINT(19) NOT NULL -!type - -select next value for "unknown_seq" as c from (values 1, 2); -From line 1, column 23 to line 1, column 35: Table 'unknown_seq' not found -!error - -# Qualified with bad schema name -select next value for "unknown_schema"."my_seq" as c from (values 1, 2); -From line 1, column 23 to line 1, column 47: Table 'unknown_schema.my_seq' not found -!error - -# Table found, but not a sequence -select next value for "metadata".tables as c from (values 1, 2); -From line 1, column 23 to line 1, column 39: Table 'metadata.TABLES' is not a sequence -!error - -# Sequences appear in the catalog as tables of type 'SEQUENCE' -select * from "metadata".tables; -+----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ -| tableCat | tableSchem | tableName | tableType | remarks | typeCat | typeSchem | typeName | selfReferencingColName | refGeneration | -+----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ -| | metadata | COLUMNS | SYSTEM_TABLE | | | | | | | -| | metadata | TABLES | SYSTEM_TABLE | | | | | | | -| | s | my_seq | SEQUENCE | | | | | | | -+----------+------------+-----------+--------------+---------+---------+-----------+----------+------------------------+---------------+ -(3 rows) - -!ok - -# End sequence.oq http://git-wip-us.apache.org/repos/asf/calcite/blob/8c2bc8f1/core/src/test/resources/sql/sort.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sort.iq b/core/src/test/resources/sql/sort.iq new file mode 100644 index 0000000..0be2add --- /dev/null +++ b/core/src/test/resources/sql/sort.iq @@ -0,0 +1,179 @@ +# 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=[2], expr#3=[>=($t0, $t2)], expr#4=[4], expr#5=[<=($t0, $t4)], expr#6=[AND($t3, $t5)], proj#0..1=[{exprs}], $condition=[$t6]) + EnumerableTableScan(table=[[foodmart2, days]]) +!plan + +# [CALCITE-970] Default collation of NULL values +# Nulls high, i.e. first if DESC +select "store_id", "grocery_sqft" from "store" +where "store_id" < 3 +order by 2 DESC; ++----------+--------------+ +| store_id | grocery_sqft | ++----------+--------------+ +| 0 | | +| 2 | 22271 | +| 1 | 17475 | ++----------+--------------+ +(3 rows) + +!ok + +# Nulls high, i.e. first if DESC, composite sort +select "store_id", "grocery_sqft" from "store" +where "store_id" < 3 +order by "florist", 2 DESC; ++----------+--------------+ +| store_id | grocery_sqft | ++----------+--------------+ +| 0 | | +| 2 | 22271 | +| 1 | 17475 | ++----------+--------------+ +(3 rows) + +!ok + +# Nulls high, i.e. last if ASC +select "store_id", "grocery_sqft" from "store" +where "store_id" < 3 +order by 2; ++----------+--------------+ +| store_id | grocery_sqft | ++----------+--------------+ +| 1 | 17475 | +| 2 | 22271 | +| 0 | | ++----------+--------------+ +(3 rows) + +!ok + +# [CALCITE-969] Composite EnumerableSort with DESC wrongly sorts NULL values low +# Nulls high, i.e. last if ASC, composite sort +select "store_id", "grocery_sqft" from "store" +where "store_id" < 3 +order by "florist", 2; ++----------+--------------+ +| store_id | grocery_sqft | ++----------+--------------+ +| 1 | 17475 | +| 2 | 22271 | +| 0 | | ++----------+--------------+ +(3 rows) + +!ok + +!use post + +# [CALCITE-603] WITH ... ORDER BY cannot find table +with e as (select "empid" as empid from "hr"."emps" where "empid" < 120) +select * from e as e1, e as e2 order by e1.empid + e2.empid, e1.empid; ++-------+--------+ +| EMPID | EMPID0 | ++-------+--------+ +| 100 | 100 | +| 100 | 110 | +| 110 | 100 | +| 110 | 110 | ++-------+--------+ +(4 rows) + +!ok + +# WITH ... LIMIT +with e as (select "empid" as empid from "hr"."emps" where "empid" < 200) +select * from e where empid > 100 limit 5; ++-------+ +| EMPID | ++-------+ +| 150 | +| 110 | ++-------+ +(2 rows) + +!ok + +# [CALCITE-634] Allow ORDER BY aggregate function in SELECT DISTINCT, provided +# that it occurs in SELECT clause +select distinct "deptno", count(*) as c +from "hr"."emps" +group by "deptno" +order by count(*) desc; ++--------+---+ +| deptno | C | ++--------+---+ +| 10 | 3 | +| 20 | 1 | ++--------+---+ +(2 rows) + +!ok + +select distinct count("empid") as c +from "hr"."emps" +group by "empid" +order by 1; ++---+ +| C | ++---+ +| 1 | ++---+ +(1 row) + +!ok + +# End sort.iq
