diff options
| author | Sergey Glukhov <Sergey.Glukhov@sun.com> | 2010-03-19 13:09:22 +0400 |
|---|---|---|
| committer | Sergey Glukhov <Sergey.Glukhov@sun.com> | 2010-03-19 13:09:22 +0400 |
| commit | ad6e00e3b2b9bf26805c90cbd7655c6d2b20cab4 (patch) | |
| tree | aa4577efde3e75d5c6d58f04ef55f9605282d49d /mysql-test/r/having.result | |
| parent | a76b8f9a1d58991f58771043034308895b368549 (diff) | |
| download | mariadb-git-ad6e00e3b2b9bf26805c90cbd7655c6d2b20cab4.tar.gz | |
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. These parts may be lost
during further having condition transformation
in JOIN::exec. The fix is adding 'having'
condition check for const tables after
make_join_statistics is performed.
mysql-test/r/having.result:
test case
mysql-test/t/having.test:
test result
sql/sql_select.cc:
added 'having' condition check for const tables
after make_join_statistics is performed.
Diffstat (limited to 'mysql-test/r/having.result')
| -rw-r--r-- | mysql-test/r/having.result | 35 |
1 files changed, 35 insertions, 0 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 9568ef88786..95893510987 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -450,4 +450,39 @@ HAVING amount > 0 ORDER BY t1.id1; id1 amount DROP TABLE t1; +# +# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause +# +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); +INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +f1 f2 +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having (('7' = 8) and (`test`.`table1`.`f1` >= 6)) +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +Warnings: +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having ('7' = 8) +DROP TABLE t1; End of 5.0 tests |