dynamiclikeoptimization.out

来自「derby database source code.good for you.」· OUT 代码 · 共 565 行 · 第 1/3 页

ij> -- test the dynamic like optimization
-- NOTE: the metadata test does a bunch
-- of likes with parameters
autocommit off;
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
-- NoHoldForConnection;
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 2000;
ij> -- language layer tests
create table t1(c11 int);
0 rows inserted/updated/deleted
ij> insert into t1 values(1);
1 row inserted/updated/deleted
ij> prepare ll1 as 'select 1 from t1 where ''asdf'' like ?';
ij> execute ll1 using 'values '''' ';
1          
-----------
ij> -- no match char(1) pads to ' '
execute ll1 using 'values ''%'' ';
1          
-----------
1          
ij> execute ll1 using 'values ''%f'' ';
1          
-----------
1          
ij> execute ll1 using 'values cast(''%f'' as varchar(2)) ';
1          
-----------
1          
ij> execute ll1 using 'values ''%g'' ';
1          
-----------
ij> execute ll1 using 'values ''asd%'' ';
1          
-----------
1          
ij> execute ll1 using 'values ''_%'' ';
1          
-----------
1          
ij> execute ll1 using 'values ''%_'' ';
1          
-----------
1          
ij> execute ll1 using 'values ''_asdf'' ';
1          
-----------
ij> execute ll1 using 'values ''%asdf'' ';
1          
-----------
1          
ij> execute ll1 using 'values cast(null as char)';
1          
-----------
ij> execute ll1 using 'values '''' ';
1          
-----------
ij> -- Escape tests
prepare ll15 as 'select 1 from t1 where ''%foobar'' like ''Z%foobar'' escape ?';
ij> execute ll15 using 'values ''Z''';
1          
-----------
1          
ij> -- match: optimize to LIKE AND ==
execute ll15 using 'values cast(''Z'' as varchar(1)) ';
1          
-----------
1          
ij> execute ll15 using 'values ''raZ''';
ERROR 22019: Invalid escape sequence, 'raZ'. The escape string must be exactly one character. It cannot be a null or more than one character.
ij> -- too many like chars
execute ll15 using 'values ''%''';
1          
-----------
ij> -- no match, wrong char
select 1 from t1  where '%foobar' like '%%foobar' escape '%';
1          
-----------
1          
ij> -- match
select 1 from t1  where '_foobar' like '__foobar' escape '_';
1          
-----------
1          
ij> -- match
select 1 from t1  where 'asdf' like 'a%' escape cast(null as char);
ERROR 22501: An ESCAPE clause of NULL returns undefined results and is not allowed.
ij> -- error NULL escape
prepare ll2 as 'select 1 from t1 where ''%foobar'' like ? escape ?';
ij> execute ll2 using 'values (''Z%foobar'', ''Z'') ';
1          
-----------
1          
ij> -- match
execute ll2 using 'values (''Z%foobar'', '''') ';
ERROR 22019: Invalid escape sequence, ''. The escape string must be exactly one character. It cannot be a null or more than one character.
ij> -- error empty string escape 
prepare ll2 as 'select 1 from t1 where ''%foobar'' like ? escape ''Z''';
ij> execute ll2 using 'values ''x%foobar'' ';
1          
-----------
ij> -- no match 
execute ll2 using 'values ''Z%foobar'' ';
1          
-----------
1          
ij> -- match 
prepare ll2 as 'select 1 from t1 where ''%foobar'' like ? escape ''$''';
ij> execute ll2 using 'values ''$%f%bar'' ';
1          
-----------
1          
ij> -- match
prepare ll3 as 'select 1 from t1 where ''Z%foobar'' like ? escape ''Z''';
ij> execute ll3 using 'values ''ZZZ%foo%a_'' ';
1          
-----------
1          
ij> -- MATCH
CREATE FUNCTION GETMAXCHAR() RETURNS CHAR(1) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CharUTF8.getMaxDefinedCharAsString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> --\uFA2D - the highest valid character according to Character.isDefined() of JDK 1.4;
--prepare ll4 as 'select 1 from t1 where ''\uFA2D'' like ?';
prepare ll4 as 'select 1 from t1 where GETMAXCHAR() like ?';
ij> execute ll4 using 'values ''%'' ';
1          
-----------
1          
ij> execute ll4 using 'values '''' ';
1          
-----------
ij> execute ll4 using 'values ''_'' ';
1          
-----------
1          
ij> execute ll4 using 'values GETMAXCHAR() ';
1          
-----------
1          
ij> -- create and populate tables
create table test(id char(10), c10 char(10), vc10 varchar(10));
0 rows inserted/updated/deleted
ij> insert into test values ('V-NULL', null, null);
1 row inserted/updated/deleted
ij> insert into test values ('asdf', 'asdf', 'asdf');
1 row inserted/updated/deleted
ij> insert into test values ('asdg', 'asdg', 'asdg');
1 row inserted/updated/deleted
ij> insert into test values ('aasdf', 'aasdf', 'aasdf');
1 row inserted/updated/deleted
ij> insert into test values ('%foobar', '%foobar', '%foobar');
1 row inserted/updated/deleted
ij> insert into test values ('foo%bar', 'foo%bar', 'foo%bar');
1 row inserted/updated/deleted
ij> insert into test values ('foo_bar', 'foo_bar', 'foo_bar');
1 row inserted/updated/deleted
ij> insert into test values ('MAX_CHAR', '\uFA2D', '\uFA2D');
1 row inserted/updated/deleted
ij> -- pushing generated predicates down
prepare p1 as 'select id from test where c10 like ?';
ij> prepare p2 as 'select id from test where vc10 like ?';
ij> select vc10 from test where vc10 like 'values cast(null as varchar(1))';
VC10      
----------
ij> -- return 0 rows
execute p1 using 'values cast(null as char)';
ID        
----------
ij> execute p2 using 'values cast(null as varchar(1))';
ID        
----------
ij> -- false
execute p1 using 'values 1';
ID        
----------
ij> execute p2 using 'values 1';
ID        
----------
ij> -- false
execute p1 using 'values '''' ';
ID        
----------
ij> execute p2 using 'values '''' ';
ID        
----------
ij> -- true
execute p1 using 'values ''%'' ';