MySQL Bugs: #8893: Using "SELECT MAX(ID)" with Multiple-Column Indexes

Bug #8893	Using "SELECT MAX(ID)" with Multiple-Column Indexes
Submitted:	2 Mar 2005 14:51	Modified:	9 Mar 2005 3:41
Reporter:	Ralph Runge	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Any (all)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
--
--  Using "SELECT MAX(ID)" with Multiple-Column Indexes
--  works in some cases not properly.
--
--  
--  Sample:
-- 

DROP TABLE IF EXISTS `z`;

CREATE TABLE `z` (

  `ID` int PRIMARY KEY,

  `A`  int ,
  `B`  int ,
 
  KEY `I_ID`   (`A`,`ID`),       
  KEY `I_B_ID` (`A`,`B`,`ID`)
 
)  ENGINE=MyISAM;

INSERT INTO `z` (ID,A,B) VALUES 
(1,1,4),
(2,2,1),
(3,1,3),
(4,2,1),
(5,1,1);

SELECT MAX(ID) FROM z WHERE ID < 3 AND A=2 AND B=6;

-- 
-- ->  +---------+
--     | MAX(ID) |
--     +---------+
--     |       4 |
--     +---------+
--  
--     wrong result 
--

--
--   ** but ** 
-- 

DROP TABLE IF EXISTS `z`;

CREATE TABLE `z` (

  `ID` int PRIMARY KEY,
  `A`  int ,
  `B`  int ,
 
  KEY `I_B_ID` (`A`,`B`,`ID`),   -- Index creation order  
  KEY `I_ID`   (`A`,`ID`)        -- changed
 
)  ENGINE=MyISAM;

INSERT INTO `z` (ID,A,B) VALUES 
(1,1,4),
(2,2,1),
(3,1,3),
(4,2,1),
(5,1,1);

SELECT MAX(ID) FROM z WHERE ID < 3 AND A=2 AND B=6;

--
-- ->  +---------+
--     | MAX(ID) |
--     +---------+
--     |    NULL |
--     +---------+
--   
--  result ok  - but can i be sure that it works in ane cases ?
--  
--
--
--

How to repeat:
Please mail to Ralph.Runge@stoll.com

Verified w/latest bk changeset in 4.1

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/internals/22684

The first releases with this fix will be: 4.1.11 and 5.0.3.

ChangeSet
  1.2083 05/03/04 20:24:13 igor@linux.local +4 -0
  logging_ok:
    Logging to logging@openlogging.org accepted
  func_group.result, func_group.test:
    Added a test case for bug #8893.
  opt_sum.cc:
    A misplaced initialization for the returned parameter
    prefix_len in the function find_key_for_maxmin caused
    usage of a wrong key prefix by the min/max optimization
    in cases when the matching index was not the first index
    that contained the min/max field.

Noted in 4.1.11, 5.0.3 changelogs.