| # operator.iq - expressions involving operators |
| # |
| # 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 scott |
| !set outputformat mysql |
| |
| # [CALCITE-1095] NOT precedence |
| select * from "scott".emp where not sal > 1300; |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| (6 rows) |
| |
| !ok |
| |
| select count(*) as c from "scott".emp where not ename = 'SMITH'; |
| +----+ |
| | C | |
| +----+ |
| | 13 | |
| +----+ |
| (1 row) |
| |
| !ok |
| |
| select count(*) as c from "scott".emp where not not ename = 'SMITH'; |
| +---+ |
| | C | |
| +---+ |
| | 1 | |
| +---+ |
| (1 row) |
| |
| !ok |
| |
| select * from "scott".emp where not sal > 1300 and not sal < 1200; |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| (3 rows) |
| |
| !ok |
| |
| # MULTISET EXCEPT |
| values multiset ['a', 'c', 'a'] multiset except multiset ['a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [c, a] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET EXCEPT ALL |
| values multiset ['a', 'c', 'a'] multiset except all multiset ['a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [c, a] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET EXCEPT DISTINCT |
| values multiset ['a', 'c', 'a'] multiset except distinct multiset ['a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [c] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET UNION |
| values multiset ['a', 'c', 'b'] multiset union multiset ['a']; |
| +--------------+ |
| | EXPR$0 | |
| +--------------+ |
| | [a, c, b, a] | |
| +--------------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET UNION ALL |
| values multiset ['a', 'c', 'b'] multiset union all multiset ['a']; |
| +--------------+ |
| | EXPR$0 | |
| +--------------+ |
| | [a, c, b, a] | |
| +--------------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET UNION DISTINCT |
| values multiset ['a', 'c', 'b'] multiset union distinct multiset ['a']; |
| +-----------+ |
| | EXPR$0 | |
| +-----------+ |
| | [a, b, c] | |
| +-----------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET INTERSECT |
| values multiset ['a', 'c', 'a', 'a'] multiset intersect multiset ['a', 'a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [a, a] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET INTERSECT ALL |
| values multiset ['a', 'c', 'a', 'a'] multiset intersect all multiset ['a', 'a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [a, a] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # MULTISET INTERSECT DISTINCT |
| values multiset ['a', 'c', 'a', 'a'] multiset intersect distinct multiset ['a', 'a']; |
| +--------+ |
| | EXPR$0 | |
| +--------+ |
| | [a] | |
| +--------+ |
| (1 row) |
| |
| !ok |
| |
| # FLOOR and CEIL of DATE |
| select v, |
| case when b then 'ceil' else 'floor' end as op, |
| case when b then ceil(v to year) else floor(v to year) end as y, |
| case when b then ceil(v to quarter) else floor(v to quarter) end as q, |
| case when b then ceil(v to month) else floor(v to month) end as m, |
| case when b then ceil(v to week) else floor(v to week) end as w, |
| case when b then ceil(v to day) else floor(v to day) end as d |
| from (values (date '2019-07-05')) as t(v), |
| (values false, true) as u(b) |
| order by 1,2; |
| +------------+-------+------------+------------+------------+------------+------------+ |
| | V | OP | Y | Q | M | W | D | |
| +------------+-------+------------+------------+------------+------------+------------+ |
| | 2019-07-05 | ceil | 2020-01-01 | 2019-10-01 | 2019-08-01 | 2019-07-07 | 2019-07-05 | |
| | 2019-07-05 | floor | 2019-01-01 | 2019-07-01 | 2019-07-01 | 2019-06-30 | 2019-07-05 | |
| +------------+-------+------------+------------+------------+------------+------------+ |
| (2 rows) |
| |
| !ok |
| |
| # FLOOR and CEIL of TIMESTAMP |
| select v, |
| case when b then 'ceil' else 'floor' end as op, |
| case when b then ceil(v to year) else floor(v to year) end as y, |
| case when b then ceil(v to quarter) else floor(v to quarter) end as q, |
| case when b then ceil(v to month) else floor(v to month) end as m, |
| case when b then ceil(v to week) else floor(v to week) end as w, |
| case when b then ceil(v to day) else floor(v to day) end as d, |
| case when b then ceil(v to hour) else floor(v to hour) end as h, |
| case when b then ceil(v to minute) else floor(v to minute) end as mi, |
| case when b then ceil(v to second) else floor(v to second) end as s |
| from (values (timestamp '2019-07-05 12:34:56')) as t(v), |
| (values false, true) as u(b) |
| order by 1,2; |
| +---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |
| | V | OP | Y | Q | M | W | D | H | MI | S | |
| +---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |
| | 2019-07-05 12:34:56 | ceil | 2020-01-01 00:00:00 | 2019-10-01 00:00:00 | 2019-08-01 00:00:00 | 2019-07-07 00:00:00 | 2019-07-06 00:00:00 | 2019-07-05 13:00:00 | 2019-07-05 12:35:00 | 2019-07-05 12:34:56 | |
| | 2019-07-05 12:34:56 | floor | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | 2019-06-30 00:00:00 | 2019-07-05 00:00:00 | 2019-07-05 12:00:00 | 2019-07-05 12:34:00 | 2019-07-05 12:34:56 | |
| +---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ |
| (2 rows) |
| |
| !ok |
| |
| # FLOOR and CEIL of TIME |
| select v, |
| case when b then 'ceil' else 'floor' end as op, |
| case when b then ceil(v to year) else floor(v to year) end as y, |
| case when b then ceil(v to quarter) else floor(v to quarter) end as q, |
| case when b then ceil(v to month) else floor(v to month) end as m, |
| case when b then ceil(v to week) else floor(v to week) end as w, |
| case when b then ceil(v to day) else floor(v to day) end as d, |
| case when b then ceil(v to hour) else floor(v to hour) end as h, |
| case when b then ceil(v to minute) else floor(v to minute) end as mi, |
| case when b then ceil(v to second) else floor(v to second) end as s |
| from (values (time '12:34:56.7')) as t(v), |
| (values false, true) as u(b) |
| order by 1,2; |
| +----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ |
| | V | OP | Y | Q | M | W | D | H | MI | S | |
| +----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ |
| | 12:34:56 | ceil | 12:34:57 | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 13:00:00 | 12:35:00 | 12:34:57 | |
| | 12:34:56 | floor | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 12:00:00 | 12:34:00 | 12:34:56 | |
| +----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ |
| (2 rows) |
| |
| !ok |
| |
| select "T"."X"[1] as x1 from (VALUES (ROW(ROW(3, 7), ROW(4, 8)))) as T(x, y); |
| |
| X1 INTEGER(10) NOT NULL |
| !type |
| +----+ |
| | X1 | |
| +----+ |
| | 3 | |
| +----+ |
| (1 row) |
| |
| !ok |
| |
| select "T"."X"[CAST(2 AS BIGINT)] as x2 from (VALUES (ROW(ROW(3, 7), ROW(4, 8)))) as T(x, y); |
| |
| X2 INTEGER(10) NOT NULL |
| !type |
| +----+ |
| | X2 | |
| +----+ |
| | 7 | |
| +----+ |
| (1 row) |
| |
| !ok |
| |
| select "T"."Y"[CAST(1 AS TINYINT)] as y1 from (VALUES (ROW(ROW(3, 7), ROW(4, 8)))) as T(x, y); |
| |
| Y1 INTEGER(10) NOT NULL |
| !type |
| +----+ |
| | Y1 | |
| +----+ |
| | 4 | |
| +----+ |
| (1 row) |
| |
| !ok |
| |
| select "T"."Y"[CAST(2 AS SMALLINT)] as y2 from (VALUES (ROW(ROW(3, 7), ROW(4, 8)))) as T(x, y); |
| |
| Y2 INTEGER(10) NOT NULL |
| !type |
| +----+ |
| | Y2 | |
| +----+ |
| | 8 | |
| +----+ |
| (1 row) |
| |
| !ok |
| |
| !use bookstore |
| |
| select au."birthPlace"['city'] as city from "bookstore"."authors" au; |
| |
| CITY VARCHAR |
| !type |
| +-----------+ |
| | CITY | |
| +-----------+ |
| | Besançon | |
| | Heraklion | |
| | Ionia | |
| +-----------+ |
| (3 rows) |
| |
| !ok |
| |
| # we have "birthPlace(coords, city, country)", so city has index 2 |
| select au."birthPlace"[2] as city from "bookstore"."authors" au; |
| |
| CITY VARCHAR |
| !type |
| +-----------+ |
| | CITY | |
| +-----------+ |
| | Besançon | |
| | Heraklion | |
| | Ionia | |
| +-----------+ |
| (3 rows) |
| |
| !ok |
| |
| select au."birthPlace"[CAST(2 AS SMALLINT)] as city from "bookstore"."authors" au; |
| |
| CITY VARCHAR |
| !type |
| +-----------+ |
| | CITY | |
| +-----------+ |
| | Besançon | |
| | Heraklion | |
| | Ionia | |
| +-----------+ |
| (3 rows) |
| |
| !ok |
| |
| select au."birthPlace"[CAST(NULL AS INTEGER)] as city from "bookstore"."authors" au; |
| Cannot infer type of field at position null within ROW type: RecordType(RecordType(JavaType(class java.math.BigDecimal) latitude, JavaType(class java.math.BigDecimal) longtitude) coords, JavaType(class java.lang.String) city, JavaType(class java.lang.String) country) |
| !error |
| |
| select au."birthPlace"[2] as city from "bookstore"."authors" au; |
| |
| CITY VARCHAR |
| !type |
| +-----------+ |
| | CITY | |
| +-----------+ |
| | Besançon | |
| | Heraklion | |
| | Ionia | |
| +-----------+ |
| (3 rows) |
| |
| !ok |
| |
| # End operator.iq |
