diff options
| author | unknown <timour@askmonty.org> | 2011-07-15 09:17:22 +0300 |
|---|---|---|
| committer | unknown <timour@askmonty.org> | 2011-07-15 09:17:22 +0300 |
| commit | 06fb641c0b5f918e81172171df1325131619a0a3 (patch) | |
| tree | 3f0ed74923f6c4486941063e089badd58fb012b9 /mysql-test/r/subselect4.result | |
| parent | 03081bc1fd25ab2f414a4559d709d3ef0ec9b4d8 (diff) | |
| parent | 445fcaa81ab19a7e49eeb6252a25c6021f25036b (diff) | |
| download | mariadb-git-06fb641c0b5f918e81172171df1325131619a0a3.tar.gz | |
Automatic merge.
Diffstat (limited to 'mysql-test/r/subselect4.result')
| -rw-r--r-- | mysql-test/r/subselect4.result | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 0929657173f..2098745ac88 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1190,6 +1190,54 @@ set @@optimizer_switch='materialization=off,semijoin=on'; set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; # +# LP BUG#777691 Wrong result with subqery in select list and subquery cache=off in maria-5.3 +# +CREATE TABLE t1 ( f1 varchar(32)) ; +INSERT INTO t1 VALUES ('b'),('x'),('c'),('x'); +CREATE TABLE t2 ( f2 int, f3 varchar(32)) ; +INSERT INTO t2 VALUES (1,'x'); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off'; +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch='materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off'; +Even when t2 is not constant table, the result must be the same. +INSERT INTO t2 VALUES (2,'y'); +EXPLAIN +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.f1, ( SELECT MAX( f2 ) FROM t2 WHERE t2.f3 = t1.f1 ) as max_f2 FROM t1; +f1 max_f2 +b NULL +x 1 +c NULL +x 1 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE) # CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); |