| ij> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| |
| -- |
| -- this test is for the values clause functionality |
| -- |
| |
| -- create the tables |
| create table t1 (i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (k int, l int); |
| 0 rows inserted/updated/deleted |
| ij> -- populate t2 |
| insert into t2 values (1, 2); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (3, 4); |
| 1 row inserted/updated/deleted |
| ij> -- negative tests |
| values(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 clause |
| values(); |
| ERROR 42X01: Syntax error: Encountered ")" at line 2, column 8. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- positive tests |
| |
| -- single value |
| values 1; |
| 1 |
| ----------- |
| 1 |
| ij> values (1); |
| 1 |
| ----------- |
| 1 |
| ij> insert into t1 values (1, null); |
| 1 row inserted/updated/deleted |
| ij> select * from t1; |
| I |J |
| ----------------------- |
| 1 |NULL |
| ij> delete from t1; |
| 1 row inserted/updated/deleted |
| ij> -- multiple values |
| values (1, 2, 3); |
| 1 |2 |3 |
| ----------------------------------- |
| 1 |2 |3 |
| ij> -- values in derived table |
| select * 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 clause |
| values (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/deleted |
| ij> select * from t1; |
| I |J |
| ----------------------- |
| 1 |4 |
| ij> delete from t1; |
| 1 row inserted/updated/deleted |
| ij> -- values clause in set clause |
| update t2 set k = (values 5) where l = 2; |
| 1 row inserted/updated/deleted |
| ij> select * from t2; |
| K |L |
| ----------------------- |
| 5 |2 |
| 3 |4 |
| ij> -- k should be set to null |
| update t2 set k = (values (select 2 from t2 where l = 5)); |
| 2 rows inserted/updated/deleted |
| ij> select * from t2; |
| K |L |
| ----------------------- |
| NULL |2 |
| NULL |4 |
| ij> -- table constructor tests |
| |
| -- negative tests |
| |
| -- non-matching # of elements |
| values 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 element |
| values 1, , 2; |
| ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. |
| ij> -- all ? parameters in a column position |
| prepare 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 tests |
| |
| values 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 set |
| 1 |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 set |
| 1 |2 |3 |
| ----------------------------------------- |
| 1.0 |1 |NULL |
| 1.0 |NULL |abc |
| NULL |0 |def |
| ij> remove v2; |
| ij> -- type precedence tests. tinyint not supported by DB2 Cloudscape |
| values (1 = 1.2); |
| 1 |
| ----- |
| false |
| 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 mode |
| values (1.2 = 1); |
| 1 |
| ----- |
| false |
| 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 mode |
| values (1 = cast(1 as bigint)); |
| 1 |
| ----- |
| true |
| 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 mode |
| values (1 = cast(1 as smallint)); |
| 1 |
| ----- |
| true |
| 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 mode |
| values (cast(1 as bigint) = 1); |
| 1 |
| ----- |
| true |
| 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 mode |
| values (cast(1 as smallint) = 1); |
| 1 |
| ----- |
| true |
| ij> -- inserts |
| create table insert_test1 (c1 int); |
| 0 rows inserted/updated/deleted |
| ij> 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/deleted |
| ij> insert into insert_test1 values 1, 2, 3; |
| 3 rows inserted/updated/deleted |
| ij> select * from insert_test1; |
| C1 |
| ----------- |
| 1 |
| 2 |
| 3 |
| ij> delete from insert_test1; |
| 3 rows inserted/updated/deleted |
| ij> insert into insert_test1 values 1, null, 3; |
| 3 rows inserted/updated/deleted |
| ij> select * from insert_test1; |
| C1 |
| ----------- |
| 1 |
| NULL |
| 3 |
| ij> delete from insert_test1; |
| 3 rows inserted/updated/deleted |
| ij> 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/deleted |
| ij> 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/deleted |
| ij> 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/deleted |
| ij> 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/deleted |
| ij> 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/deleted |
| ij> 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/deleted |
| ij> insert into insert_test2 (r, d) values (1e2, 1e1), |
| (2e2, 2e1), |
| (3e2, 3e1); |
| 3 rows inserted/updated/deleted |
| ij> 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/deleted |
| ij> 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 set |
| 3 rows inserted/updated/deleted |
| ij> execute v3 using 'values (cast(null as float), cast(null as char(10)), cast(null as real))'; |
| IJ WARNING: Autocommit may close using result set |
| 3 rows inserted/updated/deleted |
| ij> remove v3; |
| ij> -- insert with a table constructor with all ?s in one column |
| prepare v4 as 'insert into insert_test2 values (?, null, null, null, null, null, null, null), |
| (?, null, null, null, null, null, null, null), |
| (?, null, null, null, null, null, null, null)'; |
| ij> execute v4 using 'values (10, 20, 30)'; |
| IJ WARNING: Autocommit may close using result set |
| 3 rows inserted/updated/deleted |
| ij> 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 |
| 1 |1 |NULL |10.0 |111 |1111111111 |111 |111111111 |
| 2 |2 |200.0 |200.0 |222 |NULL |222 |222222222 |
| 3 |3 |3000.0 |NULL |333 |3333333333 |333 |333333333 |
| 10 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 20 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 30 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> remove v4; |
| ij> delete from insert_test2; |
| 9 rows inserted/updated/deleted |
| ij> -- negative test - all ?s in one column |
| prepare v3 as 'values (1, ?, ?, 1e1, ''111'', ''1111111111'', ''111'', ''111111111''), |
| (2, ?, 2e2, 2e2, ''222'', ?, ''222'', ''222222222''), |
| (3, ?, 3e3, ?, ''333'', ''3333333333'', ''333'', ''333333333'')'; |
| 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> -- values clause with a subquery in a derived table (bug 2335) |
| create table x(x int); |
| 0 rows inserted/updated/deleted |
| ij> insert into x values 1, 2, 3, 4; |
| 4 rows inserted/updated/deleted |
| ij> select * from (values (1, (select max(x) from x), 1)) c; |
| 1 |2 |3 |
| ----------------------------------- |
| 1 |4 |1 |
| ij> select * from x, (values (1, (select max(x) from x), 1)) c(a, b, c) where x = c; |
| X |A |B |C |
| ----------------------------------------------- |
| 1 |1 |4 |1 |
| ij> drop table x; |
| 0 rows inserted/updated/deleted |
| ij> -- drop the tables |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test1; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test2; |
| 0 rows inserted/updated/deleted |
| ij> --- supporting <TABLE> in table expression. |
| create table target (a int, b int); |
| 0 rows inserted/updated/deleted |
| ij> create index idx on target(b); |
| 0 rows inserted/updated/deleted |
| ij> insert into target values (1, 2), (2, 3), (0, 2); |
| 3 rows inserted/updated/deleted |
| ij> create table sub (a int, b int); |
| 0 rows inserted/updated/deleted |
| ij> insert into sub values (1, 2), (2, 3), (2, 4); |
| 3 rows inserted/updated/deleted |
| ij> select * |
| from (select b from sub) as q(b); |
| B |
| ----------- |
| 2 |
| 3 |
| 4 |
| ij> select * |
| from table (select b from sub) as q(b); |
| B |
| ----------- |
| 2 |
| 3 |
| 4 |
| ij> select * |
| from table (select * from table (select b from sub) as q(b)) as p(a); |
| A |
| ----------- |
| 2 |
| 3 |
| 4 |
| ij> select * |
| from table (select b from sub) as q(b), target; |
| B |A |B |
| ----------------------------------- |
| 2 |1 |2 |
| 2 |2 |3 |
| 2 |0 |2 |
| 3 |1 |2 |
| 3 |2 |3 |
| 3 |0 |2 |
| 4 |1 |2 |
| 4 |2 |3 |
| 4 |0 |2 |
| ij> select * |
| from table (select b from sub) as q(b), target where q.b = target.b; |
| B |A |B |
| ----------------------------------- |
| 2 |1 |2 |
| 2 |0 |2 |
| 3 |2 |3 |
| ij> select * |
| from target, table (select b from sub) as q(b); |
| A |B |B |
| ----------------------------------- |
| 1 |2 |2 |
| 1 |2 |3 |
| 1 |2 |4 |
| 2 |3 |2 |
| 2 |3 |3 |
| 2 |3 |4 |
| 0 |2 |2 |
| 0 |2 |3 |
| 0 |2 |4 |
| ij> select * |
| from (values (1)) as q(a); |
| A |
| ----------- |
| 1 |
| ij> select * |
| from table (values (1)) as q(a), table (values ('a'), ('b'), ('c')) as p(a); |
| A |A |
| ------------- |
| 1 |a |
| 1 |b |
| 1 |c |
| ij> -- should fail because <TABLE> can appear in front of derived table |
| select * |
| from table target; |
| ERROR 42X01: Syntax error: Encountered "target" at line 3, column 13. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * |
| from table (target); |
| ERROR 42X01: Syntax error: Encountered ")" at line 2, column 20. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select * |
| from table (target as q); |
| ERROR 42X01: Syntax error: Encountered "as" at line 2, column 21. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> drop table sub; |
| 0 rows inserted/updated/deleted |
| ij> drop table target; |
| 0 rows inserted/updated/deleted |
| ij> create table t1 (c1 int, c11 boolean); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1( c1, c11 ) values ( 1, true ); |
| 1 row inserted/updated/deleted |
| ij> select nullif(c1, 1) is null from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values 1 is null; |
| 1 |
| ----- |
| false |
| ij> values 1 = 1; |
| 1 |
| ----- |
| true |
| 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 mode |
| select 1 = 1 from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values (nullif('abc','a') = 'abc'); |
| 1 |
| ----- |
| true |
| 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 mode |
| select (nullif('abc','a') = 'abc') from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| select c11 = any (select c11 from t1) from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values 2 > 1; |
| 1 |
| ----- |
| true |
| 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 mode |
| select 2 > 1 from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values 2 >= 1; |
| 1 |
| ----- |
| true |
| 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 mode |
| select 2 >= 1 from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values 1 < 2; |
| 1 |
| ----- |
| true |
| 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 mode |
| select 1 < 2 from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values 1 <= 2; |
| 1 |
| ----- |
| true |
| 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 mode |
| select 1 <= 2 from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values (1>1); |
| 1 |
| ----- |
| false |
| 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 mode |
| select (c1 < 2) from t1; |
| 1 |
| ----- |
| true |
| 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 mode |
| values (1 between 2 and 5); |
| 1 |
| ----- |
| false |
| 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 mode |
| select (c1 between 1 and 3) from t1; |
| 1 |
| ----- |
| true |
| ij> prepare ll1 as 'values ''asdf'' like ?'; |
| ij> prepare ll1 as 'select ''asdf'' like ? from t1'; |
| ij> prepare ll15 as 'values ''%foobar'' like ''Z%foobar'' escape ?'; |
| ij> prepare ll15 as 'select ''%foobar'' like ''Z%foobar'' escape ? from t1'; |
| 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 mode |
| values '%foobar' like '%%foobar' escape '%'; |
| 1 |
| ----- |
| true |
| 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 mode |
| select '_foobar' like '__foobar' escape '_' from t1; |
| 1 |
| ----- |
| true |
| ij> prepare ll4 as 'values org.apache.derbyTesting.functionTests.tests.lang.CharUTF8::getMaxDefinedCharAsString() like ?'; |
| ERROR 42X51: The class 'org.apache.derbyTesting.functionTests.tests.lang.CharUTF8' does not exist or is inaccessible. This can happen if the class is not public. |
| ERROR XJ001: Java exception: 'org.apache.derbyTesting.functionTests.tests.lang.CharUTF8: java.lang.ClassNotFoundException'. |
| ij> -- boolean expression INSTANCEOF disallowed in values and select clause |
| values 1 instanceof int; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 2, column 10. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| values 1 instanceof java.lang.Integer between false and true; |
| ERROR 42X01: Syntax error: Encountered "instanceof" at line 2, column 10. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> select exists (values 1) from t1; |
| 1 |
| ----- |
| true |
| ij> values exists (values 2); |
| 1 |
| ----- |
| true |
| ij> update t1 set c11 = exists(values 1); |
| 1 row inserted/updated/deleted |
| ij> -- ?: not supported anymore |
| values not true ? false : true; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select not true ? false : true from t1; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| values 1 ? 2 : 3; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 10. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select c1 is null ? true : false from t1; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 19. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select new java.lang.Integer(c1 is null ? 0 : c1) from t1; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 41. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select c1, (c1=1? cast(null as int) : c1) is null from t1; |
| ERROR 42X01: Syntax error: Encountered "?" at line 2, column 17. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- try few tests in cloudscape mode for boolean expressions in values or select clause |
| -- 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 mode |
| values new java.lang.String() = ''; |
| ERROR 42X01: Syntax error: java.lang.String. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| values new java.lang.String('asdf') = 'asdf'; |
| ERROR 42X01: Syntax error: java.lang.String. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select new java.lang.String() = '' from t1; |
| ERROR 42X01: Syntax error: java.lang.String. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| 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 mode |
| select new java.lang.String('asdf') = 'asdf' from t1; |
| ERROR 42X01: Syntax error: java.lang.String. |
| Issue the 'help' command for general information on IJ command syntax. |
| Any unrecognized commands are treated as potential SQL commands and executed directly. |
| Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. |
| ij> -- DERBY-1587 |
| VALUES INTEGER(1.5); |
| 1 |
| ----------- |
| 1 |
| ij> VALUES INT(1.5); |
| 1 |
| ----------- |
| 1 |
| ij> -- DERBY-2218 |
| drop table t3; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T3' because it does not exist. |
| ij> create table t3 (i int); |
| 0 rows inserted/updated/deleted |
| ij> -- expect errors |
| -- where clause |
| select * from t3 where (values null); |
| ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. |
| ij> -- order by clause |
| select * from t3 order by (values null); |
| ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. |
| ij> -- result column |
| select (values null) from t3; |
| ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. |
| ij> -- group by clause |
| select * from t3 group by (values null); |
| ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. |
| ij> -- having clause |
| select * from t3 group by i having (values null); |
| ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement. |
| ij> drop table t3; |
| 0 rows inserted/updated/deleted |
| ij> |
