Bug #43737 Select query return bad result
Submitted: 18 Mar 2009 21:16 Modified: 15 May 2009 2:09
Reporter: Federico Quercia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.1.32, 5.0, 5.1 bzr OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: INDEX
Triage: Triaged: D2 (Serious)

[18 Mar 2009 21:16] Federico Quercia
Description:
With the referenced query, described below, we get an unexpected result based on the populated database.

We found that the problem is related with index "idx_transactions2".

We get a workarround, removing this index. 

Was tested in version 5.0.18, 5.0.45 and 5.1.32 (MySQL  Ver 14.14 Distrib 5.1.32, for pc-linux-gnu (i686) using readline 5.1) with identical results.
Tested on SO: Linux Enterprise Server 10.

Thanks for your help.

How to repeat:
To repeat this issue following the next steps:

1) Create database X.
2) Load the attached mysqldump on the database X.
3) Excecute the following query on X database:

SELECT * FROM transactions where acquirer_terminal='34007' and reversed=0 and reversable=1 and ((state_code=1) or (state_code=2 and authorized=1));

Note: This time the query use "idx_transactions2" as we can see with an explain of the query, and not return any result.

If the index is removed, the query schedule is modified, and MySQL choose another existing index to execute the query and  returns the expected result.

If we try this other query we get the expected result.

SELECT * FROM transactions where acquirer_terminal='34007' and reversed<>1 and reversable=1 and ((state_code=1) or (state_code=2 and authorized=1));
[18 Mar 2009 22:01] Sveta Smirnova
Thank you for the report.

Verified as described. Version 6.0 is not affected (tested since 6.0.3)
[25 Mar 2009 8:54] Sveta Smirnova
test case

Attachment: bug43737.test (application/octet-stream, text), 29.29 KiB.

[25 Mar 2009 8:57] Sveta Smirnova
Bug is still repeatable with 5.0 and 5.1 for me.
[29 Apr 2009 10:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72998

2877 Martin Hansson	2009-04-29
      Bug#43737: Select query return bad result
      
      A bug in the initialization of key segment information pointed to
      the wrong bit, since a bit index was used when the value of that
      bit was needed. This lead to misinterpretation of bit columns
      read from MyISAM record format when they occured above a byte 
      boundary.
      Fixed by using the value of the bit instead.
     @ mysql-test/r/myisam.result
        Bug#43737: Test result.
     @ mysql-test/t/myisam.test
        Bug#43737: Test case.
     @ storage/myisam/mi_open.c
        Bug#43737: fix.
[29 Apr 2009 12:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73014

2877 Martin Hansson	2009-04-29
      Bug#43737: Select query return bad result
            
      A bug in the initialization of key segment information made it point
      to the wrong bit, since a bit index was used when its int value
      was needed. This lead to misinterpretation of bit columns
      read from MyISAM record format when a NULL bit pushed them over
      a byte boundary.
      Fixed by using the int value of the bit instead.
     @ mysql-test/r/myisam.result
        Bug#43737: Test result.
     @ mysql-test/t/myisam.test
        Bug#43737: Test case.
     @ storage/myisam/mi_open.c
        Bug#43737: fix.
[29 Apr 2009 17:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73046

3237 Martin Hansson	2009-04-29 [merge]
      Bug#43737: Merge
[29 Apr 2009 18:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73058

3238 Martin Hansson	2009-04-29 [merge]
      Bug#43737: Merge
[5 May 2009 19:42] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:10] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:mhansson@mysql.com-20090429185530-u4nuo9ijnf0oaxh3) (merge vers: 6.0.11-alpha) (pib:6)
[15 May 2009 2:09] Paul Dubois
Noted 5.1.35, 6.0.12 changelogs.

Incorrect initialization of MyISAM table indexes could cause
incorrect query results.
[15 Jun 2009 8:29] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:08] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:49] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)