📄 inbetween.out
字号:
ij> ---- Test IN lists and BETWEEN on the builtin types-- assumes these builtin types exist:-- int, smallint, char, varchar, real---- other things we might test:-- compatibility with dynamic parameters set isolation to rr;0 rows inserted/updated/deletedij> CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128)EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker'LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> autocommit off;ij> -- Create the tablescreate table s (i int);0 rows inserted/updated/deletedij> create table t (i int, s smallint, c char(10), v varchar(50), d double precision, r real, e date, t time, p timestamp);0 rows inserted/updated/deletedij> create table test (i int, d double precision);0 rows inserted/updated/deletedij> -- Populate the tablesinsert into s values (1);1 row inserted/updated/deletedij> insert into s values (1);1 row inserted/updated/deletedij> insert into s values (2);1 row inserted/updated/deletedij> insert into t values (null, null, null, null, null, null, null, null, null);1 row inserted/updated/deletedij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 300.0e0, '1992-01-01', '12:30:30', 'xxxxxxFILTERED-TIMESTAMPxxxxx);1 row inserted/updated/deletedij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -300.0e0, '1992-01-02', '12:30:59', 'xxxxxxFILTERED-TIMESTAMPxxxxx);1 row inserted/updated/deletedij> insert into test values (2, 4.0);1 row inserted/updated/deletedij> insert into test values (3, 10.0);1 row inserted/updated/deletedij> insert into test values (4, 12.0);1 row inserted/updated/deletedij> insert into test values (5, 25.0);1 row inserted/updated/deletedij> insert into test values (10, 100.0);1 row inserted/updated/deletedij> insert into test values (-6, 36);1 row inserted/updated/deletedij> -- BETWEEN-- negative tests-- type mismatchesselect * from t where i between i and e;ERROR 42818: Comparisons between 'INTEGER' and 'DATE' are not supported.ij> select * from t where i between i and t;ERROR 42818: Comparisons between 'INTEGER' and 'TIME' are not supported.ij> select * from t where i between i and p;ERROR 42818: Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported.ij> select * from t where e between p and p;ERROR 42818: Comparisons between 'DATE' and 'TIMESTAMP' are not supported.ij> select * from t where 1 between e and p;ERROR 42818: Comparisons between 'INTEGER' and 'DATE' are not supported.ij> -- between null andselect * from t where i between null and i;ERROR 42X01: Syntax error: Encountered "null" at line 2, column 33.ij> -- cardinality violation on a subqueryselect * from t where i between i and (select i from s);ERROR 21000: Scalar subquery is only allowed to return a single row.ij> -- all parametersselect * from t where ? between ? and ?;ERROR 42X35: It is not allowed for both operands of 'BETWEEN' to be ? parameters.ij> -- positive tests-- type comparisonsselect i from t where i between s and r;I -----------ij> select i from t where i between r and d;I -----------ij> select i from t where s between i and r;I -----------0 ij> select i from t where s between r and d;I -----------ij> select i from t where r between s and i;I -----------ij> select i from t where d between s and i;I -----------ij> select i from t where i between 40e1 and 50e1;I -----------ij> select i from t where s between 40e1 and 50e1;I -----------ij> select i from t where c between c and v;I -----------ij> select i from t where 40e1 between i and s;I -----------ij> select i from t where 'goodbye' between c and c;I ------------1 ij> select i from t where 'xxxxxxFILTERED-TIMESTAMPxxxxxbetween p and p;I ------------1 ij> -- between 2 and 1select * from t where i between 2 and 1;I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> select * from t where not i not between 2 and 1;I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------ij> select * from t where not i between 2 and 1;I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |100 |hello |everyone is here |200.0 |300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx-1 |-100 |goodbye |everyone is there |-200.0 |-300.0 |1992-01-02|12:30:59|xxxxxxFILTERED-TIMESTAMPxxxxxij> select * from t where i not between 2 and 1;I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |100 |hello |everyone is here |200.0 |300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx-1 |-100 |goodbye |everyone is there |-200.0 |-300.0 |1992-01-02|12:30:59|xxxxxxFILTERED-TIMESTAMPxxxxxij> -- between arbitrary expressionsselect * from test where sqrt(d) between 5 and 10;I |D ----------------------------------5 |25.0 10 |100.0 -6 |36.0 ij> select * from test where (i+d) between 20 and 50;I |D ----------------------------------5 |25.0 -6 |36.0 ij> select * from test where {fn abs (i)} between 5 and 8;I |D ----------------------------------5 |25.0 -6 |36.0 ij> select * from test where (i+d) not between 20 and 50;I |D ----------------------------------2 |4.0 3 |10.0 4 |12.0 10 |100.0 ij> select * from test where sqrt(d) not between 5 and 20;I |D ----------------------------------2 |4.0 3 |10.0 4 |12.0 ij> -- not (test clone() once its implemented)select i from t where i not between i and i;I -----------ij> select i from t where s not between s and s;I -----------ij> select i from t where c not between c and c;I -----------ij> select i from t where v not between v and v;I -----------ij> select i from t where d not between d and d;I -----------ij> select i from t where r not between r and r;I -----------ij> select i from t where e not between e and e;I -----------ij> select i from t where t not between t and t;I -----------ij> select i from t where p not between p and p;I -----------ij> -- between complex expressionsselect i from t where s between (select i from s where i = 2) and (select 100 from s where i = 2);I -----------0 ij> select * from t where i between i and (select max(i) from s);I |S |C |V |D |R |E |T |P --------------------------------------------------------------------------------------------------------------------------------------------------------------------0 |100 |hello |everyone is here |200.0 |300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx-1 |-100 |goodbye |everyone is there |-200.0 |-300.0 |1992-01-02|12:30:59|xxxxxxFILTERED-TIMESTAMPxxxxxij> -- subquery betweenselect i from t where (select i from s where i = 2) between 1 and 2;I -----------NULL 0 -1 ij> -- parametersprepare q1 as 'select i from t where ? between 2 and 3';ij> prepare q2 as 'select i from t where ? between ? and 3';ij> prepare q3 as 'select i from t where ? between 2 and ?';ij> prepare q4 as 'select i from t where i between ? and ?';ij> execute q1 using 'values 2';I -----------NULL 0 -1 ij> execute q2 using 'values (2, 2)';I -----------NULL 0 -1 ij> execute q3 using 'values (2, 3)';I -----------NULL 0 -1 ij> execute q4 using 'values (0, 1)';I -----------0 ij> remove q1;ij> remove q2;ij> remove q3;ij> remove q4;ij> update s set i = 5 where i between 2 and 3;1 row inserted/updated/deletedij> select * from s;I -----------1 1 5 ij> -- delete - where clausedelete from s where i between 3 and 5;1 row inserted/updated/deletedij> select * from s;I -----------1 1 ij> -- check consistency of scans, etc.values ConsistencyChecker();1 --------------------------------------------------------------------------------------------------------------------------------No open scans, etc.2 dependencies found ij> drop table s;0 rows inserted/updated/deletedij> -- IN List-- recreate s as sscreate table ss (i int);0 rows inserted/updated/deletedij> insert into ss values (1);1 row inserted/updated/deletedij> insert into ss values (1);1 row inserted/updated/deletedij> insert into ss values (2);1 row inserted/updated/deletedij> -- negative tests-- empty listselect i from t where i in ();ERROR 42X01: Syntax error: Encountered ")" at line 4, column 29.ij> -- null in listselect i from t where i in (null);ERROR 42X01: Syntax error: Encountered "null" at line 2, column 29.ij> -- cardinality violation from subqueryselect i from t where i in (1, 3, 5, 6, (select i from ss));ERROR 21000: Scalar subquery is only allowed to return a single row.⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -