| >> |
| >>create table t001t1 (a int, b char(9), c int, d char(4)); |
| |
| --- SQL operation complete. |
| >>create table t001tn (a int, b char(9), c int, d char(4) COLLATE SJIS); |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4069] Column D uses an unsupported collation. |
| |
| --- SQL operation failed with errors. |
| >>create table t001t2 (a int not null, b char(9), c int, d char(4), primary key (a)); |
| |
| --- SQL operation complete. |
| >>create table t001t3 (a int not null, b char(9) not null, c int, d char(4), primary key (a, b)); |
| |
| --- SQL operation complete. |
| >> |
| >>#ifMX |
| >>create table t001ut1 (a int, b nchar(9), c int, d nchar(4)); |
| |
| --- SQL operation complete. |
| >>create table t001ut2 (a int not null, b nchar(9), c int, d nchar(4), primary key (a)); |
| |
| --- SQL operation complete. |
| >>create table t001ut3 (a int not null, b nchar(9) not null, c int, d nchar(4), primary key (a, b)); |
| |
| --- SQL operation complete. |
| >>#ifMX |
| >> |
| >>?section dml |
| >> |
| >>invoke t001t1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001T1 |
| -- Definition current Mon Apr 22 18:33:14 2019 |
| |
| ( |
| SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , A INT DEFAULT NULL |
| , B CHAR(9) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >>invoke t001tn; |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>invoke t001t2; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001T2 |
| -- Definition current Mon Apr 22 18:33:17 2019 |
| |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B CHAR(9) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >>invoke t001t3; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001T3 |
| -- Definition current Mon Apr 22 18:33:18 2019 |
| |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B CHAR(9) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC, B ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>#ifMX |
| >>invoke $$TEST_SCHEMA$$.t001ut1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001UT1 |
| -- Definition current Mon Apr 22 18:33:20 2019 |
| |
| ( |
| SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , A INT DEFAULT NULL |
| , B CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT |
| DEFAULT NULL |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT |
| DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >>invoke $$TEST_SCHEMA_NAME$$.t001ut2; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001UT2 |
| -- Definition current Mon Apr 22 18:33:21 2019 |
| |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT |
| DEFAULT NULL |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT |
| DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >>invoke t001ut3; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T001UT3 |
| -- Definition current Mon Apr 22 18:33:23 2019 |
| |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT |
| NO DEFAULT NOT NULL NOT DROPPABLE |
| , C INT DEFAULT NULL |
| , D CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT |
| DEFAULT NULL |
| ) |
| PRIMARY KEY (A ASC, B ASC) |
| |
| --- SQL operation complete. |
| >>#ifMX |
| >> |
| >>-- INSERT queries |
| >>insert into t001t1 values (10, 'abc', 20, 'xy'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t001t1(b,d,a,c) values ('defg', 'wx', 10+10, 30); |
| |
| --- 1 row(s) inserted. |
| >>insert into t001t2 select * from t001t1; |
| |
| --- 2 row(s) inserted. |
| >>insert into t001t3(a,b,c,d) select a,b,c,d from t001t2; |
| |
| --- 2 row(s) inserted. |
| >> |
| >>#ifMX |
| >>insert into t001ut1 values (10, N'abc', 20, N'xy'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t001ut1(b,d,a,c) values (N'defg', N'wx', 10+10, 30); |
| |
| --- 1 row(s) inserted. |
| >>insert into t001ut2 select * from t001ut1; |
| |
| --- 2 row(s) inserted. |
| >>insert into t001ut3(a,b,c,d) select a,b,c,d from t001ut2; |
| |
| --- 2 row(s) inserted. |
| >>#ifMX |
| >> |
| >>-- SELECT queries |
| >>select * from t001t1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select t001t2.* from t001t2; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select * from t001t3; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>#ifMX |
| >>select * from t001ut1; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select t001ut2.* from t001ut2; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select * from t001ut3; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>#ifMX |
| >> |
| >>-- Error case |
| >>select 0, '0' + 1 from t001t1; |
| |
| (EXPR) (EXPR) |
| ------ -------------------- |
| |
| 0 1 |
| 0 1 |
| |
| --- 2 row(s) selected. |
| >>-- should get type incompatibility error |
| >> |
| >>select cast(b as ansivarchar(8)) from t001t1; |
| |
| *** ERROR[3178] One or more of the following external (host-language) data types incorrectly appears within the SQL query or operation: ANSIVARCHAR. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- err 3178 |
| >>create table t001t1_foo(a lsdecimal); |
| |
| *** ERROR[3178] One or more of the following external (host-language) data types incorrectly appears within the SQL query or operation: LSDECIMAL. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- err 3178 |
| >> |
| >>select * from (select * from t001t1) x; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>select a,b from t001t1; |
| |
| A B |
| ----------- --------- |
| |
| 10 abc |
| 20 defg |
| |
| --- 2 row(s) selected. |
| >>select a,a from t001t1; |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| 20 20 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select a+1,a-1,a*1,a/1 from t001t1; |
| |
| (EXPR) (EXPR) (EXPR) (EXPR) |
| -------------------- -------------------- -------------------- --------------------- |
| |
| 11 9 10 10.0 |
| 21 19 20 20.0 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select t001t1.a, t001t1.d from t001t1; |
| |
| A D |
| ----------- ---- |
| |
| 10 xy |
| 20 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>select 1 from t001t1; |
| |
| (EXPR) |
| ------ |
| |
| 1 |
| 1 |
| |
| --- 2 row(s) selected. |
| >>select 1+1 from t001t1; |
| |
| (EXPR) |
| ------ |
| |
| 2 |
| 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t001t1 where 1 = 1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select * from t001t1 where a = 10; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| |
| --- 1 row(s) selected. |
| >>select * from t001t1 where a <> 10; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 20 defg 30 wx |
| |
| --- 1 row(s) selected. |
| >>select * from t001t1 where a = 10 or a = 20; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>select * from t001t1 where a = 10 and a = 20; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- Error case |
| >>#ifMX |
| >>select 0, N'0' + 1 from t001ut1; |
| |
| (EXPR) (EXPR) |
| ------ -------------------- |
| |
| 0 1 |
| 0 1 |
| |
| --- 2 row(s) selected. |
| >>-- should get type incompatibility error |
| >> |
| >>select * from (select * from t001ut1) x; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>select a,b from t001ut1; |
| |
| A B |
| ----------- ------------------ |
| |
| 10 abc |
| 20 defg |
| |
| --- 2 row(s) selected. |
| >> |
| >>select t001ut1.a, t001ut1.d from t001ut1; |
| |
| A D |
| ----------- -------- |
| |
| 10 xy |
| 20 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t001ut1 where a = 10 and a = 20; |
| |
| --- 0 row(s) selected. |
| >> |
| >>select * from (select * from t001ut1) x , t001ut2; |
| |
| A B C D A B C D |
| ----------- ------------------ ----------- -------- ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy 10 abc 20 xy |
| 10 abc 20 xy 20 defg 30 wx |
| 20 defg 30 wx 10 abc 20 xy |
| 20 defg 30 wx 20 defg 30 wx |
| |
| --- 4 row(s) selected. |
| >>#ifMX |
| >> |
| >>#ifMP |
| >>set envvar NCHAR_SJIS_DEBUG; |
| >> -- allow charset SJIS on MP |
| >>#ifMP |
| >> |
| >>select a from t001t1 where b like _SJIS'sj'; |
| |
| *** ERROR[3010] Character set SJIS is not yet supported. |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| select a from t001t1 where b like _SJIS'sj'; |
| ^ (39 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where b like 'sj'COLLATE SJIS; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_. |
| |
| *** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- ok, B gets coerced |
| >>select a from t001t1 where b COLLATE DEFAULT like 'sj'COLLATE SJIS; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_. |
| |
| *** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where b COLLATE DEFAULT like 'jj' escape 's'COLLATE SJIS; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(1) CHARACTER SET ISO88591 COLLATE _unknown_. |
| |
| *** ERROR[4050] The operands of the LIKE predicate must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>select a from t001t1 where trim(1 from 2) = ''; |
| |
| *** ERROR[4133] For the TRIM function, the trim character and source must be of CHARACTER type. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where trim(1 from b) = ''; |
| |
| *** ERROR[4133] For the TRIM function, the trim character and source must be of CHARACTER type. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where trim(b from 2) = ''; |
| |
| *** ERROR[8404] The trim character argument of function TRIM must be one character in length. |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- comment out because of the behavior change to NATIONAL_CHARSET in R2. |
| >>-- It is a read-only CQD. |
| >>--control query default NATIONAL_CHARSET 'kanji'; |
| >>--select a from t001t1 where trim(N' ' from b) = ''; |
| >>--control query default NATIONAL_CHARSET 'sql_text'; |
| >>--select a from t001t1 where trim(N' ' from b) = ''; -- ok |
| >>--control query default NATIONAL_CHARSET reset; |
| >>select a from t001t1 where trim(N' ' from b) = ''; |
| |
| --- 0 row(s) selected. |
| >>select a from t001t1 where trim(' 'COLLATE SJIS from b COLLATE DEFAULT) = ''; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(1) CHARACTER SET ISO88591 COLLATE _unknown_ cannot be compared with type CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT. |
| |
| *** ERROR[4063] The operands of function TRIM, LTRIM, or RTRIM must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>select a from t001t1 where '' = replace(1,2,3); |
| |
| *** ERROR[4064] The operands of function REPLACE must be compatible character data types (that is, of the same character set). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where '' = replace('a','x','z'COLLATE SJIS); |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| --- 0 row(s) selected. |
| >> -- ok, compatible |
| >>select a from t001t1 where '' = replace('a'COLLATE DEFAULT,'xx'COLLATE SJIS,'z'); |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT cannot be compared with type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_. |
| |
| *** ERROR[4063] The operands of function REPLACE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- not comparable |
| >>select a from t001t1 where '' = replace('a',2,'z'); |
| |
| *** ERROR[4041] Type CHAR(1) cannot be compared with type NUMERIC(1). |
| |
| *** ERROR[4063] The operands of function REPLACE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- not comparable |
| >>select a from t001t1 where 99 = position(1 in 2); |
| |
| *** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where 99 = position('x' in 2); |
| |
| *** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where 99 = position(1 in 'y'); |
| |
| *** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select a from t001t1 where 99 = position('xx'COLLATE SJIS in 'y'COLLATE DEFAULT); |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4041] Type CHAR(2) CHARACTER SET ISO88591 COLLATE _unknown_ cannot be compared with type CHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT. |
| |
| *** ERROR[4063] The operands of function POSITION or LOCATE must be comparable character data types (that is, of the same character set and collation). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>select b from t001tn UNION select d from t001tn; |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- not comparable |
| >>select b from t001tn UNION ALL select d from t001tn; |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- ok, no resulting collating sequence, but no comparison |
| >>select * from ( |
| +> select b from t001tn UNION ALL select d from t001tn) u(b) where b='x'; |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- not comparable |
| >>select * from ( |
| +> select b from t001tn UNION ALL select d from t001tn) u(b) where b=(select b from t001tn); |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- not comparable |
| >>select b from t001tn UNION select d COLLATE SJIS from t001tn; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- ok, coerced |
| >>select b COLLATE SJIS from t001tn UNION select d COLLATE SJIS from t001tn; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> -- ok |
| >> |
| >>select a COLLATE SJIS from t001tn; |
| |
| *** WARNING[3169] SJIS is not a known collation. |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select b COLLATE Xyzw from t001tn; |
| |
| *** WARNING[3169] XYZW is not a known collation. |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- ok, unknown-coll warning, coll not used |
| >>select b from (select b COLLATE Xyzw from t001tn)x where b>'a'; |
| |
| *** WARNING[3169] XYZW is not a known collation. |
| |
| *** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >> |
| >>-- JOIN queries |
| >>select t001t1.a, t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a; |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| 20 20 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- Genesis test case added 12/18/96 |
| >>select t001t1.a + t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a; |
| |
| (EXPR) |
| -------------------- |
| |
| 20 |
| 40 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = t001t2.a and t001t1.a = 10 and t001t2.a = 10; |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| |
| --- 1 row(s) selected. |
| >>select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = 10 or t001t2.a = 10; |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| 10 20 |
| 20 10 |
| |
| --- 3 row(s) selected. |
| >>select t001t1.a, t001t2.a from t001t1 join t001t2 on |
| +>t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20) |
| +>or t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20); |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| 10 20 |
| 20 10 |
| |
| --- 3 row(s) selected. |
| >>select t001t1.a, t001t2.a from t001t1 join t001t2 on |
| +>t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20) |
| +>and t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20); |
| |
| A A |
| ----------- ----------- |
| |
| 10 10 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * from (select * from t001t1) x , t001t2; |
| |
| A B C D A B C D |
| ----------- --------- ----------- ---- ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy 10 abc 20 xy |
| 10 abc 20 xy 20 defg 30 wx |
| 20 defg 30 wx 10 abc 20 xy |
| 20 defg 30 wx 20 defg 30 wx |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- NATURAL JOIN queries |
| >>select a from t001t1 x natural join t001t1 y; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select a from t001t1 natural join t001t2; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select a from t001t1 natural join t001t2 natural join t001t3; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select x.a, x.b from (select a, b, c from t001t1 natural join t001t2) x join t001t3 |
| +>on x.c = t001t3.c; |
| |
| A B |
| ----------- --------- |
| |
| 10 abc |
| 20 defg |
| |
| --- 2 row(s) selected. |
| >> |
| >>#ifMX |
| >>select a from t001ut1 x natural join t001ut1 y; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select a from t001ut1 natural join t001ut2; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select a from t001ut1 natural join t001ut2 natural join t001ut3; |
| |
| A |
| ----------- |
| |
| 10 |
| 20 |
| |
| --- 2 row(s) selected. |
| >>select x.a, x.b from (select a, b, c from t001ut1 natural join t001ut2) x join t001ut3 |
| +>on x.c = t001ut3.c; |
| |
| A B |
| ----------- ------------------ |
| |
| 10 abc |
| 20 defg |
| |
| --- 2 row(s) selected. |
| >> |
| >>select count(*) from (select * from t001ut1) x natural join (select * from t001ut2) y , |
| +> (select * from t001ut3) z; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >>#ifMX |
| >> |
| >>-- AGGREGATE queries |
| >> |
| >>select count(*), min(a), max(a), sum(a), avg(a) from t001t1; |
| |
| (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) |
| -------------------- ----------- ----------- -------------------- -------------------- |
| |
| 2 10 20 30 15 |
| |
| --- 1 row(s) selected. |
| >>select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) > 0; |
| |
| (EXPR) (EXPR) (EXPR) (EXPR) (EXPR) |
| -------------------- ----------- ----------- -------------------- -------------------- |
| |
| 2 10 20 30 15 |
| |
| --- 1 row(s) selected. |
| >>select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) < 0; |
| |
| --- 0 row(s) selected. |
| >>select count(*), sum(a) from t001t1 having count(*) = 2 and sum(a) = 30; |
| |
| (EXPR) (EXPR) |
| -------------------- -------------------- |
| |
| 2 30 |
| |
| --- 1 row(s) selected. |
| >>select sum(t001t1.a) from t001t1 , t001t2; |
| |
| (EXPR) |
| -------------------- |
| |
| 60 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from (select * from t001t1) x natural join (select * from t001t2) y , |
| +> (select * from t001t3) z; |
| |
| (EXPR) |
| -------------------- |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- UPDATE queries |
| >>update t001t1 set a = 10 where a = 10; |
| |
| --- 1 row(s) updated. |
| >>select * from t001t1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>update t001t1 set a = 100, d = 'yx' where b = 'abc'; |
| |
| --- 1 row(s) updated. |
| >>select * from t001t1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 100 abc 20 yx |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >>update t001t1 set c = 55; |
| |
| --- 2 row(s) updated. |
| >>select * from t001t1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 100 abc 55 yx |
| 20 defg 55 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>update t001t2 set c = c+1, b = 'gfe' where d = 'wx'; |
| |
| --- 1 row(s) updated. |
| >>select * from t001t2; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 xy |
| 20 gfe 31 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>#ifMX |
| >>update t001ut1 set a = 100, d = N'yx' where b = N'abc'; |
| |
| --- 1 row(s) updated. |
| >>select * from t001ut1; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 100 abc 20 yx |
| 20 defg 30 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>update t001ut2 set c = c+1, b = N'gfe' where d = N'wx'; |
| |
| --- 1 row(s) updated. |
| >>select * from t001ut2; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 xy |
| 20 gfe 31 wx |
| |
| --- 2 row(s) selected. |
| >>#ifMX |
| >> |
| >> |
| >>-- DELETE queries |
| >>delete from t001t1 where a = 100; |
| |
| --- 1 row(s) deleted. |
| >>select * from t001t1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 20 defg 55 wx |
| |
| --- 1 row(s) selected. |
| >> |
| >>delete from t001t3; |
| |
| --- 2 row(s) deleted. |
| >>select * from t001t3; |
| |
| --- 0 row(s) selected. |
| >> |
| >>delete from t001t1; |
| |
| --- 1 row(s) deleted. |
| >>delete from t001t2; |
| |
| --- 2 row(s) deleted. |
| >> |
| >>#ifMX |
| >>delete from t001ut3; |
| |
| --- 2 row(s) deleted. |
| >>select * from t001ut3; |
| |
| --- 0 row(s) selected. |
| >> |
| >>delete from t001ut1; |
| |
| --- 2 row(s) deleted. |
| >>delete from t001ut2; |
| |
| --- 2 row(s) deleted. |
| >>#ifMX |
| >> |
| >>-- PREPAREd queries |
| >>prepare s1 from insert into t001t1 values (10, 'abc', 20, 'yz'); |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| --- 1 row(s) inserted. |
| >>insert into t001t1 values (30, 'def', 40, 'wx'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>prepare s2 from select * from t001t1; |
| |
| --- SQL command prepared. |
| >>execute s2; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 yz |
| 30 def 40 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>execute s1; |
| |
| --- 1 row(s) inserted. |
| >>execute s2; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 yz |
| 30 def 40 wx |
| 10 abc 20 yz |
| |
| --- 3 row(s) selected. |
| >> |
| >>prepare s1 from select * from t001t1 where a = 10; |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 10 abc 20 yz |
| 10 abc 20 yz |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare s2 from select * from t001t1 where a = 30; |
| |
| --- SQL command prepared. |
| >>execute s2; |
| |
| A B C D |
| ----------- --------- ----------- ---- |
| |
| 30 def 40 wx |
| |
| --- 1 row(s) selected. |
| >> |
| >>show prepared; |
| S1 |
| SELECT * FROM T001T1 WHERE A = 10; |
| |
| S2 |
| SELECT * FROM T001T1 WHERE A = 30; |
| |
| >> |
| >>#ifMX |
| >>prepare s1 from insert into t001ut1 values (10, N'abc', 20, N'yz'); |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| --- 1 row(s) inserted. |
| >>insert into t001ut1 values (30, N'def', 40, N'wx'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>prepare s2 from select * from t001ut1; |
| |
| --- SQL command prepared. |
| >>execute s2; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 yz |
| 30 def 40 wx |
| |
| --- 2 row(s) selected. |
| >> |
| >>execute s1; |
| |
| --- 1 row(s) inserted. |
| >>execute s2; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 yz |
| 30 def 40 wx |
| 10 abc 20 yz |
| |
| --- 3 row(s) selected. |
| >> |
| >>prepare s1 from select * from t001ut1 where a = 10; |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| A B C D |
| ----------- ------------------ ----------- -------- |
| |
| 10 abc 20 yz |
| 10 abc 20 yz |
| |
| --- 2 row(s) selected. |
| >> |
| >>show prepared; |
| S2 |
| SELECT * FROM T001UT1; |
| |
| S1 |
| SELECT * FROM T001UT1 WHERE A = 10; |
| |
| >>#ifMX |
| >> |
| >>-- PARAM queries (all moved to TEST001(core)) |
| >> |
| >>-- ---------------------- |
| >>-- test reverse scanning |
| >>-- ---------------------- |
| >>create table t001desc |
| +> ( |
| +> a numeric(18) signed not null, |
| +> primary key ( a DESC ) |
| +> ) |
| +>; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t001desc values(1), (2), (3), (5), (7), (10); |
| |
| --- 6 row(s) inserted. |
| >>select a from t001desc where a < 5 order by a; |
| |
| A |
| -------------------- |
| |
| 1 |
| 2 |
| 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------ |
| >> |
| >>create table t001asc |
| +> ( |
| +> a numeric(18) signed not null, |
| +> primary key ( a ASC ) |
| +> ) |
| +>; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t001asc values(1), (2), (3), (5), (7), (10); |
| |
| --- 6 row(s) inserted. |
| >>select a from t001asc where a < 5 order by a DESC; |
| |
| A |
| -------------------- |
| |
| 3 |
| 2 |
| 1 |
| |
| --- 3 row(s) selected. |
| >> |
| >>------------------------------ |
| >> |
| >>-- This next bit works if Debug, fails if Release; |
| >>-- and the results (char_length of E) will change after MX-NSK-Rel1, |
| >>-- when we correctly support KANJI as double-byte instead of single. |
| >>-- |
| >>-- For now, don't run these tests for MX objects on NSK platform. It is too |
| >>-- difficult to create all the different known results for release, debug, |
| >>-- MX format objects, etc. (rsm) |
| >> |
| >>-- comment out because of the behavior change to NATIONAL_CHARSET in R2. |
| >>-- It is a read-only CQD. |
| >>--#ifNT |
| >>-- control query default NATIONAL_CHARSET 'kanji'; |
| >>-- set envvar NCHAR_DEBUG; |
| >>-- create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS, |
| >>-- e NCHAR(4)); |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| >>-- reset envvar NCHAR_DEBUG; |
| >>-- invoke t001vn; |
| >>-- invoke t001tn2; |
| >>-- table t001vn; |
| >>-- select * from t001tn2 where b=e; -- not comparable |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| >>-- select e collate sjis from t001tn2; |
| >>-- set envvar NCHAR_DEBUG 2; |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; -- 3179 |
| >>-- select e collate sjis from t001tn2; -- 3179 |
| >>-- reset envvar NCHAR_DEBUG; |
| >>-- update t001tn2 set e = _kanji'km' collate SJIS || e; |
| >>-- update t001tn2 set e = e || _kanji'km' collate SJISfoo; |
| >>-- control query default NATIONAL_CHARSET 'reset'; |
| >>--#ifNT |
| >>-- |
| >>--#ifMP |
| >>-- control query default NATIONAL_CHARSET 'kanji'; |
| >>-- set envvar NCHAR_DEBUG; |
| >>-- create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS, |
| >>-- e NCHAR(4)); |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| >>-- reset envvar NCHAR_DEBUG; |
| >>-- invoke t001vn; |
| >>-- invoke t001tn2; |
| >>-- table t001vn; |
| >>-- select * from t001tn2 where b=e; -- not comparable |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| >>-- select e collate sjis from t001tn2; |
| >>-- set envvar NCHAR_DEBUG 2; |
| >>-- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; -- 3179 |
| >>-- select e collate sjis from t001tn2; -- 3179 |
| >>-- reset envvar NCHAR_DEBUG; |
| >>-- update t001tn2 set e = _kanji'km' collate SJIS || e; |
| >>-- update t001tn2 set e = e || _kanji'km' collate SJISfoo; |
| >>-- control query default NATIONAL_CHARSET 'reset'; |
| >>--#ifMP |
| >> |
| >>------------------------------ |
| >> |
| >>log; |
