| # struct.iq - Queries involving structured types |
| # |
| # 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-2677] Struct types with one field are not mapped correctly to Java Classes |
| select * from (values |
| (1, ROW(1)), |
| (2, ROW(2))) as v(id,struct); |
| +----+--------+ |
| | ID | STRUCT | |
| +----+--------+ |
| | 1 | {1} | |
| | 2 | {2} | |
| +----+--------+ |
| (2 rows) |
| |
| !ok |
| |
| # [CALCITE-3021] Equality of nested ROWs returns false for identical values |
| select distinct * from (values |
| (1, ROW(1,1)), |
| (1, ROW(1,1)), |
| (2, ROW(2,2))) as v(id,struct); |
| +----+--------+ |
| | ID | STRUCT | |
| +----+--------+ |
| | 1 | {1, 1} | |
| | 2 | {2, 2} | |
| +----+--------+ |
| (2 rows) |
| |
| !ok |
| |
| # [CALCITE-3482] Equality of nested ROWs returns false for identical literal value |
| select * from |
| (values |
| (1, ROW(1,2)), |
| (2, ROW(2,1)), |
| (3, ROW(1,2)), |
| (4, ROW(2,1))) as t(id,struct) |
| where t.struct = ROW(2,1); |
| +----+--------+ |
| | ID | STRUCT | |
| +----+--------+ |
| | 2 | {2, 1} | |
| | 4 | {2, 1} | |
| +----+--------+ |
| (2 rows) |
| |
| !ok |
| |
| # [CALCITE-3482] Equality of nested ROWs returns false for identical literal value |
| select * from |
| (values |
| (1, ROW(2, ROW(4,3))), |
| (2, ROW(2, ROW(3,4))), |
| (3, ROW(1, ROW(3,4))), |
| (4, ROW(2, ROW(3,4)))) as t(id,struct) |
| where t.struct = ROW(2, ROW(3,4)); |
| +----+-------------+ |
| | ID | STRUCT | |
| +----+-------------+ |
| | 2 | {2, {3, 4}} | |
| | 4 | {2, {3, 4}} | |
| +----+-------------+ |
| (2 rows) |
| |
| !ok |
| |
| !use scott |
| |
| # [CALCITE-4434] Cannot implement 'CASE row WHEN row ...' |
| SELECT deptno, job, |
| CASE (deptno, job) |
| WHEN (20, 'CLERK') THEN 1 |
| WHEN (30, 'SALESMAN') THEN 2 |
| ELSE 3 |
| END AS x |
| FROM "scott".emp |
| WHERE empno < 7600; |
| +--------+----------+---+ |
| | DEPTNO | JOB | X | |
| +--------+----------+---+ |
| | 20 | CLERK | 1 | |
| | 20 | MANAGER | 3 | |
| | 30 | SALESMAN | 2 | |
| | 30 | SALESMAN | 2 | |
| +--------+----------+---+ |
| (4 rows) |
| |
| !ok |
| |
| # Equivalent to previous |
| SELECT deptno, job, |
| CASE |
| WHEN deptno = 20 AND job = 'CLERK' THEN 1 |
| WHEN deptno = 30 AND job = 'SALESMAN' THEN 2 |
| ELSE 3 |
| END AS x |
| FROM "scott".emp |
| WHERE empno < 7600; |
| +--------+----------+---+ |
| | DEPTNO | JOB | X | |
| +--------+----------+---+ |
| | 20 | CLERK | 1 | |
| | 20 | MANAGER | 3 | |
| | 30 | SALESMAN | 2 | |
| | 30 | SALESMAN | 2 | |
| +--------+----------+---+ |
| (4 rows) |
| |
| !ok |
| |
| |
| # [CALCITE-3627] Null check if all fields of ROW are null |
| select |
| ROW(null, null, null) is null AS all_null_is_null, |
| ROW(null, null, null) is not null AS all_null_is_not_null, |
| ROW(null, 1, null) is null AS except_one_all_null_is_null, |
| ROW(null, 1, null) is not null AS except_one_all_null_is_not_null, |
| NOT(ROW(null, 1, null) is null) AS reverse_null_check_except_one_all_null, |
| ROW(null, ROW(null, null), null) is null AS all_null_including_nested_row_is_null, |
| ROW(null, ROW(null, 1), null) is null AS all_null_except_nested_row_is_null; |
| +------------------+----------------------+-----------------------------+---------------------------------+----------------------------------------+---------------------------------------+------------------------------------+ |
| | ALL_NULL_IS_NULL | ALL_NULL_IS_NOT_NULL | EXCEPT_ONE_ALL_NULL_IS_NULL | EXCEPT_ONE_ALL_NULL_IS_NOT_NULL | REVERSE_NULL_CHECK_EXCEPT_ONE_ALL_NULL | ALL_NULL_INCLUDING_NESTED_ROW_IS_NULL | ALL_NULL_EXCEPT_NESTED_ROW_IS_NULL | |
| +------------------+----------------------+-----------------------------+---------------------------------+----------------------------------------+---------------------------------------+------------------------------------+ |
| | true | false | false | true | true | true | false | |
| +------------------+----------------------+-----------------------------+---------------------------------+----------------------------------------+---------------------------------------+------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| # [CALCITE-7311] Support the syntax ROW(*) to create a nested ROW type with all columns |
| select row(*) from emp limit 1; |
| +----------------------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------------------+ |
| | {7369, SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} | |
| +----------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(emp.*) from emp limit 1; |
| +----------------------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------------------+ |
| | {7369, SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} | |
| +----------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(emp.*, dept.*) from emp join dept on emp.deptno = dept.deptno limit 1; |
| +---------------------------------------------------------------------------------------+ |
| | EXPR$0 | |
| +---------------------------------------------------------------------------------------+ |
| | {7782, CLARK, MANAGER, 7839, 1981-06-09, 2450.00, null, 10, 10, ACCOUNTING, NEW YORK} | |
| +---------------------------------------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(d.*, row(d.*)) from dept d limit 1; |
| +--------------------------------------------------------+ |
| | EXPR$0 | |
| +--------------------------------------------------------+ |
| | {10, ACCOUNTING, NEW YORK, {10, ACCOUNTING, NEW YORK}} | |
| +--------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| # [CALCITE-7364] Support the syntax ROW(T.* EXCLUDE cols) for creating nested ROW values |
| select row(* exclude(empno)) from emp order by empno limit 1; |
| +----------------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------------+ |
| | {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} | |
| +----------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(emp.* exclude(emp.empno)) from emp order by empno limit 1; |
| +----------------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------------+ |
| | {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} | |
| +----------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(* exclude(empno, mgr)) from emp order by empno limit 1; |
| +----------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------+ |
| | {SMITH, CLERK, 1980-12-17, 800.00, null, 20} | |
| +----------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(emp.* exclude(emp.empno), dept.*) from emp join dept on emp.deptno = dept.deptno order by emp.empno limit 1; |
| +--------------------------------------------------------------------------+ |
| | EXPR$0 | |
| +--------------------------------------------------------------------------+ |
| | {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20, 20, RESEARCH, DALLAS} | |
| +--------------------------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| select row(emp.* exclude(emp.empno), dept.* exclude(dept.deptno)) from emp join dept on emp.deptno = dept.deptno order by emp.empno limit 1; |
| +----------------------------------------------------------------------+ |
| | EXPR$0 | |
| +----------------------------------------------------------------------+ |
| | {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20, RESEARCH, DALLAS} | |
| +----------------------------------------------------------------------+ |
| (1 row) |
| |
| !ok |
| |
| # End struct.iq |
