- developers - lists.mariadb.org

Re: [Maria-developers] Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/
by Sergei Petrunia 17 May '12

17 May '12

On Mon, May 14, 2012 at 11:22:52PM +0300, Timour Katchaounov wrote: > > > /** > > > + 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. > > timour: > > In this function we want to estimate how many rows will be *examined*, not > produced by each JOIN operator. For the estimate I assume a simple nested loops > model, where the JOIN read every row of its right table as many times as many > rows there are in the left operand. The partial join that serves as left > operand, contains records_read rows. This is the multiplier of the number of > rows that will be examined in the right table. > > Of course, for each partial join the join condition will filter a subset of > these rows. > > I agree that blocking algorithms may examine a lot less rows, but it doesn't > make sense to have a very tight bound here. We use an upper bound, because it > is better to miss some constant optimizations, rather than execute very > expensive subqueries here. I was talking about something much more simpler than that. Consider this example: create table ten (a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k (a int); insert into one_k select A.a + 10*B.a + 100*C.a from ten A, ten B, ten C; create table ti1 ( a int); insert into ti1 select a from ten limit 4; alter table ti1 add b int; create table ti2 (a int primary key, b int); create table ti3 (a int primary key, b int); insert into ti2 select a, a from one_k; insert into ti3 select a, a from one_k; MariaDB [test]> explain select * from ten where 3 in (select ti2.b + ti3.b from ti1, ti2, ti3 where ti2.a=ti1.a and ti3.a=ti1.a) or a <5; +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | 1 | PRIMARY | ten | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 2 | DEPENDENT SUBQUERY | ti1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | | 2 | DEPENDENT SUBQUERY | ti3 | eq_ref | PRIMARY | PRIMARY | 4 | test.ti1.a | 1 | | | 2 | DEPENDENT SUBQUERY | ti2 | eq_ref | PRIMARY | PRIMARY | 4 | test.ti1.a | 1 | Using where | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ For this example, JOIN::get_examined_rows() produces 6. The number comes from 4 rows examined in table ti1 (correct) 1 row examined in table ti2 (incorrect, should be 4) 1 row examined in table ti3 (incorrect, should be 4) Do you agree that the number of 6 is incorrect and needs to be fixed? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] KB Question: Check constraints support
by Daniel Bartholomew 15 May '12

15 May '12

The following question has been posted to the Knowledgebase: http://kb.askmonty.org/en/check-constraints-support - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Check constraints support Hi, I have been looking in JIRA, opened bugs, etc. But haven't been able to find an answer. Are check constraints going to be supported? Because they give me the opportunity to give more consistency to my Database, and avoid problems of corruption. The mysql bug is this: http://bugs.mysql.com/bug.php?id=3464 Has been 8 years abandoned, and I expect that you don't do the same, Thank you in advance, txomon - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/
by Sergei Petrunia 14 May '12

14 May '12

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(a)askmonty.org-20120511152503-zbjewctjclx0sajt > parent: knielsen(a)knielsen-hq.org-20120508122744-v5okh33kuolot3kb > fixes bug(s): https://launchpad.net/bugs/944706 > committer: timour(a)askmonty.org > 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

1 1

[Maria-developers] Question about Query plan
by Markus Pilman 14 May '12

14 May '12

Hello all, At ETH Zurich we are working on a new storage engine, that allows us to test several new architectures for transactional databases. So far we worked with MySQL, but we had massive performance issues. After some investigation we figured out, that MySQL generates different query plans for InnoDB than for our engine. One query which killed our performance was the following (this is a query from the TPC-W benchmark): SELECT ol2.ol_i_id, SUM(ol2.ol_qty) AS sum_ol FROM order_line ol, order_line ol2, (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 10000) AS t WHERE ol.ol_o_id = t.o_id AND ol.ol_i_id = 10 AND ol2.ol_o_id = t.o_id AND ol2.ol_i_id <> 10 GROUP BY ol2.ol_i_id ORDER BY sum_ol DESC LIMIT 0,5 MySQL generated the following plan for InnoDB: +----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10000 | Using temporary; Using filesort | | 1 | PRIMARY | ol | ref | orderline_o_id,orderline_i_id | orderline_o_id | 8 | t.o_id | 1 | Using where | | 1 | PRIMARY | ol2 | ref | orderline_o_id,orderline_i_id | orderline_o_id | 8 | tpcw.ol.OL_O_ID | 1 | Using where | | 2 | DERIVED | orders | index | NULL | orders_o_date | 4 | NULL | 10000 | Using index | +----+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ while it generated the following one for our storage engine: +----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+ | 1 | PRIMARY | ol | ref | orderline_o_id,orderline_i_id | orderline_i_id | 5 | const | 10 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ol2 | range | orderline_o_id,orderline_i_id | orderline_i_id | 5 | NULL | 20 | Using where; Using join buffer | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10000 | Using where; Using join buffer | | 2 | DERIVED | orders | index | NULL | orders_o_date | 4 | NULL | 10000 | | +----+-------------+------------+-------+-------------------------------+----------------+---------+-------+-------+----------------------------------------------+ The second one is obviously a very bad one. So we decided to try with MariaDB, which generates the following query plan: +------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ | 1 | PRIMARY | ol | ref | orderline_o_id,orderline_i_id | orderline_i_id | 5 | const | 10 | Using temporary; Using filesort | | 1 | PRIMARY | ol2 | ref | orderline_o_id,orderline_i_id | orderline_o_id | 8 | test.ol.OL_O_ID | 11 | Using where | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | test.ol.OL_O_ID | 10 | | | 2 | DERIVED | orders | index | NULL | orders_o_date | 4 | NULL | 10000 | | +------+-------------+------------+-------+-------------------------------+----------------+---------+-----------------+-------+---------------------------------+ The query plan from MariaDB looks sane to me, and the numbers approve this (the query runs on a middle sized data set about 200 times faster with MariaDB than with MySQL). So we will continue our work with MariaDB. But I have a question to these query plans: why are we getting this differences in MySQL between our storage engine and InnoDB? Is there a feature in our storage engine missing (we first thought we need the ability to support HA_KEYREAD_ONLY - but implementing this feature did not change the query plan)? Or does MySQL some kind of "cheating"? We should understand this issue to be able to present our results we get later (may be we will compare MariaDB and MySQL, but in a paper we would have to explain why MySQL sucks that much). And btw: good work with MariaDB!! The optimizer seems to do a much better job than MySQL - even with InnoDB/XtraDB (we had to rewrite some queries in MySQL to force it to generate sane query plans - with MariaDB this does not seem to be necessary anymore). Thanks for your help in advance and best regards Markus

2 4

Re: [Maria-developers] Review request for: [Commits] Rev 3403: Fix for bug lp:944706, task MDEV-193 in file:///home/tsk/mprog/src/5.5-lpb944706/
by Sergei Petrunia 14 May '12

14 May '12

On Sun, May 13, 2012 at 02:11:15PM +0300, Timour Katchaounov wrote: > === 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)); "considered non-expensive" may not mean much for the outside user. Please change the wording to explicitly say that subqueries that enumerate less than expensive_subquery_limit rows may be evaluated at the optimization phase. Which version will this fix be pushed to? 5.5-ga? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] federated bug: Bug #61526 create table .. like .. federated table crashes with auto_increment column
by xiaobing jiang 14 May '12

14 May '12

hi all: I try to fix the bug: http://bugs.mysql.com/bug.php?id=61526. but after dig the code, I found that the federated seems don't support "create table xx like yy". the function miss the "share->connect_string", so it always report: ERROR 1 (HY000): server name: '' doesn't exist! from the page: http://kb.askmonty.org/en/about-federatedx, it says: The FederatedX Storage Engine is a fork of the Federated Storage Engine, the latter of the two no longer being maintained by Oracle. The purpose of FederatedX is to keep this storage engine's development progressing-- to both add new features as well as fix old bugs. is it really? so will oracle accept the patch? Thanks!

3 4

Re: [Maria-developers] Added --continue-on-error to mysqltest and mysql-test-run in lp:maria/5.5
by Sergei Golubchik 12 May '12

12 May '12

Hi, Monty! On May 04, Michael Widenius wrote: > At lp:maria/5.5 > > ------------------------------------------------------------ > revno: 3393 > revision-id: monty(a)askmonty.org-20120504133709-w1l2t1vhvpqupu3v > parent: monty(a)askmonty.org-20120503130041-fb9myku0uh1qybty > committer: Michael Widenius <monty(a)askmonty.org> > branch nick: maria-5.5 > timestamp: Fri 2012-05-04 16:37:09 +0300 > message: > Added --continue-on-error to mysqltest and mysql-test-run > This will contune the test case even if there was an error. This makes it easier to run a test that contains many sub tests against one engine. First, you forgot to test the new feature. see mysqltest.test for examples > === modified file 'client/mysqltest.cc' > --- a/client/mysqltest.cc 2012-02-23 06:50:11 +0000 > +++ b/client/mysqltest.cc 2012-05-04 13:37:09 +0000 > @@ -1746,9 +1791,15 @@ static int diff_check(const char *diff_n > my_snprintf(buf, sizeof(buf), "%s -v", diff_name); > > if (!(res_file= popen(buf, "r"))) > - die("popen(\"%s\", \"r\") failed", buf); > + { > + report_or_die("popen(\"%s\", \"r\") failed", buf); > + return; > + } I doubt it makes sense to continue in this case > - /* if diff is not present, nothing will be in stdout to increment have_diff */ > + /* > + if diff is not present, nothing will be in stdout to increment > + have_diff > + */ > if (fgets(buf, sizeof(buf), res_file)) > have_diff= 1; > > @@ -2069,9 +2120,13 @@ void check_result() > > switch (compare_files(log_file.file_name(), result_file_name)) { > case RESULT_OK: > - break; /* ok */ > + if (!error_count) > + break; /* ok */ > + mess= "Got errors while running test"; I wonder whether you need it. As the error goes into the result, it'll inevitably end up in the .reject file. one can get RESULT_OK only if the same error is present in the .result file. But in this case you won't increment error_count, because an error in the result file means --error directive. > + /* Fallthrough */ > case RESULT_LENGTH_MISMATCH: > - mess= "Result length mismatch\n"; > + if (!mess) > + mess= "Result length mismatch\n"; > /* Fallthrough */ > case RESULT_CONTENT_MISMATCH: > { > @@ -3240,8 +3319,10 @@ void do_exec(struct st_command *command) > log_msg("exec of '%s' failed, error: %d, status: %d, errno: %d", > ds_cmd.str, error, status, errno); > dynstr_free(&ds_cmd); > - die("command \"%s\" failed\n\nOutput from before failure:\n%s\n", > - command->first_argument, ds_res.str); > + if (!opt_continue_on_error) > + die("command \"%s\" failed\n\nOutput from before failure:\n%s\n", > + command->first_argument, ds_res.str); why not report_or_die ? > + return; > } > > DBUG_PRINT("info", > @@ -3941,12 +4030,12 @@ void read_until_delimiter(DYNAMIC_STRING > No characters except \n are allowed on > the same line as the command > */ > - die("Trailing characters found after command"); > + report_or_die("Trailing characters found after command"); I think --continue-on-error should not affect syntax errors in the test script itself. They should always abort the execution. in general, you've documented the new option as "This is mostly useful when testing a storage engine and one wants to see how much of a multi-test file it can execute." which is also what BigFish wanted it to do. So, most (if not all) mysqltest failures not related to a storage engine should not be ignored. > } > > if (feof(cur_file->file)) > - die("End of file encountered before '%s' delimiter was found", > - ds_delimiter->str); > + report_or_die("End of file encountered before '%s' delimiter was found", > + ds_delimiter->str); > > if (match_delimiter(c, ds_delimiter->str, ds_delimiter->length)) > { > === modified file 'mysql-test/mysql-test-run.pl' > --- a/mysql-test/mysql-test-run.pl 2012-05-03 13:00:41 +0000 > +++ b/mysql-test/mysql-test-run.pl 2012-05-04 13:37:09 +0000 > @@ -5805,6 +5807,11 @@ sub start_mysqltest ($) { > mtr_add_arg($args, "--max-connections=%d", $opt_max_connections); > } > > + if ($opt_continue_on_error) > + { > + mtr_add_arg($args, "--continue-on-error"); I think this should enable --force too. or, perhaps, --force should enable this and one does not need a separate --continue-on-error in mtr. > + } > + > if ( $opt_embedded_server ) > { > Regards, Sergei

1 0

[Maria-developers] Request for testing: MWL#182 SHOW EXPLAIN
by Sergei Petrunia 11 May '12

11 May '12

Hi Elena, I need testing for MWL#182 SHOW EXPLAIN. The code is here: lp:~maria-captains/maria/5.3-show-explain2 (note the '2' at the end) The branch is currently based on 5.3, although will move to 5.5 soon (I suppose this doesn't cause any problems for testing). Currently, I can think of two things that need testing: === "Asynchronous Procedure Call" module === This is a module that allows one thread to schedule a call in the other thread, and wait until the call has been invoked. Suggested way to test: - run a set of queries that look at each other with "SHOW EXPLAIN FOR <thread>" statements. - have some "victim" threads which run long queries, and have "attacker" threads which submit a lot of "SHOW EXPLAIN FOR <victim_thread>" queries. === On-the-fly EXPLAIN printing functionality === Before this WL, the code that produced EXPLAIN output - Required that optimizer produces a slightly different structures when EXPLAIN is running - Did destructive modifications to the query plan (that is, it was not possible to call these functions two times to get two EXPLAIN outputs for the same planned query) Suggested way to test: - Run various complicated queries, with UNIONs, JOINs, etc. - While they are running, do SHOW EXPLAIN FOR command for them, expecting it to crash or produce nonsensical output at some point. If SHOW EXPLAIN crashes, the crash report will show the query it was trying to obtain the EXPLAIN for. My current suggestion is to use some "runner" threads to run the queries and use another set of threads to hit the "runner" threads with SHOW EXPLAIN request. When creating tests in mysql-test/t/show_explain.test, I made a framework that allows to stop query execution at some particular point and then wait until some number of SHOW EXPLAIN requests were arrived and served. This requires insertion of "probes" into the source code, though. Perhaps, we could use something like that for your testing: to have the query pause at some "interesting" points and wait for SHOW EXPLAIN requests to arrive and be served. I wasn't able to come up with any intelligent way to find interesting point though, so this is still just an idea. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

Re: [Maria-developers] Added --continue-on-error to mysqltest and mysql-test-run
by Sergei Golubchik 07 May '12

07 May '12

Hi, Monty! On May 04, Michael Widenius wrote: > At lp:maria/5.5 > > ------------------------------------------------------------ > revno: 3393 > committer: Michael Widenius <monty(a)askmonty.org> > branch nick: maria-5.5 > message: > Added --continue-on-error to mysqltest and mysql-test-run > This will contune the test case even if there was an error. This > makes it easier to run a test that contains many sub tests against > one engine. Monty, why did you do your parallel implementation of this feature, instead of using the existing abort_on_error flag? Regards, Sergei

1 0

Re: [Maria-developers] MDEV-255: Compile handlersocket plugin in 5.5
by Sergei Golubchik 07 May '12

07 May '12

Hi, Vladislav! On May 05, Vladislav Vaintroub wrote: > ------------------------------------------------------------ > revno: 3398 > revision-id: wlad(a)montyprogram.com-20120505003610-rz89d70v8c6n5v0c > parent: wlad(a)montyprogram.com-20120504152240-ptie9ox0vjqqukd4 > committer: Vladislav Vaintroub <wlad(a)montyprogram.com> > branch nick: 5.5 > timestamp: Sat 2012-05-05 02:36:10 +0200 > message: > MDEV-255: Compile handlersocket plugin in 5.5 > === added file 'plugin/handler_socket/CMakeLists.txt' > --- a/plugin/handler_socket/CMakeLists.txt 1970-01-01 00:00:00 +0000 > +++ b/plugin/handler_socket/CMakeLists.txt 2012-05-05 00:36:10 +0000 > @@ -0,0 +1,41 @@ > + > +IF(WIN32 OR CMAKE_SYSTEM_NAME MATCHES "FreeBSD") > + # Handlersocket does not compile on Windows, compiles but does > + # not start on FreeBSD. Why does it not start of BSD? > + RETURN() > +ENDIF() > + > +#Remove -fno-implicit-templates from compiler flags(handlersocket would not work with it) > +IF(CMAKE_COMPILER_IS_GNUCXX) > + STRING(REPLACE "-fno-implicit-templates" "" CMAKE_CXX_FLAGS ${CMAKE_CXX_FLAGS}) Do we still compile with -fno-implicit-templates? I thought we don't use it anymore. We shouldn't. > +ENDIF() > + > === modified file 'plugin/handler_socket/handlersocket/mysql_incl.hpp' > --- a/plugin/handler_socket/handlersocket/mysql_incl.hpp 2011-06-07 11:19:49 +0000 > +++ b/plugin/handler_socket/handlersocket/mysql_incl.hpp 2012-05-05 00:36:10 +0000 > @@ -13,7 +13,10 @@ > #define HAVE_CONFIG_H > #endif > > +#ifndef MYSQL_DYNAMIC_PLUGIN > #define MYSQL_DYNAMIC_PLUGIN > +#endif I prefer not to have #ifdef, but instead write #define MYSQL_DYNAMIC_PLUGIN 1 you apparently got macro redefinition warning, right? Because there was -DMYSQL_DYNAMIC_PLUGIN on the gcc command line? It defines MYSQL_DYNAMIC_PLUGIN to be 1, and when it's redefined in the source to the empty string you get a warning. "Redefining" it to the same value of 1 is okay, there will be no warning for it. That's why all such symbols should be defined to 1, not to an empty string. > + > #define MYSQL_SERVER 1 > Regards, Sergei

2 2