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

[2 Mar 2005 14:51] Ralph Runge
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
[2 Mar 2005 22:05] Jorge del Conde
Verified w/latest bk changeset in 4.1
[5 Mar 2005 4:22] 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/internals/22684
[5 Mar 2005 22:21] Igor Babaev
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.
[9 Mar 2005 3:41] Paul DuBois
Noted in 4.1.11, 5.0.3 changelogs.