MySQL Bugs: #43737: Select query return bad result

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

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));

Thank you for the report.

Verified as described. Version 6.0 is not affected (tested since 6.0.3)

test case

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

Bug is still repeatable with 5.0 and 5.1 for me.

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.

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.

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

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

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)

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)

Noted 5.1.35, 6.0.12 changelogs.

Incorrect initialization of MyISAM table indexes could cause
incorrect query results.

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)

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)

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)