MySQL Bugs: #48916: Server incorrectly processing HAVING clauses with an ORDER BY clause

Bug #48916	Server incorrectly processing HAVING clauses with an ORDER BY clause
Submitted:	19 Nov 2009 17:12	Modified:	22 Apr 2011 13:31
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	having

Description:
The server (5.0 -> 6.0) is incorrectly processing HAVING clauses for queries that don't also have an ORDER BY clause:

These two queries:
SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx' 
ORDER  BY table2 .`pk`   ;

SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx' ;

Produce the following diff:
field1
-u
-y
-w

The second query is correct as no rows should match that HAVING clause.

This appears related to the following bugs:
Bug#48118	Server processing HAVING clauses incorrectly without a LIMIT clause
Bug#48044	6.0-codebase processing HAVING clauses incorrectly without DISTINCT clause

However, this differs as the two aforementioned bugs *require* the clauses for the HAVING to be correctly processed.  Here, we cannot have an ORDER BY clause.

EXPLAIN output:

EXPLAIN SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx' 
ORDER  BY table2 .`pk`   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	100	Using where
3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DERIVED	D	ALL	NULL	NULL	NULL	NULL	100	

EXPLAIN SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx'  /* TRANSFORM_OUTCOME_UNORDERED_MATCH */ ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	20	
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	100	Using where
3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DERIVED	D	ALL	NULL	NULL	NULL	NULL	100

How to repeat:
MTR test case:
This is set up for use with a 6.0 server, please disable SET statements as needed for other versions

#/* Server0: MySQL 6.0.14-alpha-gcov-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 = 'force' */;
/*!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 */ D;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v','v');
INSERT INTO `CC` VALUES (11,9,'r','r');
INSERT INTO `CC` VALUES (12,9,'a','a');
INSERT INTO `CC` VALUES (13,186,'m','m');
INSERT INTO `CC` VALUES (14,NULL,'y','y');
INSERT INTO `CC` VALUES (15,2,'j','j');
INSERT INTO `CC` VALUES (16,3,'d','d');
INSERT INTO `CC` VALUES (17,0,'z','z');
INSERT INTO `CC` VALUES (18,133,'e','e');
INSERT INTO `CC` VALUES (19,1,'h','h');
INSERT INTO `CC` VALUES (20,8,'b','b');
INSERT INTO `CC` VALUES (21,5,'s','s');
INSERT INTO `CC` VALUES (22,5,'e','e');
INSERT INTO `CC` VALUES (23,8,'j','j');
INSERT INTO `CC` VALUES (24,6,'e','e');
INSERT INTO `CC` VALUES (25,51,'f','f');
INSERT INTO `CC` VALUES (26,4,'v','v');
INSERT INTO `CC` VALUES (27,7,'x','x');
INSERT INTO `CC` VALUES (28,6,'m','m');
INSERT INTO `CC` VALUES (29,4,'c','c');
CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,NULL,'r','r');
INSERT INTO `D` VALUES (2,0,'c','c');
INSERT INTO `D` VALUES (3,0,'o','o');
INSERT INTO `D` VALUES (4,7,'c','c');
INSERT INTO `D` VALUES (5,8,'d','d');
INSERT INTO `D` VALUES (6,4,'v','v');
INSERT INTO `D` VALUES (7,6,'m','m');
INSERT INTO `D` VALUES (8,5,'j','j');
INSERT INTO `D` VALUES (9,NULL,'f','f');
INSERT INTO `D` VALUES (10,NULL,'n','n');
INSERT INTO `D` VALUES (11,8,'z','z');
INSERT INTO `D` VALUES (12,8,'h','h');
INSERT INTO `D` VALUES (13,8,'q','q');
INSERT INTO `D` VALUES (14,1,'w','w');
INSERT INTO `D` VALUES (15,1,'z','z');
INSERT INTO `D` VALUES (16,5,'j','j');
INSERT INTO `D` VALUES (17,2,'a','a');
INSERT INTO `D` VALUES (18,7,'m','m');
INSERT INTO `D` VALUES (19,6,'n','n');
INSERT INTO `D` VALUES (20,4,'e','e');
INSERT INTO `D` VALUES (21,7,'u','u');
INSERT INTO `D` VALUES (22,0,'s','s');
INSERT INTO `D` VALUES (23,9,'u','u');
INSERT INTO `D` VALUES (24,3,'r','r');
INSERT INTO `D` VALUES (25,5,'g','g');
INSERT INTO `D` VALUES (26,1,'o','o');
INSERT INTO `D` VALUES (27,1,'w','w');
INSERT INTO `D` VALUES (28,5,'b','b');
INSERT INTO `D` VALUES (29,9,NULL,NULL);
INSERT INTO `D` VALUES (30,2,'y','y');
INSERT INTO `D` VALUES (31,5,'y','y');
INSERT INTO `D` VALUES (32,248,'u','u');
INSERT INTO `D` VALUES (33,0,'p','p');
INSERT INTO `D` VALUES (34,8,'s','s');
INSERT INTO `D` VALUES (35,1,'e','e');
INSERT INTO `D` VALUES (36,255,'d','d');
INSERT INTO `D` VALUES (37,9,'d','d');
INSERT INTO `D` VALUES (38,9,'c','c');
INSERT INTO `D` VALUES (39,3,'b','b');
INSERT INTO `D` VALUES (40,9,'t','t');
INSERT INTO `D` VALUES (41,6,NULL,NULL);
INSERT INTO `D` VALUES (42,4,'y','y');
INSERT INTO `D` VALUES (43,60,'c','c');
INSERT INTO `D` VALUES (44,7,'d','d');
INSERT INTO `D` VALUES (45,1,'x','x');
INSERT INTO `D` VALUES (46,6,'p','p');
INSERT INTO `D` VALUES (47,4,'e','e');
INSERT INTO `D` VALUES (48,NULL,'g','g');
INSERT INTO `D` VALUES (49,8,'x','x');
INSERT INTO `D` VALUES (50,0,'s','s');
INSERT INTO `D` VALUES (51,8,'e','e');
INSERT INTO `D` VALUES (52,151,'l','l');
INSERT INTO `D` VALUES (53,7,'p','p');
INSERT INTO `D` VALUES (54,6,'h','h');
INSERT INTO `D` VALUES (55,NULL,'m','m');
INSERT INTO `D` VALUES (56,23,'n','n');
INSERT INTO `D` VALUES (57,2,'v','v');
INSERT INTO `D` VALUES (58,4,'b','b');
INSERT INTO `D` VALUES (59,NULL,'x','x');
INSERT INTO `D` VALUES (60,NULL,'r','r');
INSERT INTO `D` VALUES (61,77,'t','t');
INSERT INTO `D` VALUES (62,NULL,'w','w');
INSERT INTO `D` VALUES (63,NULL,'w','w');
INSERT INTO `D` VALUES (64,7,'k','k');
INSERT INTO `D` VALUES (65,1,'a','a');
INSERT INTO `D` VALUES (66,9,'t','t');
INSERT INTO `D` VALUES (67,6,'z','z');
INSERT INTO `D` VALUES (68,2,'e','e');
INSERT INTO `D` VALUES (69,3,'q','q');
INSERT INTO `D` VALUES (70,0,'e','e');
INSERT INTO `D` VALUES (71,NULL,'v','v');
INSERT INTO `D` VALUES (72,6,'d','d');
INSERT INTO `D` VALUES (73,3,'u','u');
INSERT INTO `D` VALUES (74,195,'o','o');
INSERT INTO `D` VALUES (75,5,'b','b');
INSERT INTO `D` VALUES (76,2,'c','c');
INSERT INTO `D` VALUES (77,7,'q','q');
INSERT INTO `D` VALUES (78,25,NULL,NULL);
INSERT INTO `D` VALUES (79,NULL,'h','h');
INSERT INTO `D` VALUES (80,0,'d','d');
INSERT INTO `D` VALUES (81,98,'w','w');
INSERT INTO `D` VALUES (82,6,'m','m');
INSERT INTO `D` VALUES (83,5,'i','i');
INSERT INTO `D` VALUES (84,0,'w','w');
INSERT INTO `D` VALUES (85,3,'f','f');
INSERT INTO `D` VALUES (86,1,'k','k');
INSERT INTO `D` VALUES (87,1,'v','v');
INSERT INTO `D` VALUES (88,147,'c','c');
INSERT INTO `D` VALUES (89,3,'y','y');
INSERT INTO `D` VALUES (90,3,'h','h');
INSERT INTO `D` VALUES (91,NULL,NULL,NULL);
INSERT INTO `D` VALUES (92,2,'t','t');
INSERT INTO `D` VALUES (93,1,'l','l');
INSERT INTO `D` VALUES (94,8,'a','a');
INSERT INTO `D` VALUES (95,8,'r','r');
INSERT INTO `D` VALUES (96,8,'s','s');
INSERT INTO `D` VALUES (97,0,'z','z');
INSERT INTO `D` VALUES (98,1,'j','j');
INSERT INTO `D` VALUES (99,8,'c','c');
INSERT INTO `D` VALUES (100,5,'f','f');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w','w');
INSERT INTO `C` VALUES (2,9,'m','m');
INSERT INTO `C` VALUES (3,3,'m','m');
INSERT INTO `C` VALUES (4,9,'k','k');
INSERT INTO `C` VALUES (5,NULL,'r','r');
INSERT INTO `C` VALUES (6,9,'t','t');
INSERT INTO `C` VALUES (7,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (9,8,'h','h');
INSERT INTO `C` VALUES (10,53,'o','o');
INSERT INTO `C` VALUES (11,0,NULL,NULL);
INSERT INTO `C` VALUES (12,5,'k','k');
INSERT INTO `C` VALUES (13,166,'e','e');
INSERT INTO `C` VALUES (14,3,'n','n');
INSERT INTO `C` VALUES (15,0,'t','t');
INSERT INTO `C` VALUES (16,1,'c','c');
INSERT INTO `C` VALUES (17,9,'m','m');
INSERT INTO `C` VALUES (18,5,'y','y');
INSERT INTO `C` VALUES (19,6,'f','f');
INSERT INTO `C` VALUES (20,2,'d','d');

 
SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx' 
ORDER  BY table2 .`pk`   ;

#/* End of test case for query 0 */

#/* Begin test case for query 1 */
 
SELECT table1 .`varchar_nokey` field1  
FROM C table1  LEFT  JOIN (  
SELECT * 
FROM D  ) table2  ON table1 .`varchar_key`  
HAVING (  3  ,  2  )  IN (  
SELECT `int_key`  , `pk`  
FROM CC  )  AND field1  >= 'tx'  /* TRANSFORM_OUTCOME_UNORDERED_MATCH */ ;

DROP TABLE CC;
DROP TABLE D;
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.

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

3432 Sergey Glukhov	2010-06-16
      Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
      Before sorting HAVING condition is splitted into two parts,
      first past is a table related condition and the rest of is
      HAVING part. Extraction of HAVING part does not take into account
      the fact that some of conditions might be non-const but
      have 'used_tables' equal to zero(particulary IN subselect)
      and because of that these conditions are cut off by
      make_cond_for_table() function.
      The fix is to split extraction of HAVING part into two steps:
      1. extract condition which does not belong to the sorted table
      2. extract condition which has 'used_tables' equal to zero
      and depending on result use one or union of them.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        The fix is to split extraction of HAVING part into two steps:
        1. extract condition which does not belong to sorted table
        2. extract condition which has 'used_tables' equal to zero
        and depending on result use one or union of them.

Gluh,

With a proper patch for bug #52336 this bug #48916 will be fixed as well.

A proper fix for bug #52336 could look like the one accepted in the code of MariaDB 5.1.47:  

-      Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
- 						  used_tables,
- 						  used_tables);
+      Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
+ 						  used_tables,
+ 						  (table_map) 0);

Please, reconsider the fix for bug ##52336.

Regards,
Igor.

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

3515 Sergey Glukhov	2010-12-10
      Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
      Before sorting HAVING condition is splitted into two parts,
      first past is a table related condition and the rest of is
      HAVING part. Extraction of HAVING part does not take into account
      the fact that some of conditions might be non-const but
      have 'used_tables' == 0 (undependent subqueries)
      and because of that these conditions are cut off by
      make_cond_for_table() function.
      The fix is to use (table_map) 0 instead of used_tables in
      third argument for make_cond_for_table() function.
      It allows to extract elements which belong to sorted
      table and in addition elements which are undependend
      subqueries.
     @ mysql-test/r/having.result
        test case
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        The fix is to use (table_map) 0 instead of used_tables in
        third argument for make_cond_for_table() function.
        It allows to extract elements which belong to sorted
        table and in addition elements which are undependend
        subqueries.

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

3537 Sergey Glukhov	2010-12-30
      Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
      Before sorting HAVING condition is split into two parts,
      first part is a table related condition and the rest of is
      HAVING part. Extraction of HAVING part does not take into account
      the fact that some of conditions might be non-const but
      have 'used_tables' == 0 (undependent subqueries)
      and because of that these conditions are cut off by
      make_cond_for_table() function.
      The fix is to use (table_map) 0 instead of used_tables in
      third argument for make_cond_for_table() function.
      It allows to extract elements which belong to sorted
      table and in addition elements which are undependend
      subqueries.
     @ mysql-test/r/having.result
        test result
     @ mysql-test/t/having.test
        test case
     @ sql/sql_select.cc
        The fix is to use (table_map) 0 instead of used_tables in
        third argument for make_cond_for_table() function.
        It allows to extract elements which belong to sorted
        table and in addition elements which are undependend
        subqueries.

Noted in 5.1.58, 5.5.13, 5.6.3 changelogs.

The optimizer sometimes incorrectly processed HAVING clauses for
queries that did not also have an ORDER BY clause. 

CHANGESET - http://lists.mysql.com/commits/135944