| Bug #54416 | MAX from JOIN with HAVING returning NULL with 5.1 and Empty set | ||
|---|---|---|---|
| Submitted: | 11 Jun 2010 6:15 | Modified: | 14 Nov 2010 2:46 |
| Reporter: | Sergei Glukhov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | having, regression | ||
[11 Jun 2010 6:29]
Ole John Aske
I do not agree that this is a bug - at least not according to the SQL standard ISO9075:1992 According to ISO9075, the set functions MAX, MIN, SUM and AVG should return NULL if the underlying table is empty. Ref. ISO9075:1992, Chap 6.5, General Rules 2b): b) If AVG, MAX, MIN or SUM is specified, then, Case i) If TXA is empty, then the result is the null value (Previous rules defines TXA to be the underlying query expression wo/ the set function)
[11 Jun 2010 7:04]
Roy Lyseng
According to my knowledge of the SQL standard, the above interpretation is wrong. The result, according to the SQL standard, should be an empty set. Using HAVING implies a grouping of the table, and without GROUP BY, there will be maximum 1 group. The JOIN produces zero rows, so no groups will ever be created. The MAX(table1.pk) applies to each group, but since there are no groups, no rows should be output from this query. MySQL may not agree with standard SQL in this case, but the outcome of the query is still the same.
[11 Jun 2010 7:04]
John Embretsen
In any case, if this is not a bug in 5.1 it is a bug in mysql-next-mr-opt-backporting (as well as the former 6.0 based branches), since the results differ (the latter returns the Empty set, while 5.1 and mysql-next-mr returns NULL).
[11 Jun 2010 8:37]
Ole John Aske
I overlooked the HAVING clause in the example query, and agree that this is a bug.
[21 Jun 2010 16:57]
Peter Gulutzan
The first SELECT in the 'How to Repeat',
with a MAX() clause, shows there is a bug.
Since field1 is NULL, the condition in the
HAVING clause ("field1 < 7") isn't true,
and the result set should contain zero rows.
We'd get the same result with
"HAVING MAX(table1.pk)<7" so thinking
about the SQL standard is legitimate.
The second SELECT in the 'How to Repeat',
without a MAX() clause, is troublesome.
The SQL standard is irrelevant here because
the query syntax is non-standard, this is a
MySQL "extension". Assume @@sql_mode is not
only_full_group_by, what is in 'table1.pk'?
Well, never mind, that's not what this bug
report is about.
The bug report is correct, there is a bug.
[7 Jul 2010 11:53]
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/113013 3468 Sergey Glukhov 2010-07-07 Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set The problem there is that HAVING condition evaluates const parts of condition despite the condition has references on aggregate functions. Table t1 became const tables after make_join_statistics and table1.pk = 1, HAVING is transformed into MAX(1) < 7 and taken away from HAVING. The fix is to skip evaluation of HAVING conts parts if HAVING condition has references on aggregate functions. @ mysql-test/r/having.result test case @ mysql-test/t/having.test test case @ sql/sql_select.cc skip evaluation of HAVING conts parts if HAVING condition has references on aggregate functions.
[9 Jul 2010 10:36]
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/113222 3470 Sergey Glukhov 2010-07-09 Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set The problem there is that HAVING condition evaluates const parts of condition despite the condition has references on aggregate functions. Table t1 became const tables after make_join_statistics and table1.pk = 1, HAVING is transformed into MAX(1) < 7 and taken away from HAVING. The fix is to skip evaluation of HAVING conts parts if HAVING condition has references on aggregate functions. @ mysql-test/r/having.result test case @ mysql-test/t/having.test test case @ sql/sql_select.cc skip evaluation of HAVING conts parts if HAVING condition has references on aggregate functions.
[23 Jul 2010 12:27]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:34]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[19 Aug 2010 15:42]
Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[14 Oct 2010 8:36]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:51]
Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:06]
Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 10:44]
Jon Stephens
No separate changelog entries needed for the -telco pushes.
[14 Nov 2010 2:46]
Paul DuBois
Noted in 5.1.50, 5.5.6 changelogs. A join with an aggregated function and impossible WHERE condition returned an extra row.
Description: Consider the query SELECT MAX(table1 .`pk`) field1 FROM t1 table1 JOIN t2 table2 ON table2 .`col_varchar_nokey` LIKE 'x' HAVING field1 < 7; The JOIN itself (query without MAX) returns the Empty set, with MAX it returns NULL. +--------+ | field1 | +--------+ | NULL | +--------+ How to repeat: CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`) ); INSERT INTO t1 VALUES (1,'f'); CREATE TABLE t2 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`) ); INSERT INTO t2 VALUES (2,'m'); INSERT INTO t2 VALUES (3,'m'); INSERT INTO t2 VALUES (11,NULL); INSERT INTO t2 VALUES (12,'k'); SELECT MAX(table1 .`pk`) field1 FROM t1 table1 JOIN t2 table2 ON table2 .`col_varchar_nokey` LIKE 'x' HAVING field1 < 7; SELECT table1 .`pk` field1 FROM t1 table1 JOIN t2 table2 ON table2 .`col_varchar_nokey` LIKE 'x' HAVING field1 < 7; Suggested fix: --- sql/sql_select.cc 2010-06-10 08:23:33 +0000 +++ sql/sql_select.cc 2010-06-11 05:52:36 +0000 @@ -1124,7 +1124,7 @@ elements may be lost during further having condition transformation in JOIN::exec. */ - if (having && const_table_map) + if (having && const_table_map && !having->with_sum_func) { having->update_used_tables(); having= remove_eq_conds(thd, having, &having_value)