diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2012-08-27 18:13:17 +0200 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2012-08-27 18:13:17 +0200 |
| commit | 9a64d0794c3dce1652839820350a8c4b9b510e99 (patch) | |
| tree | 33ebd07354a15944288a071bd9319dfa34f226ef /mysql-test/r/subselect_no_mat.result | |
| parent | ced3907c02dfa3b237e14d79aa800b3a0769e94a (diff) | |
| parent | e988c4dde6c1b88523b1d4e748cbd4f5025b128d (diff) | |
| download | mariadb-git-9a64d0794c3dce1652839820350a8c4b9b510e99.tar.gz | |
5.3 merge
Diffstat (limited to 'mysql-test/r/subselect_no_mat.result')
| -rw-r--r-- | mysql-test/r/subselect_no_mat.result | 30 |
1 files changed, 27 insertions, 3 deletions
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 48ab96dcaaa..87402a23a99 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6611,7 +6611,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U min_a a drop table t1; # -# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +# MDEV-367: Different results with and without subquery_cache on +# a query with a constant NOT IN condition +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2),(3); +set @mdev367_optimizer_switch = @@optimizer_switch; +set optimizer_switch = 'subquery_cache=on'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +set optimizer_switch = 'subquery_cache=off'; +SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100; +a +SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1; +a ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) +1 NULL +2 NULL +3 NULL +set optimizer_switch=@mdev367_optimizer_switch; +DROP TABLE t1; +# +# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not # precomputed and thus not part of optimization # CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); @@ -6804,8 +6830,6 @@ Table_schema Table_name Index_name Rows_read test t2 b 1 set global userstat=@tmp_mdev410; DROP TABLE t1,t2,t3,t4; -# return optimizer switch changed in the beginning of this test -set optimizer_switch=@subselect_tmp; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' |