diff options
40 files changed, 714 insertions, 327 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index a6fa21ad800..863526c637e 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -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/join_outer.result b/mysql-test/r/join_outer.result index ebe8255fd8b..49a6a0d76d3 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1871,10 +1871,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 DEPENDENT 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 3d0e4cbfc79..d1333136f1c 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1882,10 +1882,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 DEPENDENT 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/subselect.result b/mysql-test/r/subselect.result index dca872f0bbd..a75d0f960af 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -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'; @@ -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,7 +3096,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -3111,7 +3108,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -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 @@ -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 @@ -5960,7 +5957,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) @@ -6121,7 +6118,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; # @@ -6562,5 +6559,40 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE COUNT(f1) f4 0 0 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 DEPENDENT 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 DEPENDENT 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; # 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 82360f9fe9f..c17ffe75282 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -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,8 +1257,7 @@ 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 DEPENDENT 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 SELECT COUNT(t2.f3), @@ -1267,8 +1273,7 @@ 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 DEPENDENT 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 SELECT COUNT(t2.f3), @@ -1277,7 +1282,7 @@ 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,8 +1290,7 @@ 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 DEPENDENT 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 SELECT COUNT(t2.f3), @@ -1302,8 +1306,7 @@ 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 DEPENDENT 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 SELECT COUNT(t2.f3), @@ -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 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 2 DEPENDENT 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_innodb.result b/mysql-test/r/subselect_innodb.result index 60faea8853f..bbaf26e6249 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -272,8 +272,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 = ( @@ -301,7 +301,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 >= ( diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 86442df1e8b..323da97cc89 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 @@ -2153,7 +2153,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); @@ -2222,10 +2222,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 @@ -2233,10 +2233,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 DEPENDENT 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_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 96f904c722c..e656bf8e22f 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 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 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 d5c7084c48d..507c8741d25 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -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'; @@ -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,7 +3102,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -3117,7 +3114,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -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 DEPENDENT 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 DEPENDENT 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; # @@ -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 @@ -5961,7 +5958,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) @@ -6120,7 +6117,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; # @@ -6561,6 +6558,41 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE COUNT(f1) f4 0 0 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 DEPENDENT 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 DEPENDENT 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; # 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 7b44b1a1f5d..9ca09ac061c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -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,7 +3098,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -3113,7 +3110,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -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 DEPENDENT 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 DEPENDENT 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; # @@ -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 @@ -5957,7 +5954,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) @@ -6116,7 +6113,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; # @@ -6557,6 +6554,41 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE COUNT(f1) f4 0 0 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 DEPENDENT 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 DEPENDENT 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; # 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 9b24af0dc3a..f7a8e8efd01 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -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'; @@ -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,7 +3102,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -3117,7 +3114,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -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 @@ -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 @@ -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) @@ -6127,7 +6124,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; # @@ -6568,6 +6565,41 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE COUNT(f1) f4 0 0 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 DEPENDENT 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 DEPENDENT 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; # 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 ba42ff881fc..f0186c682a4 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -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,7 +3098,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -3113,7 +3110,7 @@ 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 +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 2 DEPENDENT 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' @@ -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 DEPENDENT 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 DEPENDENT 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; # @@ -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 @@ -5957,7 +5954,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) @@ -6116,7 +6113,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; # @@ -6557,6 +6554,41 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE COUNT(f1) f4 0 0 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 DEPENDENT 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 DEPENDENT 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; # 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 dc1d9b407fd..d329c31fd16 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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 DEPENDENT 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/subselect.test b/mysql-test/t/subselect.test index 6e98c064d94..d649c300ab5 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'); @@ -5529,5 +5530,36 @@ SELECT COUNT(f1), f2 IN (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 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 # 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 8915cdd6500..9ec282ba3db 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 # diff --git a/sql/item.h b/sql/item.h index b3801e6c488..df220118745 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1395,21 +1395,21 @@ public: { return cmp_context == IMPOSSIBLE_RESULT || item->cmp_context == cmp_context; } - /* + /** Test whether an expression is expensive to compute. Used during optimization to avoid computing expensive expressions during this phase. Also used to force temp tables when sorting on expensive functions. - TODO: + @todo Normally we should have a method: cost Item::execution_cost(), where 'cost' is either 'double' or some structure of various cost parameters. - NOTE - This function is now used to prevent evaluation of materialized IN - subquery predicates before it is allowed. grep for - DontEvaluateMaterializedSubqueryTooEarly to see the uses. + @note + This function is now used to prevent evaluation of expensive subquery + predicates during the optimization phase. It also prevents evaluation + of predicates that are not computable at this moment. */ virtual bool is_expensive() { diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index d3251bc1fb3..b45b138ced2 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5539,7 +5539,15 @@ void Item_equal::add_const(Item *c, Item *f) else { Item_func_eq *func= new Item_func_eq(c, const_item); - func->set_cmp_func(); + if (func->set_cmp_func()) + { + /* + Setting a comparison function fails when trying to compare + incompatible charsets. Charset compatibility is checked earlier, + except for constant subqueries where we may do it here. + */ + return; + } func->quick_fix_field(); cond_false= !func->val_int(); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index b9740c7af03..875fdc3b34f 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -370,9 +370,9 @@ public: Item_bool_func2(Item *a,Item *b) :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {} void fix_length_and_dec(); - void set_cmp_func() + int set_cmp_func() { - cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE); + return cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE); } optimize_type select_optimize() const { return OPTIMIZE_OP; } virtual enum Functype rev_functype() const { return UNKNOWN_FUNC; } diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index ad854b02765..b71408bd5f5 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -829,7 +829,7 @@ public: { DBUG_ASSERT(args[0]->fixed); conv_charset= cs; - if (cache_if_const && args[0]->const_item() && !args[0]->with_subselect) + if (cache_if_const && args[0]->const_item() && !args[0]->is_expensive()) { uint errors= 0; String tmp, *str= args[0]->val_str(&tmp); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 276f35fe301..1367037d17e 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -522,6 +522,48 @@ void Item_subselect::recalc_used_tables(st_select_lex *new_parent, */ } + +/** + Determine if a subquery is expensive to execute during query optimization. + + @details The cost of execution of a subquery is estimated based on an + estimate of the number of rows the subquery will access during execution. + This measure is used instead of JOIN::read_time, because it is considered + to be much more reliable than the cost estimate. + + @return true if the subquery is expensive + @return false otherwise +*/ +bool Item_subselect::is_expensive() +{ + double examined_rows= 0; + + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) + { + JOIN *cur_join= sl->join; + if (!cur_join) + continue; + + /* If a subquery is not optimized we cannot estimate its cost. */ + if (!cur_join->join_tab) + return true; + + if (sl->first_inner_unit()) + { + /* + Subqueries that contain subqueries are considered expensive. + @todo: accumulate the cost of subqueries. + */ + return true; + } + + examined_rows+= cur_join->get_examined_rows(); + } + + return (examined_rows > thd->variables.expensive_subquery_limit); +} + + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, uchar *argument) { diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 1a4c8a25a01..746813407c2 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -209,7 +209,7 @@ public: */ bool is_evaluated() const; bool is_uncacheable() const; - bool is_expensive() { return TRUE; } + bool is_expensive(); /* Used by max/min subquery to initialize value presence registration @@ -235,7 +235,7 @@ public: @retval TRUE if the predicate is expensive @retval FALSE otherwise */ - bool is_expensive_processor(uchar *arg) { return TRUE; } + bool is_expensive_processor(uchar *arg) { return is_expensive(); } /** Get the SELECT_LEX structure associated with this Item. @@ -581,6 +581,10 @@ public: bool fix_fields(THD *thd, Item **ref); void fix_length_and_dec(); void fix_after_pullout(st_select_lex *new_parent, Item **ref); + bool const_item() const + { + return Item_subselect::const_item() && left_expr->const_item(); + } void update_used_tables(); bool setup_mat_engine(); bool init_left_expr_cache(); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 9bf1aaf4039..3e21187e6fa 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -4872,7 +4872,43 @@ static void remove_subq_pushed_predicates(JOIN *join, Item **where) bool JOIN::optimize_unflattened_subqueries() { - return select_lex->optimize_unflattened_subqueries(); + return select_lex->optimize_unflattened_subqueries(false); +} + +/** + Optimize all constant subqueries of a query that were not flattened into + a semijoin. + + @details + Similar to other constant conditions, constant subqueries can be used in + various constant optimizations. Having optimized constant subqueries before + these constant optimizations, makes it possible to estimate if a subquery + is "cheap" enough to be executed during the optimization phase. + + Constant subqueries can be optimized and evaluated independent of the outer + query, therefore if const_only = true, this method can be called early in + the optimization phase of the outer query. + + @return Operation status + @retval FALSE success. + @retval TRUE error occurred. +*/ + +bool JOIN::optimize_constant_subqueries() +{ + ulonglong save_options= select_lex->options; + bool res; + /* + Constant subqueries may be executed during the optimization phase. + In EXPLAIN mode the optimizer doesn't initialize many of the data structures + needed for execution. In order to make it possible to execute subqueries + during optimization, constant subqueries must be optimized for execution, + not for EXPLAIN. + */ + select_lex->options&= ~SELECT_DESCRIBE; + res= select_lex->optimize_unflattened_subqueries(true); + select_lex->options= save_options; + return res; } @@ -5273,7 +5309,14 @@ bool JOIN::choose_subquery_plan(table_map join_tables) by the IN predicate. */ outer_join= unit->outer_select() ? unit->outer_select()->join : NULL; - if (outer_join && outer_join->table_count > 0) + /* + Get the cost of the outer join if: + (1) It has at least one table, and + (2) It has been already optimized (if there is no join_tab, then the + outer join has not been optimized yet). + */ + if (outer_join && outer_join->table_count > 0 && // (1) + outer_join->join_tab) // (2) { /* TODO: diff --git a/sql/sql_class.h b/sql/sql_class.h index c6c46975076..ee472e2e7e6 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -482,6 +482,7 @@ typedef struct system_variables ulonglong group_concat_max_len; ha_rows select_limit; ha_rows max_join_size; + ha_rows expensive_subquery_limit; ulong auto_increment_increment, auto_increment_offset; ulong lock_wait_timeout; ulong join_cache_level; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index cc358eca440..5128b1284dd 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -120,7 +120,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ - if (select_lex->optimize_unflattened_subqueries()) + if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); const_cond= (!conds || conds->const_item()); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 6fd6e53424f..fb5307f9e3a 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3405,7 +3405,23 @@ bool st_select_lex::add_index_hint (THD *thd, char *str, uint length) } -bool st_select_lex::optimize_unflattened_subqueries() +/** + Optimize all subqueries that have not been flattened into semi-joins. + + @details + This functionality is a method of SELECT_LEX instead of JOIN because + SQL statements as DELETE/UPDATE do not have a corresponding JOIN object. + + @see JOIN::optimize_unflattened_subqueries + + @param const_only Restrict subquery optimization to constant subqueries + + @return Operation status + @retval FALSE success. + @retval TRUE error occurred. +*/ + +bool st_select_lex::optimize_unflattened_subqueries(bool const_only) { for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit()) { @@ -3415,11 +3431,17 @@ bool st_select_lex::optimize_unflattened_subqueries() { if (subquery_predicate->substype() == Item_subselect::IN_SUBS) { - Item_in_subselect *in_subs=(Item_in_subselect*)subquery_predicate; + Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate; if (in_subs->is_jtbm_merged) continue; } + if (const_only && !subquery_predicate->const_item()) + { + /* Skip non-constant subqueries if the caller asked so. */ + continue; + } + bool empty_union_result= true; /* If the subquery is a UNION, optimize all the subqueries in the UNION. If diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f1ee6cf22ec..922386151f8 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -997,12 +997,7 @@ public: void clear_index_hints(void) { index_hints= NULL; } bool is_part_of_union() { return master_unit()->is_union(); } - /* - Optimize all subqueries that have not been flattened into semi-joins. - This functionality is a method of SELECT_LEX instead of JOIN because - some SQL statements as DELETE do not have a corresponding JOIN object. - */ - bool optimize_unflattened_subqueries(); + bool optimize_unflattened_subqueries(bool const_only); /* Set the EXPLAIN type for this subquery. */ void set_explain_type(); bool handle_derived(LEX *lex, uint phases); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 916e521d3b9..933679a0d9c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -986,7 +986,10 @@ JOIN::optimize() } eval_select_list_used_tables(); - + + if (optimize_constant_subqueries()) + DBUG_RETURN(1); + table_count= select_lex->leaf_tables.elements; if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ @@ -1273,6 +1276,12 @@ JOIN::optimize() { conds= substitute_for_best_equal_field(NO_PARTICULAR_TAB, conds, cond_equal, map2table); + if (thd->is_error()) + { + error= 1; + DBUG_PRINT("error",("Error from substitute_for_best_equal")); + DBUG_RETURN(1); + } conds->update_used_tables(); DBUG_EXECUTE("where", print_where(conds, @@ -1293,6 +1302,12 @@ JOIN::optimize() *tab->on_expr_ref, tab->cond_equal, map2table); + if (thd->is_error()) + { + error= 1; + DBUG_PRINT("error",("Error from substitute_for_best_equal")); + DBUG_RETURN(1); + } (*tab->on_expr_ref)->update_used_tables(); } } @@ -6592,6 +6607,33 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, /** + Estimate the number of rows that query execution will read. + + @todo This is a very pessimistic upper bound. Use join selectivity + when available to produce a more realistic number. +*/ + +double JOIN::get_examined_rows() +{ + /* Each constant table examines one row, and the result is at most one row. */ + ha_rows examined_rows= const_tables; + uint i= const_tables; + double prev_fanout= 1; + + if (table_count == const_tables) + return examined_rows; + + examined_rows+= join_tab[i++].get_examined_rows(); + for (; i < table_count ; i++) + { + prev_fanout *= best_positions[i-1].records_read; + examined_rows+= join_tab[i].get_examined_rows() * prev_fanout; + } + return examined_rows; +} + + +/** Find a good, possibly optimal, query execution plan (QEP) by a possibly exhaustive search. @@ -8011,36 +8053,15 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) row_limit= unit->select_limit_cnt; do_send_rows= row_limit ? 1 : 0; - join_tab->use_join_cache= FALSE; - join_tab->cache=0; /* No caching */ + bzero(join_tab, sizeof(JOIN_TAB)); join_tab->table=temp_table; - join_tab->cache_select= 0; - join_tab->select=0; - join_tab->select_cond= 0; // Avoid valgrind warning join_tab->set_select_cond(NULL, __LINE__); - join_tab->quick=0; join_tab->type= JT_ALL; /* Map through all records */ join_tab->keys.init(); join_tab->keys.set_all(); /* test everything in quick */ - join_tab->info=0; - join_tab->on_expr_ref=0; - join_tab->last_inner= 0; - join_tab->first_unmatched= 0; join_tab->ref.key = -1; - join_tab->not_used_in_distinct=0; join_tab->read_first_record= join_init_read_record; - join_tab->preread_init_done= FALSE; join_tab->join= this; - join_tab->ref.key_parts= 0; - join_tab->keep_current_rowid= FALSE; - join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL; - join_tab->do_firstmatch= NULL; - join_tab->loosescan_match_tab= NULL; - join_tab->emb_sj_nest= NULL; - join_tab->pre_idx_push_select_cond= NULL; - join_tab->bush_root_tab= NULL; - join_tab->bush_children= NULL; - join_tab->last_leaf_in_bush= FALSE; bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); temp_table->status=0; temp_table->null_row=0; @@ -10225,6 +10246,51 @@ double JOIN_TAB::scan_time() return res; } + +/** + Estimate the number of rows that a an access method will read from a table. + + @todo: why not use JOIN_TAB::found_records +*/ + +ha_rows JOIN_TAB::get_examined_rows() +{ + ha_rows examined_rows; + + if (select && select->quick) + examined_rows= select->quick->records; + else if (type == JT_NEXT || type == JT_ALL || + type == JT_HASH || type ==JT_HASH_NEXT) + { + if (limit) + { + /* + @todo This estimate is wrong, a LIMIT query may examine much more rows + than the LIMIT itself. + */ + examined_rows= limit; + } + else + { + if (table->is_filled_at_execution()) + examined_rows= records; + else + { + /* + handler->info(HA_STATUS_VARIABLE) has been called in + make_join_statistics() + */ + examined_rows= table->file->stats.records; + } + } + } + else + examined_rows= (ha_rows) records_read; + + return examined_rows; +} + + /** Initialize the join_tab before reading. Currently only derived table/view materialization is done here. @@ -11204,9 +11270,9 @@ static bool check_simple_equality(Item *left_item, Item *right_item, if (!item) { Item_func_eq *eq_item; - if ((eq_item= new Item_func_eq(orig_left_item, orig_right_item))) + if (!(eq_item= new Item_func_eq(orig_left_item, orig_right_item)) || + eq_item->set_cmp_func()) return FALSE; - eq_item->set_cmp_func(); eq_item->quick_fix_field(); item= eq_item; } @@ -11299,9 +11365,9 @@ static bool check_row_equality(THD *thd, Item *left_row, Item_row *right_row, if (!is_converted) { Item_func_eq *eq_item; - if (!(eq_item= new Item_func_eq(left_item, right_item))) + if (!(eq_item= new Item_func_eq(left_item, right_item)) || + eq_item->set_cmp_func()) return FALSE; - eq_item->set_cmp_func(); eq_item->quick_fix_field(); eq_list->push_back(eq_item); } @@ -11987,9 +12053,8 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, eq_item= new Item_func_eq(field_item->real_item(), head_item); - if (!eq_item) + if (!eq_item || eq_item->set_cmp_func()) return 0; - eq_item->set_cmp_func(); eq_item->quick_fix_field(); } current_sjm= field_sjm; @@ -12076,7 +12141,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, Item_equal::get_first() for details. @return - The transformed condition + The transformed condition, or NULL in case of error */ static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, @@ -18544,6 +18609,7 @@ check_reverse_order: tab->ref.key_parts= 0; if (select_limit < table->file->stats.records) tab->limit= select_limit; + table->disable_keyread(); } } else if (tab->type != JT_ALL) @@ -21269,10 +21335,17 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, } else { - TABLE_LIST *real_table= table->pos_in_table_list; - item_list.push_back(new Item_string(real_table->alias, - strlen(real_table->alias), - cs)); + TABLE_LIST *real_table= table->pos_in_table_list; + /* + Internal temporary tables have no corresponding table reference + object. Such a table may appear in EXPLAIN when a subquery that needs + a temporary table has been executed, and JOIN::exec replaced the + original JOIN with a plan to access the data in the temp table + (made by JOIN::make_simple_join). + */ + const char *tab_name= real_table ? real_table->alias : + "internal_tmp_table"; + item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs)); } /* "partitions" column */ if (join->thd->lex->describe & DESCRIBE_PARTITIONS) @@ -21430,32 +21503,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, } else { - ha_rows examined_rows; - if (tab->select && tab->select->quick) - examined_rows= tab->select->quick->records; - else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj) - { - if (tab->limit) - examined_rows= tab->limit; - else - { - if (tab->table->is_filled_at_execution()) - { - examined_rows= tab->records; - } - else - { - /* - handler->info(HA_STATUS_VARIABLE) has been called in - make_join_statistics() - */ - examined_rows= tab->table->file->stats.records; - } - } - } - else - examined_rows=(ha_rows)tab->records_read; - + ha_rows examined_rows= tab->get_examined_rows(); + item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, MY_INT64_NUM_DECIMAL_DIGITS)); diff --git a/sql/sql_select.h b/sql/sql_select.h index 08ddc27e204..ee91928fa15 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -512,6 +512,7 @@ typedef struct st_join_table { return (is_hash_join_key_no(key) ? hj_key : table->key_info+key); } double scan_time(); + ha_rows get_examined_rows(); bool preread_init(); bool is_sjm_nest() { return test(bush_children); } @@ -1281,6 +1282,7 @@ public: bool alloc_func_list(); bool flatten_subqueries(); bool optimize_unflattened_subqueries(); + bool optimize_constant_subqueries(); bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, bool before_group_by, bool recompute= FALSE); @@ -1380,6 +1382,7 @@ public: void get_prefix_cost_and_fanout(uint n_tables, double *read_time_arg, double *record_count_arg); + double get_examined_rows(); /* defined in opt_subselect.cc */ bool transform_max_min_subquery(); /* True if this JOIN is a subquery under an IN predicate. */ diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 7d5fe875d64..f2b6c5c9f92 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -368,7 +368,7 @@ int mysql_update(THD *thd, } /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ - if (select_lex->optimize_unflattened_subqueries()) + if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); if (select_lex->inner_refs_list.elements && diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index ca434821800..65b21273348 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3780,4 +3780,9 @@ static Sys_var_ulong Sys_debug_binlog_fsync_sleep( CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1)); #endif - +static Sys_var_harows Sys_expensive_subquery_limit( + "expensive_subquery_limit", + "The maximum number of rows a subquery may examine in order to be " + "executed during optimization and used for constant optimization", + SESSION_VAR(expensive_subquery_limit), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, HA_POS_ERROR), DEFAULT(100), BLOCK_SIZE(1)); diff --git a/sql/table.cc b/sql/table.cc index 40304dc6fdc..bc7ad0e5831 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5985,7 +5985,13 @@ void TABLE::use_index(int key_to_save) bool TABLE::is_filled_at_execution() { - return test(pos_in_table_list->jtbm_subselect || + /* + pos_in_table_list == NULL for internal temporary tables because they + do not have a corresponding table reference. Such tables are filled + during execution. + */ + return test(!pos_in_table_list || + pos_in_table_list->jtbm_subselect || pos_in_table_list->is_active_sjm()); } |