| Bug #48044 | MySQL processing HAVING clauses incorrectly without DISTINCT clause | ||
|---|---|---|---|
| Submitted: | 14 Oct 2009 15:13 | Modified: | 1 Mar 2010 10:18 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0,5.1,6.0 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | distinct, having, regression | ||
[14 Oct 2009 20:06]
Patrick Crews
NOTE: The join_cache_level and engine_condition_pushdown values should both be 1. However, these variables do not affect the result sets of the test.
[19 Oct 2009 15:00]
Patrick Crews
EXPLAIN output: Query 1: EXPLAIN SELECT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CC index NULL time_key 4 NULL 20 Using index 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING Query 2: EXPLAIN SELECT DISTINCT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 /* TRANSFORM_OUTCOME_DISTINCT */ ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CC range NULL time_key 4 NULL 7 Using index for group-by 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
[23 Nov 2009 12:00]
Manyi Lu
Title changed since this bug is not 6.0-specific, it exists in all versions.
[1 Mar 2010 10:15]
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/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).
[1 Mar 2010 10:18]
Sergei Glukhov
This problems is fixed in Bug#51242, closed as duplicate
[1 Mar 2010 14:05]
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/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).
[10 Mar 2010 12:43]
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/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.
[5 Apr 2010 12:47]
Sergei Glukhov
Correction: duplicated with Bug#48916
Description: 6.0-codebase tree is processing HAVING clauses incorrectly without a DISTINCT clause. The addition of a DISTINCT clause seems to cause correct results. no rows should be returned given the HAVING clause, yet the non-DISTINCT query returns several rows. join_cache_level and engine_condition_pushdown settings do not affect this. Simplified query: SELECT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 Simplified transformed query: SELECT DISTINCT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 /* TRANSFORM_OUTCOME_DISTINCT */ # 20:45:42 --- /tmp//randgen11445-1255481142-server0.dump 2009-10-13 20:45:42.000000000 -0400 # 20:45:42 +++ /tmp//randgen11445-1255481142-server1.dump 2009-10-13 20:45:42.000000000 -0400 # 20:45:42 @@ -1,16 +0,0 @@ # 20:45:42 -00:25:00 # 20:45:42 -01:27:35 # 20:45:42 -05:45:11 # 20:45:42 -06:01:40 # 20:45:42 -06:11:01 # 20:45:42 -10:55:12 # 20:45:42 -11:35:49 # 20:45:42 -12:35:47 # 20:45:42 -13:02:46 # 20:45:42 -17:53:30 # 20:45:42 -19:18:56 # 20:45:42 -19:48:31 # 20:45:42 -19:53:03 # 20:45:42 -19:53:05 # 20:45:42 -21:44:25 # 20:45:42 -22:43:58 How to repeat: MTR test case: #/* 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 = 0 */; /*!50400 SET SESSION join_cache_level = 0 */; /*!50400 SET SESSION debug = '+d,optimizer_no_icp' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; --enable_warnings CREATE TABLE `CC` ( `time_key` time DEFAULT NULL, KEY `time_key` (`time_key`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (NULL); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:25:00'); INSERT INTO `CC` VALUES ('01:27:35'); INSERT INTO `CC` VALUES ('05:45:11'); INSERT INTO `CC` VALUES ('06:01:40'); INSERT INTO `CC` VALUES ('06:11:01'); INSERT INTO `CC` VALUES ('10:55:12'); INSERT INTO `CC` VALUES ('11:35:49'); INSERT INTO `CC` VALUES ('12:35:47'); INSERT INTO `CC` VALUES ('13:02:46'); INSERT INTO `CC` VALUES ('17:53:30'); INSERT INTO `CC` VALUES ('19:18:56'); INSERT INTO `CC` VALUES ('19:48:31'); INSERT INTO `CC` VALUES ('19:53:03'); INSERT INTO `CC` VALUES ('19:53:05'); INSERT INTO `CC` VALUES ('21:44:25'); INSERT INTO `CC` VALUES ('22:43:58'); SELECT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 ; DROP TABLE CC; #/* End of test case for query 0 */ #/* Begin test case for query 1 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; --enable_warnings CREATE TABLE `CC` ( `time_key` time DEFAULT NULL, KEY `time_key` (`time_key`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (NULL); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:00:00'); INSERT INTO `CC` VALUES ('00:25:00'); INSERT INTO `CC` VALUES ('01:27:35'); INSERT INTO `CC` VALUES ('05:45:11'); INSERT INTO `CC` VALUES ('06:01:40'); INSERT INTO `CC` VALUES ('06:11:01'); INSERT INTO `CC` VALUES ('10:55:12'); INSERT INTO `CC` VALUES ('11:35:49'); INSERT INTO `CC` VALUES ('12:35:47'); INSERT INTO `CC` VALUES ('13:02:46'); INSERT INTO `CC` VALUES ('17:53:30'); INSERT INTO `CC` VALUES ('19:18:56'); INSERT INTO `CC` VALUES ('19:48:31'); INSERT INTO `CC` VALUES ('19:53:03'); INSERT INTO `CC` VALUES ('19:53:05'); INSERT INTO `CC` VALUES ('21:44:25'); INSERT INTO `CC` VALUES ('22:43:58'); SELECT DISTINCT `time_key` field1 FROM CC HAVING ( 2 , 5 ) IN ( SELECT 6 , 8 ) AND field1 ORDER BY field1 /* TRANSFORM_OUTCOME_DISTINCT */ ; DROP TABLE CC; #/* End of test case for query 1 */ Suggested fix: Ensure correct query processing.