maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04700
Re: Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/
On Sun, May 13, 2012 at 02:11:15PM +0300, Timour Katchaounov wrote:
> Sergey,
>
> At the optimizer call this Tuesday Igor suggested that you
> should review the patch for this bug. The commit message
> contains a description of the patch. Let me know if you believe
> a more detailed description is needed.
>
> The patch is also pushed into the following tree:
> lp:~maria-captains/maria/5.5-timour
>
>
> Thanks,
> Timour
>
> ------------------------------------------------------------
> revno: 3403
> revision-id: timour@xxxxxxxxxxxx-20120511152503-zbjewctjclx0sajt
> parent: knielsen@xxxxxxxxxxxxxxx-20120508122744-v5okh33kuolot3kb
> fixes bug(s): https://launchpad.net/bugs/944706
> committer: timour@xxxxxxxxxxxx
> branch nick: 5.5-lpb944706
> timestamp: Fri 2012-05-11 18:25:03 +0300
> message:
> Fix for bug lp:944706, task MDEV-193
>
> The patch enables back constant subquery execution during
> query optimization after it was disabled during the development
> of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION).
>
> The main idea is that constant subqueries are allowed to be executed
> during optimization if their execution is not expensive.
>
> The approach is as follows:
> - Constant subqueries are recursively optimized in the beginning of
> JOIN::optimize of the outer query. This is done by the new method
> JOIN::optimize_constant_subqueries(). This is done so that the cost
> of executing these queries can be estimated.
> - Optimization of the outer query proceeds normally. During this phase
> the optimizer may request execution of non-expensive constant subqueries.
> Each place where the optimizer may potentially execute an expensive
> expression is guarded with the predicate Item::is_expensive().
> - The implementation of Item_subselect::is_expensive has been extended
> to use the number of examined rows (estimated by the optimizer) as a
> way to determine whether the subquery is expensive or not.
> - The new system variable "expensive_subquery_limit" controls how many
> examined rows are considered to be not expensive. The default is 100.
>
> In addition, multiple changes were needed to make this solution work
> in the light of the changes made by MWL#89. These changes were needed
> to fix various crashes and wrong results, and legacy bugs discovered
> during development.
> === modified file 'mysql-test/r/explain.result'
> --- a/mysql-test/r/explain.result 2012-01-13 14:50:02 +0000
> +++ b/mysql-test/r/explain.result 2012-05-11 15:25:03 +0000
> @@ -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.
>
> === modified file 'mysql-test/r/group_min_max.result'
> --- a/mysql-test/r/group_min_max.result 2012-03-01 22:22:22 +0000
> +++ b/mysql-test/r/group_min_max.result 2012-05-11 15:25:03 +0000
> @@ -2398,12 +2398,12 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
> (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 JO
> 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
>
> === modified file 'mysql-test/r/join_outer.result'
> --- a/mysql-test/r/join_outer.result 2012-04-07 13:58:46 +0000
> +++ b/mysql-test/r/join_outer.result 2012-05-11 15:25:03 +0000
> @@ -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
>
> === modified file 'mysql-test/r/join_outer_jcl6.result'
> --- a/mysql-test/r/join_outer_jcl6.result 2012-04-07 13:58:46 +0000
> +++ b/mysql-test/r/join_outer_jcl6.result 2012-05-11 15:25:03 +0000
> @@ -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
>
> === modified file 'mysql-test/r/key.result'
> --- a/mysql-test/r/key.result 2011-10-19 19:45:18 +0000
> +++ b/mysql-test/r/key.result 2012-05-11 15:25:03 +0000
> @@ -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
>
> === modified file 'mysql-test/r/limit_rows_examined.result'
> --- a/mysql-test/r/limit_rows_examined.result 2012-03-11 22:45:18 +0000
> +++ b/mysql-test/r/limit_rows_examined.result 2012-05-11 15:25:03 +0000
> @@ -679,7 +679,7 @@ id select_type table type possible_keys
> 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;
>
> === modified file 'mysql-test/r/myisam_mrr.result'
> --- a/mysql-test/r/myisam_mrr.result 2012-02-25 15:13:24 +0000
> +++ b/mysql-test/r/myisam_mrr.result 2012-05-11 15:25:03 +0000
> @@ -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
>
> === modified file 'mysql-test/r/mysqld--help.result'
> --- a/mysql-test/r/mysqld--help.result 2012-04-10 06:28:13 +0000
> +++ b/mysql-test/r/mysqld--help.result 2012-05-11 15:25:03 +0000
> @@ -159,6 +159,9 @@
> 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 examines in order
> + to be considered non-expensive
> --expire-logs-days=#
> If non-zero, binary logs will be purged after
> expire_logs_days days; possible purges happen at startup
> @@ -885,6 +888,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
>
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/r/subselect.result 2012-05-11 15:25:03 +0000
> @@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
> 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
> 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 FR
> 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 (sele
> 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
> 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
> 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
> 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 exist
> ((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
> 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
> 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_swit
> 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;
> #
>
> === modified file 'mysql-test/r/subselect3.result'
> --- a/mysql-test/r/subselect3.result 2012-04-07 13:58:46 +0000
> +++ b/mysql-test/r/subselect3.result 2012-05-11 15:25:03 +0000
> @@ -1479,7 +1479,7 @@ id select_type table type possible_keys
> 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 ),
>
> === modified file 'mysql-test/r/subselect3_jcl6.result'
> --- a/mysql-test/r/subselect3_jcl6.result 2012-04-07 13:58:46 +0000
> +++ b/mysql-test/r/subselect3_jcl6.result 2012-05-11 15:25:03 +0000
> @@ -1489,7 +1489,7 @@ id select_type table type possible_keys
> 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 ),
>
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result 2012-03-01 22:22:22 +0000
> +++ b/mysql-test/r/subselect4.result 2012-05-11 15:25:03 +0000
> @@ -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
> 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_exis
> 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
>
> === modified file 'mysql-test/r/subselect_cache.result'
> --- a/mysql-test/r/subselect_cache.result 2012-02-15 17:08:08 +0000
> +++ b/mysql-test/r/subselect_cache.result 2012-05-11 15:25:03 +0000
> @@ -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;
>
> === modified file 'mysql-test/r/subselect_innodb.result'
> --- a/mysql-test/r/subselect_innodb.result 2012-01-13 14:50:02 +0000
> +++ b/mysql-test/r/subselect_innodb.result 2012-05-11 15:25:03 +0000
> @@ -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 >= (
>
> === modified file 'mysql-test/r/subselect_mat.result'
> --- a/mysql-test/r/subselect_mat.result 2012-03-17 08:26:58 +0000
> +++ b/mysql-test/r/subselect_mat.result 2012-05-11 15:25:03 +0000
> @@ -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
> 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
> 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=
> # 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
> 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
> 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=
> 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
>
> === modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
> --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-01-13 14:50:02 +0000
> +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-11 15:25:03 +0000
> @@ -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
>
> === modified file 'mysql-test/r/subselect_no_mat.result'
> --- a/mysql-test/r/subselect_no_mat.result 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/r/subselect_no_mat.result 2012-05-11 15:25:03 +0000
> @@ -378,12 +378,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
> 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
> 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 FR
> 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 (sele
> 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
> 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
> 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
> 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 exist
> ((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
> 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
> 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
> 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_swit
> 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;
> #
>
> === modified file 'mysql-test/r/subselect_no_opts.result'
> --- a/mysql-test/r/subselect_no_opts.result 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/r/subselect_no_opts.result 2012-05-11 15:25:03 +0000
> @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
> 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
> 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 FR
> 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 (sele
> 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
> 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
> 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
> 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 exist
> ((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
> 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
> 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
> 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_swit
> 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;
> #
>
> === modified file 'mysql-test/r/subselect_no_scache.result'
> --- a/mysql-test/r/subselect_no_scache.result 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/r/subselect_no_scache.result 2012-05-11 15:25:03 +0000
> @@ -377,12 +377,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
> 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
> 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 FR
> 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 (sele
> 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
> 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
> 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
> 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 exist
> ((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
> 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
> 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_swit
> 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;
> #
>
> === modified file 'mysql-test/r/subselect_no_semijoin.result'
> --- a/mysql-test/r/subselect_no_semijoin.result 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/r/subselect_no_semijoin.result 2012-05-11 15:25:03 +0000
> @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
> 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
> 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 FR
> 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 (sele
> 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
> 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
> 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
> 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 exist
> ((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
> 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
> 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_swit
> 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;
> #
>
> === modified file 'mysql-test/r/subselect_sj_mat.result'
> --- a/mysql-test/r/subselect_sj_mat.result 2012-04-07 13:58:46 +0000
> +++ b/mysql-test/r/subselect_sj_mat.result 2012-05-11 15:25:03 +0000
> @@ -1197,8 +1197,8 @@ set @@optimizer_switch=@optimizer_switch
> 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
> 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
> 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
> 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
>
> === modified file 'mysql-test/suite/sys_vars/r/all_vars.result'
> --- a/mysql-test/suite/sys_vars/r/all_vars.result 2012-03-09 07:06:59 +0000
> +++ b/mysql-test/suite/sys_vars/r/all_vars.result 2012-05-11 15:25:03 +0000
> @@ -10,5 +10,6 @@ where length(variable_name) > 50;
> 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;
>
> === modified file 'mysql-test/t/subselect.test'
> --- a/mysql-test/t/subselect.test 2012-05-04 05:16:38 +0000
> +++ b/mysql-test/t/subselect.test 2012-05-11 15:25:03 +0000
> @@ -304,6 +304,7 @@ SELECT (SELECT numeropost FROM t1 HAVING
> 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');
>
> === modified file 'mysql-test/t/subselect4.test'
> --- a/mysql-test/t/subselect4.test 2012-03-01 22:22:22 +0000
> +++ b/mysql-test/t/subselect4.test 2012-05-11 15:25:03 +0000
> @@ -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 #
>
> === modified file 'sql/item.h'
> --- a/sql/item.h 2012-04-10 06:28:13 +0000
> +++ b/sql/item.h 2012-05-11 15:25:03 +0000
> @@ -1395,21 +1395,21 @@ class Item {
> {
> 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 (infinite cost).
Could you elaborate more on the "infinite cost" part? (let's discuss it on irc)
> */
> virtual bool is_expensive()
> {
>
> === modified file 'sql/item_cmpfunc.cc'
> --- a/sql/item_cmpfunc.cc 2012-03-28 18:25:31 +0000
> +++ b/sql/item_cmpfunc.cc 2012-05-11 15:25:03 +0000
> @@ -5539,7 +5539,8 @@ void Item_equal::add_const(Item *c, Item
> else
> {
> Item_func_eq *func= new Item_func_eq(c, const_item);
> - func->set_cmp_func();
> + if(func->set_cmp_func())
> + return;
Please add space after "if",
Please add a comment about what kind of condition can cause the problem. As
far as I understand, this is a kind of error?
> func->quick_fix_field();
> cond_false= !func->val_int();
> }
>
> === modified file 'sql/item_cmpfunc.h'
> --- a/sql/item_cmpfunc.h 2012-05-04 05:16:38 +0000
> +++ b/sql/item_cmpfunc.h 2012-05-11 15:25:03 +0000
> @@ -370,9 +370,9 @@ class Item_bool_func2 :public Item_int_f
> 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; }
>
> === modified file 'sql/item_strfunc.h'
> --- a/sql/item_strfunc.h 2012-03-06 19:46:07 +0000
> +++ b/sql/item_strfunc.h 2012-05-11 15:25:03 +0000
> @@ -829,7 +829,7 @@ class Item_func_conv_charset :public Ite
> {
> 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);
>
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/item_subselect.cc 2012-05-11 15:25:03 +0000
> @@ -522,6 +522,48 @@ void Item_subselect::recalc_used_tables(
> */
> }
>
> +
> +/**
> + 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)
> {
>
> === modified file 'sql/item_subselect.h'
> --- a/sql/item_subselect.h 2012-05-04 05:16:38 +0000
> +++ b/sql/item_subselect.h 2012-05-11 15:25:03 +0000
> @@ -191,6 +191,7 @@ class Item_subselect :public Item_result
> table_map used_tables() const;
> table_map not_null_tables() const { return 0; }
> bool const_item() const;
> + virtual bool const_pred() const { return const_item(); }
What is this function for? It seems to have the same semantics as const_item(),
but it is a separate function, which implies there is some difference. Could
you add a comment about this?
> inline table_map get_used_tables_cache() { return used_tables_cache; }
> Item *get_tmp_table_item(THD *thd);
> void update_used_tables();
> @@ -209,7 +210,7 @@ class Item_subselect :public Item_result
> */
> 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 +236,7 @@ class Item_subselect :public Item_result
> @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 +582,7 @@ class Item_in_subselect :public Item_exi
> 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_pred() const { return const_item() && left_expr->const_item(); }
> void update_used_tables();
> bool setup_mat_engine();
> bool init_left_expr_cache();
>
> === modified file 'sql/opt_subselect.cc'
> --- a/sql/opt_subselect.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/opt_subselect.cc 2012-05-11 15:25:03 +0000
> @@ -4872,7 +4872,43 @@ static void remove_subq_pushed_predicate
>
> 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_ma
> 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:
>
> === modified file 'sql/sql_class.h'
> --- a/sql/sql_class.h 2012-03-11 22:45:18 +0000
> +++ b/sql/sql_class.h 2012-05-11 15:25:03 +0000
> @@ -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;
>
> === modified file 'sql/sql_delete.cc'
> --- a/sql/sql_delete.cc 2012-03-24 17:21:22 +0000
> +++ b/sql/sql_delete.cc 2012-05-11 15:25:03 +0000
> @@ -120,7 +120,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
> }
>
> /* 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());
>
> === modified file 'sql/sql_lex.cc'
> --- a/sql/sql_lex.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/sql_lex.cc 2012-05-11 15:25:03 +0000
> @@ -3405,7 +3405,23 @@ bool st_select_lex::add_index_hint (THD
> }
>
>
> -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
> {
> 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_pred())
> + {
> + /* 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
>
> === modified file 'sql/sql_lex.h'
> --- a/sql/sql_lex.h 2012-04-10 06:28:13 +0000
> +++ b/sql/sql_lex.h 2012-05-11 15:25:03 +0000
> @@ -997,12 +997,7 @@ class st_select_lex: public st_select_le
>
> 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);
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/sql_select.cc 2012-05-11 15:25:03 +0000
> @@ -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,36 @@ void JOIN::get_prefix_cost_and_fanout(ui
>
>
> /**
> + 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;
> +
> + if (table_count == const_tables)
> + return examined_rows;
> +
> + examined_rows+= join_tab[i++].get_examined_rows();
> + for (; i < table_count ; i++)
> + {
> + if (join_tab[i].type == JT_EQ_REF)
> + prev_fanout= 1;
> + else
> + prev_fanout= best_positions[i-1].records_read;
This looks wrong. Declaration of POSITION::records_read has this comment:
/*
The "fanout": number of output rows that will be produced (after
pushed down selection condition is applied) per each row combination of
previous tables.
*/
note the "PER EACH ROW COMBINATION .." part. I would expect that this function
would calculate a product of records_read values.
> + 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 +8056,15 @@ JOIN::make_simple_join(JOIN *parent, TAB
> 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 +10249,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 +11273,9 @@ static bool check_simple_equality(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 +11368,9 @@ static bool check_row_equality(THD *thd,
> 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 +12056,8 @@ Item *eliminate_item_equal(COND *cond, C
>
> 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 +12144,7 @@ Item *eliminate_item_equal(COND *cond, C
> 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 +18612,7 @@ check_reverse_order:
> tab->ref.key_parts= 0;
> if (select_limit < table->file->stats.records)
> tab->limit= select_limit;
> + table->disable_keyread();
^^^ This change looks weird. Let's discuss it.
> }
> }
> else if (tab->type != JT_ALL)
> @@ -21269,10 +21338,17 @@ static void select_describe(JOIN *join,
> }
> 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 +21506,8 @@ static void select_describe(JOIN *join,
> }
> 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));
>
>
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h 2012-05-04 05:16:38 +0000
> +++ b/sql/sql_select.h 2012-05-11 15:25:03 +0000
> @@ -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 @@ class JOIN :public Sql_alloc
> 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 @@ class JOIN :public Sql_alloc
> 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. */
>
> === modified file 'sql/sql_update.cc'
> --- a/sql/sql_update.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/sql_update.cc 2012-05-11 15:25:03 +0000
> @@ -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 &&
>
> === modified file 'sql/sys_vars.cc'
> --- a/sql/sys_vars.cc 2012-04-19 14:00:13 +0000
> +++ b/sql/sys_vars.cc 2012-05-11 15:25:03 +0000
> @@ -3780,4 +3780,9 @@ static Sys_var_ulong Sys_debug_binlog_fs
> 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 examines in order to be "
> + "considered non-expensive",
> + SESSION_VAR(expensive_subquery_limit), CMD_LINE(REQUIRED_ARG),
> + VALID_RANGE(0, HA_POS_ERROR), DEFAULT(100), BLOCK_SIZE(1));
>
> === modified file 'sql/table.cc'
> --- a/sql/table.cc 2012-05-04 05:16:38 +0000
> +++ b/sql/table.cc 2012-05-11 15:25:03 +0000
> @@ -5985,7 +5985,8 @@ void TABLE::use_index(int key_to_save)
>
> bool TABLE::is_filled_at_execution()
> {
> - return test(pos_in_table_list->jtbm_subselect ||
> + return test(!pos_in_table_list ||
> + pos_in_table_list->jtbm_subselect ||
> pos_in_table_list->is_active_sjm());
Could you please add a comment for pos_in_table_list that it can be NULL for
some tables? (I assume you still remember what kind of tables have
pos_in_table_list==NULL?)
> }
>
>
A general question: was this expected to work:
MariaDB [j3]> explain extended select * from t1 where 33 in (select b from
five) or c > 11;
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | filtered | Extra |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL |
NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | five | ALL | NULL | NULL | NULL |
NULL | 5 | 100.00 | Using where |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (22 min 25.44 sec)
The subuqery is constant, table `five` has 5 records, and none of them has
b=33. When debugging, I see JOIN::get_examined_rows() to be invoked and it
returns 5 rows, but EXPLAIN still doesn't show "Impossible WHERE"?
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
Follow ups
