MySQL Bugs: #48118: Server processing HAVING clauses incorrectly without a LIMIT clause

Bug #48118	Server processing HAVING clauses incorrectly without a LIMIT clause
Submitted:	16 Oct 2009 14:22	Modified:	1 Mar 2010 10:19
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	having, limit, regression

Description:
The server (tested in 5.1 and 6.0) is not processing certain HAVING clauses correctly unless a LIMIT clause is also added to the query:

These two queries:
Simplified query:  SELECT table1 .`time_key` field1  FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  GROUP  BY field1  HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  SELECT `varchar_key`  , `varchar_nokey`  FROM C  )   

Simplified transformed query:  SELECT table1 .`time_key` field1  FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  GROUP  BY field1  HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  SELECT `varchar_key`  , `varchar_nokey`  FROM C  )    LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */

Produce this diff:
# 04:06:00 @@ -1,10 +0,0 @@
# 04:06:00 -04:56:48
# 04:06:00 -05:03:03
# 04:06:00 -13:47:24
# 04:06:00 -15:15:04
# 04:06:00 -15:59:13
# 04:06:00 -18:32:33
# 04:06:00 -18:38:59
# 04:06:00 -19:35:19
# 04:06:00 -19:56:05
# 04:06:00 -20:25:14

The second (with the LIMIT clause) is correct as there are no rows which should match the HAVING clause.

EXPLAIN query 1:
EXPLAIN SELECT table1 .`time_key` field1  
FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  
GROUP  BY field1  
HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  
SELECT `varchar_key`  , `varchar_nokey`  
FROM C  )   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	table1	ALL	varchar_key	NULL	NULL	NULL	20	Using temporary; Using filesort
1	PRIMARY	table2	ref	varchar_key	varchar_key	4	test.table1.varchar_key	2	Using index
2	DEPENDENT SUBQUERY	C	index_subquery	varchar_key	varchar_key	4	const	1	Using where

EXPLAIN query 2:
EXPLAIN SELECT table1 .`time_key` field1  
FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  
GROUP  BY field1  
HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  
SELECT `varchar_key`  , `varchar_nokey`  
FROM C  )    
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	table1	index	varchar_key	time_key	4	NULL	1	Using filesort
1	PRIMARY	table2	ref	varchar_key	varchar_key	4	test.table1.varchar_key	2	Using index
2	DEPENDENT SUBQUERY	C	index_subquery	varchar_key	varchar_key	4	const	1	Using where

How to repeat:
#/* Server0: MySQL 6.0.14-alpha-debug-log */

/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;
/*!50400 SET SESSION debug = '+d,optimizer_no_icp' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `time_key` time DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES ('01:27:35','v','v');
INSERT INTO `CC` VALUES ('19:48:31','r','r');
INSERT INTO `CC` VALUES ('00:00:00','a','a');
INSERT INTO `CC` VALUES ('19:53:05','m','m');
INSERT INTO `CC` VALUES ('19:18:56','y','y');
INSERT INTO `CC` VALUES ('10:55:12','j','j');
INSERT INTO `CC` VALUES ('00:25:00','d','d');
INSERT INTO `CC` VALUES ('12:35:47','z','z');
INSERT INTO `CC` VALUES ('19:53:03','e','e');
INSERT INTO `CC` VALUES ('17:53:30','h','h');
INSERT INTO `CC` VALUES ('11:35:49','b','b');
INSERT INTO `CC` VALUES (NULL,'s','s');
INSERT INTO `CC` VALUES ('06:01:40','e','e');
INSERT INTO `CC` VALUES ('05:45:11','j','j');
INSERT INTO `CC` VALUES ('00:00:00','e','e');
INSERT INTO `CC` VALUES ('00:00:00','f','f');
INSERT INTO `CC` VALUES ('06:11:01','v','v');
INSERT INTO `CC` VALUES ('13:02:46','x','x');
INSERT INTO `CC` VALUES ('21:44:25','m','m');
INSERT INTO `CC` VALUES ('22:43:58','c','c');
CREATE TABLE `C` (
  `time_key` time DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES ('11:28:45','w','w');
INSERT INTO `C` VALUES ('20:25:14','m','m');
INSERT INTO `C` VALUES ('13:47:24','m','m');
INSERT INTO `C` VALUES ('19:24:11','k','k');
INSERT INTO `C` VALUES ('15:59:13','r','r');
INSERT INTO `C` VALUES ('00:00:00','t','t');
INSERT INTO `C` VALUES ('15:15:04','j','j');
INSERT INTO `C` VALUES ('11:32:06','u','u');
INSERT INTO `C` VALUES ('18:32:33','h','h');
INSERT INTO `C` VALUES ('15:19:25','o','o');
INSERT INTO `C` VALUES ('19:03:19',NULL,NULL);
INSERT INTO `C` VALUES ('00:39:46','k','k');
INSERT INTO `C` VALUES (NULL,'e','e');
INSERT INTO `C` VALUES ('00:00:00','n','n');
INSERT INTO `C` VALUES ('13:12:11','t','t');
INSERT INTO `C` VALUES ('04:56:48','c','c');
INSERT INTO `C` VALUES ('19:56:05','m','m');
INSERT INTO `C` VALUES ('19:35:19','y','y');
INSERT INTO `C` VALUES ('05:03:03','f','f');
INSERT INTO `C` VALUES ('18:38:59','d','d');

 
SELECT table1 .`time_key` field1  
FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  
GROUP  BY field1  
HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  
SELECT `varchar_key`  , `varchar_nokey`  
FROM C  )   ;

DROP TABLE CC;
DROP TABLE C;
#/* End of test case for query 0 */

#/* Begin test case for query 1 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `time_key` time DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES ('01:27:35','v','v');
INSERT INTO `CC` VALUES ('19:48:31','r','r');
INSERT INTO `CC` VALUES ('00:00:00','a','a');
INSERT INTO `CC` VALUES ('19:53:05','m','m');
INSERT INTO `CC` VALUES ('19:18:56','y','y');
INSERT INTO `CC` VALUES ('10:55:12','j','j');
INSERT INTO `CC` VALUES ('00:25:00','d','d');
INSERT INTO `CC` VALUES ('12:35:47','z','z');
INSERT INTO `CC` VALUES ('19:53:03','e','e');
INSERT INTO `CC` VALUES ('17:53:30','h','h');
INSERT INTO `CC` VALUES ('11:35:49','b','b');
INSERT INTO `CC` VALUES (NULL,'s','s');
INSERT INTO `CC` VALUES ('06:01:40','e','e');
INSERT INTO `CC` VALUES ('05:45:11','j','j');
INSERT INTO `CC` VALUES ('00:00:00','e','e');
INSERT INTO `CC` VALUES ('00:00:00','f','f');
INSERT INTO `CC` VALUES ('06:11:01','v','v');
INSERT INTO `CC` VALUES ('13:02:46','x','x');
INSERT INTO `CC` VALUES ('21:44:25','m','m');
INSERT INTO `CC` VALUES ('22:43:58','c','c');
CREATE TABLE `C` (
  `time_key` time DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES ('11:28:45','w','w');
INSERT INTO `C` VALUES ('20:25:14','m','m');
INSERT INTO `C` VALUES ('13:47:24','m','m');
INSERT INTO `C` VALUES ('19:24:11','k','k');
INSERT INTO `C` VALUES ('15:59:13','r','r');
INSERT INTO `C` VALUES ('00:00:00','t','t');
INSERT INTO `C` VALUES ('15:15:04','j','j');
INSERT INTO `C` VALUES ('11:32:06','u','u');
INSERT INTO `C` VALUES ('18:32:33','h','h');
INSERT INTO `C` VALUES ('15:19:25','o','o');
INSERT INTO `C` VALUES ('19:03:19',NULL,NULL);
INSERT INTO `C` VALUES ('00:39:46','k','k');
INSERT INTO `C` VALUES (NULL,'e','e');
INSERT INTO `C` VALUES ('00:00:00','n','n');
INSERT INTO `C` VALUES ('13:12:11','t','t');
INSERT INTO `C` VALUES ('04:56:48','c','c');
INSERT INTO `C` VALUES ('19:56:05','m','m');
INSERT INTO `C` VALUES ('19:35:19','y','y');
INSERT INTO `C` VALUES ('05:03:03','f','f');
INSERT INTO `C` VALUES ('18:38:59','d','d');

 
SELECT table1 .`time_key` field1  
FROM C table1  STRAIGHT_JOIN CC table2  ON table2 .`varchar_key`  = table1 .`varchar_key`  
GROUP  BY field1  
HAVING  9  AND field1  AND ( 'a' , 'z' )  IN (  
SELECT `varchar_key`  , `varchar_nokey`  
FROM C  )    
LIMIT 1  /* TRANSFORM_OUTCOME_SINGLE_ROW */;

DROP TABLE CC;
DROP TABLE C;
#/* End of test case for query 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/commits/101828

3344 Sergey Glukhov	2010-03-01
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        It's necessary to check const part of HAVING cond as
        there is a chance that some cond parts may become
        const items after make_join_statisctics(for example
        when Item is a reference to cost table field from
        outer join).

This problems is fixed in Bug#51242, closed as duplicate

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

3344 Sergey Glukhov	2010-03-01
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        It's necessary to check const part of HAVING cond as
        there is a chance that some cond parts may become
        const items after make_join_statisctics(for example
        when Item is a reference to cost table field from
        outer join).

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

3385 Sergey Glukhov	2010-03-10
      Bug#51242 HAVING clause on table join produce incorrect results
      The problem is that when we make conditon for
      grouped result const part of condition is cut off.
      It happens because some parts of 'having' condition
      which refer to outer join become const after
      make_join_statistics. The fix is adding 'having'
      condition check for 'having' const tables after
      make_join_statistics is performed.
      Note:
      This patch also fixes problems described in
      Bug#48916, Bug #48044, Bug#48118.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        It's necessary to check const part of HAVING cond as
        there is a chance that some cond parts may become
        const items after make_join_statisctics(for example
        when Item is a reference to const table field from
        outer join).
        Note:
        This is applicable only for those conditions
        which do not use aggregate fucntions.