MySQL Bugs: #21180: Subselect with index for both WHERE and ORDER BY produces empty result

Bug #21180	Subselect with index for both WHERE and ORDER BY produces empty result
Submitted:	20 Jul 2006 14:00	Modified:	25 Sep 2006 17:54
Reporter:	Svetoslav Naidenov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.22/4.1/5.0/5.1BK	OS:	Linux (linux and windows)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Using subselect with ORDER BY LIMIT 1, produces empty result for Innodb tables
and error for MYISAM tables

How to repeat:
drop table if exists rr;
create table rr(
  routing_id int not null AUTO_INCREMENT,
  primary key (routing_id)
) engine=INNODB;
insert into rr values (1), (10);

drop table if exists rt;
create table rt
(
  route_id int not null AUTO_INCREMENT,
  route_prefix varchar(32),
  routing_id int not null,
  primary key (route_id),
  unique key route_prefix(routing_id, route_prefix)
) engine=INNODB;
insert into rt(routing_id, route_prefix) values 
  (1, '359'), (1, '35988'), (1,'35989'),(10, '359'),
  (10, '35988'), (10,'35989');

SELECT route_id
FROM rt
WHERE rt.routing_id = 10
  AND route_prefix <= '359899'
ORDER BY routing_id DESC, route_prefix DESC
LIMIT 1;
+----------+
| route_id |
+----------+
|        6 |
+----------+

SELECT
  rr.routing_id, r.route_id, r.route_prefix
FROM rr
  LEFT JOIN rt r ON r.route_id = (
    SELECT route_id
    FROM rt
    WHERE rt.routing_id = rr.routing_id
      AND route_prefix <= '359899'
    ORDER BY routing_id DESC, route_prefix DESC
    LIMIT 1)
WHERE rr.routing_id = 10;
+------------+--------------+
| routing_id | route_prefix |
+------------+--------------+
|         10 | NULL         |
+------------+--------------+

If table rt is in MYISAM format the second query either never finishes or
gives error:
Incorrect key file for table './bss/rt.MYI'; try to repair it

Rewriting the subquery with useless function to trick optimizer produces correct result (for the cost of performance if the table is large):
SELECT
  rr.routing_id, r.route_id, r.route_prefix
FROM rr
  LEFT JOIN rt r ON r.route_id = (
    SELECT route_id
    FROM rt
    WHERE rt.routing_id = rr.routing_id
      AND IFNULL(route_prefix,'') <= '359899'
    ORDER BY routing_id DESC, route_prefix DESC
    LIMIT 1)
WHERE rr.routing_id = 10;
+------------+----------+--------------+
| routing_id | route_id | route_prefix |
+------------+----------+--------------+
|         10 |        6 | 35989        |
+------------+----------+--------------+

Thank you for the bug report.

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

ChangeSet@1.2532, 2006-08-03 19:20:30+03:00, gkodinov@macbook.gmz +4 -0
  Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
   Reseting subqueries with "quick" access methods was incomplete.
   Partially backported the correct reseting of QUICK_SELECTs from 5.x.

I've partially back-ported the 5.0 code. In 5.0 this was fixed by the fix for either BUG#21077 or BUG#20869.

Pushed into 4.1.22/5.0.26/5.1.12-beta

Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

A subquery that uses an index for both the WHERE and ORDER BY clauses
produced an empty result.