[
https://issues.apache.org/jira/browse/DERBY-2740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B closed DERBY-2740.
----------------------
> LIKE parameter marker combined with index multi-probing leads to ASSERT
> failure with sane jars, wrong results with insane jars.
> -------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2740
> URL: https://issues.apache.org/jira/browse/DERBY-2740
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4
> Reporter: A B
> Assignee: A B
> Fix For: 10.3.1.4
>
> Attachments: d2740_v1.patch, d2740_v1.stat
>
>
> In cases where the optimizer chooses to do index multi-probing for an IN list
> (DERBY-47), the presence of a LIKE condition whose argument is a parameter
> marker leads to an ASSERTion failure with sane jars. With insane jars, the
> same query returns incorrect results (it only returns rows matching the first
> argument in the IN list).
> As an example, if we have the following DDL:
> create table ct (i int, c1 char(25), c2 char(40));
> insert into ct(i) values 1, 2, 3, 4, 5, 6, 7, 8, 9;
> insert into ct(i) values 0, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19;
> insert into ct(i) select 7 * i from ct;
> insert into ct(i) select 13 * i from ct;
> update ct set c1 = cast(i as char(25));
> update ct set c2 = c1 || c1;
> create index idx2 on ct (c1, c2);
> Then we see the following results:
> -- This one works.
> select i,c1,c2 from ct where c1 in ('1','2') and c2 like '%' order by i;
> -- These all work, too.
> prepare p1 as 'select i,c1,c2 from ct where c1 in (''2'',''1'') and c2 like
> ''%''';
> execute p1;
> prepare p1 as 'select i,c1,c2 from ct where c1 in (?,?) and c2 like ''%''';
> execute p1 using 'values (''1'', ''2'')';
> -- But these all fail. Something about having a parameter in the LIKE is
> triggering the assertion...
> prepare p1 as 'select i,c1,c2 from ct where c1 in (?,?) and c2 like ? order
> by i';
> execute p1 using 'values (''1'', ''2'', ''%'')';
> prepare p1 as 'select i,c1,c2 from ct where c1 in (?,?) and c2 like ?';
> execute p1 using 'values (''1'', ''2'', ''%'')';
> prepare p1 as 'select i,c1,c2 from ct where c1 in (''2'',''1'') and c2 like
> ?';
> execute p1 using 'values (''%'')';
> prepare p1 as 'select i,c1,c2 from ct where c1 in (''2'',?) and c2 like ?';
> execute p1 using 'values (''1'', ''%'')';
> The actual assertion is thrown because multiprobing expects the start and
> stop keys to be same value. But something about the LIKE parameter violates
> that expectation:
> ERROR XJ001: Java exception: 'ASSERT FAILED All multi-probing result sets are
> expected to have a single key that is both the start key AND the stop key,
> but that is not the case.:
> org.apache.derby.shared.common.sanity.AssertFailure'.
> This is a regression introduced by the DERBY-47 changes; everything runs fine
> in 10.2 and earlier.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
