📄 cast.out
字号:
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (t as time) from t1;1 --------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (ts as timestamp) from t1;1 --------------------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (v as varchar(30)) from t1;1 ------------------------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (lvc as long varchar) from t1;1 --------------------------------------------------------------------------------------------------------------------------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> -- clean up t1delete from t1;14 rows inserted/updated/deletedij> -- test casting ? to all builtin typesprepare q1 as 'insert into t1 (bt) values cast(? as char(1) for bit data)';ij> prepare q2 as 'insert into t1 (btv) values cast(? as varchar(1) for bit data)';ij> prepare q4 as 'insert into t1 (c) values cast(? as char(30))';ij> prepare q5 as 'insert into t1 (d) values cast(? as double precision)';ij> prepare q6 as 'insert into t1 (i) values cast(? as int)';ij> prepare q7 as 'insert into t1 (r) values cast(? as real)';ij> prepare q8 as 'insert into t1 (s) values cast(? as smallint)';ij> prepare q10 as 'insert into t1 (num) values cast(? as numeric(18))';ij> prepare q11 as 'insert into t1 (dc) values cast(? as decimal(18))';ij> prepare q12 as 'insert into t1 (dt) values cast(? as date)';ij> prepare q13 as 'insert into t1 (t) values cast(? as time)';ij> prepare q14 as 'insert into t1 (ts) values cast(? as timestamp)';ij> prepare q15 as 'insert into t1 (v) values cast(? as varchar(30))';ij> prepare q16 as 'insert into t1 (lvc) values cast(? as long varchar)';ij> execute q1 using 'values X''aa''';1 row inserted/updated/deletedij> execute q2 using 'values X''aa''';1 row inserted/updated/deletedij> execute q4 using 'values char(123456)';1 row inserted/updated/deletedij> execute q5 using 'values 123456.78e0';1 row inserted/updated/deletedij> execute q6 using 'values 4321';1 row inserted/updated/deletedij> -- bug 5421 - support db2 udb compatible built-in functionsexecute q7 using 'values REAL(4321.01234)';ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. ij> execute q8 using 'values SMALLINT(12321)';1 row inserted/updated/deletedij> execute q10 using 'values 123456.78';1 row inserted/updated/deletedij> execute q11 using 'values 123456.78';1 row inserted/updated/deletedij> execute q12 using 'select dt from temporal_values';1 row inserted/updated/deletedij> execute q13 using 'select t from temporal_values';1 row inserted/updated/deletedij> execute q14 using 'select ts from temporal_values';1 row inserted/updated/deletedij> execute q15 using 'values char(654321)';1 row inserted/updated/deletedij> execute q16 using 'values char(987654)';1 row inserted/updated/deletedij> select * from t1;BT |BTV |C |D |I |R |S |DC |NUM |DT |T |TS |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------aa |NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|aa |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|123456 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |123456.78 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |4321 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |12321 |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |123456 |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |123456 |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |9876-05-04|NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |01:02:34|NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |654321 |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |987654 ij> -- clean up t1delete from t1;13 rows inserted/updated/deletedij> -- more ? tests-- Truncation exception expected in non-parameter cases-- RESOLVE, no truncation expected in parameter cases-- where parameter value is not a string. This is-- currently an "extension".create table x(c1 char(1));0 rows inserted/updated/deletedij> prepare param1 as 'insert into x values cast(? as char(1))';ij> insert into x values cast('12' as char(1));1 row inserted/updated/deletedij> execute param1 using 'values ''34''';1 row inserted/updated/deletedij> select * from x;C1 ----1 3 ij> delete from x;2 rows inserted/updated/deletedij> insert into x values cast(12 as char(1));ERROR 22001: A truncation error was encountered trying to shrink CHAR '12' to length 1.ij> execute param1 using 'values 34';1 row inserted/updated/deletedij> select * from x;C1 ----3 ij> delete from x;1 row inserted/updated/deletedij> insert into x values cast(time('12:12:12') as char(1));ERROR 22001: A truncation error was encountered trying to shrink CHAR '12:12:12' to length 1.ij> execute param1 using 'values time(''21:12:12'')';1 row inserted/updated/deletedij> select * from x;C1 ----2 ij> delete from x;1 row inserted/updated/deletedij> drop table x;0 rows inserted/updated/deletedij> -- method resolution tests-- clean up the prepared statementsremove a1;ij> remove q1;ij> remove q2;ij> remove q4;ij> remove q5;ij> remove q6;ij> remove q7;ij> remove q8;ij> remove q10;ij> remove q11;ij> remove q12;ij> remove q13;ij> remove q14;ij> remove q15;ij> -- reset autocomiitcommit;ij> autocommit on;ij> -- bind time casting tests-- negativevalues cast('asdf' as smallint);ERROR 22018: Invalid character string format for type SMALLINT.ij> values cast('asdf' as int);ERROR 22018: Invalid character string format for type INTEGER.ij> values cast('asdf' as bigint);ERROR 22018: Invalid character string format for type BIGINT.ij> values cast('asdf' as real);ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> values cast('asdf' as double precision);ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values cast('asdf' as decimal(5,4));1 --------ERROR 22018: Invalid character string format for type DECIMAL.ij> values cast('asdf' as date);ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values cast('asdf' as time);ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values cast('asdf' as timestamp);ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values cast('2999999999' as int);ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> values cast(2999999999 as int);ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> values cast('99999' as smallint);ERROR 22003: The resulting value is outside the range for the data type SHORT.ij> values cast(99999 as smallint);ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> values cast(cast(99 as int) as char);ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1.ij> values cast(cast(-9 as int) as char);ERROR 22001: A truncation error was encountered trying to shrink CHAR '-9' to length 1.ij> values cast(cast(99 as smallint) as char);ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1.ij> values cast(cast(99 as bigint) as char);ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1.ij> values cast(cast(9.9 as real) as char);ERROR 42846: Cannot convert types 'REAL' to 'CHAR'.ij> values cast(cast(9.9 as double precision) as char);ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.ij> -- positivevalues cast(1 as int);1 -----------1 ij> values cast(1 as smallint);1 ------1 ij> values cast(1 as bigint);1 --------------------1 ij> values cast(1 as char);1-1ij> values cast('true' as char(4));1 ----trueij> -- drop the tablesdrop table t1;0 rows inserted/updated/deletedij> drop table temporal_values;0 rows inserted/updated/deletedij> drop table strings;0 rows inserted/updated/deletedij> -- ISO time/timestamp formatsvalues (cast ('08.08.08' as TIME));1 --------08:08:08ij> values (cast ('2001-01-01-08.08.08.123456' as TIMESTAMP));1 --------------------------xxxxxxFILTERED-TIMESTAMPxxxxxij> -- char, varcharvalues (char('abcde', 5));1 -----abcdeij> values (char('abcde', 6));1 ------abcde ij> values (char('abcde', 4));1 ----abcdij> values (varchar('', 20));1 --------------------ij> create table t1 (c5 date, c6 time, c7 timestamp, c8 char(5), c9 varchar(5));0 rows inserted/updated/deletedij> insert into t1 values ('2003-09-10', '16:44:02', 'xxxxxxFILTERED-TIMESTAMPxxxxx', 'abc', 'abcde');1 row inserted/updated/deletedij> insert into t1 values ('2005-09-10', '18.44.02', '2004-09-08-12.20.30.123456', 'cba', 'c');1 row inserted/updated/deletedij> select char(c5), char(c6), char(c7), char(c8), char(c9) from t1;1 |2 |3 |4 |5 ------------------------------------------------------------------------------2003-09-10|16:44:02|xxxxxxFILTERED-TIMESTAMPxxxxx|abc |abcde 2005-09-10|18:44:02|xxxxxxFILTERED-TIMESTAMPxxxxx|cba |c ij> select varchar(c5), varchar(c6), varchar(c7), varchar(c8), varchar(c9) from t1;1 |2 |3 |4 |5 ------------------------------------------------------------------------------2003-09-10|16:44:02|xxxxxxFILTERED-TIMESTAMPxxxxx|abc |abcde 2005-09-10|18:44:02|xxxxxxFILTERED-TIMESTAMPxxxxx|cba |c ij> select char(c8, 10), varchar(c9, 9) from t1;1 |2 --------------------abc |abcde cba |c ij> select { fn concat(c8, char(c8)) } from t1;1 --------------------abc abc cba cba ij> select { fn concat(c8, varchar(c9)) } from t1;1 --------------------abc abcde cba c ij> select { fn concat(varchar(c9, 20), char(c8, 8)) } from t1;1 ----------------------------abcdeabc ccba ij> select { fn concat(char(c9, 20), varchar(c8, 8)) } from t1;1 ----------------------------abcde abc c cba ij> -- clean updrop table t1;0 rows inserted/updated/deletedij> -- bug 5421 - support db2 udb compatible built-in functionsvalues CHAR(INT(67890));ERROR 42X01: Syntax error: Encountered "INT" at line 2, column 13.ij> values CHAR(INTEGER(12345));1 -----------12345 ij> values CHAR(DEC(67.21,4,2));ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 13.ij> values CHAR(DECIMAL(67.10,4,2));ERROR 42X01: Syntax error: Encountered "DECIMAL" at line 1, column 13.ij> values CHAR(DOUBLE(5.55));1 -----------------------------------------------------5.55 ij> values CHAR(DOUBLE_PRECISION(5.555));ERROR 42Y03: 'DOUBLE_PRECISION' is not recognized as a function or procedure.ij> values CHAR(BIGINT(1));1 --------------------1 ij> values CHAR(BIGINT(-1));1 ---------------------1 ij> values LENGTH(CAST('hello' AS CHAR(25)));1 -----------25 ij> values LENGTH(CAST('hello' AS VARCHAR(25)));1 -----------5 ij> values LENGTH(CAST('hello' AS LONG VARCHAR));1 -----------5 ij> values CAST (X'03' as CHAR(5) for bit data);1 ----------0320202020ij> values CAST (X'04' as VARCHAR(5) for bit data);1 ----------04 ij> values CAST (X'05' as LONG VARCHAR for bit data);1 --------------------------------------------------------------------------------------------------------------------------------05 ij> -- clean updrop table t1;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.ij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -