| -- Test: TEST038 (Core) |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- 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. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| -- |
| -- Functionality: Expression evaluations, including string functions, |
| -- datetime and datetime literals, current, constant |
| -- folding, arithmetic and logical evaluations, |
| -- simplifications, aggregates. It also contains |
| -- simple TPCD functionality tests (Q4, 6, 12, 14). |
| -- Expected files: EXPECTED038, EXPECTED038.MP, EXPECTED038.MX |
| -- Table created: T038edat, T038empl, T038sls, T038prts, T038dt1, |
| -- T028tm1, T038TS1, T038tcf, T038tsf, T038empt, |
| -- T038dept, T038corp, T038a, T038b, T038c, T038d, |
| -- T038e, T038f, T038g, T038h, T038i, T038j, T038k, |
| -- T038l, T038LI, T038part, T038sf (27) |
| -- Limitations: |
| -- To do: - EXPECTED038.MX need to be removed after IEEE floating data |
| -- type is supported on R2. |
| -- Revision history: |
| -- (1/28/02) - Copied from fullstack/TEST038 |
| -- (1/12/02) - Merged some tests from TEST012(Executor): POSITION, |
| -- TRIM, ASCII, CHAR, INSERT, REPEAT, REPLACE. |
| -- (1/13/02) - Added PAD tests |
| -- (10/12/06) - Changed PAD tests for New LPAD and RPAD algorithms |
| |
| obey TEST038(clnup); |
| obey TEST038(tests); |
| obey TEST038(aqr); |
| obey TEST038(clnup); |
| exit; |
| |
| ?section tests |
| log LOG038 clear; |
| |
| ----------------------------------------------------------------------------- |
| -- NOTE: This test always produces 4 mismatched lines in the DIFF038 file, |
| -- because the CURDATE and CURTIME in the test script. For example, |
| -- DIFF038 might contain the followings: |
| -- |
| -- 269c269 |
| -- < 200 1997-12-17 12:10:50 99 |
| -- --- |
| -- > 200 1997-12-17 14:34:53 99 |
| -- 271,272c271,272 |
| -- < 400 1997-05-12 12:10:51 99 |
| -- < 500 1997-12-17 12:10:51 99 |
| -- --- |
| -- > 400 1997-05-12 14:34:55 99 |
| -- > 500 1997-12-17 14:34:56 99 |
| -- 297c297 |
| -- < 600 1997-12-17 12:10:54.538192 |
| -- --- |
| -- > 600 1997-12-17 14:34:59.062995 |
| ------------------------------------------------------------------------------ |
| |
| -- CREATE database |
| |
| create TABLE T038edat (EMPNUM int NOT NULL, first_name char(20) not null, |
| last_name char(20) not null); |
| insert into T038edat values (100, 'charu', 'bhave '); |
| insert into T038edat values (200, 'linda', 'ho'); |
| insert into T038edat values (300, ' ARUN ', 'Thomas'); |
| |
| create TABLE T038empl (EMPNUM int NOT NULL, firstname char(20) not null, |
| lastname char(20) not null, age int, salary int); |
| insert into T038empl values (101,'charu','bhave', 45, 50000); |
| insert into T038empl values (102,'linda','ho ', 30, 55000); |
| insert into T038empl values (103,'Selva','Ganesan', 25, 65000); |
| insert into T038empl values (104,'Tom ','Rogers', 60, 90000); |
| |
| -- ODBC Parser Test for CONCAT, LOCATE, UCASE, LCASE, RTRIM, LTRIM, |
| -- LENGTH, SUBSTRING, LEFT, RIGHT |
| |
| select {fn concat(first_name, last_name)} from T038edat; |
| |
| select * from T038edat where {fn locate('lin', first_name)} > 0; |
| |
| select * from T038edat where {fn locate({fn ucase('aru')}, first_name)} > 0; |
| |
| select * from T038edat where {fn length ({fn rtrim(last_name)})} = 5; |
| |
| select last_name from T038edat where {fn substring(last_name, 1, 3)} = 'Tho'; |
| |
| select * from T038edat |
| where {fn locate('ma', {fn concat(first_name, last_name)})} > 0; |
| |
| select * from T038empl where {fn locate('a', firstname)} > 2; |
| |
| select * from T038edat |
| where {fn locate('ma', {fn concat(first_name, last_name)}, 1)} > 0; |
| |
| select {fn lcase(first_name)} from T038edat; |
| |
| select {fn ucase(first_name)} from T038edat; |
| |
| select {fn concat({fn rtrim(first_name)}, {fn rtrim(last_name)})} from T038edat; |
| |
| select {fn concat({fn ltrim(first_name)}, {fn ltrim(last_name)})} from T038edat; |
| |
| select last_name from T038edat where {fn left(last_name, 3)} = 'Tho'; |
| |
| select * from T038edat |
| where {fn left ({fn ltrim(first_name)}, 3)} = 'ARU'; |
| |
| -- expect 0 rows -- |
| select last_name from T038edat where {fn left(last_name, 3)} = 'ARU'; |
| |
| -- expect 0 rows -- |
| select * from T038edat where {fn right(first_name, 3)} = 'aru'; |
| |
| select * from T038edat |
| where {fn right ({fn rtrim(first_name)}, 3)} = 'aru'; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: CURDATE, CURTIME |
| |
| create table T038sls (order_num int, order_date date, order_time time, |
| order_qty int); |
| insert into T038sls values(100, date '1997-01-30', time '13:40:05', 1000); |
| insert into T038sls values(200, {fn curdate()}, {fn curtime()}, 99); |
| insert into T038sls values(300, date '1996-08-10', time '10:20:10', 6000); |
| insert into T038sls values(400, date '1997-05-12', {fn curtime()}, 99); |
| insert into T038sls values(500, {fn curdate()}, {fn curtime()}, 99); |
| select * from T038sls; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: NOW |
| |
| create table T038prts(T038part_num int, order_time timestamp); |
| insert into T038prts values(600, {fn now()}); |
| select * from T038prts; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: {d'date-literal'} |
| |
| CREATE TABLE T038dt1 (C1 DATE); |
| INSERT INTO T038dt1 VALUES ({d '1993-12-30'}); |
| SELECT C1 FROM T038dt1; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: {t'time-literal'} |
| |
| CREATE TABLE T028tm1 (C1 TIME); |
| INSERT INTO T028tm1 VALUES ({t '10:11:12'}); |
| SELECT C1 FROM T028tm1; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: {ts'timestamp-literal'} |
| |
| CREATE TABLE T038TS1 (C1 TIMESTAMP); |
| INSERT INTO T038TS1 VALUES ({ts '1992-12-31 23:45:23.123456'}); |
| SELECT C1 FROM T038TS1; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: CONVERT |
| |
| create table T038tcf (C1 CHAR(20), |
| C2 VARCHAR(20), |
| C3 DECIMAL(10,5), |
| C4 NUMERIC(10,5), |
| C5 SMALLINT, |
| C6 SMALLINT UNSIGNED, |
| C7 INTEGER, |
| C8 INTEGER UNSIGNED, |
| C9 REAL, |
| C10 FLOAT, |
| C11 DOUBLE PRECISION, |
| C12 DATE, |
| C13 TIME, |
| C14 TIMESTAMP, |
| #ifMX |
| C15 LONG VARCHAR, |
| #ifMX |
| #ifMP |
| C15 VARCHAR(100), |
| #ifMP |
| #ifMX |
| C16 BIGINT |
| #ifMX |
| #ifMP |
| C16 LARGEINT |
| #ifMP |
| ) |
| #ifNT |
| attribute blocksize 4096 |
| #ifNT |
| ; |
| insert into T038tcf values ('-0123456789','-0123456789',-1234.56789, |
| -5678.12345,-1234,6789,-12345,56789, |
| -12340.0,-12300.0,-12345670.0,{d'1993-12-30'}, |
| {t '11:45:23'}, |
| {ts '1992-12-31 23:45:23.123456'}, |
| '0123456789',9876543); |
| |
| select {fn CONVERT(C1,CHAR)} from T038tcf; |
| -- output => "-0123456789 "; |
| select {fn CONVERT(C2,VARCHAR)} from T038tcf; |
| -- output => "-0123456789"; |
| select {fn CONVERT(C3,DECIMAL)} from T038tcf; |
| -- output => "-1234.56789"; |
| select {fn CONVERT(C4,DECIMAL)} from T038tcf; |
| -- output => "-5678.12345"; |
| select {fn CONVERT(C5,SMALLINT)} from T038tcf; |
| -- output => "-1234"; |
| select {fn CONVERT(C6,SMALLINT)} from T038tcf; |
| -- output => "6789"; |
| select {fn CONVERT(C7,INTEGER)} from T038tcf; |
| -- output => "-12345"; |
| select {fn CONVERT(C8,INTEGER)} from T038tcf; |
| -- output => "56789"; |
| select {fn CONVERT(C9,REAL)} from T038tcf; |
| -- output => "-12340.0"; |
| select {fn CONVERT(C10,FLOAT)} from T038tcf; |
| -- output => "'-12300.0"; |
| select {fn CONVERT(C11,DOUBLE PRECISION)} from T038tcf; |
| -- output => "-12345670.0"; |
| select {fn CONVERT(C12,DATE)} from T038tcf; |
| -- output => "1993-12-30"; |
| select {fn CONVERT(C13,TIME)} from T038tcf; |
| -- output => "11:45:23"; |
| select {fn CONVERT(C14,TIMESTAMP)} from T038tcf; |
| -- output => "1992-12-31 23:45:23.123456"; |
| select {fn CONVERT(C15,LONG VARCHAR)} from T038tcf; |
| -- output => "0123456789"; |
| select {fn CONVERT(C16,BIGINT)} from T038tcf; |
| -- output => "9876543"; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: LCASE, UCASE, LEFT, RIGHT, LTRIM, RTRIM |
| |
| create table T038tsf (c1 char(10), |
| c2 char(10), |
| c3 char(10), |
| c4 char(10), |
| c5 int |
| ); |
| insert into T038tsf values ('ABCD','efgh',' 1234 ','CD',65); |
| select {fn LCASE(c1)} from T038tsf; |
| -- output => 'abcd'; |
| select {fn LEFT(c1,2)} from T038tsf; |
| -- output => 'AB'; |
| select {fn LTRIM(c3)} from T038tsf; |
| -- output => '1234 '; |
| select {fn RIGHT(c2,3)} from T038tsf; |
| -- output => ' '; |
| select {fn RTRIM(c3)} from T038tsf; |
| -- output => ' 1234'; |
| select {fn UCASE(c2)} from T038tsf; |
| -- output => 'EFGH'; |
| |
| -- CREATE database and perform ... |
| -- ODBC Parser Test for: nested outer join |
| |
| create table T038empt (empnum INT NOT NULL, |
| empname CHAR(20) NOT NULL, |
| deptnum INT NOT NULL |
| ); |
| insert into T038empt values (100, 'BHAVE', 6400); |
| insert into T038empt values (100, 'RAO' , 6500); |
| insert into T038empt values (100, 'GANESAN', 6400); |
| |
| create table T038dept (deptnum INT NOT NULL, |
| deptname CHAR(20) NOT NULL, |
| manager CHAR(20) NOT NULL |
| ); |
| insert into T038dept values (6400, 'DEV', 'YOW'); |
| insert into T038dept values (6500, 'QA' , 'DENNIS'); |
| insert into T038dept values (6000, 'SRC', 'JUDY'); |
| |
| create table T038corp (unitnum int not null, |
| deptnum int not null, |
| revenvue decimal(10,3) not null |
| ); |
| insert into T038corp values (101, 6400, 10000); |
| |
| SELECT EMPNAME,DEPTNAME,MANAGER |
| FROM {oj T038empt T038E LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| }; |
| SELECT EMPNAME,DEPTNAME,MANAGER |
| FROM T038empt T038E LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM; |
| |
| SELECT * |
| FROM {oj T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM |
| }; |
| SELECT * |
| FROM T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM; |
| |
| SELECT * |
| FROM {oj T038empt T038E |
| LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM |
| }; |
| SELECT * |
| FROM T038empt T038E |
| LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM; |
| |
| SELECT * |
| FROM {oj T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM} |
| WHERE T038E.DEPTNUM < 6000; |
| SELECT * |
| FROM T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| WHERE T038E.DEPTNUM < 6000; |
| |
| SELECT * |
| FROM {oj T038empt T038E |
| LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM |
| }; |
| SELECT * |
| FROM T038empt T038E |
| LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM |
| LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM; |
| |
| SELECT * |
| FROM {oj T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM AND T038E.DEPTNUM < 7000 |
| LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM |
| }; |
| SELECT * |
| FROM T038empt T038E |
| LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM AND T038E.DEPTNUM < 7000 |
| LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM; |
| |
| |
| create table T038a(a1 int); |
| |
| create table T038b(b1 date); |
| |
| create table T038c(c1 char(10)); |
| |
| create table T038d(d1 timestamp); |
| |
| create table T038e(e1 time); |
| |
| create table T038f(f1 decimal(18,4)); |
| |
| create table T038g(g1 numeric(18,10)); |
| |
| create table T038h(h1 float(30)); |
| |
| create table T038i(i1 int not null ); |
| |
| create table T038j(j1 varchar(10)); |
| |
| create table T038k(k1 PIC X(12)); |
| |
| create table T038l(l1 char varying (20) upshift); |
| |
| create table T038m(m1 largeint); |
| |
| create table T038n(n1 smallint); |
| |
| create table T038o(o1 real); |
| |
| create table T038p(p1 double precision); |
| |
| #ifMX |
| create table T038q(q1 bit precision int (5) unsigned); |
| #ifMX |
| #ifMP |
| create table T038q(q1 smallint unsigned); |
| #ifMP |
| |
| insert into T038a values (1); |
| insert into T038a values (2); |
| insert into T038a values (3); |
| insert into T038a values (4); |
| insert into T038a values (5); |
| insert into T038a values (6); |
| insert into T038a values (7); |
| insert into T038a values (7); |
| insert into T038a values (8); |
| insert into T038a values (8); |
| insert into T038a values (8); |
| insert into T038a values (8); |
| |
| insert into T038b values (date '10/01/1996'); |
| insert into T038b values (date '11/01/1996'); |
| insert into T038b values (date '12/01/1996'); |
| insert into T038b values (date '01/01/1997'); |
| insert into T038b values (date '02/02/1997'); |
| insert into T038b values (date '03/03/1997'); |
| insert into T038b values (date '04/04/1997'); |
| insert into T038b values (date '05/05/1997'); |
| insert into T038b values (date '06/06/1997'); |
| insert into T038b values (date '07/07/1997'); |
| |
| insert into T038c values ('abcd'); |
| insert into T038c values ('efghi'); |
| insert into T038c values ('jklmno'); |
| insert into T038c values ('pqrstuv'); |
| insert into T038c values ('wxyz'); |
| insert into T038c values ('1234567890'); |
| |
| insert into T038d values (timestamp '01.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '02.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '03.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '04.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '05.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '06.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '07.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '08.01.1997 03.04.55.123456'); |
| insert into T038d values (timestamp '09.01.1997 03.04.55'); |
| insert into T038d values (timestamp '10.01.1997 03.04.55'); |
| |
| insert into T038e values (time '11:58:59.236 pm' + interval '1.12' second); |
| insert into T038e values (time '01:58:59.236 pm'); |
| insert into T038e values (time '02:58:59.236 pm'); |
| insert into T038e values (time '03:58:59.236 pm'); |
| insert into T038e values (time '04:58:59.236 pm'); |
| insert into T038e values (time '05:58:59.236 pm'); |
| insert into T038e values (time '06:58:59.236 pm'); |
| insert into T038e values (time '07:58:59.236 pm'); |
| insert into T038e values (time '08:58:59.236 pm'); |
| insert into T038e values (time '09:58:59.236 pm'); |
| |
| insert into T038f values (123456789456.789 + 10000000000 * 3); |
| insert into T038f values (123476789456.789); |
| insert into T038f values (123477789456.789); |
| insert into T038f values (123478789456.789); |
| insert into T038f values (123479789456.789); |
| insert into T038f values (123489789456.789); |
| insert into T038f values (123496789456.789); |
| insert into T038f values (123576789456.789); |
| insert into T038f values (123676789456.789); |
| insert into T038f values (123776789456.789); |
| |
| insert into T038g values (123456.789); |
| insert into T038g values (123476.789); |
| insert into T038g values (123477.789); |
| insert into T038g values (123478.789); |
| insert into T038g values (123479.789); |
| insert into T038g values (123489.789); |
| insert into T038g values (123496.789); |
| insert into T038g values (123576.789); |
| insert into T038g values (123676.789); |
| insert into T038g values (123776.789); |
| |
| |
| insert into T038h values (1234567890 - 2 + 2 * 4.0); |
| insert into T038h values (1234567890 - 2 + 2 * 5.0); |
| insert into T038h values (1234567890 - 2 + 2 * 6.0); |
| insert into T038h values (1234567890 - 2 + 2 * 7.0); |
| insert into T038h values (1234567890 - 2 + 2 * 8.0); |
| insert into T038h values (1234567890 - 2 + 2 * 9.0); |
| insert into T038h values (1234567890 - 2 + 2 * 10.0); |
| insert into T038h values (1234567890 - 2 + 2 * 11.0); |
| insert into T038h values (1234567890 - 2 + 2 * 12.0); |
| insert into T038h values (1234567890 - 2 + 2 * 13.0); |
| |
| insert into T038i values (1); |
| insert into T038i values (2); |
| insert into T038i values (3); |
| insert into T038i values (4); |
| insert into T038i values (5); |
| insert into T038i values (6); |
| insert into T038i values (7); |
| insert into T038i values (8); |
| |
| insert into T038j values ('abcd'); |
| insert into T038j values ('efghi'); |
| insert into T038j values ('jklmno'); |
| insert into T038j values ('pqrstuv'); |
| insert into T038j values ('wxyz'); |
| insert into T038j values ('1234567890'); |
| |
| insert into T038k values ('abcd'); |
| insert into T038k values ('efghi'); |
| insert into T038k values ('jklmno'); |
| insert into T038k values ('pqrstuv'); |
| insert into T038k values ('wxyz'); |
| insert into T038k values ('1234567890'); |
| |
| insert into T038l values ('abcd'); |
| insert into T038l values ('efghi'); |
| insert into T038l values ('jklmno'); |
| insert into T038l values ('pqrstuv'); |
| insert into T038l values ('wxyz'); |
| insert into T038l values ('1234567890'); |
| |
| insert into T038m values (1234567890 - 2 + 2 * 4.0); |
| insert into T038m values (1234567890 - 2 + 2 * 5.0); |
| insert into T038m values (1234567890 - 2 + 2 * 6.0); |
| insert into T038m values (1234567890 - 2 + 2 * 7.0); |
| insert into T038m values (1234567890 - 2 + 2 * 8.0); |
| insert into T038m values (1234567890 - 2 + 2 * 9.0); |
| insert into T038m values (1234567890 - 2 + 2 * 10.0); |
| insert into T038m values (1234567890 - 2 + 2 * 11.0); |
| insert into T038m values (1234567890 - 2 + 2 * 12.0); |
| insert into T038m values (1234567890 - 2 + 2 * 13.0); |
| |
| insert into T038n values (1); |
| insert into T038n values (2); |
| insert into T038n values (3); |
| insert into T038n values (4); |
| insert into T038n values (5); |
| insert into T038n values (6); |
| insert into T038n values (7); |
| insert into T038n values (8); |
| |
| insert into T038o values (123456.789); |
| insert into T038o values (123476.789); |
| insert into T038o values (123477.789); |
| insert into T038o values (123478.789); |
| insert into T038o values (123479.789); |
| insert into T038o values (123489.789); |
| insert into T038o values (123496.789); |
| insert into T038o values (123576.789); |
| insert into T038o values (123676.789); |
| insert into T038o values (123776.789); |
| |
| insert into T038p values (123456.789); |
| insert into T038p values (123476.789); |
| insert into T038p values (123477.789); |
| insert into T038p values (123478.789); |
| insert into T038p values (123479.789); |
| insert into T038p values (123489.789); |
| insert into T038p values (123496.789); |
| insert into T038p values (123576.789); |
| insert into T038p values (123676.789); |
| insert into T038p values (123776.789); |
| |
| insert into T038q values(0); |
| insert into T038q values(31); |
| insert into T038q values(2); |
| insert into T038q values(4); |
| insert into T038q values(8); |
| insert into T038q values(16); |
| insert into T038q values(17); |
| insert into T038q values(18); |
| insert into T038q values(19); |
| insert into T038q values(20); |
| |
| select * from T038a where a1 > 1 + 1 and a1 < 3.2 - 3.2 + 5; |
| select * from T038a t1, T038a t2 where t1.a1 > 1 + 1 and t2.a1 < 3.2 - 3.2 + 5; |
| |
| select * from T038a where a1 < 1.0 / 2 + 3 or a1 > 3 + 3 / 1; |
| select * from T038a t1, T038a t2 where t1.a1 < 1.0 / 2 + 3 or t1.a1 > 3 + 3 / 1; |
| |
| select * from T038a where a1 = cast(2.0 + 3.0 as int) + 3; |
| select * from T038a t1, T038a t2 where t1.a1 = cast(2.0 + 3.0 as int) + 3; |
| |
| select * from T038a where a1 > char_length(trim(' abc ') || trim(' ef ')); |
| select * from T038a t1, T038a t2 where t1.a1 > char_length(trim(' abc ') || trim(' ef ')); |
| |
| select * from T038a group by a1 having 1 + 1 > 1 - 1 + sum(a1); |
| select * from T038a group by a1 having 1 + 1 + count(a1) > 1 - 1 + avg(a1); |
| select * from T038a group by a1 having min(a1) > 1 + 1 - 2; |
| select * from T038a group by a1 having max(a1) > 1 + 1 - 2; |
| select * from T038a where 999999999999999998 + 1 > juliantimestamp(date '1997-01-01' + interval '1' day); |
| select * from T038a where interval '1' day + current_date = current_date + interval '1' day; |
| select * from T038a where cast(current_time as timestamp) = current_timestamp; |
| |
| select * from T038a where not(not(not(not(not(a1 > 10))))); |
| |
| select * from T038a where not(not(not(not(not(not(a1 > 10)))))); |
| |
| select * from T038a where not(not(not(not(not(not(a1 > 10)))))); |
| |
| select * from T038a where not(not(not(not(not(a1 > 10 or 3 < 5))))); |
| select * from T038a where not(not(not(a1 > 10 or not(not(not(not(not(3 < 5)))))))); |
| select * from T038a where not(not(not(not(a1 > 10 or not(not(3 < 5)))))); |
| select * from T038a where not(not(not(not(not(a1 > 10 and not(not(3 < 5))))))); |
| select * from T038a where not(not(not(not(not(a1 > 10 and not(not(not(not(not(3 < 5)))))))))); |
| |
| select * from T038a where a1 < {fn CONVERT(a1,DECIMAL)} + |
| {fn CONVERT(a1,SMALLINT)} + |
| {fn CONVERT(a1,SMALLINT)} + |
| {fn CONVERT(a1,INTEGER)} + |
| {fn CONVERT(a1,INTEGER)} + |
| {fn CONVERT(a1,REAL)} + |
| {fn CONVERT(a1,FLOAT)} + |
| {fn CONVERT(a1,DOUBLE PRECISION)}; |
| |
| -- Genesis cases 10-980619-2242, 10-980707-3476, 10-980715-3485 |
| select a1 from T038a |
| where 94/1 between -47 and a1 + 0; |
| |
| select a1 from T038a |
| where 94/0 between -47 and a1 + 0; |
| |
| select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.5 and t0.a1; |
| |
| select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.4999999 and |
| t0.a1; |
| |
| select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.5000000001 and t0.a1; |
| |
| prepare s from |
| select t0.a1 |
| from T038a t0 |
| where -14.50 / 1.0 between 1.88 and t0.a1; |
| |
| execute s; |
| |
| prepare s from |
| select min(t0.a1) |
| from T038a t0 |
| where -14.50 / 0 between 1.88 and t0.a1; |
| |
| execute s; |
| |
| prepare s from |
| select min(t0.a1) |
| from T038a t0 |
| where -14.50 / 1 between 1.88/0 and t0.a1/0; |
| |
| execute s; |
| |
| select * from T038a where (1 + 1, 2 + 0, 2 + 2, 2 + 1 + 2) = (1 + 0 + 1, 2, 4, 5); |
| |
| select * from T038a where (-99 - 1, 2) = (-(select a1 from T038a where a1 = 1) + 1 - 1,1 + 1); |
| |
| select * from T038a where (select a1 from T038a where a1 = 0 + 1) = 0 + 1; |
| |
| select (select * from T038a where a1 = 0 + 1) from T038a; |
| |
| select 12 from T038a where case when a1 = 0 + 1 or a1 = 1 + 1 then 99 + 1 |
| else (select a1 from T038a where a1 = 0 + 1) end = 0 + 1; |
| |
| select * from T038a where a1 <= 10 and 3 >= 5; |
| |
| select * from T038a where 3 >= 5 and a1 <= 10; |
| |
| select * from T038a where a1 <= 10 and 3 < 5; |
| |
| select * from T038a where 3 < 5 and a1 <= 10; |
| |
| select * from T038a where a1 > 10 and 3 >= 5; |
| |
| select * from T038a where 3 >= 5 and a1 > 10; |
| |
| select * from T038a where a1 > 10 and 3 < 5; |
| |
| select * from T038a where 3 < 5 and a1 > 10; |
| |
| |
| select * from T038a where 1 <= 10 and 3 < 5; |
| |
| select * from T038a where 1 <= 10 and 3 >= 5; |
| |
| select * from T038a where 3 >= 5 and 1 <= 10; |
| |
| select * from T038a where 3 >= 5 and 1 > 10; |
| |
| |
| select * from T038a where a1 <= 10 or 3 >= 5; |
| |
| select * from T038a where 3 >= 5 or a1 <= 10; |
| |
| select * from T038a where a1 <= 10 or 3 < 5; |
| |
| select * from T038a where 3 < 5 or a1 <= 10; |
| |
| select * from T038a where a1 > 10 or 3 >= 5; |
| |
| select * from T038a where 3 >= 5 or a1 > 10; |
| |
| select * from T038a where a1 > 10 or 3 < 5; |
| |
| select * from T038a where 3 < 5 or a1 > 10; |
| |
| |
| select * from T038a where 1 <= 10 or 3 < 5; |
| |
| select * from T038a where 1 <= 10 or 3 >= 5; |
| |
| select * from T038a where 3 >= 5 or 1 <= 10; |
| |
| select * from T038a where 3 >= 5 or 1 > 10; |
| |
| |
| |
| -- Test simplifications |
| |
| select * from T038a where 1*(a1 + 0) = ((0 + a1) + (a1 * 0) + (0 * a1)) * 1; |
| |
| select * from T038a where a1 * 1 - 0 = a1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 * 1 - 0-0 -0 -0 -0 -0 -0 -0 -0; |
| |
| select * from T038a where (1 = 1 and a1 = 0) or 1 = 2; |
| |
| select * from T038a where 1 = 1 or 1 + 1 = 2 + 2; |
| |
| select * from T038a where 1 = 1 and 1 + 1 = 2; |
| |
| select * from T038a where a1 - 0 = a1 / 1; |
| |
| select * from T038a where a1 * 1 < a1 - 0; |
| |
| select * from T038a where a1 <= a1; |
| |
| select * from T038a where a1 > a1; |
| |
| select * from T038a where a1 >= a1; |
| |
| select * from T038a where a1 <> a1; |
| |
| select * from T038a where a1 between a1 / 1 + 0 and a1 - 0; |
| |
| select * from T038a where 17.0/17 * (select a1 from T038a where a1 = 1+1) / 1 + 0 between (select a1 from T038a where a1 = 0+1) - 0 and (select a1 from T038a where a1 = 2+1) / 1; |
| |
| SELECT SUM ( ( 78.59 )+0 ) , T0.a1 , ( AVG ( T0.a1 ) )+0 FROM T038a T0 WHERE -83 BETWEEN ( ( ( T0.a1 )/1 )*1 ) AND ( ( ( ( ( 42.88 )*1 )+0 )/1 )/1 ) GROUP BY T0.a1 HAVING MAX ( T0.a1 ) BETWEEN ( ( -30.44 )-0 ) AND ( AVG ( ( ( (T0.a1)+0 )-0 )*1 ) ) ORDER BY 1, 2, 1; |
| |
| SELECT DISTINCT T0.a1 , T0.a1 , T0.a1 from T038a T0 WHERE ( T0.a1 <= ( T0.a1 |
| )*1 ) AND ( ( NOT ( ( ( ( 56.1 )/1 )+0 BETWEEN (T0.a1 ) AND ( ( T0.a1 )-0 ) ) |
| OR ( ( ( T0.a1 )*1 >= T0.a1 ) OR ( NOT (( NOT ( ( NOT ( T0.a1 < ( ( 27.76 )+0 |
| )+0 ) ) OR ( ( ( ( ( T0.a1 )*1 BETWEEN ( T0.a1 ) AND ( T0.a1 ) ) AND ( ( 2.44 |
| )+0 BETWEEN ( T0.a1) AND ( T0.a1 ) ) ) AND ( ( ( T0.a1 )-0 <= ( T0.a1 )*1 ) OR |
| ( ( NOT (NOT ( NOT ( ( -50.2 )*1 > T0.a1 ) ) ) ) OR ( 59.75 BETWEEN ( T0.a1) |
| AND ( T0.a1 ) ) ) ) ) AND ( ( ( T0.a1 <> T0.a1 ) AND ( T0.a1 >=T0.a1 ) ) AND ( |
| NOT ( ( NOT ( T0.a1 = ( T0.a1 )*1 ) ) OR ( T0.a1 BETWEEN ( T0.a1 ) AND ( T0.a1 ) ) ) ) ) ) ) ) OR ( NOT ( NOT ( ( |
| T0.a1 )*1 > T0.a1 ) ) ) ) ) ) ) ) AND ( ( ( ( ( ( ( T0.a1 )/1 )-0 )/1 |
| )*1 BETWEEN ( T0.a1 ) AND ( ( T0.a1 )-0 ) ) OR ( NOT ( T0.a1 IN ( |
| T0.a1 , T0.a1 ) ) ) ) AND ( ( NOT ( NOT ( NOT ( T0.a1 BETWEEN ( T0.a1 |
| ) AND ( T0.a1 ) ) ) ) ) AND ( T0.a1 = T0.a1 ) ) ) ); |
| |
| SELECT |
| ( MIN ( T0.a1 ) )*1 , T0.a1 , AVG ( ( T0.a1 )*1 ) |
| from T038a T0 |
| WHERE |
| ( NOT ( 23 BETWEEN ( ( -46.68 )-0 ) AND ( T0.a1 ) ) ) OR ( ( T0.a1 )+0 |
| >= T0.a1 ) |
| GROUP BY T0.a1, T0.a1, T0.a1 |
| HAVING ( NOT ( ( ( ( NOT ( ( ( ( ( ( 0 )/1 < SUM ( T0.a1 ) ) OR ( ( |
| NOT ( ( T0.a1 )+0 IS NOT NULL ) ) OR ( T0.a1 > MIN ( ( T0.a1 )*1 ) ) ) |
| ) OR ( T0.a1 BETWEEN ( T0.a1 ) AND ( T0.a1 ) ) ) OR ( ( ( ( ( T0.a1 |
| IN ( ( T0.a1 )+0 , T0.a1 ) ) OR ( 0 BETWEEN ( T0.a1 ) AND ( T0.a1 ) ) |
| ) AND ( NOT ( ( ( NOT ( NOT ( ( T0.a1 BETWEEN ( AVG ( ( -76.27 )+0 ) |
| ) AND ( ( ( ( ( 16.46 )/1 )+0 )/1 )*1 ) ) AND ( T0.a1 < T0.a1 ) ) ) |
| ) AND ( T0.a1 IN ( ( T0.a1 )+0 , SUM ( ( ( ( -44.54 )/1 )-0 )-0 ) ) ) |
| ) OR ( NOT ( ( ( 39.53 )*1 )-0 BETWEEN ( MIN ( ( ( ( ( -96.86 )+0 )-0 |
| )+0 )-0 ) ) AND ( MIN ( ( a1 )*1 ) ) ) ) ) ) ) AND ( MIN ( T0.a1 ) <= |
| ( T0.a1 )-0 ) ) OR ( ( ( T0.a1 )+0 )*1 <= MAX ( T0.a1 ) ) ) ) AND ( |
| T0.a1 BETWEEN ( T0.a1 ) AND ( ( MIN ( T0.a1 ) )-0 ) ) ) ) OR ( ( |
| T0.a1 BETWEEN ( T0.a1 ) AND ( ( -11.50 )/1 ) ) AND ( NOT ( NOT ( ( |
| -35.70 BETWEEN ( ( MIN ( T0.a1 ) )*1 ) AND ( T0.a1 ) ) AND ( ( ( |
| T0.a1 > T0.a1 ) OR ( SUM ( ( T0.a1 )/1 ) BETWEEN ( ( ( T0.a1 )/1 )-0 |
| ) AND ( T0.a1 ) ) ) AND ( MAX ( T0.a1 ) >= T0.a1 ) ) ) ) ) ) ) AND ( |
| T0.a1 >= AVG ( ( ( ( T0.a1 )/1 )-0 )+0 ) ) ) AND ( ( T0.a1 )*1 IS NOT |
| NULL ) ) ) OR ( T0.a1 <> T0.a1 ) |
| ORDER BY 3 |
| ; |
| |
| select * from T038a where a1 < 2 + {fn CONVERT(a1,BIGINT)}; |
| |
| update T038a set a1 = a1 + 1 + 1 where a1 = 1 - 1 + 1 * 2; |
| |
| select * from T038a where case a1 when 1 + 1 then 2 + 1 when 3 + 1 then 4 + 5 else 5 end = 2 + 1; |
| |
| select * from T038a group by a1 having case a1 when 1 + 1 then 2 + 1 else 5 + 1 end = 2; |
| |
| select * from T038a where 2 + 2 + NULL = cast(char_length(trim(' abc ')) as int) + 2; |
| select * from T038a t1, T038a t2 where 2 + 2 + NULL = cast(char_length(trim(' abc ')) as int) + 2; |
| select * from T038a where a1 > dayofweek(date '1997-01-01' + interval '1' day) + 1; |
| |
| select * from T038a where a1 < 9 + 1 and 3 + 1 < 5 + 1 and a1 > 20 and not(4 + 1 < 3 + 1); |
| |
| select * from T038a where a1 = cast (-2147483648 as int) - 1; |
| select * from T038a where a1 = cast(2147483647 as int) + 1; |
| select * from T038a where a1 = cast (-2147483647 as int) - 1; |
| select * from T038a where a1 = cast(2147483646 as int) + 1; |
| select * from T038a where a1 = cast (0 as int unsigned) - 1; |
| select * from T038a where a1 = cast (4294967295 as int unsigned) + 1; |
| select * from T038a where a1 = cast (1 as int unsigned) - 1; |
| select * from T038a where a1 = cast (4294967294 as int unsigned) + 1; |
| |
| select * from T038b where b1 = date '01/01/1997' + interval '1' day - interval '1' day * 2; |
| select * from T038b t1, T038b t2 where t1.b1 = date '01/01/1997' + interval '1' day - interval '1' day * 2; |
| |
| select * from T038b where b1 = date '01/01/1997' + interval '90-02' year to month; |
| select * from T038b t1, T038b t2 where t1.b1 = date '01/01/1997' + interval '90-02' year to month; |
| |
| select * from T038b where b1 < interval '1' day + {fn CONVERT(b1,DATE)}; |
| select * from T038b where b1 < cast (timestamp'12/31/9999 11:59:59.999999 pm' as date) + interval '1' day; |
| select * from T038b where b1 < cast('1997-28-02' as DATE) + interval '2' day; |
| |
| -- Begin |
| -- Test fix for Target RFE solution: 10-040426-5430 |
| -- This checks for implicit casting of strings to date |
| -- Need to turn ON the following CQD to use the implicit casting functionality |
| control query default allow_incompatible_comparison 'ON'; |
| -- test implicit casting of string in yyyy-mm-dd format |
| select * from T038b where b1 < '1997-02-28'; |
| -- test implicit casting of string in mm/dd/yyyy format |
| select * from T038b where b1 < '02/28/1997'; |
| -- turn OFF the CQD after testing |
| control query default allow_incompatible_comparison 'OFF'; |
| -- End |
| |
| select * from T038c where c1 < 'abcde' || 'fghijk' || 'lmnop'; |
| select * from T038c t1, T038c t2 where t1.c1 > trim(' abc ') || trim(' ef '); |
| |
| select * from T038c where char_length(substring('abcdef' from 1 for 3) || 'cde') > |
| position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| |
| select * from T038c t1, T038c t2 where char_length(substring('abcdef' from 1 for 3) || 'cde') > position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| select * from T038c where c1 < upshift(lower('ABCDE')) || 'fg'; |
| select * from T038c where c1 = {fn CONVERT(C1,CHAR)} || {fn CONVERT(C1,CHAR)}; |
| |
| select * from T038c where {fn locate('ab', c1)} + 1 > 0; |
| |
| select * from T038c where {fn locate({fn ucase('ab')}, c1)} + 1 > 0; |
| |
| select c1 from T038c where {fn substring(c1, 1, 3)} || 'm' = 'jklm'; |
| |
| select * from T038c where {fn length ({fn rtrim(c1)})} + 1 = 5; |
| |
| select * from T038d where d1 < interval '1.1' second + {fn CONVERT(d1,TIMESTAMP)}; |
| |
| select * from T038d where d1 < interval - '1.1' second + {fn now()}; |
| |
| select * from T038d where d1 < timestamp '1997-01-01 21:11:52.145' + interval - '1.1' second; |
| select * from T038d t1, T038d t2 where t1.d1 < timestamp '1997-01-01 21:11:52.145' + interval - '1.1' second; |
| |
| select * from T038d where d1 > cast('01.01.1997 03.04.55.123456' as timestamp) + interval '1' day - interval '1' day; |
| select * from T038d t1, T038d t2 where t1.d1 > cast('01.01.1997 03.04.55.123456' as timestamp) + interval '1' day - interval '1' day; |
| select * from T038d where cast(d1 as timestamp(3)) = cast (date'12/31/9999' as timestamp(3)) + interval '1' day; |
| |
| select * from T038e where e1 > time '11:58:59.236 pm' + interval '1.12' second; |
| select * from T038e t1, T038e t2 where t1.e1 > time '11:58:59.236 pm' + interval '1.12' second; |
| select * from T038e where e1 < interval '1.12' second + {fn CONVERT(e1,TIME)}; |
| |
| select * from T038f where f1 > 1234567890123456.789 + 10000000000 * 3; |
| select * from T038f t1, T038f t2 where t1.f1 > 1234567890123456.789 + 10000000000 * 3; |
| |
| select * from T038f where f1 = cast (-999999999999999999 as decimal(18)) - 1; |
| select * from T038f where f1 = cast (999999999999999999 as decimal(18)) + 1; |
| |
| select * from T038g where g1 > 1234567890123456.789 + 10000000000 * 3; |
| select * from T038g t1, T038g t2 where t1.g1 > 1234567890123456.789 + 10000000000 * 3; |
| |
| select * from T038g where cast(g1 as numeric(1)) = cast (-8 as numeric(1)) - 1; |
| select * from T038g where cast(g1 as numeric(1)) = cast (-9 as numeric(1)) - 1; |
| select * from T038g where cast(g1 as numeric(1)) = cast (8 as numeric(1)) + 1; |
| select * from T038g where cast(g1 as numeric(1)) = cast (9 as numeric(1)) + 1; |
| select * from T038g where g1 = cast (1 as numeric(1)) / cast (0 as numeric(1)); |
| |
| -- test division by zero, with and without rows |
| select * from T038g where g1 = g1 / 0; |
| select * from T038g where g1 = 2 / 0; |
| select * from T038g where g1 = 2 / (1 - 1); |
| select * from T038g where g1 = 0 / ((1 - 1) * (2 + 3)) + g1; |
| select * from T038g where g1 = g1 + 0 / ((1 - 1) * (2 + 3)); |
| delete from T038g; |
| select * from T038g where g1 = g1 / 0; |
| select * from T038g where g1 = 2 / 0; |
| select * from T038g where g1 = 2 / (1 - 1); |
| select * from T038g where g1 = 0 / ((1 - 1) * (2 + 3)) + g1; |
| select * from T038g where g1 = g1 + 0 / ((1 - 1) * (2 + 3)); |
| |
| select * from T038h where h1 >= 1234567890 - 2 + 2 * 4.0; |
| select * from T038h t1, T038h t2 where t1.h1 >= 1234567890 - 2 + 2 * 4.0; |
| |
| select * from T038i where i1 > 1 + 1 and i1 < 3.2 - 3.2 + 5; |
| select * from T038i t1, T038i t2 where t1.i1 > 1 + 1 and t1.i1 < 3.2 - 3.2 + 5; |
| |
| select * from T038i where i1 < 1.0 / 2 + 3 or i1 > 3 + 3 / 1; |
| select * from T038i t1, T038i t2 where t1.i1 < 1.0 / 2 + 3 or t1.i1 > 3 + 3 / 1; |
| |
| select * from T038i where i1 = cast(2.0 + 2 as int) + 3; |
| select * from T038i t1, T038i t2 where t1.i1 = cast(2.0 - 1 as int) + 3; |
| |
| select * from T038i where i1 > char_length(trim(' abc ') || trim(' ef ')); |
| select * from T038i t1, T038i t2 where t1.i1 > char_length(trim(' abc ') || trim(' ef ')); |
| |
| select * from T038j where j1 < 'abcde' || 'fghijk' || 'lmnop'; |
| select * from T038j t1, T038j t2 where t1.j1 > trim(' abc ') || trim(' ef '); |
| |
| select * from T038j where char_length(substring('abcdef' from 1 for 3) || 'cde') > |
| position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| select * from T038j where j1 < {fn concat('abc', {fn CONVERT(j1,LONG VARCHAR)})}; |
| |
| select * from T038k t1, T038j t2 where char_length(substring('abcdef' from 1 for 3) || 'cde') > position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| |
| select * from T038k where k1 < 'abcde' || 'fghijk' || 'lmnop'; |
| select * from T038k t1, T038k t2 where t1.k1 > trim(' abc ') || trim(' ef '); |
| |
| select * from T038k where char_length(substring('abcdef' from 1 for 3) || 'cde') > |
| position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| select * from T038k t1, T038k t2 where char_length(substring('abcdef' from 1 for 3) || 'cde') > position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| |
| select * from T038l t1, T038l t2 where char_length(substring('abcdef' from 1 for 3) || 'cde') > position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| |
| select * from T038l where l1 < 'ABCDE' || 'FGHIJK' || 'LMNOP'; |
| select * from T038l t1, T038l t2 where t1.l1 > trim(' abc ') || trim(' ef '); |
| |
| select * from T038l where char_length(substring('abcdef' from 1 for 3) || 'cde') > |
| position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| select * from T038l t1, T038l t2 where char_length(substring('abcdef' from 1 for 3) || 'cde') > position('B' in upper('abcdef')) + octet_length(lower('abcdef')); |
| |
| select * from T038m where m1 >= 1234567890 - 2 + 2 * 4.0; |
| select * from T038m t1, T038m t2 where t1.m1 >= 1234567890 - 2 + 2 * 4.0; |
| select * from T038m where m1 = cast (-9223372036854775808 as largeint) - 1; |
| select * from T038m where m1 = cast (9223372036854775807 as largeint) + 1; |
| |
| select * from T038n where n1 > 1 + 1 and n1 < 3.2 - 3.2 + 5; |
| select * from T038n t1, T038n t2 where t1.n1 > 1 + 1 and t2.n1 < 3.2 - 3.2 + 5; |
| |
| select * from T038n where n1 < 1.0 / 2 + 3 or n1 > 3 + 3 / 1; |
| select * from T038n t1, T038n t2 where t1.n1 < 1.0 / 2 + 3 or t1.n1 > 3 + 3 / 1; |
| |
| select * from T038n where n1 = cast(2.0 + 1 as int) + 3; |
| select * from T038n t1, T038n t2 where t1.n1 = cast(2.0 as int) + 3; |
| |
| select * from T038n where n1 > char_length(trim(' abc ') || trim(' ef ')); |
| select * from T038n t1, T038n t2 where t1.n1 > char_length(trim(' abc ') || trim(' ef ')); |
| |
| select * from T038n where n1 = cast (-32767 as smallint) - 1; |
| select * from T038n where n1 = cast (-32768 as smallint) - 1; |
| select * from T038n where n1 = cast (32766 as smallint) + 1; |
| select * from T038n where n1 = cast (32767 as smallint) + 1; |
| |
| select * from T038o where o1 > 1234567890123456.789 + 10000000000 * 3; |
| select * from T038o t1, T038o t2 where t1.o1 > 1234567890123456.789 + 10000000000 * 3; |
| select * from T038o where o1 < cast(100 + 100 as smallint unsigned) - 100; |
| select * from T038o where o1 <= cast(500 + 500 as integer unsigned) + 500; |
| select * from T038o where o1 <= cast(10000 + 20000 as largeint signed) + 1000; |
| select * from T038o where o1 >= cast(1 + 2 as decimal(7) unsigned) + 3 |
| and o1 < cast(100 as decimal(3) unsigned); |
| select * from T038o where o1 <= cast(3e38 - 3e37 as real) + 3e36; |
| select * from T038o where o1 <= cast(1e30 + 1e50 as double precision) + 1e50; |
| select * from T038o where o1 <= cast (9999999998 as real) + 1; |
| select * from T038o where o1 <= cast (9999999999 as real) + 1; |
| |
| |
| select * from T038p where p1 > abs(-1234567890123456.789 - 10000000000 * 3); |
| select * from T038p t1, T038p t2 where t1.p1 > 1234567890123456.789 + 10000000000 * 3; |
| select * from T038p where p1 < cast (4294967296 as double precision) + 1; |
| |
| select * from T038q where q1 > 1 + 1 and q1 < 3.2 - 3.2 + 50; |
| select * from T038q t1, T038q t2 where t1.q1 > 1 + 1 and t2.q1 < 3.2 - 3.2 + 50; |
| |
| -- some TPC-D tests |
| |
| CREATE TABLE T038ORD |
| ( |
| o_orderpriority INT DEFAULT NULL |
| , o_orderdate DATE DEFAULT NULL |
| , o_orderkey INT DEFAULT NULL |
| ) |
| ; |
| |
| CREATE TABLE T038LI |
| ( |
| L_orderKEY INT DEFAULT NULL |
| , L_T038partKEY INT DEFAULT NULL |
| , L_RECEIPTDATE DATE DEFAULT NULL |
| , L_SHIPDATE DATE DEFAULT NULL |
| , L_DISCOUNT INT DEFAULT NULL |
| , L_QUANTITY INT DEFAULT NULL |
| , L_EXTENDEDPRICE INT DEFAULT NULL |
| , L_SHIPMODE CHAR |
| , L_COMMITDATE DATE DEFAULT NULL |
| ) |
| ; |
| |
| create table T038part(p_T038partkey int default null, |
| p_type char); |
| |
| |
| insert into T038ORD values(1,date '1994-12-01', 2); |
| insert into T038ORD values(1,date '1994-12-01', 4); |
| insert into T038LI values(2, 2, date '1995-01-01', date '1995-02-03', 8, 10, 15, 'A', date '1994-02-01'); |
| insert into T038LI values(2, 2, date '1995-01-03', date '1995-02-03', 7, 13, 13, 'B', date '1997-03-04'); |
| insert into T038LI values(2, 2, date '1993-01-01', date '1995-02-03', 14, 12, 4, 'C', date '1998-09-09'); |
| insert into T038LI values(3, 2, date '1995-01-01', date '1995-02-03', 19, 22, 23, 'D', date '1985-09-08'); |
| |
| -- Q4 |
| select o_orderpriority, count(*) from T038ORD |
| where o_orderdate > date '1994-08-01' + interval '3' month |
| and o_orderdate < date '1995-08-01' |
| and exists (select * from T038LI |
| where l_orderkey = o_orderkey |
| and date '1994-08-01' - interval '3' month |
| < l_receiptdate) |
| group by o_orderpriority |
| order by o_orderpriority; |
| |
| -- Simplified Q6 |
| select l_orderkey, l_shipdate from T038LI |
| where l_shipdate >= date '1994-01-01' |
| and l_shipdate < date '1994-02-04' + interval '1' year |
| and l_discount between 12.2 / 2 and 12.2 / 2 + 1 |
| and l_quantity < 25; |
| |
| -- Simplified Q6 again |
| select |
| l_orderkey, l_shipdate |
| from T038LI where |
| l_discount between 12.2 / 2 and 12.2 / 2 / 5 |
| and 25 + 2 > 3 * l_quantity; |
| |
| -- Q12 |
| select |
| l_shipmode, |
| sum(case when o_orderpriority = 1 |
| or o_orderpriority = 2 |
| then 1 |
| else 0 |
| end), |
| sum(case when o_orderpriority <> 1 |
| and o_orderpriority <> 2 |
| then 1 |
| else 0 |
| end) |
| from T038ORD,T038LI |
| where o_orderkey = l_orderkey |
| and l_shipmode in ('RAIL','AIR') |
| and l_commitdate < l_receiptdate |
| and l_shipdate < l_commitdate |
| and l_receiptdate >= date '1995-01-01' |
| and l_receiptdate < date '1995-01-01' + interval '1' year |
| group by l_shipmode |
| order by l_shipmode |
| ; |
| |
| -- Q14 |
| select |
| 100.00 * sum( |
| case |
| when p_type like 'PROMO%' |
| then |
| cast((l_extendedprice*(1-l_discount)) as numeric(18,2)) |
| else 0 |
| end) |
| / cast(sum((l_extendedprice*(1-l_discount))) as numeric(18,2)) |
| |
| from T038LI, T038part |
| where l_T038partkey = p_T038partkey |
| and l_shipdate >= date '1994-08-01' |
| and l_shipdate < date '1994-08-01' + interval '1' month |
| ; |
| |
| -- String function tests from TEST012 (Executor) start here |
| |
| -- Create and populate table |
| create table T038sf(a int, b char(10), c varchar(30)); |
| |
| insert into T038sf values(10, 'abcdef', 'ghij'); |
| insert into T038sf values(20, 'ABCDEF', 'GHIJ'); |
| insert into T038sf values(30, ' abc ', ' def '); |
| insert into T038sf values(40, 'test', 'trim on non-space'); |
| |
| -- testing POSITION function |
| select b, position('xy' in b) from T038sf; |
| select b, position('f' in b) from T038sf; |
| select c, position('hi' in c) from T038sf; |
| select b, position('' in b), c, position('' in c) from T038sf; |
| select b, position('' in '') from T038sf; |
| select * from T038sf where position('b' in b) < 3; |
| select * from T038sf where position('b' in b) <= 2; |
| |
| -- testing TRIM function |
| select c,trim(leading from c),char_length(trim(leading from c)) from T038sf; |
| select b,trim(leading ' ' from b),char_length(trim(leading ' ' from b)) from T038sf; |
| select b,trim(trailing from b),char_length(trim(trailing from b)) from T038sf; |
| select b,trim(both from b),char_length(trim(both from b)) from T038sf; |
| select b,trim('b' from trim('a' from b)),char_length(trim('b' from trim('a' from b))) from T038sf; |
| select * from T038sf where trim(c)='def'; |
| select * from T038sf where trim(leading from c) = 'def'; |
| -- The following query should raise a SQL exception.(To be implemented) |
| -- select b,trim('ab' from b),c from T038sf; |
| |
| -- test ASCII, CHAR functions |
| select b, { fn CHAR(ASCII(b)) }, c, { fn CHAR(ASCII(c)) } from T038sf; |
| select b, CHAR(ASCII(b)), c, CHAR(ASCII(c)) from T038sf; |
| select 'c', CHAR(ASCII('cba')) from T038sf; |
| |
| select 65, { fn ASCII(CHAR(65)) } from T038sf; |
| select 65, ASCII(CHAR(65)) from T038sf; |
| |
| -- -ve |
| select { fn ASCII(1) } from T038sf; |
| |
| select CHAR(b) from T038sf; |
| select CHAR(256) from T038sf; |
| select CHAR(1.1) from T038sf; |
| |
| |
| -- INSERT function |
| select insert('abc', 2, 1, 'zz') from T038sf; |
| |
| -- REPEAT |
| select repeat('aa', 10) from T038sf; |
| |
| -- test fix to genesis case: 10-981211-6071 |
| -- makes sure we catch and diagnose invalid repeat count |
| select REPEAT(b, 9999999999999999999) from T038sf; |
| select REPEAT(c, 9999999999999999999) from T038sf; |
| |
| -- REPLACE |
| select replace('abcdabcdab', 'cd', 'abc') from T038sf; |
| |
| -- SPACE |
| select 's' || space(10) || 'e', char_length(space(10)) from T038sf; |
| |
| -- VEG conversion should not change the result of string function. |
| -- should return 10 |
| select char_length(b) from T038sf where b = 'abcdef'; |
| |
| -- should return 'abcdef abcdef ' |
| select repeat(b, 2) from T038sf where b = 'abcdef'; |
| |
| -- String function tests from TEST012 (Executor) end here |
| |
| -- LPAD and RPAD |
| select LPAD ('Robert John Smith', 20) from T038sf |
| where RPAD (c, 20, 'x') like 'ghijx%'; |
| -- should get one row (" Robert John Smith") |
| |
| select a, RPAD (c, 20, '7') from T038sf; |
| -- 4 rows |
| |
| select RPAD (b, 12, '5'), LPAD (c, 13) from T038sf where RPAD( RPAD(b, 13, 'X'), 10, 'W') = b; |
| -- 4 rows |
| |
| -- test SPLIT_PART function |
| |
| select split_part('sa:sbl:sc', ':', 1) from t038sf; |
| select split_part('sa:sbl:sc', ':', 2) from t038sf; |
| select split_part('sa:sbl:sc', ':', 3) from t038sf; |
| |
| -- **EMPTY RESULT** |
| select split_part('sa:sbl:sc', ':', 0) from t038sf; |
| select split_part('sa:sbl:sc', ':', 4) from t038sf; |
| |
| insert into T038sf values(110, 'a/b/c', 'sa/dsd/s'); |
| insert into T038sf values(111, 'sasd', 'dsa:/~sd'); |
| insert into T038sf values(112, '#$%$#@', 'dsasggggsad'); |
| insert into T038sf values(114, 'a', '这是^中文:测试'); |
| |
| select split_part(c, '/', 2) from t038sf; |
| select split_part(c, 'ggg', 2) from t038sf; |
| select split_part(c, 'sd', 1) from t038sf; |
| select split_part(c, ':', 1) from t038sf; |
| |
| |
| -- Test for Genesis soln: 10-071004-7982 |
| -- Previously this would run out of memory in the Generator. |
| -- Now it compiles successfully with Query Cache off. |
| -- If Query Cache is on, then this query takes a long time to compile. |
| -- |
| control query default query_cache_max_exprs '117'; |
| prepare s1 from |
| select case when e1 = 1 then e1 + 1 |
| when e1 = 2 then e1 + 2 |
| when e1 = 3 then e1 + 3 |
| when e1 = 4 then e1 + 4 |
| when e1 = 5 then e1 + 5 |
| when e1 = 6 then e1 + 6 |
| when e1 = 7 then e1 + 7 |
| when e1 = 8 then e1 + 8 |
| when e1 = 9 then e1 + 9 |
| else e1 |
| end |
| from (select case when e2 = 101 then e2 + 1 |
| when e2 = 102 then e2 + 2 |
| when e2 = 103 then e2 + 3 |
| when e2 = 104 then e2 + 4 |
| when e2 = 105 then e2 + 5 |
| when e2 = 106 then e2 + 6 |
| when e2 = 107 then e2 + 7 |
| when e2 = 108 then e2 + 8 |
| when e2 = 109 then e2 + 9 |
| else e2 |
| end e1 |
| from (select case when e3 = 101 then 1 |
| when e3 = 102 then 2 |
| when e3 = 103 then 3 |
| when e3 = 104 then 4 |
| when e3 = 105 then 5 |
| when e3 = 106 then 6 |
| when e3 = 107 then 7 |
| when e3 = 108 then 8 |
| when e3 = 109 then 9 |
| else e3 |
| end e2 |
| from (select case when empnum = 101 then 1 |
| when empnum = 102 then 2 |
| when empnum = 103 then 3 |
| when empnum = 104 then 4 |
| when empnum = 105 then 5 |
| when empnum = 106 then 6 |
| else empnum |
| end e3 |
| from T038empl) T1 |
| ) T2 |
| ) T3 |
| ; |
| control query default query_cache reset; |
| |
| execute s1; |
| |
| -- nvl function test |
| create table T038nvl (a int not null, b int, primary key(a)); |
| insert into T038nvl values (1, null), (2, 2), (3, null), (4, 4); |
| select nvl(b,a) from T038nvl; |
| |
| |
| -- floor/ceil function test |
| create table T038fcl (a int , b decimal(5,2), c numeric (4,1), d largeint, e largeint); |
| insert into T038fcl values |
| (1, 300.10, 202.2, 1, 1), (2, -300.10, -202.2, 2, 2); |
| |
| select floor(a) floor_a, floor(b) floor_b, floor(c) floor_c, floor(d+e) bignumfloor, |
| ceil(a) ceil_a, ceil(b) ceil_b, ceil(c) ceil_c, ceil(d+e) bignumceil |
| from T038fcl order by 1; |
| |
| create table T038fc2 as |
| ( |
| select floor(a) floor_a, floor(b) floor_b, floor(c) floor_c, floor(d+e) bignumfloor, |
| ceil(a) ceil_a, ceil(b) ceil_b, ceil(c) ceil_c, ceil(d+e) bignumceil |
| from T038fcl |
| ); |
| |
| showddl T038fc2; |
| |
| log; |
| |
| ?section aqr |
| log LOG038; |
| |
| create table t038aqr( a int not null, b int not null, primary key(a) ); |
| insert into t038aqr values(1, 1); |
| select a, b from t038aqr; |
| -- let aqr_other_session drop and recreate the t038aqr |
| sh sqlci -i "TEST038(aqr_other_session)" ; |
| drop table t038aqr; |
| log; |
| |
| ?section aqr_other_session; |
| set schema $$TEST_SCHEMA$$; |
| log OTHER_SESSION_LOG038 clear; |
| drop table t038aqr; |
| create table t038aqr( a int not null, b int not null, primary key(a) ); |
| log; |
| |
| ?section clnup |
| -- CLEANUP database |
| drop table T038edat; |
| drop table T038empl; |
| drop table T038sls; |
| drop table T038prts; |
| DROP TABLE T038dt1; |
| DROP TABLE T028tm1; |
| DROP TABLE T038TS1; |
| drop table T038tcf; |
| drop table T038tsf; |
| drop table T038empt; |
| drop table T038dept; |
| drop table T038corp; |
| drop table t038aqr; |
| |
| -- Test constant folding |
| |
| drop table T038a; |
| drop table T038b; |
| drop table T038c; |
| drop table T038d cascade; |
| drop table T038e; |
| drop table T038f; |
| drop table T038g; |
| drop table T038h; |
| drop table T038i; |
| drop table T038j; |
| drop table T038k; |
| drop table T038l; |
| drop table T038m; |
| drop table T038n; |
| drop table T038o; |
| drop table T038p; |
| drop table T038q; |
| |
| drop table T038ORD; |
| drop table T038LI; |
| drop table T038part; |
| drop table T038sf; |
| drop table T038nvl; |
| drop table T038fcl; |
| drop table T038fc2; |
| |
| |