MySQL Bugs: #35206: select query result different if the key is indexed or not

Bug #35206	select query result different if the key is indexed or not
Submitted:	11 Mar 2008 11:25	Modified:	7 Apr 2008 17:32
Reporter:	Frederic Steinfels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.23	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	different, INDEX, key, limit, order by, query, result, selct

Description:
When using a SELECT query on two tables, the result from the statement might be 0 rows if the following conditions are met:
- You are using 5.1.23. The same statement was perfectly working under the 5.0 branch
- You are using ORDER BY.
- You are using LIMIT 1 (in my case LIMIT 1 up to LIMIT 8 will result 0 rows, LIMIT 9 will report 9 rows)
- The KEY joining the second table to the first is NOT indexed

How to repeat:
the query in question is

SELECT 
  (sp.`price`+10)*1.1 
FROM
  `shipzonecountry` AS sc,
  `shipprice` AS sp
WHERE
  sp.`shipmode_id`=14 
  AND
  sp.`shipzonecountry_code`=sc.`code` 
  AND
  5.1<sp.`maxweight` 
  AND 
  sc.`country_code`='DE'
ORDER BY 
  sp.`maxweight` 
LIMIT 1

to create tables;

CREATE TABLE `shipprice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shipzonecountry_code` varchar(32) NOT NULL,
  `shipmode_id` int(11) NOT NULL,
  `maxweight` float NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipzonecountry_code` (`shipzonecountry_code`,`shipmode_id`,`maxweight`),
  KEY `shipzonecountry_code_2` (`shipzonecountry_code`),
  KEY `shipmode_id` (`shipmode_id`),
  KEY `maxweight` (`maxweight`),
  KEY `price` (`price`)
) ENGINE=MyISAM AUTO_INCREMENT=557 DEFAULT CHARSET=latin1 

 CREATE TABLE `shipzonecountry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `country_code` char(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`,`country_code`),
  KEY `code_2` (`code`),
  KEY `country_code` (`country_code`)
) ENGINE=MyISAM AUTO_INCREMENT=997 DEFAULT CHARSET=latin1

try the SELECT statement from above with country_code indexed and not indexed.

to fill tables with something, use the attached files

use this to fill the tables

Attachment: fillup.txt (text/plain), 39.44 KiB.

Thank you for a bug report. Verified just as described. With key on column we have:

mysql> explain SELECT
    ->   (sp.`price`+10)*1.1
    -> FROM
    ->   `shipzonecountry` AS sc,
    ->   `shipprice` AS sp
    -> WHERE
    ->   sp.`shipmode_id`=14
    ->   AND
    ->   sp.`shipzonecountry_code`=sc.`code`
    ->   AND
    ->   5.1<sp.`maxweight`
    ->   AND
    ->   sc.`country_code`='DE'
    -> ORDER BY
    ->   sp.`maxweight`
    -> LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
         type: ref
possible_keys: code,code_2,country_code
          key: country_code
      key_len: 3
          ref: const
         rows: 3
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sp
         type: ref
possible_keys: shipzonecountry_code,shipzonecountry_code_2,shipmode_id,maxweight

          key: shipzonecountry_code
      key_len: 38
          ref: test.sc.code,const
         rows: 3
        Extra: Using where
2 rows in set (0.00 sec)

(Query returns one row)

And without key:

mysql> alter table `shipzonecountry`  drop key `country_code`;
Query OK, 991 rows affected (0.17 sec)
Records: 991  Duplicates: 0  Warnings: 0

mysql> explain SELECT
    ->   (sp.`price`+10)*1.1
    -> FROM
    ->   `shipzonecountry` AS sc,
    ->   `shipprice` AS sp
    -> WHERE
    ->   sp.`shipmode_id`=14
    ->   AND
    ->   sp.`shipzonecountry_code`=sc.`code`
    ->   AND
    ->   5.1<sp.`maxweight`
    ->   AND
    ->   sc.`country_code`='DE'
    -> ORDER BY
    ->   sp.`maxweight`
    -> LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sp
         type: ref
possible_keys: shipzonecountry_code,shipzonecountry_code_2,shipmode_id,maxweight

          key: shipmode_id
      key_len: 4
          ref: const
         rows: 431
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
         type: eq_ref
possible_keys: code,code_2
          key: code
      key_len: 37
          ref: test.sp.shipzonecountry_code,const
         rows: 1
        Extra: Using where; Using index
2 rows in set (0.00 sec)

(Query returns zero rows)

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/44526

ChangeSet@1.2571, 2008-03-27 19:39:21+02:00, gkodinov@magare.gmz +3 -0
  Bug #35206: select query result different if the key is indexed or not
  
  The code for executing indexed ORDER BY was not setting all the 
  internal fields correctly when selecting to execute ORDER BY over
  and index.
  Fixed by change the access method to one that will use the 
  quick indexed access if one is selected while selecting indexed
  ORDER BY.

Pushed into 5.1.24-rc

Noted in 5.1.24 changelog.

Queries could return different results depending on whether ORDER BY
columns were indexed.  

Resetting to Patch queued waiting for push into 6.0.x.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.

Bug #36484 was marked as a duplicate of this one.

Bug #39234 was marked as duplicate of this one.