Bug #46087 MAX/MIN + SUBQUERY + AND fails to return any rows

• Submitted: 9 Jul 2009 14:17
• Modified: 29 Dec 2012 21:18
• Reporter: Philip Stoev
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1,5.4
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[9 Jul 2009 14:17] Philip Stoev

Description:
With semijoin, materialization, MRR and ICP disabled, queries of the form:

SELECT  MIN( `pk`  )
FROM C
WHERE `pk`  IN ( SELECT `int_key` FROM CC )
AND `pk`  =  6 ;

fail to return any rows, even though a SELECT MAX() should always return one row, even if it is NULL.

See also similar bug #40037.

How to repeat:
SET SESSION optimizer_switch = 'materialization=off,semijoin=off';
SET SESSION optimizer_use_mrr = 'disable';
SET SESSION engine_condition_pushdown = 0;

--disable_warnings
DROP TABLE IF EXISTS C;
--enable_warnings

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0),(7,7),(8,0),(9,7),(10,7),(11,9),(12,2),(13,4),(14,3),(15,9),(16,5),(17,5),(18,0),(19,8),(20,5);

SELECT  MIN( `pk`  )
FROM C
WHERE `pk`  IN (
SELECT `int_key` FROM C ) AND `pk`  =  6;

[29 Dec 2012 21:18] Erlend Dahl

Duplicate of an internally filed bug, fixed in 5.6.5.