diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-12-27 13:19:13 -0800 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-12-27 13:19:13 -0800 |
| commit | c9259f166bfcc757338c957f806e3d18637da17a (patch) | |
| tree | 7c3044dd7ef5ec0e4fe5e6d7716e63193e4bd47f /mysql-test/t/func_group.test | |
| parent | a22ab047e58d4acc62035012252d805e744d4fba (diff) | |
| download | mariadb-git-c9259f166bfcc757338c957f806e3d18637da17a.tar.gz | |
Fixed LP bug #904345.
The MIN/MAX optimizer code from the function opt_sum_query erroneously
did not take into account conjunctive conditions that did not depend on
any table, yet were not identified as constant items. These could be
items containing rand() or PS/SP parameters. These items are supposed
to be evaluated at the execution phase. That's why if such conditions
can be extracted from the WHERE condition the MIN/MAX optimization is
not applied as currently it is always done at the optimization phase.
(In 5.3 expensive subqueries are also evaluated only at the execution
phase. So, if a constant condition with such subquery can be extracted
from the WHERE clause the MIN/MAX optimization should not be applied
in 5.3.)
IF an IN/ALL/SOME predicate with a constant left part is transformed
into an EXISTS subquery the resulting subquery should not be considered
uncacheable if the right part of the predicate is not uncacheable.
Backported the function dbug_print_item() from 5.3. The function is used
only for debugging.
Diffstat (limited to 'mysql-test/t/func_group.test')
| -rw-r--r-- | mysql-test/t/func_group.test | 27 |
1 files changed, 26 insertions, 1 deletions
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 8c2be79ee7d..84afe328aeb 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1150,4 +1150,29 @@ SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9; drop table t1; --echo # ---echo End of 5.1 tests +--echo # Bug #904345: MIN/MAX optimization with constant FALSE condition +--echo # + +CREATE TABLE t1 (a int NOT NULL, KEY(a)); +INSERT INTO t1 VALUES (10), (8), (11), (7), (15), (12), (9); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES + (8,2), (6,9), (8,4), (5,3), (9,1); + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10; +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10; + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; +SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; + +EXPLAIN EXTENDED +SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10; +SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10; + +DROP TABLE t1,t2; + +--echo # +--echo End of 5.2 tests |