ij> -- test the dynamic like optimization-- NOTE: the metadata test does a bunch-- of likes with parametersautocommit 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/deletedij> maximumdisplaywidth 2000;ij> -- language layer testscreate table t1(c11 int);0 rows inserted/updated/deletedij> insert into t1 values(1);1 row inserted/updated/deletedij> 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 testsprepare 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 charsexecute ll15 using 'values ''%''';1          -----------ij> -- no match, wrong charselect 1 from t1  where '%foobar' like '%%foobar' escape '%';1          -----------1          ij> -- matchselect 1 from t1  where '_foobar' like '__foobar' escape '_';1          -----------1          ij> -- matchselect 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 escapeprepare ll2 as 'select 1 from t1 where ''%foobar'' like ? escape ?';ij> execute ll2 using 'values (''Z%foobar'', ''Z'') ';1          -----------1          ij> -- matchexecute 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> -- matchprepare ll3 as 'select 1 from t1 where ''Z%foobar'' like ? escape ''Z''';ij> execute ll3 using 'values ''ZZZ%foo%a_'' ';1          -----------1          ij> -- MATCHCREATE FUNCTION GETMAXCHAR() RETURNS CHAR(1) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CharUTF8.getMaxDefinedCharAsString' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> --\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 tablescreate table test(id char(10), c10 char(10), vc10 varchar(10));0 rows inserted/updated/deletedij> insert into test values ('V-NULL', null, null);1 row inserted/updated/deletedij> insert into test values ('asdf', 'asdf', 'asdf');1 row inserted/updated/deletedij> insert into test values ('asdg', 'asdg', 'asdg');1 row inserted/updated/deletedij> insert into test values ('aasdf', 'aasdf', 'aasdf');1 row inserted/updated/deletedij> insert into test values ('%foobar', '%foobar', '%foobar');1 row inserted/updated/deletedij> insert into test values ('foo%bar', 'foo%bar', 'foo%bar');1 row inserted/updated/deletedij> insert into test values ('foo_bar', 'foo_bar', 'foo_bar');1 row inserted/updated/deletedij> insert into test values ('MAX_CHAR', '\uFA2D', '\uFA2D');1 row inserted/updated/deletedij> -- pushing generated predicates downprepare 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 rowsexecute p1 using 'values cast(null as char)';ID        ----------ij> execute p2 using 'values cast(null as varchar(1))';ID        ----------ij> -- falseexecute p1 using 'values 1';ID        ----------ij> execute p2 using 'values 1';ID        ----------ij> -- falseexecute p1 using 'values '''' ';ID        ----------ij> execute p2 using 'values '''' ';ID        ----------ij> -- trueexecute p1 using 'values ''%'' ';