Bug #37208 Wrong query result for big innodb table when index condition pushdown is used
Submitted: 4 Jun 2008 22:19 Modified: 18 Dec 2009 12:18
Reporter: Sergey Petrunya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Assigned Account
Tags: index_condition_pushdown, optimizer_switch
Triage: Triaged: D2 (Serious)

[4 Jun 2008 22:19] Sergey Petrunya
Description:
Running this query on DBT-3 scale=1 loaded in innodb may produces wrong query result:

select count(*) from part, lineitem, partsupp
     where l_partkey=p_partkey and p_retailprice>1525 and l_discount>0.04 and
           ps_partkey=p_partkey and ps_supplycost>520
;

The problem occurs only when the query uses index condition pushdown.

How to repeat:
See above.
[4 Jun 2008 22:21] Sergey Petrunya
Here's a patch that fixes the problem. The testcase part is difficult because in order to repeat one needs to scan a certain number of records and InnoDB's record prefetch buffer must hold certain data.

===== row0sel.c 1.127 vs edited =====
--- 1.127/storage/innobase/row/row0sel.c        2008-03-27 02:56:11 +01:00
+++ edited/row0sel.c    2008-06-01 04:43:15 +02:00
@@ -3112,8 +3112,12 @@ row_sel_push_cache_row_for_mysql(
 
             if (templ->mysql_null_bit_mask) {
               offs= templ->mysql_null_byte_offset;
-              *(prebuilt->fetch_cache[prebuilt->n_fetch_cached] + offs) ^= 
-                (*(remainder_buf + offs) & templ->mysql_null_bit_mask);
+              if (*(remainder_buf + offs) & templ->mysql_null_bit_mask)
+                *(prebuilt->fetch_cache[prebuilt->n_fetch_cached] + offs) |= 
+                /*  (*(remainder_buf + offs) &*/( templ->mysql_null_bit_mask);
+              else
+                *(prebuilt->fetch_cache[prebuilt->n_fetch_cached] + offs) &= 
+                  ~templ->mysql_null_bit_mask;
             }
             offs= templ->mysql_col_offset;
             memcpy(prebuilt->fetch_cache[prebuilt->n_fetch_cached] + offs,
[8 Oct 2009 11:58] Guilhem Bichot
trusting Sergey's assessment, I remove tag engine_condition_pushdown as bug is said to be in index condition pushdown.
[1 Dec 2009 10:44] Sergey Petrunya
The testcase that was missing in this bug can be found in BUG#43249.
[2 Dec 2009 9:46] Manyi Lu
Contribution from sca@askmonty.org 11/23/09:
http://lists.mysql.com/internals/37545
[18 Dec 2009 12:18] Jørgen Løland
Trusting Sergey's assessment that the this bug is the same as the testcase of BUG#43249, this bug is a duplicate of BUG#42580.