Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <25758881.1180737675630.JavaMail.jira@brutus>
Date: Fri, 1 Jun 2007 15:41:15 -0700 (PDT)
From: "A B (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-2740) LIKE parameter marker combined with
 index multi-probing leads to ASSERT failure with sane jars, wrong results
 with insane jars.
In-Reply-To: <19354393.1180653435848.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-2740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

A B updated DERBY-2740:
-----------------------

    Attachment: d2740_v1.stat
                d2740_v1.patch

Attaching d2740_v1.patch, which is a patch for this issue.

The optimizer can in fact choose to do multiprobing in situations where the start key and stop key are different.  This can happen if the index that the optimizer chooses has more than one column and there are start/stop predicates on one or more of the "other" (meaning non-probe) columns.  In such a situation we simply need to set the start/stop key appropriately when scanning.  That's what this patch does (more details in the code comments).  The patch also adds new test cases to lang/InListMultiProbeTest.java.

I ran derbyall and suites.All on SUSE Linux with ibm142 and the only failures were metadata failures that are not related (and have since been corrected in trunk).

If there are no objections, I'll plan to commit this patch on Monday.

> 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.0.0
>            Reporter: A B
>            Assignee: A B
>             Fix For: 10.3.0.0
>
>         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.