diff options
author | unknown <timour@askmonty.org> | 2012-06-14 23:55:22 +0300 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2012-06-14 23:55:22 +0300 |
commit | cf3a499f541e66e1b8aa96fe16e36a48ed1c0a0e (patch) | |
tree | f355d8c09324971c8c04a36d0b3953b6fd2f384a /mysql-test | |
parent | 28b4aba40a65006af33cc8e1464ab28643442309 (diff) | |
parent | c2677de7aca09a0ba4b680b5227bda3865ab9290 (diff) | |
download | mariadb-git-cf3a499f541e66e1b8aa96fe16e36a48ed1c0a0e.tar.gz |
Merge the fix for lp:944706, mdev-193
Diffstat (limited to 'mysql-test')
32 files changed, 1270 insertions, 347 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index c5376bee756..11216a32e61 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -161,24 +161,24 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; drop tables t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a6fa21ad800..e081b56e6ce 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -225,10 +225,10 @@ INSERT INTO t2 VALUES (NULL), (0); EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL))) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual +Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual DROP TABLE t1, t2; # # Bug#30302: Tables that were optimized away are printed in the @@ -260,7 +260,7 @@ FLUSH TABLES; EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table DROP TABLE t1, t2; # # Bug #48573: difference of index selection between rpm binary and @@ -287,7 +287,7 @@ WHERE t1.f1 GROUP BY t1.f1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a @@ -297,12 +297,12 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 @@ -313,12 +313,12 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY a system NULL NULL NULL NULL 1 -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index c5c5f65df29..95caa6853b8 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2398,12 +2398,12 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 index NULL a 10 NULL 1 Using index +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra @@ -2419,9 +2419,9 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 2 Using where; Using index +1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; id select_type table type possible_keys key key_len ref rows Extra @@ -2756,8 +2756,8 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Using where; Using index -x x x x x x x x x Using where; Using index +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL @@ -2828,8 +2828,8 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Using where; Using index -x x x x x x x x x Using where; Using index +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL @@ -2907,8 +2907,8 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Using where; Using index -x x x x x x x x x Using where; Using index +x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) NULL diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index e5025acc998..b8eda092291 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -654,7 +654,7 @@ EXPLAIN SELECT t1.f1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index -2 DEPENDENT SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index +2 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index DROP TABLE t1,t2; # # BUG#56862/640419: Wrong result with sort_union index merge when one diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index b560c1e5176..bf151a872cf 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1487,7 +1487,7 @@ EXPLAIN SELECT t1.f1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where +2 SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where DROP TABLE t1,t2; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index f16b38772a6..fd2a948847c 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1895,10 +1895,10 @@ EXPLAIN EXTENDED SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1 +Note 1003 select NULL AS `a` from `test`.`t2` where 1 DROP TABLE t1,t2,t3; # # LP bug #817384 Wrong result with outer join + subquery in ON diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index a4aaaf6085d..d891f5c49b2 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1906,10 +1906,10 @@ EXPLAIN EXTENDED SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1 +Note 1003 select NULL AS `a` from `test`.`t2` where 1 DROP TABLE t1,t2,t3; # # LP bug #817384 Wrong result with outer join + subquery in ON diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 4035f61e36b..e215caadb1c 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -598,8 +598,8 @@ VALUES EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index f4242f17a14..3a5212818ef 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -679,7 +679,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Distinct 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 SELECT DISTINCT a AS field1 FROM t1, t2 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20; diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index d4b25f25d40..03d212dbc04 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -349,10 +349,10 @@ WHERE t2.int_key IS NULL GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition Warnings: -Note 1003 select min(1) AS `MIN(t1.pk)` from dual where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`)) +Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; # # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index fcadee7aced..ad55bfa3003 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -159,6 +159,10 @@ The following options may be given as the first argument: Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely deactivated, it cannot be activated run-time) + --expensive-subquery-limit=# + The maximum number of rows a subquery may examine in + order to be executed during optimization and used for + constant optimization --expire-logs-days=# If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup @@ -885,6 +889,7 @@ delayed-queue-size 1000 div-precision-increment 4 engine-condition-pushdown FALSE event-scheduler OFF +expensive-subquery-limit 100 expire-logs-days 0 external-locking FALSE extra-max-connections 1 diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 1bc1ea31671..431c5dda116 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2461,3 +2461,15 @@ SELECT 1 FROM t1 JOIN t1 AS t2 USING (a); 1 1 drop table t1; +# +# LP BUG#1001117 Crash on a simple select that uses a temptable view +# MySQL Bug #12330344 Crash and/or valgrind errors in free_io_cache with join, view, +# partitioned table +# +CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a); +CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS +SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1); +SELECT * FROM vtmp; +1 +DROP VIEW vtmp; +DROP TABLE t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 27131438d16..82a944cc81b 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -160,26 +160,26 @@ execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; set optimizer_switch=@tmp_optimizer_switch; drop tables t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 235d211840f..bb858ecc864 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -51,12 +51,12 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -517,6 +517,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -547,11 +548,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -907,7 +904,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1608,25 +1605,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -1675,34 +1672,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1764,7 +1761,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) +Note 1003 select 'e' AS `s1` from dual where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -3099,8 +3096,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3111,8 +3108,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3163,7 +3160,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3699,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3727,8 +3724,8 @@ explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found -3 UNION t12 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -4580,7 +4577,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4626,7 +4623,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5969,7 +5966,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6130,7 +6127,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # @@ -6542,7 +6539,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6550,7 +6547,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6589,5 +6586,162 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#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) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 014ff5b5810..b33e7e113f2 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1479,7 +1479,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 SELECT * FROM t1 WHERE ( ( SELECT a FROM t2 WHERE a = 9 ), diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index a62048db506..4660cd60603 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1489,7 +1489,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 SELECT * FROM t1 WHERE ( ( SELECT a FROM t2 WHERE a = 9 ), diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 617d2e9e592..1e4b6c9df0a 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -226,10 +226,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual first equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -237,10 +237,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL second equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -248,10 +248,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL DROP TABLE t1,t2; # # BUG#45928 "Differing query results depending on MRR and @@ -562,7 +562,7 @@ WHERE f3 = ( SELECT t1.f3 FROM t1 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f3 f3 5 const 0 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 @@ -577,7 +577,7 @@ WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f3 f3 5 const 0 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 8 func,func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 @@ -1220,6 +1220,13 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary 3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 +WHERE f4 <= ALL +(SELECT max(SQ1_t1.f4) +FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 +GROUP BY SQ1_t1.f4)); +ERROR 21000: Subquery returns more than 1 row drop table t1, t2, t3; # # BUG#52317: Assertion failing in Field_varstring::store() @@ -1250,10 +1257,9 @@ FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1267,17 +1273,16 @@ FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; COUNT(t2.f3) f9 -0 2 +0 NULL EXPLAIN SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 @@ -1285,10 +1290,9 @@ FROM t2 JOIN t1 WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1302,10 +1306,9 @@ FROM t2 JOIN t1 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1325,7 +1328,7 @@ EXPLAIN SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; field1 NULL @@ -1333,7 +1336,7 @@ EXPLAIN SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; field1 NULL @@ -1631,7 +1634,7 @@ SET SESSION optimizer_switch='in_to_exists=on,materialization=off'; EXPLAIN SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t3 system NULL NULL NULL NULL 1 2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); @@ -1852,8 +1855,8 @@ GROUP BY 1, 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 Using temporary; Using filesort 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 MATERIALIZED t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table PREPARE st1 FROM " SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) FROM t2 JOIN t3 ON t3.f4 = t2.f4 diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index e38e8b42e9a..7bd10dc11a6 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -486,9 +486,9 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 -Handler_read_rnd 10 +Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 42 +Handler_read_rnd_next 31 set optimizer_switch='subquery_cache=off'; flush status; select a from t1 ORDER BY (select d from t2 where b=c); @@ -514,9 +514,9 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 -Handler_read_rnd 10 +Handler_read_rnd 0 Handler_read_rnd_deleted 0 -Handler_read_rnd_next 72 +Handler_read_rnd_next 61 set optimizer_switch='subquery_cache=on'; #single value subquery test (distinct ORDER BY) flush status; diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index 773ee9898a4..ba001c71e3f 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -46,7 +46,7 @@ select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0))) @@ -58,7 +58,7 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0))) diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 5ee56b25fee..a0f05a26a46 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -332,8 +332,8 @@ FROM t2 WHERE (SELECT DISTINCT b FROM t3) > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 -3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary SELECT * FROM t1 WHERE t1.a = ( @@ -361,7 +361,7 @@ GROUP BY 1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 SELECT MAX( f1 ) FROM t2 WHERE f2 >= ( @@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort +3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); 1 1 diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 78472e99326..75cc0089983 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1152,8 +1152,8 @@ create table t2 (b1 int); insert into t1 values (5); explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL @@ -1162,8 +1162,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL @@ -1171,8 +1171,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='semijoin=off'; explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL @@ -1181,16 +1181,16 @@ set @@optimizer_switch='materialization=off,in_to_exists=on'; # with MariaDB and MWL#90, this particular case is solved: explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL # but when we go around MWL#90 code, the problem still shows up: explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL @@ -1932,7 +1932,7 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 2 MATERIALIZED s1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 SELECT a, c FROM t1, t2 @@ -1951,8 +1951,8 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index -2 MATERIALIZED s1 hash_ALL NULL #hash#$hj 5 test.s2.d 8 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index +2 MATERIALIZED s1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 SELECT a, c FROM t1, t2 WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 @@ -2166,7 +2166,7 @@ set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_ EXPLAIN SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); @@ -2175,7 +2175,7 @@ EXPLAIN SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1) @@ -2192,7 +2192,7 @@ EXPLAIN SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1) @@ -2235,10 +2235,10 @@ NULL EXPLAIN EXTENDED SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL b 5 NULL 2 100.00 Using index +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,9 in ( <materialize> (select `test`.`t2`.`a` from `test`.`t2` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery2>`.`a`)))))) +Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0 set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); max_res @@ -2246,10 +2246,10 @@ NULL EXPLAIN EXTENDED SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL b 5 NULL 2 100.00 Using index +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,<exists>(select `test`.`t2`.`a` from `test`.`t2` where (9 = `test`.`t2`.`a`)))) +Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0 DROP TABLE t1,t2; # # LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index 893209a07fc..081196a227b 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -496,7 +496,7 @@ from City where City.population > 10000000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY City range Population Population 5 NULL 4 Using index condition; Rowid-ordered scan -2 DEPENDENT SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where +2 SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where select Name, City.id in (select capital from Country where capital is not null) as is_capital from City where City.population > 10000000; diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index ca4aa57417e..635e868dd31 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -124,7 +124,7 @@ FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT t1.* FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); @@ -148,7 +148,7 @@ FROM t2 GROUP BY f1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 system NULL NULL NULL NULL 1 -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort drop table t1, t2, t3; # # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index b08148f8057..84fd6a51f6b 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -58,12 +58,12 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -378,12 +378,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -524,6 +524,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -554,11 +555,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -914,7 +911,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1615,25 +1612,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -1682,34 +1679,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1771,7 +1768,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) +Note 1003 select 'e' AS `s1` from dual where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -3105,8 +3102,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3117,8 +3114,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3169,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3703,7 +3700,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3731,8 +3728,8 @@ explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found -3 UNION t12 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); @@ -4202,8 +4199,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -4531,13 +4528,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 0 SET join_cache_level=@save_join_cache_level; DROP TABLE t1; # @@ -4582,7 +4579,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4628,7 +4625,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5970,7 +5967,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6129,7 +6126,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # @@ -6541,7 +6538,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6549,7 +6546,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6588,6 +6585,162 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#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) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary; Using join buffer (flat, BNL join) +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 End temporary; Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set optimizer_switch=default; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 36d03d8db77..c147271f0f6 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -54,7 +54,7 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -910,7 +907,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1611,25 +1608,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1767,7 +1764,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) +Note 1003 select 'e' AS `s1` from dual where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -3101,8 +3098,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3113,8 +3110,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3165,7 +3162,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3699,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3727,8 +3724,8 @@ explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found -3 UNION t12 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -4527,13 +4524,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 0 SET join_cache_level=@save_join_cache_level; DROP TABLE t1; # @@ -4578,7 +4575,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4624,7 +4621,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5966,7 +5963,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6125,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # @@ -6537,7 +6534,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6545,7 +6542,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6584,6 +6581,163 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#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) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 7fcacfda470..dba1b3f53c0 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -57,7 +57,7 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 @@ -377,12 +377,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -523,6 +523,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -553,11 +554,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -913,7 +910,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1614,25 +1611,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -1681,34 +1678,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1770,7 +1767,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) +Note 1003 select 'e' AS `s1` from dual where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -3105,8 +3102,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3117,8 +3114,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3169,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3705,7 +3702,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3733,8 +3730,8 @@ explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found -3 UNION t12 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); @@ -4204,8 +4201,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -4586,7 +4583,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4632,7 +4629,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5975,7 +5972,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6136,7 +6133,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # @@ -6548,7 +6545,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6556,7 +6553,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6595,6 +6592,163 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#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) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 58 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set optimizer_switch=default; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 405261c9ec6..52b01896ac0 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -54,12 +54,12 @@ ERROR 42S22: Reference 'a' not supported (forward reference in item list) EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than 1 row show warnings; Level Code Message Error 1242 Subquery returns more than 1 row +Error 1028 Sort aborted: Subquery returns more than 1 row drop table t1; create table t1 (a int); insert into t1 values (1),(2),(3); @@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) +ERROR 21000: Subquery returns more than 1 row SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; @@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= any (select b from t2 group by 1); a explain extended select * from t3 where NULL >= any (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 select * from t3 where NULL >= some (select b from t2 group by 1); a explain extended select * from t3 where NULL >= some (select b from t2 group by 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -1767,7 +1764,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 UNION t1 system NULL NULL NULL NULL 1 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) +Note 1003 select 'e' AS `s1` from dual where 1 drop table t1; CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); @@ -3101,8 +3098,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3113,8 +3110,8 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -3699,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -3727,8 +3724,8 @@ explain select * from t1 where not exists ((select t11.i from t1 t11) union (select t12.i from t1 t12)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found -3 UNION t12 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); INSERT INTO t1 VALUES @@ -4525,15 +4522,15 @@ SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where 0 SET join_cache_level=@save_join_cache_level; DROP TABLE t1; # @@ -4578,7 +4575,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4624,7 +4621,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5966,7 +5963,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) @@ -6125,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch; EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -2 SUBQUERY t1 ref a a 5 const 1 Using index +2 SUBQUERY t1 ref a a 5 const 1 DROP TABLE t1; # @@ -6537,7 +6534,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6545,7 +6542,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6584,6 +6581,163 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#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) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); +field +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); +MAX( alias2.a ) +Arden-Arcade +drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; +# +# MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +a +drop table t1,t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index e3f73d3993b..49f15f4ec1f 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1197,8 +1197,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL @@ -1206,8 +1206,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='semijoin=off'; explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL @@ -1223,8 +1223,8 @@ NULL # but when we go around MWL#90 code, the problem still shows up: explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL @@ -1971,7 +1971,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 index c c 5 NULL 8 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index 2 MATERIALIZED s1 ALL c NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 SELECT a, c FROM t1, t2 @@ -1991,8 +1991,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 index c c 5 NULL 8 Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index -2 MATERIALIZED s1 hash_ALL c #hash#$hj 10 const,test.s2.d 8 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index +2 MATERIALIZED s1 hash_ALL c #hash#$hj 5 const 8 Using where; Using join buffer (flat, BNLH join) 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 SELECT a, c FROM t1, t2 WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index 1bd4e394f6a..692545ae0fb 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -10,5 +10,6 @@ there should be *no* long test name listed below: select distinct variable_name as `there should be *no* variables listed below:` from t2 left join t1 on variable_name=test_name where test_name is null; there should be *no* variables listed below: +expensive_subquery_limit drop table t1; drop table t2; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index d6d49c59255..08c028c8224 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2463,3 +2463,16 @@ INSERT INTO t1 VALUES (6,8,10); SELECT 1 FROM t1 JOIN t1 AS t2 USING (a); drop table t1; + +--echo # +--echo # LP BUG#1001117 Crash on a simple select that uses a temptable view +--echo # MySQL Bug #12330344 Crash and/or valgrind errors in free_io_cache with join, view, +--echo # partitioned table +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a); +CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS +SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1); +SELECT * FROM vtmp; +DROP VIEW vtmp; +DROP TABLE t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index b02b1d4a4a0..1061dd7f480 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -304,6 +304,7 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); +-- error ER_SUBQUERY_NO_1_ROW EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); -- error ER_SUBQUERY_NO_1_ROW SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); @@ -5556,5 +5557,116 @@ SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELE drop table t1,t2; +--echo # +--echo # LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not +--echo # precomputed and thus not part of optimization +--echo # + +CREATE TABLE t1 ( a VARCHAR(16), KEY (a) ); +INSERT INTO t1 VALUES ('Abilene'),('Akron'),('Albany'),('Albuquerque'),('Alexandria'),('Allentown'), +('Amarillo'),('Anaheim'),('Anchorage'),('Ann Arbor'),('Arden-Arcade'); + +EXPLAIN +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); + +SELECT MAX( alias2.a ) AS field +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR alias1.a = 'y' +HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); + +EXPLAIN +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); + +SELECT MAX( alias2.a ) +FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 +WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); + +drop table t1; + +--echo # +--echo # MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +--echo # with semijoin+materialization, IN and = subqueries +--echo # + +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); + +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); + +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); + +drop table t1, t2; + +--echo # +--echo # MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +--echo # inner joins takes hundreds times longer +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); + +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); + +set @@expensive_subquery_limit= 0; + +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +flush status; + +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set @@expensive_subquery_limit= default; + +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +flush status; + +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +drop table t1, t2, t3; + +--echo # +--echo # MDEV-288 CHEAP SQ: Valgrind warnings "Memory lost" with IN and EXISTS nested subquery, materialization+semijoin +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(8); + +CREATE TABLE t2 (b INT PRIMARY KEY); +INSERT INTO t2 VALUES (1),(2); + +EXPLAIN +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); +SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1)); + +drop table t1,t2; + + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index e6ff625d3fe..a560148bdd6 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -977,6 +977,14 @@ SELECT * FROM t1 WHERE FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); +--error ER_SUBQUERY_NO_1_ROW +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 + WHERE f4 <= ALL + (SELECT max(SQ1_t1.f4) + FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 + GROUP BY SQ1_t1.f4)); + drop table t1, t2, t3; --echo # |