Google Git
Sign in
apache / trafodion / 9e5b5844f08d87abe8ff3557e48c51526e34e8e4 / . / core / sql / regress / core / EXPECTED038.LINUX
blob: aceb087140b04cd8c018c73371bbb22099af292e [file] [log] [blame]
>>
>>-----------------------------------------------------------------------------
>>-- 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);
--- SQL operation complete.
>>insert into T038edat values (100, 'charu', 'bhave ');
--- 1 row(s) inserted.
>>insert into T038edat values (200, 'linda', 'ho');
--- 1 row(s) inserted.
>>insert into T038edat values (300, ' ARUN ', 'Thomas');
--- 1 row(s) inserted.
>>
>>create TABLE T038empl (EMPNUM int NOT NULL, firstname char(20) not null,
+> lastname char(20) not null, age int, salary int);
--- SQL operation complete.
>>insert into T038empl values (101,'charu','bhave', 45, 50000);
--- 1 row(s) inserted.
>>insert into T038empl values (102,'linda','ho ', 30, 55000);
--- 1 row(s) inserted.
>>insert into T038empl values (103,'Selva','Ganesan', 25, 65000);
--- 1 row(s) inserted.
>>insert into T038empl values (104,'Tom ','Rogers', 60, 90000);
--- 1 row(s) inserted.
>>
>>-- ODBC Parser Test for CONCAT, LOCATE, UCASE, LCASE, RTRIM, LTRIM,
>>-- LENGTH, SUBSTRING, LEFT, RIGHT
>>
>>select {fn concat(first_name, last_name)} from T038edat;
(EXPR)
----------------------------------------
charu bhave
linda ho
ARUN Thomas
--- 3 row(s) selected.
>>
>>select * from T038edat where {fn locate('lin', first_name)} > 0;
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
200 linda ho
--- 1 row(s) selected.
>>
>>select * from T038edat where {fn locate({fn ucase('aru')}, first_name)} > 0;
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
300 ARUN Thomas
--- 1 row(s) selected.
>>
>>select * from T038edat where {fn length ({fn rtrim(last_name)})} = 5;
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
100 charu bhave
--- 1 row(s) selected.
>>
>>select last_name from T038edat where {fn substring(last_name, 1, 3)} = 'Tho';
LAST_NAME
--------------------
Thomas
--- 1 row(s) selected.
>>
>>select * from T038edat
+> where {fn locate('ma', {fn concat(first_name, last_name)})} > 0;
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
300 ARUN Thomas
--- 1 row(s) selected.
>>
>>select * from T038empl where {fn locate('a', firstname)} > 2;
EMPNUM FIRSTNAME LASTNAME AGE SALARY
----------- -------------------- -------------------- ----------- -----------
101 charu bhave 45 50000
102 linda ho 30 55000
103 Selva Ganesan 25 65000
--- 3 row(s) selected.
>>
>>select * from T038edat
+> where {fn locate('ma', {fn concat(first_name, last_name)}, 1)} > 0;
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
300 ARUN Thomas
--- 1 row(s) selected.
>>
>>select {fn lcase(first_name)} from T038edat;
(EXPR)
--------------------
charu
linda
arun
--- 3 row(s) selected.
>>
>>select {fn ucase(first_name)} from T038edat;
(EXPR)
--------------------
CHARU
LINDA
ARUN
--- 3 row(s) selected.
>>
>>select {fn concat({fn rtrim(first_name)}, {fn rtrim(last_name)})} from T038edat;
(EXPR)
----------------------------------------
charubhave
lindaho
ARUNThomas
--- 3 row(s) selected.
>>
>>select {fn concat({fn ltrim(first_name)}, {fn ltrim(last_name)})} from T038edat;
(EXPR)
----------------------------------------
charu bhave
linda ho
ARUN Thomas
--- 3 row(s) selected.
>>
>>select last_name from T038edat where {fn left(last_name, 3)} = 'Tho';
LAST_NAME
--------------------
Thomas
--- 1 row(s) selected.
>>
>>select * from T038edat
+> where {fn left ({fn ltrim(first_name)}, 3)} = 'ARU';
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
300 ARUN Thomas
--- 1 row(s) selected.
>>
>>-- expect 0 rows --
>>select last_name from T038edat where {fn left(last_name, 3)} = 'ARU';
--- 0 row(s) selected.
>>
>>-- expect 0 rows --
>>select * from T038edat where {fn right(first_name, 3)} = 'aru';
--- 0 row(s) selected.
>>
>>select * from T038edat
+> where {fn right ({fn rtrim(first_name)}, 3)} = 'aru';
EMPNUM FIRST_NAME LAST_NAME
----------- -------------------- --------------------
100 charu bhave
--- 1 row(s) selected.
>>
>>-- 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);
--- SQL operation complete.
>>insert into T038sls values(100, date '1997-01-30', time '13:40:05', 1000);
--- 1 row(s) inserted.
>>insert into T038sls values(200, {fn curdate()}, {fn curtime()}, 99);
--- 1 row(s) inserted.
>>insert into T038sls values(300, date '1996-08-10', time '10:20:10', 6000);
--- 1 row(s) inserted.
>>insert into T038sls values(400, date '1997-05-12', {fn curtime()}, 99);
--- 1 row(s) inserted.
>>insert into T038sls values(500, {fn curdate()}, {fn curtime()}, 99);
--- 1 row(s) inserted.
>>select * from T038sls;
ORDER_NUM ORDER_DATE ORDER_TIME ORDER_QTY
----------- ---------- ---------- -----------
100 1997-01-30 13:40:05 1000
200 2018-03-08 04:11:27 99
300 1996-08-10 10:20:10 6000
400 1997-05-12 04:11:27 99
500 2018-03-08 04:11:27 99
--- 5 row(s) selected.
>>
>>-- CREATE database and perform ...
>>-- ODBC Parser Test for: NOW
>>
>>create table T038prts(T038part_num int, order_time timestamp);
--- SQL operation complete.
>>insert into T038prts values(600, {fn now()});
--- 1 row(s) inserted.
>>select * from T038prts;
T038PART_NUM ORDER_TIME
------------ --------------------------
600 2018-03-08 04:11:32.295835
--- 1 row(s) selected.
>>
>>-- CREATE database and perform ...
>>-- ODBC Parser Test for: {d'date-literal'}
>>
>>CREATE TABLE T038dt1 (C1 DATE);
--- SQL operation complete.
>>INSERT INTO T038dt1 VALUES ({d '1993-12-30'});
--- 1 row(s) inserted.
>>SELECT C1 FROM T038dt1;
C1
----------
1993-12-30
--- 1 row(s) selected.
>>
>>-- CREATE database and perform ...
>>-- ODBC Parser Test for: {t'time-literal'}
>>
>>CREATE TABLE T028tm1 (C1 TIME);
--- SQL operation complete.
>>INSERT INTO T028tm1 VALUES ({t '10:11:12'});
--- 1 row(s) inserted.
>>SELECT C1 FROM T028tm1;
C1
--------
10:11:12
--- 1 row(s) selected.
>>
>>-- CREATE database and perform ...
>>-- ODBC Parser Test for: {ts'timestamp-literal'}
>>
>>CREATE TABLE T038TS1 (C1 TIMESTAMP);
--- SQL operation complete.
>>INSERT INTO T038TS1 VALUES ({ts '1992-12-31 23:45:23.123456'});
--- 1 row(s) inserted.
>>SELECT C1 FROM T038TS1;
C1
--------------------------
1992-12-31 23:45:23.123456
--- 1 row(s) selected.
>>
>>-- 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
+>;
*** WARNING[3250] The specified blocksize is not valid. Blocksize of 32768 is used instead.
--- SQL operation completed with warnings.
>>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);
--- 1 row(s) inserted.
>>
>>select {fn CONVERT(C1,CHAR)} from T038tcf;
(EXPR)
--------------------
-0123456789
--- 1 row(s) selected.
>>-- output => "-0123456789 ";
>>select {fn CONVERT(C2,VARCHAR)} from T038tcf;
(EXPR)
--------------------
-0123456789
--- 1 row(s) selected.
>>-- output => "-0123456789";
>>select {fn CONVERT(C3,DECIMAL)} from T038tcf;
(EXPR)
----------
-1234
--- 1 row(s) selected.
>>-- output => "-1234.56789";
>>select {fn CONVERT(C4,DECIMAL)} from T038tcf;
(EXPR)
----------
-5678
--- 1 row(s) selected.
>>-- output => "-5678.12345";
>>select {fn CONVERT(C5,SMALLINT)} from T038tcf;
(EXPR)
------
-1234
--- 1 row(s) selected.
>>-- output => "-1234";
>>select {fn CONVERT(C6,SMALLINT)} from T038tcf;
(EXPR)
------
6789
--- 1 row(s) selected.
>>-- output => "6789";
>>select {fn CONVERT(C7,INTEGER)} from T038tcf;
(EXPR)
-----------
-12345
--- 1 row(s) selected.
>>-- output => "-12345";
>>select {fn CONVERT(C8,INTEGER)} from T038tcf;
(EXPR)
-----------
56789
--- 1 row(s) selected.
>>-- output => "56789";
>>select {fn CONVERT(C9,REAL)} from T038tcf;
(EXPR)
---------------
-1.2340000E+004
--- 1 row(s) selected.
>>-- output => "-12340.0";
>>select {fn CONVERT(C10,FLOAT)} from T038tcf;
(EXPR)
-------------------------
-1.23000000000000000E+004
--- 1 row(s) selected.
>>-- output => "'-12300.0";
>>select {fn CONVERT(C11,DOUBLE PRECISION)} from T038tcf;
(EXPR)
-------------------------
-1.23456700000000000E+007
--- 1 row(s) selected.
>>-- output => "-12345670.0";
>>select {fn CONVERT(C12,DATE)} from T038tcf;
(EXPR)
----------
1993-12-30
--- 1 row(s) selected.
>>-- output => "1993-12-30";
>>select {fn CONVERT(C13,TIME)} from T038tcf;
(EXPR)
--------
11:45:23
--- 1 row(s) selected.
>>-- output => "11:45:23";
>>select {fn CONVERT(C14,TIMESTAMP)} from T038tcf;
(EXPR)
--------------------------
1992-12-31 23:45:23.123456
--- 1 row(s) selected.
>>-- output => "1992-12-31 23:45:23.123456";
>>select {fn CONVERT(C15,LONG VARCHAR)} from T038tcf;
(EXPR)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0123456789
--- 1 row(s) selected.
>>-- output => "0123456789";
>>select {fn CONVERT(C16,BIGINT)} from T038tcf;
(EXPR)
--------------------
9876543
--- 1 row(s) selected.
>>-- 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
+> );
--- SQL operation complete.
>>insert into T038tsf values ('ABCD','efgh',' 1234 ','CD',65);
--- 1 row(s) inserted.
>>select {fn LCASE(c1)} from T038tsf;
(EXPR)
----------
abcd
--- 1 row(s) selected.
>>-- output => 'abcd';
>>select {fn LEFT(c1,2)} from T038tsf;
(EXPR)
------
AB
--- 1 row(s) selected.
>>-- output => 'AB';
>>select {fn LTRIM(c3)} from T038tsf;
(EXPR)
----------
1234
--- 1 row(s) selected.
>>-- output => '1234 ';
>>select {fn RIGHT(c2,3)} from T038tsf;
(EXPR)
----------
--- 1 row(s) selected.
>>-- output => ' ';
>>select {fn RTRIM(c3)} from T038tsf;
(EXPR)
----------
1234
--- 1 row(s) selected.
>>-- output => ' 1234';
>>select {fn UCASE(c2)} from T038tsf;
(EXPR)
----------
EFGH
--- 1 row(s) selected.
>>-- 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
+> );
--- SQL operation complete.
>>insert into T038empt values (100, 'BHAVE', 6400);
--- 1 row(s) inserted.
>>insert into T038empt values (100, 'RAO' , 6500);
--- 1 row(s) inserted.
>>insert into T038empt values (100, 'GANESAN', 6400);
--- 1 row(s) inserted.
>>
>>create table T038dept (deptnum INT NOT NULL,
+> deptname CHAR(20) NOT NULL,
+> manager CHAR(20) NOT NULL
+> );
--- SQL operation complete.
>>insert into T038dept values (6400, 'DEV', 'YOW');
--- 1 row(s) inserted.
>>insert into T038dept values (6500, 'QA' , 'DENNIS');
--- 1 row(s) inserted.
>>insert into T038dept values (6000, 'SRC', 'JUDY');
--- 1 row(s) inserted.
>>
>>create table T038corp (unitnum int not null,
+> deptnum int not null,
+> revenvue decimal(10,3) not null
+> );
--- SQL operation complete.
>>insert into T038corp values (101, 6400, 10000);
--- 1 row(s) inserted.
>>
>>SELECT EMPNAME,DEPTNAME,MANAGER
+> FROM {oj T038empt T038E LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> };
EMPNAME DEPTNAME MANAGER
-------------------- -------------------- --------------------
BHAVE DEV YOW
RAO QA DENNIS
GANESAN DEV YOW
--- 3 row(s) selected.
>>SELECT EMPNAME,DEPTNAME,MANAGER
+> FROM T038empt T038E LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM;
EMPNAME DEPTNAME MANAGER
-------------------- -------------------- --------------------
BHAVE DEV YOW
RAO QA DENNIS
GANESAN DEV YOW
--- 3 row(s) selected.
>>
>>SELECT *
+> FROM {oj T038empt T038E
+> LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM
+> };
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 RAO 6500 6500 QA DENNIS ? ? ?
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>SELECT *
+> FROM T038empt T038E
+> LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> LEFT JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM;
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
100 RAO 6500 6500 QA DENNIS ? ? ?
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>
>>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
+> };
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 RAO 6500 6500 QA DENNIS ? ? ?
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>SELECT *
+> FROM T038empt T038E
+> LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM;
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 RAO 6500 6500 QA DENNIS ? ? ?
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>
>>SELECT *
+> FROM {oj T038empt T038E
+> LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM}
+> WHERE T038E.DEPTNUM < 6000;
--- 0 row(s) selected.
>>SELECT *
+> FROM T038empt T038E
+> LEFT JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> WHERE T038E.DEPTNUM < 6000;
--- 0 row(s) selected.
>>
>>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
+> };
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 RAO 6500 6500 QA DENNIS ? ? ?
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>SELECT *
+> FROM T038empt T038E
+> LEFT OUTER JOIN T038dept T038D ON T038E.DEPTNUM = T038D.DEPTNUM
+> LEFT OUTER JOIN T038corp T038C ON T038D.DEPTNUM = T038C.DEPTNUM;
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
100 RAO 6500 6500 QA DENNIS ? ? ?
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>
>>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
+> };
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
100 RAO 6500 6500 QA DENNIS ? ? ?
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>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;
EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE
----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------
100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000
100 RAO 6500 6500 QA DENNIS ? ? ?
100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000
--- 3 row(s) selected.
>>
>>
>>create table T038a(a1 int);
--- SQL operation complete.
>>
>>create table T038b(b1 date);
--- SQL operation complete.
>>
>>create table T038c(c1 char(10));
--- SQL operation complete.
>>
>>create table T038d(d1 timestamp);
--- SQL operation complete.
>>
>>create table T038e(e1 time);
--- SQL operation complete.
>>
>>create table T038f(f1 decimal(18,4));
--- SQL operation complete.
>>
>>create table T038g(g1 numeric(18,10));
--- SQL operation complete.
>>
>>create table T038h(h1 float(30));
--- SQL operation complete.
>>
>>create table T038i(i1 int not null );
--- SQL operation complete.
>>
>>create table T038j(j1 varchar(10));
--- SQL operation complete.
>>
>>create table T038k(k1 PIC X(12));
--- SQL operation complete.
>>
>>create table T038l(l1 char varying (20) upshift);
--- SQL operation complete.
>>
>>create table T038m(m1 largeint);
--- SQL operation complete.
>>
>>create table T038n(n1 smallint);
--- SQL operation complete.
>>
>>create table T038o(o1 real);
--- SQL operation complete.
>>
>>create table T038p(p1 double precision);
--- SQL operation complete.
>>
>>#ifMX
>>create table T038q(q1 bit precision int (5) unsigned);
--- SQL operation complete.
>>#ifMX
>>#ifMP
>>create table T038q(q1 smallint unsigned);
>>#ifMP
>>
>>insert into T038a values (1);
--- 1 row(s) inserted.
>>insert into T038a values (2);
--- 1 row(s) inserted.
>>insert into T038a values (3);
--- 1 row(s) inserted.
>>insert into T038a values (4);
--- 1 row(s) inserted.
>>insert into T038a values (5);
--- 1 row(s) inserted.
>>insert into T038a values (6);
--- 1 row(s) inserted.
>>insert into T038a values (7);
--- 1 row(s) inserted.
>>insert into T038a values (7);
--- 1 row(s) inserted.
>>insert into T038a values (8);
--- 1 row(s) inserted.
>>insert into T038a values (8);
--- 1 row(s) inserted.
>>insert into T038a values (8);
--- 1 row(s) inserted.
>>insert into T038a values (8);
--- 1 row(s) inserted.
>>
>>insert into T038b values (date '10/01/1996');
--- 1 row(s) inserted.
>>insert into T038b values (date '11/01/1996');
--- 1 row(s) inserted.
>>insert into T038b values (date '12/01/1996');
--- 1 row(s) inserted.
>>insert into T038b values (date '01/01/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '02/02/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '03/03/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '04/04/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '05/05/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '06/06/1997');
--- 1 row(s) inserted.
>>insert into T038b values (date '07/07/1997');
--- 1 row(s) inserted.
>>
>>insert into T038c values ('abcd');
--- 1 row(s) inserted.
>>insert into T038c values ('efghi');
--- 1 row(s) inserted.
>>insert into T038c values ('jklmno');
--- 1 row(s) inserted.
>>insert into T038c values ('pqrstuv');
--- 1 row(s) inserted.
>>insert into T038c values ('wxyz');
--- 1 row(s) inserted.
>>insert into T038c values ('1234567890');
--- 1 row(s) inserted.
>>
>>insert into T038d values (timestamp '01.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '02.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '03.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '04.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '05.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '06.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '07.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '08.01.1997 03.04.55.123456');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '09.01.1997 03.04.55');
--- 1 row(s) inserted.
>>insert into T038d values (timestamp '10.01.1997 03.04.55');
--- 1 row(s) inserted.
>>
>>insert into T038e values (time '11:58:59.236 pm' + interval '1.12' second);
--- 1 row(s) inserted.
>>insert into T038e values (time '01:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '02:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '03:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '04:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '05:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '06:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '07:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '08:58:59.236 pm');
--- 1 row(s) inserted.
>>insert into T038e values (time '09:58:59.236 pm');
--- 1 row(s) inserted.
>>
>>insert into T038f values (123456789456.789 + 10000000000 * 3);
--- 1 row(s) inserted.
>>insert into T038f values (123476789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123477789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123478789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123479789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123489789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123496789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123576789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123676789456.789);
--- 1 row(s) inserted.
>>insert into T038f values (123776789456.789);
--- 1 row(s) inserted.
>>
>>insert into T038g values (123456.789);
--- 1 row(s) inserted.
>>insert into T038g values (123476.789);
--- 1 row(s) inserted.
>>insert into T038g values (123477.789);
--- 1 row(s) inserted.
>>insert into T038g values (123478.789);
--- 1 row(s) inserted.
>>insert into T038g values (123479.789);
--- 1 row(s) inserted.
>>insert into T038g values (123489.789);
--- 1 row(s) inserted.
>>insert into T038g values (123496.789);
--- 1 row(s) inserted.
>>insert into T038g values (123576.789);
--- 1 row(s) inserted.
>>insert into T038g values (123676.789);
--- 1 row(s) inserted.
>>insert into T038g values (123776.789);
--- 1 row(s) inserted.
>>
>>
>>insert into T038h values (1234567890 - 2 + 2 * 4.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 5.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 6.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 7.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 8.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 9.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 10.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 11.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 12.0);
--- 1 row(s) inserted.
>>insert into T038h values (1234567890 - 2 + 2 * 13.0);
--- 1 row(s) inserted.
>>
>>insert into T038i values (1);
--- 1 row(s) inserted.
>>insert into T038i values (2);
--- 1 row(s) inserted.
>>insert into T038i values (3);
--- 1 row(s) inserted.
>>insert into T038i values (4);
--- 1 row(s) inserted.
>>insert into T038i values (5);
--- 1 row(s) inserted.
>>insert into T038i values (6);
--- 1 row(s) inserted.
>>insert into T038i values (7);
--- 1 row(s) inserted.
>>insert into T038i values (8);
--- 1 row(s) inserted.
>>
>>insert into T038j values ('abcd');
--- 1 row(s) inserted.
>>insert into T038j values ('efghi');
--- 1 row(s) inserted.
>>insert into T038j values ('jklmno');
--- 1 row(s) inserted.
>>insert into T038j values ('pqrstuv');
--- 1 row(s) inserted.
>>insert into T038j values ('wxyz');
--- 1 row(s) inserted.
>>insert into T038j values ('1234567890');
--- 1 row(s) inserted.
>>
>>insert into T038k values ('abcd');
--- 1 row(s) inserted.
>>insert into T038k values ('efghi');
--- 1 row(s) inserted.
>>insert into T038k values ('jklmno');
--- 1 row(s) inserted.
>>insert into T038k values ('pqrstuv');
--- 1 row(s) inserted.
>>insert into T038k values ('wxyz');
--- 1 row(s) inserted.
>>insert into T038k values ('1234567890');
--- 1 row(s) inserted.
>>
>>insert into T038l values ('abcd');
--- 1 row(s) inserted.
>>insert into T038l values ('efghi');
--- 1 row(s) inserted.
>>insert into T038l values ('jklmno');
--- 1 row(s) inserted.
>>insert into T038l values ('pqrstuv');
--- 1 row(s) inserted.
>>insert into T038l values ('wxyz');
--- 1 row(s) inserted.
>>insert into T038l values ('1234567890');
--- 1 row(s) inserted.
>>
>>insert into T038m values (1234567890 - 2 + 2 * 4.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 5.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 6.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 7.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 8.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 9.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 10.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 11.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 12.0);
--- 1 row(s) inserted.
>>insert into T038m values (1234567890 - 2 + 2 * 13.0);
--- 1 row(s) inserted.
>>
>>insert into T038n values (1);
--- 1 row(s) inserted.
>>insert into T038n values (2);
--- 1 row(s) inserted.
>>insert into T038n values (3);
--- 1 row(s) inserted.
>>insert into T038n values (4);
--- 1 row(s) inserted.
>>insert into T038n values (5);
--- 1 row(s) inserted.
>>insert into T038n values (6);
--- 1 row(s) inserted.
>>insert into T038n values (7);
--- 1 row(s) inserted.
>>insert into T038n values (8);
--- 1 row(s) inserted.
>>
>>insert into T038o values (123456.789);
--- 1 row(s) inserted.
>>insert into T038o values (123476.789);
--- 1 row(s) inserted.
>>insert into T038o values (123477.789);
--- 1 row(s) inserted.
>>insert into T038o values (123478.789);
--- 1 row(s) inserted.
>>insert into T038o values (123479.789);
--- 1 row(s) inserted.
>>insert into T038o values (123489.789);
--- 1 row(s) inserted.
>>insert into T038o values (123496.789);
--- 1 row(s) inserted.
>>insert into T038o values (123576.789);
--- 1 row(s) inserted.
>>insert into T038o values (123676.789);
--- 1 row(s) inserted.
>>insert into T038o values (123776.789);
--- 1 row(s) inserted.
>>
>>insert into T038p values (123456.789);
--- 1 row(s) inserted.
>>insert into T038p values (123476.789);
--- 1 row(s) inserted.
>>insert into T038p values (123477.789);
--- 1 row(s) inserted.
>>insert into T038p values (123478.789);
--- 1 row(s) inserted.
>>insert into T038p values (123479.789);
--- 1 row(s) inserted.
>>insert into T038p values (123489.789);
--- 1 row(s) inserted.
>>insert into T038p values (123496.789);
--- 1 row(s) inserted.
>>insert into T038p values (123576.789);
--- 1 row(s) inserted.
>>insert into T038p values (123676.789);
--- 1 row(s) inserted.
>>insert into T038p values (123776.789);
--- 1 row(s) inserted.
>>
>>insert into T038q values(0);
--- 1 row(s) inserted.
>>insert into T038q values(31);
--- 1 row(s) inserted.
>>insert into T038q values(2);
--- 1 row(s) inserted.
>>insert into T038q values(4);
--- 1 row(s) inserted.
>>insert into T038q values(8);
--- 1 row(s) inserted.
>>insert into T038q values(16);
--- 1 row(s) inserted.
>>insert into T038q values(17);
--- 1 row(s) inserted.
>>insert into T038q values(18);
--- 1 row(s) inserted.
>>insert into T038q values(19);
--- 1 row(s) inserted.
>>insert into T038q values(20);
--- 1 row(s) inserted.
>>
>>select * from T038a where a1 > 1 + 1 and a1 < 3.2 - 3.2 + 5;
A1
-----------
3
4
--- 2 row(s) selected.
>>select * from T038a t1, T038a t2 where t1.a1 > 1 + 1 and t2.a1 < 3.2 - 3.2 + 5;
A1 A1
----------- -----------
3 1
3 2
3 3
3 4
4 1
4 2
4 3
4 4
5 1
5 2
5 3
5 4
6 1
6 2
6 3
6 4
7 1
7 2
7 3
7 4
7 1
7 2
7 3
7 4
8 1
8 2
8 3
8 4
8 1
8 2
8 3
8 4
8 1
8 2
8 3
8 4
8 1
8 2
8 3
8 4
--- 40 row(s) selected.
>>
>>select * from T038a where a1 < 1.0 / 2 + 3 or a1 > 3 + 3 / 1;
A1
-----------
1
2
3
7
7
8
8
8
8
--- 9 row(s) selected.
>>select * from T038a t1, T038a t2 where t1.a1 < 1.0 / 2 + 3 or t1.a1 > 3 + 3 / 1;
A1 A1
----------- -----------
1 1
2 1
3 1
7 1
7 1
8 1
8 1
8 1
8 1
1 2
2 2
3 2
7 2
7 2
8 2
8 2
8 2
8 2
1 3
2 3
3 3
7 3
7 3
8 3
8 3
8 3
8 3
1 4
2 4
3 4
7 4
7 4
8 4
8 4
8 4
8 4
1 5
2 5
3 5
7 5
7 5
8 5
8 5
8 5
8 5
1 6
2 6
3 6
7 6
7 6
8 6
8 6
8 6
8 6
1 7
2 7
3 7
7 7
7 7
8 7
8 7
8 7
8 7
1 7
2 7
3 7
7 7
7 7
8 7
8 7
8 7
8 7
1 8
2 8
3 8
7 8
7 8
8 8
8 8
8 8
8 8
1 8
2 8
3 8
7 8
7 8
8 8
8 8
8 8
8 8
1 8
2 8
3 8
7 8
7 8
8 8
8 8
8 8
8 8
1 8
2 8
3 8
7 8
7 8
8 8
8 8
8 8
8 8
--- 108 row(s) selected.
>>
>>select * from T038a where a1 = cast(2.0 + 3.0 as int) + 3;
A1
-----------
8
8
8
8
--- 4 row(s) selected.
>>select * from T038a t1, T038a t2 where t1.a1 = cast(2.0 + 3.0 as int) + 3;
A1 A1
----------- -----------
8 1
8 1
8 1
8 1
8 2
8 2
8 2
8 2
8 3
8 3
8 3
8 3
8 4
8 4
8 4
8 4
8 5
8 5
8 5
8 5
8 6
8 6
8 6
8 6
8 7
8 7
8 7
8 7
8 7
8 7
8 7
8 7
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
8 8
--- 48 row(s) selected.
>>
>>select * from T038a where a1 > char_length(trim(' abc ') || trim(' ef '));
A1
-----------
6
7
7
8
8
8
8
--- 7 row(s) selected.
>>select * from T038a t1, T038a t2 where t1.a1 > char_length(trim(' abc ') || trim(' ef '));
A1 A1
----------- -----------
6 1
7 1
7 1
8 1
8 1
8 1
8 1
6 2
7 2
7 2
8 2
8 2
8 2
8 2
6 3
7 3
7 3
8 3
8 3
8 3
8 3
6 4
7 4
7 4
8 4
8 4
8 4
8 4
6 5
7 5
7 5
8 5
8 5
8 5
8 5
6 6
7 6
7 6
8 6
8 6
8 6
8 6
6 7
7 7
7 7
8 7
8 7
8 7
8 7
6 7
7 7
7 7
8 7
8 7
8 7
8 7
6 8
7 8
7 8
8 8
8 8
8 8
8 8
6 8
7 8
7 8
8 8
8 8
8 8
8 8
6 8
7 8
7 8
8 8
8 8
8 8
8 8
6 8
7 8
7 8
8 8
8 8
8 8
8 8
--- 84 row(s) selected.
>>
>>select * from T038a group by a1 having 1 + 1 > 1 - 1 + sum(a1);
A1
-----------
1
--- 1 row(s) selected.
>>select * from T038a group by a1 having 1 + 1 + count(a1) > 1 - 1 + avg(a1);
A1
-----------
2
1
--- 2 row(s) selected.
>>select * from T038a group by a1 having min(a1) > 1 + 1 - 2;
A1
-----------
2
6
3
8
5
7
1
4
--- 8 row(s) selected.
>>select * from T038a group by a1 having max(a1) > 1 + 1 - 2;
A1
-----------
2
6
3
8
5
7
1
4
--- 8 row(s) selected.
>>select * from T038a where 999999999999999998 + 1 > juliantimestamp(date '1997-01-01' + interval '1' day);
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>select * from T038a where interval '1' day + current_date = current_date + interval '1' day;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>select * from T038a where cast(current_time as timestamp) = current_timestamp;
--- 0 row(s) selected.
>>
>>select * from T038a where not(not(not(not(not(a1 > 10)))));
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where not(not(not(not(not(not(a1 > 10))))));
--- 0 row(s) selected.
>>
>>select * from T038a where not(not(not(not(not(not(a1 > 10))))));
--- 0 row(s) selected.
>>
>>select * from T038a where not(not(not(not(not(a1 > 10 or 3 < 5)))));
--- 0 row(s) selected.
>>select * from T038a where not(not(not(a1 > 10 or not(not(not(not(not(3 < 5))))))));
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>select * from T038a where not(not(not(not(a1 > 10 or not(not(3 < 5))))));
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>select * from T038a where not(not(not(not(not(a1 > 10 and not(not(3 < 5)))))));
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>select * from T038a where not(not(not(not(not(a1 > 10 and not(not(not(not(not(3 < 5))))))))));
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>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)};
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>-- Genesis cases 10-980619-2242, 10-980707-3476, 10-980715-3485
>>select a1 from T038a
+>where 94/1 between -47 and a1 + 0;
--- 0 row(s) selected.
>>
>>select a1 from T038a
+>where 94/0 between -47 and a1 + 0;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:9400 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>
>>select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.5 and t0.a1;
(EXPR)
-----------
1
--- 1 row(s) selected.
>>
>>select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.4999999 and
+> t0.a1;
(EXPR)
-----------
?
--- 1 row(s) selected.
>>
>>select min(t0.a1) from T038a t0 where -14.50 / 1.0 between -14.5000000001 and t0.a1;
(EXPR)
-----------
1
--- 1 row(s) selected.
>>
>>prepare s from
+> select t0.a1
+> from T038a t0
+> where -14.50 / 1.0 between 1.88 and t0.a1;
--- SQL command prepared.
>>
>>execute s;
--- 0 row(s) selected.
>>
>>prepare s from
+> select min(t0.a1)
+> from T038a t0
+> where -14.50 / 0 between 1.88 and t0.a1;
--- SQL command prepared.
>>
>>execute s;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:-145000000000 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>
>>prepare s from
+> select min(t0.a1)
+> from T038a t0
+> where -14.50 / 1 between 1.88/0 and t0.a1/0;
--- SQL command prepared.
>>
>>execute s;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:18800 Target Type:LARGEINT(IBIN64S) Max Target Value:9999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>
>>select * from T038a where (1 + 1, 2 + 0, 2 + 2, 2 + 1 + 2) = (1 + 0 + 1, 2, 4, 5);
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where (-99 - 1, 2) = (-(select a1 from T038a where a1 = 1) + 1 - 1,1 + 1);
--- 0 row(s) selected.
>>
>>select * from T038a where (select a1 from T038a where a1 = 0 + 1) = 0 + 1;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select (select * from T038a where a1 = 0 + 1) from T038a;
(EXPR)
-----------
1
1
1
1
1
1
1
1
1
1
1
1
--- 12 row(s) selected.
>>
>>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;
(EXPR)
------
12
12
12
12
12
12
12
12
12
12
--- 10 row(s) selected.
>>
>>select * from T038a where a1 <= 10 and 3 >= 5;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 >= 5 and a1 <= 10;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 <= 10 and 3 < 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 < 5 and a1 <= 10;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 > 10 and 3 >= 5;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 >= 5 and a1 > 10;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 > 10 and 3 < 5;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 < 5 and a1 > 10;
--- 0 row(s) selected.
>>
>>
>>select * from T038a where 1 <= 10 and 3 < 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 1 <= 10 and 3 >= 5;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 >= 5 and 1 <= 10;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 >= 5 and 1 > 10;
--- 0 row(s) selected.
>>
>>
>>select * from T038a where a1 <= 10 or 3 >= 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 >= 5 or a1 <= 10;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 <= 10 or 3 < 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 < 5 or a1 <= 10;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 > 10 or 3 >= 5;
--- 0 row(s) selected.
>>
>>select * from T038a where 3 >= 5 or a1 > 10;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 > 10 or 3 < 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 < 5 or a1 > 10;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>
>>select * from T038a where 1 <= 10 or 3 < 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 1 <= 10 or 3 >= 5;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 >= 5 or 1 <= 10;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 3 >= 5 or 1 > 10;
--- 0 row(s) selected.
>>
>>
>>
>>-- Test simplifications
>>
>>select * from T038a where 1*(a1 + 0) = ((0 + a1) + (a1 * 0) + (0 * a1)) * 1;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>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;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where (1 = 1 and a1 = 0) or 1 = 2;
--- 0 row(s) selected.
>>
>>select * from T038a where 1 = 1 or 1 + 1 = 2 + 2;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where 1 = 1 and 1 + 1 = 2;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 - 0 = a1 / 1;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 * 1 < a1 - 0;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 <= a1;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 > a1;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 >= a1;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>select * from T038a where a1 <> a1;
--- 0 row(s) selected.
>>
>>select * from T038a where a1 between a1 / 1 + 0 and a1 - 0;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>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;
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>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;
--- 0 row(s) selected.
>>
>>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 ) ) ) );
--- 0 row(s) selected.
>>
>>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
+> ;
(EXPR) A1 (EXPR)
-------------------- ----------- --------------------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
--- 8 row(s) selected.
>>
>>select * from T038a where a1 < 2 + {fn CONVERT(a1,BIGINT)};
A1
-----------
1
2
3
4
5
6
7
7
8
8
8
8
--- 12 row(s) selected.
>>
>>update T038a set a1 = a1 + 1 + 1 where a1 = 1 - 1 + 1 * 2;
--- 1 row(s) updated.
>>
>>select * from T038a where case a1 when 1 + 1 then 2 + 1 when 3 + 1 then 4 + 5 else 5 end = 2 + 1;
--- 0 row(s) selected.
>>
>>select * from T038a group by a1 having case a1 when 1 + 1 then 2 + 1 else 5 + 1 end = 2;
--- 0 row(s) selected.
>>
>>select * from T038a where 2 + 2 + NULL = cast(char_length(trim(' abc ')) as int) + 2;
*** ERROR[4098] A NULL operand is not allowed in operation ((2 + 2) + NULL).
*** ERROR[8822] The statement was not prepared.
>>select * from T038a t1, T038a t2 where 2 + 2 + NULL = cast(char_length(trim(' abc ')) as int) + 2;
*** ERROR[4098] A NULL operand is not allowed in operation ((2 + 2) + NULL).
*** ERROR[8822] The statement was not prepared.
>>select * from T038a where a1 > dayofweek(date '1997-01-01' + interval '1' day) + 1;
A1
-----------
7
7
8
8
8
8
--- 6 row(s) selected.
>>
>>select * from T038a where a1 < 9 + 1 and 3 + 1 < 5 + 1 and a1 > 20 and not(4 + 1 < 3 + 1);
--- 0 row(s) selected.
>>
>>select * from T038a where a1 = cast (-2147483648 as int) - 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast(2147483647 as int) + 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast (-2147483647 as int) - 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast(2147483646 as int) + 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast (0 as int unsigned) - 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast (4294967295 as int unsigned) + 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast (1 as int unsigned) - 1;
--- 0 row(s) selected.
>>select * from T038a where a1 = cast (4294967294 as int unsigned) + 1;
--- 0 row(s) selected.
>>
>>select * from T038b where b1 = date '01/01/1997' + interval '1' day - interval '1' day * 2;
--- 0 row(s) selected.
>>select * from T038b t1, T038b t2 where t1.b1 = date '01/01/1997' + interval '1' day - interval '1' day * 2;
--- 0 row(s) selected.
>>
>>select * from T038b where b1 = date '01/01/1997' + interval '90-02' year to month;
--- 0 row(s) selected.
>>select * from T038b t1, T038b t2 where t1.b1 = date '01/01/1997' + interval '90-02' year to month;
--- 0 row(s) selected.
>>
>>select * from T038b where b1 < interval '1' day + {fn CONVERT(b1,DATE)};
B1
----------
1996-10-01
1996-11-01
1996-12-01
1997-01-01
1997-02-02
1997-03-03
1997-04-04
1997-05-05
1997-06-06
1997-07-07
--- 10 row(s) selected.
>>select * from T038b where b1 < cast (timestamp'12/31/9999 11:59:59.999999 pm' as date) + interval '1' day;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select * from T038b where b1 < cast('1997-28-02' as DATE) + interval '2' day;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1997-28-02
--- 0 row(s) selected.
>>
>>-- 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';
--- SQL operation complete.
>>-- test implicit casting of string in yyyy-mm-dd format
>>select * from T038b where b1 < '1997-02-28';
B1
----------
1996-10-01
1996-11-01
1996-12-01
1997-01-01
1997-02-02
--- 5 row(s) selected.
>>-- test implicit casting of string in mm/dd/yyyy format
>>select * from T038b where b1 < '02/28/1997';
B1
----------
1996-10-01
1996-11-01
1996-12-01
1997-01-01
1997-02-02
--- 5 row(s) selected.
>>-- turn OFF the CQD after testing
>>control query default allow_incompatible_comparison 'OFF';
--- SQL operation complete.
>>-- End
>>
>>select * from T038c where c1 < 'abcde' || 'fghijk' || 'lmnop';
C1
----------
abcd
1234567890
--- 2 row(s) selected.
>>select * from T038c t1, T038c t2 where t1.c1 > trim(' abc ') || trim(' ef ');
C1 C1
---------- ----------
efghi abcd
jklmno abcd
pqrstuv abcd
wxyz abcd
efghi efghi
jklmno efghi
pqrstuv efghi
wxyz efghi
efghi jklmno
jklmno jklmno
pqrstuv jklmno
wxyz jklmno
efghi pqrstuv
jklmno pqrstuv
pqrstuv pqrstuv
wxyz pqrstuv
efghi wxyz
jklmno wxyz
pqrstuv wxyz
wxyz wxyz
efghi 1234567890
jklmno 1234567890
pqrstuv 1234567890
wxyz 1234567890
--- 24 row(s) selected.
>>
>>select * from T038c where char_length(substring('abcdef' from 1 for 3) || 'cde') >
+> position('B' in upper('abcdef')) + octet_length(lower('abcdef'));
--- 0 row(s) selected.
>>
>>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'));
--- 0 row(s) selected.
>>select * from T038c where c1 < upshift(lower('ABCDE')) || 'fg';
C1
----------
1234567890
--- 1 row(s) selected.
>>select * from T038c where c1 = {fn CONVERT(C1,CHAR)} || {fn CONVERT(C1,CHAR)};
--- 0 row(s) selected.
>>
>>select * from T038c where {fn locate('ab', c1)} + 1 > 0;
C1
----------
abcd
efghi
jklmno
pqrstuv
wxyz
1234567890
--- 6 row(s) selected.
>>
>>select * from T038c where {fn locate({fn ucase('ab')}, c1)} + 1 > 0;
C1
----------
abcd
efghi
jklmno
pqrstuv
wxyz
1234567890
--- 6 row(s) selected.
>>
>>select c1 from T038c where {fn substring(c1, 1, 3)} || 'm' = 'jklm';
C1
----------
jklmno
--- 1 row(s) selected.
>>
>>select * from T038c where {fn length ({fn rtrim(c1)})} + 1 = 5;
C1
----------
abcd
wxyz
--- 2 row(s) selected.
>>
>>select * from T038d where d1 < interval '1.1' second + {fn CONVERT(d1,TIMESTAMP)};
D1
--------------------------
1997-01-01 03:04:55.123456
1997-01-02 03:04:55.123456
1997-01-03 03:04:55.123456
1997-01-04 03:04:55.123456
1997-01-05 03:04:55.123456
1997-01-06 03:04:55.123456
1997-01-07 03:04:55.123456
1997-01-08 03:04:55.123456
1997-01-09 03:04:55.000000
1997-01-10 03:04:55.000000
--- 10 row(s) selected.
>>
>>select * from T038d where d1 < interval - '1.1' second + {fn now()};
D1
--------------------------
1997-01-01 03:04:55.123456
1997-01-02 03:04:55.123456
1997-01-03 03:04:55.123456
1997-01-04 03:04:55.123456
1997-01-05 03:04:55.123456
1997-01-06 03:04:55.123456
1997-01-07 03:04:55.123456
1997-01-08 03:04:55.123456
1997-01-09 03:04:55.000000
1997-01-10 03:04:55.000000
--- 10 row(s) selected.
>>
>>select * from T038d where d1 < timestamp '1997-01-01 21:11:52.145' + interval - '1.1' second;
D1
--------------------------
1997-01-01 03:04:55.123456
--- 1 row(s) selected.
>>select * from T038d t1, T038d t2 where t1.d1 < timestamp '1997-01-01 21:11:52.145' + interval - '1.1' second;
D1 D1
-------------------------- --------------------------
1997-01-01 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-01 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-01 03:04:55.123456 1997-01-10 03:04:55.000000
--- 10 row(s) selected.
>>
>>select * from T038d where d1 > cast('01.01.1997 03.04.55.123456' as timestamp) + interval '1' day - interval '1' day;
D1
--------------------------
1997-01-02 03:04:55.123456
1997-01-03 03:04:55.123456
1997-01-04 03:04:55.123456
1997-01-05 03:04:55.123456
1997-01-06 03:04:55.123456
1997-01-07 03:04:55.123456
1997-01-08 03:04:55.123456
1997-01-09 03:04:55.000000
1997-01-10 03:04:55.000000
--- 9 row(s) selected.
>>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;
D1 D1
-------------------------- --------------------------
1997-01-02 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-01 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-01 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-01 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-02 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-02 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-02 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-03 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-03 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-03 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-04 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-04 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-04 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-05 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-05 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-05 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-06 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-06 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-06 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-07 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-07 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-07 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-03 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-04 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-05 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-06 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-07 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-08 03:04:55.123456 1997-01-08 03:04:55.123456
1997-01-09 03:04:55.000000 1997-01-08 03:04:55.123456
1997-01-10 03:04:55.000000 1997-01-08 03:04:55.123456
1997-01-02 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-03 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-04 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-05 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-06 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-07 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-08 03:04:55.123456 1997-01-09 03:04:55.000000
1997-01-09 03:04:55.000000 1997-01-09 03:04:55.000000
1997-01-10 03:04:55.000000 1997-01-09 03:04:55.000000
1997-01-02 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-03 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-04 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-05 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-06 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-07 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-08 03:04:55.123456 1997-01-10 03:04:55.000000
1997-01-09 03:04:55.000000 1997-01-10 03:04:55.000000
1997-01-10 03:04:55.000000 1997-01-10 03:04:55.000000
--- 90 row(s) selected.
>>select * from T038d where cast(d1 as timestamp(3)) = cast (date'12/31/9999' as timestamp(3)) + interval '1' day;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>
>>select * from T038e where e1 > time '11:58:59.236 pm' + interval '1.12' second;
--- 0 row(s) selected.
>>select * from T038e t1, T038e t2 where t1.e1 > time '11:58:59.236 pm' + interval '1.12' second;
--- 0 row(s) selected.
>>select * from T038e where e1 < interval '1.12' second + {fn CONVERT(e1,TIME)};
E1
--------
23:59:00
13:58:59
14:58:59
15:58:59
16:58:59
17:58:59
18:58:59
19:58:59
20:58:59
21:58:59
--- 10 row(s) selected.
>>
>>select * from T038f where f1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>select * from T038f t1, T038f t2 where t1.f1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>
>>select * from T038f where f1 = cast (-999999999999999999 as decimal(18)) - 1;
--- 0 row(s) selected.
>>select * from T038f where f1 = cast (999999999999999999 as decimal(18)) + 1;
--- 0 row(s) selected.
>>
>>select * from T038g where g1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>select * from T038g t1, T038g t2 where t1.g1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>
>>select * from T038g where cast(g1 as numeric(1)) = cast (-8 as numeric(1)) - 1;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:123456 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select * from T038g where cast(g1 as numeric(1)) = cast (-9 as numeric(1)) - 1;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:123456 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select * from T038g where cast(g1 as numeric(1)) = cast (8 as numeric(1)) + 1;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:123456 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select * from T038g where cast(g1 as numeric(1)) = cast (9 as numeric(1)) + 1;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:123456 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select * from T038g where g1 = cast (1 as numeric(1)) / cast (0 as numeric(1));
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:100 Target Type:LARGEINT(IBIN64S) Max Target Value:99. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>
>>-- test division by zero, with and without rows
>>select * from T038g where g1 = g1 / 0;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select * from T038g where g1 = 2 / 0;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:200 Target Type:LARGEINT(IBIN64S) Max Target Value:99. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select * from T038g where g1 = 2 / (1 - 1);
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:20000 Target Type:LARGEINT(IBIN64S) Max Target Value:999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select * from T038g where g1 = 0 / ((1 - 1) * (2 + 3)) + g1;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:0 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>select * from T038g where g1 = g1 + 0 / ((1 - 1) * (2 + 3));
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:0 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>delete from T038g;
--- 10 row(s) deleted.
>>select * from T038g where g1 = g1 / 0;
--- 0 row(s) selected.
>>select * from T038g where g1 = 2 / 0;
--- 0 row(s) selected.
>>select * from T038g where g1 = 2 / (1 - 1);
--- 0 row(s) selected.
>>select * from T038g where g1 = 0 / ((1 - 1) * (2 + 3)) + g1;
--- 0 row(s) selected.
>>select * from T038g where g1 = g1 + 0 / ((1 - 1) * (2 + 3));
--- 0 row(s) selected.
>>
>>select * from T038h where h1 >= 1234567890 - 2 + 2 * 4.0;
H1
-------------------------
1.23456789600000016E+009
1.23456789800000016E+009
1.23456790000000000E+009
1.23456790200000000E+009
1.23456790400000016E+009
1.23456790600000016E+009
1.23456790800000000E+009
1.23456791000000000E+009
1.23456791200000016E+009
1.23456791400000016E+009
--- 10 row(s) selected.
>>select * from T038h t1, T038h t2 where t1.h1 >= 1234567890 - 2 + 2 * 4.0;
H1 H1
------------------------- -------------------------
1.23456789600000016E+009 1.23456789600000016E+009
1.23456789800000016E+009 1.23456789600000016E+009
1.23456790000000000E+009 1.23456789600000016E+009
1.23456790200000000E+009 1.23456789600000016E+009
1.23456790400000016E+009 1.23456789600000016E+009
1.23456790600000016E+009 1.23456789600000016E+009
1.23456790800000000E+009 1.23456789600000016E+009
1.23456791000000000E+009 1.23456789600000016E+009
1.23456791200000016E+009 1.23456789600000016E+009
1.23456791400000016E+009 1.23456789600000016E+009
1.23456789600000016E+009 1.23456789800000016E+009
1.23456789800000016E+009 1.23456789800000016E+009
1.23456790000000000E+009 1.23456789800000016E+009
1.23456790200000000E+009 1.23456789800000016E+009
1.23456790400000016E+009 1.23456789800000016E+009
1.23456790600000016E+009 1.23456789800000016E+009
1.23456790800000000E+009 1.23456789800000016E+009
1.23456791000000000E+009 1.23456789800000016E+009
1.23456791200000016E+009 1.23456789800000016E+009
1.23456791400000016E+009 1.23456789800000016E+009
1.23456789600000016E+009 1.23456790000000000E+009
1.23456789800000016E+009 1.23456790000000000E+009
1.23456790000000000E+009 1.23456790000000000E+009
1.23456790200000000E+009 1.23456790000000000E+009
1.23456790400000016E+009 1.23456790000000000E+009
1.23456790600000016E+009 1.23456790000000000E+009
1.23456790800000000E+009 1.23456790000000000E+009
1.23456791000000000E+009 1.23456790000000000E+009
1.23456791200000016E+009 1.23456790000000000E+009
1.23456791400000016E+009 1.23456790000000000E+009
1.23456789600000016E+009 1.23456790200000000E+009
1.23456789800000016E+009 1.23456790200000000E+009
1.23456790000000000E+009 1.23456790200000000E+009
1.23456790200000000E+009 1.23456790200000000E+009
1.23456790400000016E+009 1.23456790200000000E+009
1.23456790600000016E+009 1.23456790200000000E+009
1.23456790800000000E+009 1.23456790200000000E+009
1.23456791000000000E+009 1.23456790200000000E+009
1.23456791200000016E+009 1.23456790200000000E+009
1.23456791400000016E+009 1.23456790200000000E+009
1.23456789600000016E+009 1.23456790400000016E+009
1.23456789800000016E+009 1.23456790400000016E+009
1.23456790000000000E+009 1.23456790400000016E+009
1.23456790200000000E+009 1.23456790400000016E+009
1.23456790400000016E+009 1.23456790400000016E+009
1.23456790600000016E+009 1.23456790400000016E+009
1.23456790800000000E+009 1.23456790400000016E+009
1.23456791000000000E+009 1.23456790400000016E+009
1.23456791200000016E+009 1.23456790400000016E+009
1.23456791400000016E+009 1.23456790400000016E+009
1.23456789600000016E+009 1.23456790600000016E+009
1.23456789800000016E+009 1.23456790600000016E+009
1.23456790000000000E+009 1.23456790600000016E+009
1.23456790200000000E+009 1.23456790600000016E+009
1.23456790400000016E+009 1.23456790600000016E+009
1.23456790600000016E+009 1.23456790600000016E+009
1.23456790800000000E+009 1.23456790600000016E+009
1.23456791000000000E+009 1.23456790600000016E+009
1.23456791200000016E+009 1.23456790600000016E+009
1.23456791400000016E+009 1.23456790600000016E+009
1.23456789600000016E+009 1.23456790800000000E+009
1.23456789800000016E+009 1.23456790800000000E+009
1.23456790000000000E+009 1.23456790800000000E+009
1.23456790200000000E+009 1.23456790800000000E+009
1.23456790400000016E+009 1.23456790800000000E+009
1.23456790600000016E+009 1.23456790800000000E+009
1.23456790800000000E+009 1.23456790800000000E+009
1.23456791000000000E+009 1.23456790800000000E+009
1.23456791200000016E+009 1.23456790800000000E+009
1.23456791400000016E+009 1.23456790800000000E+009
1.23456789600000016E+009 1.23456791000000000E+009
1.23456789800000016E+009 1.23456791000000000E+009
1.23456790000000000E+009 1.23456791000000000E+009
1.23456790200000000E+009 1.23456791000000000E+009
1.23456790400000016E+009 1.23456791000000000E+009
1.23456790600000016E+009 1.23456791000000000E+009
1.23456790800000000E+009 1.23456791000000000E+009
1.23456791000000000E+009 1.23456791000000000E+009
1.23456791200000016E+009 1.23456791000000000E+009
1.23456791400000016E+009 1.23456791000000000E+009
1.23456789600000016E+009 1.23456791200000016E+009
1.23456789800000016E+009 1.23456791200000016E+009
1.23456790000000000E+009 1.23456791200000016E+009
1.23456790200000000E+009 1.23456791200000016E+009
1.23456790400000016E+009 1.23456791200000016E+009
1.23456790600000016E+009 1.23456791200000016E+009
1.23456790800000000E+009 1.23456791200000016E+009
1.23456791000000000E+009 1.23456791200000016E+009
1.23456791200000016E+009 1.23456791200000016E+009
1.23456791400000016E+009 1.23456791200000016E+009
1.23456789600000016E+009 1.23456791400000016E+009
1.23456789800000016E+009 1.23456791400000016E+009
1.23456790000000000E+009 1.23456791400000016E+009
1.23456790200000000E+009 1.23456791400000016E+009
1.23456790400000016E+009 1.23456791400000016E+009
1.23456790600000016E+009 1.23456791400000016E+009
1.23456790800000000E+009 1.23456791400000016E+009
1.23456791000000000E+009 1.23456791400000016E+009
1.23456791200000016E+009 1.23456791400000016E+009
1.23456791400000016E+009 1.23456791400000016E+009
--- 100 row(s) selected.
>>
>>select * from T038i where i1 > 1 + 1 and i1 < 3.2 - 3.2 + 5;
I1
-----------
3
4
--- 2 row(s) selected.
>>select * from T038i t1, T038i t2 where t1.i1 > 1 + 1 and t1.i1 < 3.2 - 3.2 + 5;
I1 I1
----------- -----------
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
4 1
4 2
4 3
4 4
4 5
4 6
4 7
4 8
--- 16 row(s) selected.
>>
>>select * from T038i where i1 < 1.0 / 2 + 3 or i1 > 3 + 3 / 1;
I1
-----------
1
2
3
7
8
--- 5 row(s) selected.
>>select * from T038i t1, T038i t2 where t1.i1 < 1.0 / 2 + 3 or t1.i1 > 3 + 3 / 1;
I1 I1
----------- -----------
1 1
2 1
3 1
7 1
8 1
1 2
2 2
3 2
7 2
8 2
1 3
2 3
3 3
7 3
8 3
1 4
2 4
3 4
7 4
8 4
1 5
2 5
3 5
7 5
8 5
1 6
2 6
3 6
7 6
8 6
1 7
2 7
3 7
7 7
8 7
1 8
2 8
3 8
7 8
8 8
--- 40 row(s) selected.
>>
>>select * from T038i where i1 = cast(2.0 + 2 as int) + 3;
I1
-----------
7
--- 1 row(s) selected.
>>select * from T038i t1, T038i t2 where t1.i1 = cast(2.0 - 1 as int) + 3;
I1 I1
----------- -----------
4 1
4 2
4 3
4 4
4 5
4 6
4 7
4 8
--- 8 row(s) selected.
>>
>>select * from T038i where i1 > char_length(trim(' abc ') || trim(' ef '));
I1
-----------
6
7
8
--- 3 row(s) selected.
>>select * from T038i t1, T038i t2 where t1.i1 > char_length(trim(' abc ') || trim(' ef '));
I1 I1
----------- -----------
6 1
7 1
8 1
6 2
7 2
8 2
6 3
7 3
8 3
6 4
7 4
8 4
6 5
7 5
8 5
6 6
7 6
8 6
6 7
7 7
8 7
6 8
7 8
8 8
--- 24 row(s) selected.
>>
>>select * from T038j where j1 < 'abcde' || 'fghijk' || 'lmnop';
J1
----------
abcd
1234567890
--- 2 row(s) selected.
>>select * from T038j t1, T038j t2 where t1.j1 > trim(' abc ') || trim(' ef ');
J1 J1
---------- ----------
efghi abcd
jklmno abcd
pqrstuv abcd
wxyz abcd
efghi efghi
jklmno efghi
pqrstuv efghi
wxyz efghi
efghi jklmno
jklmno jklmno
pqrstuv jklmno
wxyz jklmno
efghi pqrstuv
jklmno pqrstuv
pqrstuv pqrstuv
wxyz pqrstuv
efghi wxyz
jklmno wxyz
pqrstuv wxyz
wxyz wxyz
efghi 1234567890
jklmno 1234567890
pqrstuv 1234567890
wxyz 1234567890
--- 24 row(s) selected.
>>
>>select * from T038j where char_length(substring('abcdef' from 1 for 3) || 'cde') >
+> position('B' in upper('abcdef')) + octet_length(lower('abcdef'));
--- 0 row(s) selected.
>>select * from T038j where j1 < {fn concat('abc', {fn CONVERT(j1,LONG VARCHAR)})};
J1
----------
1234567890
--- 1 row(s) selected.
>>
>>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'));
--- 0 row(s) selected.
>>
>>select * from T038k where k1 < 'abcde' || 'fghijk' || 'lmnop';
K1
------------
abcd
1234567890
--- 2 row(s) selected.
>>select * from T038k t1, T038k t2 where t1.k1 > trim(' abc ') || trim(' ef ');
K1 K1
------------ ------------
efghi abcd
jklmno abcd
pqrstuv abcd
wxyz abcd
efghi efghi
jklmno efghi
pqrstuv efghi
wxyz efghi
efghi jklmno
jklmno jklmno
pqrstuv jklmno
wxyz jklmno
efghi pqrstuv
jklmno pqrstuv
pqrstuv pqrstuv
wxyz pqrstuv
efghi wxyz
jklmno wxyz
pqrstuv wxyz
wxyz wxyz
efghi 1234567890
jklmno 1234567890
pqrstuv 1234567890
wxyz 1234567890
--- 24 row(s) selected.
>>
>>select * from T038k where char_length(substring('abcdef' from 1 for 3) || 'cde') >
+> position('B' in upper('abcdef')) + octet_length(lower('abcdef'));
--- 0 row(s) selected.
>>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'));
--- 0 row(s) selected.
>>
>>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'));
--- 0 row(s) selected.
>>
>>select * from T038l where l1 < 'ABCDE' || 'FGHIJK' || 'LMNOP';
L1
--------------------
ABCD
1234567890
--- 2 row(s) selected.
>>select * from T038l t1, T038l t2 where t1.l1 > trim(' abc ') || trim(' ef ');
--- 0 row(s) selected.
>>
>>select * from T038l where char_length(substring('abcdef' from 1 for 3) || 'cde') >
+> position('B' in upper('abcdef')) + octet_length(lower('abcdef'));
--- 0 row(s) selected.
>>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'));
--- 0 row(s) selected.
>>
>>select * from T038m where m1 >= 1234567890 - 2 + 2 * 4.0;
M1
--------------------
1234567896
1234567898
1234567900
1234567902
1234567904
1234567906
1234567908
1234567910
1234567912
1234567914
--- 10 row(s) selected.
>>select * from T038m t1, T038m t2 where t1.m1 >= 1234567890 - 2 + 2 * 4.0;
M1 M1
-------------------- --------------------
1234567896 1234567896
1234567898 1234567896
1234567900 1234567896
1234567902 1234567896
1234567904 1234567896
1234567906 1234567896
1234567908 1234567896
1234567910 1234567896
1234567912 1234567896
1234567914 1234567896
1234567896 1234567898
1234567898 1234567898
1234567900 1234567898
1234567902 1234567898
1234567904 1234567898
1234567906 1234567898
1234567908 1234567898
1234567910 1234567898
1234567912 1234567898
1234567914 1234567898
1234567896 1234567900
1234567898 1234567900
1234567900 1234567900
1234567902 1234567900
1234567904 1234567900
1234567906 1234567900
1234567908 1234567900
1234567910 1234567900
1234567912 1234567900
1234567914 1234567900
1234567896 1234567902
1234567898 1234567902
1234567900 1234567902
1234567902 1234567902
1234567904 1234567902
1234567906 1234567902
1234567908 1234567902
1234567910 1234567902
1234567912 1234567902
1234567914 1234567902
1234567896 1234567904
1234567898 1234567904
1234567900 1234567904
1234567902 1234567904
1234567904 1234567904
1234567906 1234567904
1234567908 1234567904
1234567910 1234567904
1234567912 1234567904
1234567914 1234567904
1234567896 1234567906
1234567898 1234567906
1234567900 1234567906
1234567902 1234567906
1234567904 1234567906
1234567906 1234567906
1234567908 1234567906
1234567910 1234567906
1234567912 1234567906
1234567914 1234567906
1234567896 1234567908
1234567898 1234567908
1234567900 1234567908
1234567902 1234567908
1234567904 1234567908
1234567906 1234567908
1234567908 1234567908
1234567910 1234567908
1234567912 1234567908
1234567914 1234567908
1234567896 1234567910
1234567898 1234567910
1234567900 1234567910
1234567902 1234567910
1234567904 1234567910
1234567906 1234567910
1234567908 1234567910
1234567910 1234567910
1234567912 1234567910
1234567914 1234567910
1234567896 1234567912
1234567898 1234567912
1234567900 1234567912
1234567902 1234567912
1234567904 1234567912
1234567906 1234567912
1234567908 1234567912
1234567910 1234567912
1234567912 1234567912
1234567914 1234567912
1234567896 1234567914
1234567898 1234567914
1234567900 1234567914
1234567902 1234567914
1234567904 1234567914
1234567906 1234567914
1234567908 1234567914
1234567910 1234567914
1234567912 1234567914
1234567914 1234567914
--- 100 row(s) selected.
>>select * from T038m where m1 = cast (-9223372036854775808 as largeint) - 1;
--- 0 row(s) selected.
>>select * from T038m where m1 = cast (9223372036854775807 as largeint) + 1;
--- 0 row(s) selected.
>>
>>select * from T038n where n1 > 1 + 1 and n1 < 3.2 - 3.2 + 5;
N1
------
3
4
--- 2 row(s) selected.
>>select * from T038n t1, T038n t2 where t1.n1 > 1 + 1 and t2.n1 < 3.2 - 3.2 + 5;
N1 N1
------ ------
3 1
3 2
3 3
3 4
4 1
4 2
4 3
4 4
5 1
5 2
5 3
5 4
6 1
6 2
6 3
6 4
7 1
7 2
7 3
7 4
8 1
8 2
8 3
8 4
--- 24 row(s) selected.
>>
>>select * from T038n where n1 < 1.0 / 2 + 3 or n1 > 3 + 3 / 1;
N1
------
1
2
3
7
8
--- 5 row(s) selected.
>>select * from T038n t1, T038n t2 where t1.n1 < 1.0 / 2 + 3 or t1.n1 > 3 + 3 / 1;
N1 N1
------ ------
1 1
2 1
3 1
7 1
8 1
1 2
2 2
3 2
7 2
8 2
1 3
2 3
3 3
7 3
8 3
1 4
2 4
3 4
7 4
8 4
1 5
2 5
3 5
7 5
8 5
1 6
2 6
3 6
7 6
8 6
1 7
2 7
3 7
7 7
8 7
1 8
2 8
3 8
7 8
8 8
--- 40 row(s) selected.
>>
>>select * from T038n where n1 = cast(2.0 + 1 as int) + 3;
N1
------
6
--- 1 row(s) selected.
>>select * from T038n t1, T038n t2 where t1.n1 = cast(2.0 as int) + 3;
N1 N1
------ ------
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
--- 8 row(s) selected.
>>
>>select * from T038n where n1 > char_length(trim(' abc ') || trim(' ef '));
N1
------
6
7
8
--- 3 row(s) selected.
>>select * from T038n t1, T038n t2 where t1.n1 > char_length(trim(' abc ') || trim(' ef '));
N1 N1
------ ------
6 1
7 1
8 1
6 2
7 2
8 2
6 3
7 3
8 3
6 4
7 4
8 4
6 5
7 5
8 5
6 6
7 6
8 6
6 7
7 7
8 7
6 8
7 8
8 8
--- 24 row(s) selected.
>>
>>select * from T038n where n1 = cast (-32767 as smallint) - 1;
--- 0 row(s) selected.
>>select * from T038n where n1 = cast (-32768 as smallint) - 1;
--- 0 row(s) selected.
>>select * from T038n where n1 = cast (32766 as smallint) + 1;
--- 0 row(s) selected.
>>select * from T038n where n1 = cast (32767 as smallint) + 1;
--- 0 row(s) selected.
>>
>>select * from T038o where o1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>select * from T038o t1, T038o t2 where t1.o1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>select * from T038o where o1 < cast(100 + 100 as smallint unsigned) - 100;
--- 0 row(s) selected.
>>select * from T038o where o1 <= cast(500 + 500 as integer unsigned) + 500;
--- 0 row(s) selected.
>>select * from T038o where o1 <= cast(10000 + 20000 as largeint signed) + 1000;
--- 0 row(s) selected.
>>select * from T038o where o1 >= cast(1 + 2 as decimal(7) unsigned) + 3
+> and o1 < cast(100 as decimal(3) unsigned);
--- 0 row(s) selected.
>>select * from T038o where o1 <= cast(3e38 - 3e37 as real) + 3e36;
O1
---------------
1.2345678E+005
1.2347678E+005
1.2347778E+005
1.2347878E+005
1.2347978E+005
1.2348978E+005
1.2349678E+005
1.2357678E+005
1.2367678E+005
1.2377678E+005
--- 10 row(s) selected.
>>select * from T038o where o1 <= cast(1e30 + 1e50 as double precision) + 1e50;
O1
---------------
1.2345678E+005
1.2347678E+005
1.2347778E+005
1.2347878E+005
1.2347978E+005
1.2348978E+005
1.2349678E+005
1.2357678E+005
1.2367678E+005
1.2377678E+005
--- 10 row(s) selected.
>>select * from T038o where o1 <= cast (9999999998 as real) + 1;
O1
---------------
1.2345678E+005
1.2347678E+005
1.2347778E+005
1.2347878E+005
1.2347978E+005
1.2348978E+005
1.2349678E+005
1.2357678E+005
1.2367678E+005
1.2377678E+005
--- 10 row(s) selected.
>>select * from T038o where o1 <= cast (9999999999 as real) + 1;
O1
---------------
1.2345678E+005
1.2347678E+005
1.2347778E+005
1.2347878E+005
1.2347978E+005
1.2348978E+005
1.2349678E+005
1.2357678E+005
1.2367678E+005
1.2377678E+005
--- 10 row(s) selected.
>>
>>
>>select * from T038p where p1 > abs(-1234567890123456.789 - 10000000000 * 3);
--- 0 row(s) selected.
>>select * from T038p t1, T038p t2 where t1.p1 > 1234567890123456.789 + 10000000000 * 3;
--- 0 row(s) selected.
>>select * from T038p where p1 < cast (4294967296 as double precision) + 1;
P1
-------------------------
1.23456789000000016E+005
1.23476789000000016E+005
1.23477789000000016E+005
1.23478789000000016E+005
1.23479789000000000E+005
1.23489789000000000E+005
1.23496789000000000E+005
1.23576789000000016E+005
1.23676789000000016E+005
1.23776789000000016E+005
--- 10 row(s) selected.
>>
>>select * from T038q where q1 > 1 + 1 and q1 < 3.2 - 3.2 + 50;
Q1
-----
31
4
8
16
17
18
19
20
--- 8 row(s) selected.
>>select * from T038q t1, T038q t2 where t1.q1 > 1 + 1 and t2.q1 < 3.2 - 3.2 + 50;
Q1 Q1
----- -----
31 0
31 31
31 2
31 4
31 8
31 16
31 17
31 18
31 19
31 20
4 0
4 31
4 2
4 4
4 8
4 16
4 17
4 18
4 19
4 20
8 0
8 31
8 2
8 4
8 8
8 16
8 17
8 18
8 19
8 20
16 0
16 31
16 2
16 4
16 8
16 16
16 17
16 18
16 19
16 20
17 0
17 31
17 2
17 4
17 8
17 16
17 17
17 18
17 19
17 20
18 0
18 31
18 2
18 4
18 8
18 16
18 17
18 18
18 19
18 20
19 0
19 31
19 2
19 4
19 8
19 16
19 17
19 18
19 19
19 20
20 0
20 31
20 2
20 4
20 8
20 16
20 17
20 18
20 19
20 20
--- 80 row(s) selected.
>>
>>-- some TPC-D tests
>>
>>CREATE TABLE T038ORD
+> (
+> o_orderpriority INT DEFAULT NULL
+> , o_orderdate DATE DEFAULT NULL
+> , o_orderkey INT DEFAULT NULL
+> )
+> ;
--- SQL operation complete.
>>
>>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
+> )
+> ;
--- SQL operation complete.
>>
>>create table T038part(p_T038partkey int default null,
+> p_type char);
--- SQL operation complete.
>>
>>
>>insert into T038ORD values(1,date '1994-12-01', 2);
--- 1 row(s) inserted.
>>insert into T038ORD values(1,date '1994-12-01', 4);
--- 1 row(s) inserted.
>>insert into T038LI values(2, 2, date '1995-01-01', date '1995-02-03', 8, 10, 15, 'A', date '1994-02-01');
--- 1 row(s) inserted.
>>insert into T038LI values(2, 2, date '1995-01-03', date '1995-02-03', 7, 13, 13, 'B', date '1997-03-04');
--- 1 row(s) inserted.
>>insert into T038LI values(2, 2, date '1993-01-01', date '1995-02-03', 14, 12, 4, 'C', date '1998-09-09');
--- 1 row(s) inserted.
>>insert into T038LI values(3, 2, date '1995-01-01', date '1995-02-03', 19, 22, 23, 'D', date '1985-09-08');
--- 1 row(s) inserted.
>>
>>-- 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;
O_ORDERPRIORITY (EXPR)
--------------- --------------------
1 1
--- 1 row(s) selected.
>>
>>-- 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;
L_ORDERKEY L_SHIPDATE
----------- ----------
2 1995-02-03
--- 1 row(s) selected.
>>
>>-- 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;
--- 0 row(s) selected.
>>
>>-- 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
+> ;
--- 0 row(s) selected.
>>
>>-- 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
+> ;
(EXPR)
--------------------
?
--- 1 row(s) selected.
>>
>>-- String function tests from TEST012 (Executor) start here
>>
>>-- Create and populate table
>>create table T038sf(a int, b char(10), c varchar(30));
--- SQL operation complete.
>>
>>insert into T038sf values(10, 'abcdef', 'ghij');
--- 1 row(s) inserted.
>>insert into T038sf values(20, 'ABCDEF', 'GHIJ');
--- 1 row(s) inserted.
>>insert into T038sf values(30, ' abc ', ' def ');
--- 1 row(s) inserted.
>>insert into T038sf values(40, 'test', 'trim on non-space');
--- 1 row(s) inserted.
>>
>>-- testing POSITION function
>>select b, position('xy' in b) from T038sf;
B (EXPR)
---------- ----------
abcdef 0
ABCDEF 0
abc 0
test 0
--- 4 row(s) selected.
>>select b, position('f' in b) from T038sf;
B (EXPR)
---------- ----------
abcdef 6
ABCDEF 0
abc 0
test 0
--- 4 row(s) selected.
>>select c, position('hi' in c) from T038sf;
C (EXPR)
------------------------------ ----------
ghij 2
GHIJ 0
def 0
trim on non-space 0
--- 4 row(s) selected.
>>select b, position('' in b), c, position('' in c) from T038sf;
B (EXPR) C (EXPR)
---------- ---------- ------------------------------ ----------
abcdef 1 ghij 1
ABCDEF 1 GHIJ 1
abc 1 def 1
test 1 trim on non-space 1
--- 4 row(s) selected.
>>select b, position('' in '') from T038sf;
B (EXPR)
---------- ----------
abcdef 1
ABCDEF 1
abc 1
test 1
--- 4 row(s) selected.
>>select * from T038sf where position('b' in b) < 3;
A B C
----------- ---------- ------------------------------
10 abcdef ghij
20 ABCDEF GHIJ
40 test trim on non-space
--- 3 row(s) selected.
>>select * from T038sf where position('b' in b) <= 2;
A B C
----------- ---------- ------------------------------
10 abcdef ghij
20 ABCDEF GHIJ
40 test trim on non-space
--- 3 row(s) selected.
>>
>>-- testing TRIM function
>>select c,trim(leading from c),char_length(trim(leading from c)) from T038sf;
C (EXPR) (EXPR)
------------------------------ ------------------------------ ----------
ghij ghij 4
GHIJ GHIJ 4
def def 6
trim on non-space trim on non-space 17
--- 4 row(s) selected.
>>select b,trim(leading ' ' from b),char_length(trim(leading ' ' from b)) from T038sf;
B (EXPR) (EXPR)
---------- ---------- ----------
abcdef abcdef 10
ABCDEF ABCDEF 10
abc abc 7
test test 10
--- 4 row(s) selected.
>>select b,trim(trailing from b),char_length(trim(trailing from b)) from T038sf;
B (EXPR) (EXPR)
---------- ---------- ----------
abcdef abcdef 6
ABCDEF ABCDEF 6
abc abc 6
test test 4
--- 4 row(s) selected.
>>select b,trim(both from b),char_length(trim(both from b)) from T038sf;
B (EXPR) (EXPR)
---------- ---------- ----------
abcdef abcdef 6
ABCDEF ABCDEF 6
abc abc 3
test test 4
--- 4 row(s) selected.
>>select b,trim('b' from trim('a' from b)),char_length(trim('b' from trim('a' from b))) from T038sf;
B (EXPR) (EXPR)
---------- ---------- ----------
abcdef cdef 8
ABCDEF ABCDEF 10
abc abc 10
test test 10
--- 4 row(s) selected.
>>select * from T038sf where trim(c)='def';
A B C
----------- ---------- ------------------------------
30 abc def
--- 1 row(s) selected.
>>select * from T038sf where trim(leading from c) = 'def';
A B C
----------- ---------- ------------------------------
30 abc def
--- 1 row(s) selected.
>>-- 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;
B (EXPR) C (EXPR)
---------- ------ ------------------------------ ------
abcdef a ghij g
ABCDEF A GHIJ G
abc def
test t trim on non-space t
--- 4 row(s) selected.
>>select b, CHAR(ASCII(b)), c, CHAR(ASCII(c)) from T038sf;
B (EXPR) C (EXPR)
---------- ------ ------------------------------ ------
abcdef a ghij g
ABCDEF A GHIJ G
abc def
test t trim on non-space t
--- 4 row(s) selected.
>>select 'c', CHAR(ASCII('cba')) from T038sf;
(EXPR) (EXPR)
------ ------
c c
c c
c c
c c
--- 4 row(s) selected.
>>
>>select 65, { fn ASCII(CHAR(65)) } from T038sf;
(EXPR) (EXPR)
------ ----------
65 65
65 65
65 65
65 65
--- 4 row(s) selected.
>>select 65, ASCII(CHAR(65)) from T038sf;
(EXPR) (EXPR)
------ ----------
65 65
65 65
65 65
65 65
--- 4 row(s) selected.
>>
>>-- -ve
>>select { fn ASCII(1) } from T038sf;
*** ERROR[4043] The operand of function ASCII must be character.
*** ERROR[8822] The statement was not prepared.
>>
>>select CHAR(b) from T038sf;
*** ERROR[4045] The operand of function CHAR must be numeric.
*** ERROR[8822] The statement was not prepared.
>>select CHAR(256) from T038sf;
*** ERROR[8428] The argument to function CHAR is not valid.
--- 0 row(s) selected.
>>select CHAR(1.1) from T038sf;
*** ERROR[4047] The operands of function CHAR must have a scale of 0.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>-- INSERT function
>>select insert('abc', 2, 1, 'zz') from T038sf;
(EXPR)
--------
azzc
azzc
azzc
azzc
--- 4 row(s) selected.
>>
>>-- REPEAT
>>select repeat('aa', 10) from T038sf;
(EXPR)
--------------------
aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa
--- 4 row(s) selected.
>>
>>-- test fix to genesis case: 10-981211-6071
>>-- makes sure we catch and diagnose invalid repeat count
>>select REPEAT(b, 9999999999999999999) from T038sf;
*** ERROR[4116] The second operand of function REPEAT is not valid.
*** ERROR[8822] The statement was not prepared.
>>select REPEAT(c, 9999999999999999999) from T038sf;
*** ERROR[4116] The second operand of function REPEAT is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>-- REPLACE
>>select replace('abcdabcdab', 'cd', 'abc') from T038sf;
(EXPR)
---------------
ababcababcab
ababcababcab
ababcababcab
ababcababcab
--- 4 row(s) selected.
>>
>>-- SPACE
>>select 's' || space(10) || 'e', char_length(space(10)) from T038sf;
(EXPR) (EXPR)
------------ ----------
s e 10
s e 10
s e 10
s e 10
--- 4 row(s) selected.
>>
>>-- VEG conversion should not change the result of string function.
>>-- should return 10
>>select char_length(b) from T038sf where b = 'abcdef';
(EXPR)
----------
10
--- 1 row(s) selected.
>>
>>-- should return 'abcdef abcdef '
>>select repeat(b, 2) from T038sf where b = 'abcdef';
(EXPR)
--------------------
abcdef abcdef
--- 1 row(s) selected.
>>
>>-- 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%';
(EXPR)
--------------------
Robert John Smith
--- 1 row(s) selected.
>>-- should get one row (" Robert John Smith")
>>
>>select a, RPAD (c, 20, '7') from T038sf;
A (EXPR)
----------- --------------------
10 ghij7777777777777777
20 GHIJ7777777777777777
30 def 77777777777
40 trim on non-space777
--- 4 row(s) selected.
>>-- 4 rows
>>
>>select RPAD (b, 12, '5'), LPAD (c, 13) from T038sf where RPAD( RPAD(b, 13, 'X'), 10, 'W') = b;
(EXPR) (EXPR)
------------ -------------
abcdef 55 ghij
ABCDEF 55 GHIJ
abc 55 def
test 55 trim on non-s
--- 4 row(s) selected.
>>-- 4 rows
>>
>>-- test SPLIT_PART function
>>
>>select split_part('sa:sbl:sc', ':', 1) from t038sf;
(EXPR)
---------
sa
sa
sa
sa
--- 4 row(s) selected.
>>select split_part('sa:sbl:sc', ':', 2) from t038sf;
(EXPR)
---------
sbl
sbl
sbl
sbl
--- 4 row(s) selected.
>>select split_part('sa:sbl:sc', ':', 3) from t038sf;
(EXPR)
---------
sc
sc
sc
sc
--- 4 row(s) selected.
>>
>>-- **EMPTY RESULT**
>>select split_part('sa:sbl:sc', ':', 0) from t038sf;
*** ERROR[8691] Field position must be greater than zero, currently is 0.
--- 0 row(s) selected.
>>select split_part('sa:sbl:sc', ':', 4) from t038sf;
(EXPR)
---------
--- 4 row(s) selected.
>>
>>insert into T038sf values(110, 'a/b/c', 'sa/dsd/s');
--- 1 row(s) inserted.
>>insert into T038sf values(111, 'sasd', 'dsa:/~sd');
--- 1 row(s) inserted.
>>insert into T038sf values(112, '#$%$#@', 'dsasggggsad');
--- 1 row(s) inserted.
>>insert into T038sf values(114, 'a', '这是^中文：测试');
--- 1 row(s) inserted.
>>
>>select split_part(c, '/', 2) from t038sf;
(EXPR)
------------------------------
dsd
~sd
--- 8 row(s) selected.
>>select split_part(c, 'ggg', 2) from t038sf;
(EXPR)
------------------------------
gsad
--- 8 row(s) selected.
>>select split_part(c, 'sd', 1) from t038sf;
(EXPR)
------------------------------
sa/d
dsa:/~
--- 8 row(s) selected.
>>select split_part(c, '：', 1) from t038sf;
(EXPR)
------------------------------
这是^中文
--- 8 row(s) selected.
>>
>>
>>-- 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';
--- SQL operation complete.
>>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
+>;
--- SQL command prepared.
>>control query default query_cache reset;
--- SQL operation complete.
>>
>>execute s1;
(EXPR)
--------------------
2
4
6
8
--- 4 row(s) selected.
>>
>>-- nvl function test
>>create table T038nvl (a int not null, b int, primary key(a));
--- SQL operation complete.
>>insert into T038nvl values (1, null), (2, 2), (3, null), (4, 4);
--- 4 row(s) inserted.
>>select nvl(b,a) from T038nvl;
(EXPR)
-----------
1
2
3
4
--- 4 row(s) selected.
>>
>>
>>-- floor/ceil function test
>>create table T038fcl (a int , b decimal(5,2), c numeric (4,1), d largeint, e largeint);
--- SQL operation complete.
>>insert into T038fcl values
+>(1, 300.10, 202.2, 1, 1), (2, -300.10, -202.2, 2, 2);
--- 2 row(s) inserted.
>>
>>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;
FLOOR_A FLOOR_B FLOOR_C BIGNUMFLOOR CEIL_A CEIL_B CEIL_C BIGNUMCEIL
----------- ------- ------- -------------------- ----------- ------ ------ --------------------
1 300 202 2 1 301 203 2
2 -301 -203 4 2 -300 -202 4
--- 2 row(s) selected.
>>
>>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
+>);
--- 2 row(s) inserted.
>>
>>showddl T038fc2;
CREATE TABLE TRAFODION.SCH.T038FC2
(
FLOOR_A INT DEFAULT NULL
, FLOOR_B DECIMAL(5, 0) DEFAULT NULL
, FLOOR_C NUMERIC(4, 0) DEFAULT NULL
, BIGNUMFLOOR LARGEINT DEFAULT NULL
, CEIL_A INT DEFAULT NULL
, CEIL_B DECIMAL(5, 0) DEFAULT NULL
, CEIL_C NUMERIC(4, 0) DEFAULT NULL
, BIGNUMCEIL LARGEINT DEFAULT NULL
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>log;
>>
>>create table t038aqr( a int not null, b int not null, primary key(a) );
--- SQL operation complete.
>>insert into t038aqr values(1, 1);
--- 1 row(s) inserted.
>>select a, b from t038aqr;
A B
----------- -----------
1 1
--- 1 row(s) selected.
>>-- let aqr_other_session drop and recreate the t038aqr
>>sh sqlci -i "TEST038(aqr_other_session)" ;
>>drop table t038aqr;
--- SQL operation complete.
>>log;