| >> |
| >>----------------------------------------------------------------------------- |
| >>-- 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 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; |