📄 valuesclause.out
字号:
ij> ---- this test is for the values clause functionality---- create the tablescreate table t1 (i int, j int);0 rows inserted/updated/deletedij> create table t2 (k int, l int);0 rows inserted/updated/deletedij> -- populate t2insert into t2 values (1, 2);1 row inserted/updated/deletedij> insert into t2 values (3, 4);1 row inserted/updated/deletedij> -- negative testsvalues(null);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> values(1,null);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> values(null,1);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> values(null),(1);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> values(1),(null);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> select x from (values(null,1)) as x(x,y);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> select x from (values(1,null)) as x(x,y);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> select x from (values null) as x(x);ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.ij> -- empty values clausevalues();ERROR 42X01: Syntax error: Encountered ")" at line 2, column 8.ij> -- positive tests-- single valuevalues 1;1 -----------1 ij> values (1);1 -----------1 ij> insert into t1 values (1, null);1 row inserted/updated/deletedij> select * from t1;I |J -----------------------1 |NULL ij> delete from t1;1 row inserted/updated/deletedij> -- multiple valuesvalues (1, 2, 3);1 |2 |3 -----------------------------------1 |2 |3 ij> -- values in derived tableselect * from (values (1, 2, 3)) a;1 |2 |3 -----------------------------------1 |2 |3 ij> select a, b from (values (1, 2, 3)) a (a, b, c);A |B -----------------------1 |2 ij> select * from (values (1, 2, 3)) a, (values (4, 5, 6)) b;1 |2 |3 |4 |5 |6 -----------------------------------------------------------------------1 |2 |3 |4 |5 |6 ij> select * from t2, (values (1, 2, 3)) a;K |L |3 |4 |5 -----------------------------------------------------------1 |2 |1 |2 |3 3 |4 |1 |2 |3 ij> select * from (values (1, 2, 3)) a (a, b, c), t2 where l = b;A |B |C |K |L -----------------------------------------------------------1 |2 |3 |1 |2 ij> -- subquery in values clausevalues (select k from t2 where k = 1);1 -----------1 ij> values (2, (select k from t2 where k = 1));1 |2 -----------------------2 |1 ij> values ((select k from t2 where k = 1), 2);1 |2 -----------------------1 |2 ij> values ((select k from t2 where k = 1), (select l from t2 where l = 4));1 |2 -----------------------1 |4 ij> insert into t1 values ((select k from t2 where k = 1), (select l from t2 where l = 4));1 row inserted/updated/deletedij> select * from t1;I |J -----------------------1 |4 ij> delete from t1;1 row inserted/updated/deletedij> -- values clause in set clauseupdate t2 set k = (values 5) where l = 2;1 row inserted/updated/deletedij> select * from t2;K |L -----------------------5 |2 3 |4 ij> -- k should be set to nullupdate t2 set k = (values (select 2 from t2 where l = 5));2 rows inserted/updated/deletedij> select * from t2;K |L -----------------------NULL |2 NULL |4 ij> -- table constructor tests-- negative tests-- non-matching # of elementsvalues 1, (2, 3), 4;ERROR 42X59: The number of columns in each VALUES constructor must be the same.ij> values (2, 3), (4, 5, 6);ERROR 42X59: The number of columns in each VALUES constructor must be the same.ij> -- empty elementvalues 1, , 2;ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. ij> -- all ? parameters in a column positionprepare v1 as 'values (1, ?, 2), (3, ?, 4), (5, ?, 7)';ERROR 42Y10: A table constructor that is not in an INSERT statement has all ? parameters in one of its columns. For each column, at least one of the rows must have a non-parameter.ij> -- positive testsvalues 1, 2, 3;1 -----------1 2 3 ij> values (1, 2, 3), (4, 5, 6);1 |2 |3 -----------------------------------1 |2 |3 4 |5 |6 ij> prepare v2 as 'values (1, 1, ?), (1e0, ?, ''abc''), (?, 0, ''def'')';ij> execute v2 using 'values (''ghi'', 1, 2)';IJ WARNING: Autocommit may close using result set1 |2 |3 ---------------------------------------1.0 |1 |ghi 1.0 |1 |abc 2.0 |0 |def ij> execute v2 using 'values (cast(null as char(3)), cast(null as smallint), cast(null as float))';IJ WARNING: Autocommit may close using result set1 |2 |3 ---------------------------------------1.0 |1 |NULL1.0 |NULL |abc NULL |0 |def ij> remove v2;ij> -- type precedence tests. tinyint not supported by DB2 Cloudscapevalues (1 = 1.2);ERROR 42X01: Syntax error: Encountered "=" at line 2, column 11.ij> -- this test runs in SPS mode too, hence adding a comment line before the sql, so we get correct column number in error message in both SPS and non-SPS modevalues (1.2 = 1);ERROR 42X01: Syntax error: Encountered "=" at line 2, column 13.ij> -- this test runs in SPS mode too, hence adding a comment line before the sql, so we get correct column number in error message in both SPS and non-SPS modevalues (1 = cast(1 as bigint));ERROR 42X01: Syntax error: Encountered "=" at line 2, column 11.ij> -- this test runs in SPS mode too, hence adding a comment line before the sql, so we get correct column number in error message in both SPS and non-SPS modevalues (1 = cast(1 as smallint));ERROR 42X01: Syntax error: Encountered "=" at line 2, column 11.ij> -- this test runs in SPS mode too, hence adding a comment line before the sql, so we get correct column number in error message in both SPS and non-SPS modevalues (cast(1 as bigint) = 1);ERROR 42X01: Syntax error: Encountered "=" at line 2, column 27.ij> -- this test runs in SPS mode too, hence adding a comment line before the sql, so we get correct column number in error message in both SPS and non-SPS modevalues (cast(1 as smallint) = 1);ERROR 42X01: Syntax error: Encountered "=" at line 2, column 29.ij> -- insertscreate table insert_test1 (c1 int);0 rows inserted/updated/deletedij> create table insert_test2 (i int, s smallint, d double precision, r real, c10 char(10), c30 char(30), vc10 varchar(10), vc30 varchar(30));0 rows inserted/updated/deletedij> insert into insert_test1 values 1, 2, 3;3 rows inserted/updated/deletedij> select * from insert_test1;C1 -----------1 2 3 ij> delete from insert_test1;3 rows inserted/updated/deletedij> insert into insert_test1 values 1, null, 3;3 rows inserted/updated/deletedij> select * from insert_test1;C1 -----------1 NULL 3 ij> delete from insert_test1;3 rows inserted/updated/deletedij> insert into insert_test2 values (1, 1, 1e1, 1e1, '111', '1111111111', '111', '111111111'), (2, 2, 2e2, 2e2, '222', '2222222222', '222', '222222222'), (3, 3, 3e3, 3e3, '333', '3333333333', '333', '333333333');3 rows inserted/updated/deletedij> select * from insert_test2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |10.0 |10.0 |111 |1111111111 |111 |111111111 2 |2 |200.0 |200.0 |222 |2222222222 |222 |222222222 3 |3 |3000.0 |3000.0 |333 |3333333333 |333 |333333333 ij> delete from insert_test2;3 rows inserted/updated/deletedij> insert into insert_test2 values (1, 1, null, null, null, null, null, null), (2, 2, null, null, null, null, null, null), (3, 3, null, null, null, null, null, null);3 rows inserted/updated/deletedij> select * from insert_test2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |3 |NULL |NULL |NULL |NULL |NULL |NULL ij> delete from insert_test2;3 rows inserted/updated/deletedij> insert into insert_test2 values (1, null, null, null, null, null, null, null), (null, 2, null, null, null, null, null, null), (3, null, null, null, null, null, null, null);3 rows inserted/updated/deletedij> select * from insert_test2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> delete from insert_test2;3 rows inserted/updated/deletedij> insert into insert_test2 (r, d) values (1e2, 1e1), (2e2, 2e1), (3e2, 3e1);3 rows inserted/updated/deletedij> select * from insert_test2;I |S |D |R |C10 |C30 |VC10 |VC30 -------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |10.0 |100.0 |NULL |NULL |NULL |NULL NULL |NULL |20.0 |200.0 |NULL |NULL |NULL |NULL NULL |NULL |30.0 |300.0 |NULL |NULL |NULL |NULL ij> delete from insert_test2;3 rows inserted/updated/deletedij> prepare v3 as 'insert into insert_test2 values (1, 1, ?, 1e1, ''111'', ''1111111111'', ''111'', ''111111111''), (2, 2, 2e2, 2e2, ''222'', ?, ''222'', ''222222222''), (3, 3, 3e3, ?, ''333'', ''3333333333'', ''333'', ''333333333'')';ij> execute v3 using 'values (1e1, ''2222222222'', 3e3)';IJ WARNING: Autocommit may close using result set3 rows inserted/updated/deletedij> execute v3 using 'values (cast(null as float), cast(null as char(10)), cast(null as real))';IJ WARNING: Autocommit may close using result set3 rows inserted/updated/deletedij> remove v3;ij> -- insert with a table constructor with all ?s in one columnprepare v4 as 'insert into insert_test2 values (?, null, null, null, null, null, null, null), (?, null, null, null, null, null, null, null),⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -