developers
Threads by month
- ----- 2025 -----
- May
- April
- March
- February
- January
- ----- 2024 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2023 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2022 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2021 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2020 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2019 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2018 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2017 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2016 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2015 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2014 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2013 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2012 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2011 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2010 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2009 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- 5 participants
- 6857 discussions
Re: [Maria-developers] MDEV-342: Do not mark old binlog file as cleanly closed during rotate until
by Sergei Golubchik 21 Jun '12
by Sergei Golubchik 21 Jun '12
21 Jun '12
Hi, Kristian!
Look good. Just one comment, see below, and ok to push
On Jun 20, Kristian Nielsen wrote:
>
> ------------------------------------------------------------
> revno: 3449
> revision-id: knielsen(a)knielsen-hq.org-20120619130327-7vvemizq0akoysvn
> parent: knielsen(a)knielsen-hq.org-20120620122323-bimn76haijiwpo7u
> committer: knielsen(a)knielsen-hq.org
> branch nick: mariadb-5.5
> timestamp: Tue 2012-06-19 15:03:27 +0200
> message:
> MDEV-342: Do not mark old binlog file as cleanly closed during rotate until
> the new file is fully synced to disk and binlog index. This fixes a window
> where a crash would leave next server restart unable to detect that a crash
> occured, causing recovery to fail.
> === modified file 'sql/log.cc'
> --- a/sql/log.cc 2012-05-21 18:54:41 +0000
> +++ b/sql/log.cc 2012-06-19 13:03:27 +0000
> @@ -6344,6 +6373,21 @@ void MYSQL_BIN_LOG::close(uint exiting)
> }
>
>
> +/*
> + For binlog rotation, this function is used to ensure that the old binlog
> + file is not marked as cleanly closed until the new binlog file is synced to
> + disk and index. This is needed to ensure that we will always detect a prior
> + crash at next server restart.
> +*/
> +void MYSQL_BIN_LOG::delayed_close(File file)
> +{
> + my_off_t offset= BIN_LOG_HEADER_SIZE + FLAGS_OFFSET;
> + uchar flags= 0; // clearing LOG_EVENT_BINLOG_IN_USE_F
> + mysql_file_pwrite(file, &flags, 1, offset, MYF(0));
I'd rather reuse this piece of code that overwrites the flag.
If you move mysql_file_close() to the caller, you can call this function
set_binlog_flag_when_closing() and use it both for normal and delayed
close.
> + mysql_file_close(file, MYF(MY_WME));
> +}
> +
Regards,
Sergei
1
0
Hi Timour,
Please find below some input re MDEV-83:
* I expect that MDEV-83 will have an @@optimizer_switch flag. Is that so?
* MDEV-83 will change optimizer behavior, however the change will not be
visible in EXPLAIN. We have a history of such attempts and we've always
ended up having to fix the EXPLAIN so that the change is visible.
Do you have any ideas how to do it? My proposal is to show "subquery#n"
in the Extra column of the table to which the subquery predicate is attached.
* You need to take join buffering into account. For example, for this query:
explain select * from ten A, ten B where cond(B.col) and cond(A.col, B.col);
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+
|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra |
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+
| 1|SIMPLE |A |ALL |NULL |NULL|NULL |NULL| 10| |
| 1|SIMPLE |B |ALL |NULL |NULL|NULL |NULL| 10|Using where; Using join buffer (flat, BNL join) |
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+
both cond(B.col) and cond(A.col, B.col) will be attached to table B, however
they will have different cardinalities.
* The same goes for semi-join strategies. It is possible to calculate output
cardinality for any condition, however it is much less obvious.
* Suppose you have a query:
select * from ot1, ot2
where expr in (select it1.col1 from it1, it2 where subquery_cond(it2.col2))
suppose the join order is:
SJ-Materialize-scan(it1, it2), ot1, ot2
Here, subquery_cond may only be attached inside the SJ-Materialization nest.
This is because subquery cond relies on the value of it2.col2, which is not
available outside the SJ-Materialization nest.
* LEFT JOINs: when the query has outer joins, predicates that are attached to
tables that are inner w.r.t some LEFT JOIN, are wrapped in Item_func_trig_cond.
I suppose you'll reuse the wrapping code, so that repositioned conditions are
wrapped into appropriate Item_func_trig_conds.
I'm not sure whether we should support predicate repositioning for parts of ON
expressions. I suppose not implementing this allows to deliver sooner. If this
is the case, we should not support repositioning inside ON expressions in the
first milestone.
* Section 1.2 says:
" An estimate is made of the partial result cardinality, such that it takes
into account actual pushed predicate selectivity"
As far as I understand, you only have ability to estimate selectivity of
predicates in form
"tbl1.col CMP const"
For such conditions, it is known in advance which table they will be attached
to. There is no need to traverse the "cheap" pushed-down conditions. Then, what
exactly do you mean by "take into account actual pushed predicate selectivity"?
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
1
0
Re: [Maria-developers] Please review: [Commits] Rev 3538: Fix bug lp:1008773 in file:///home/tsk/mprog/src/5.3-lpb1008773/
by Sergei Petrunia 18 Jun '12
by Sergei Petrunia 18 Jun '12
18 Jun '12
Hi Timour,
Ok to push.
On Tue, Jun 05, 2012 at 04:20:35PM +0300, Timour Katchaounov wrote:
> Sergey,
>
> Could you please review yet another rather trivial fix.
> The only thing here is whether the semantics is correct.
> As the bug report says, all MariaDB/MySQL versions (except
> mariadb 5.3/5.5), and Postgresql return the value of the
> constant as part of the empty row produced when we have
> implicit grouping. I didn't verify this in the SQL standard,
> because AFAIR this is non-standard.
>
> Timour
>
> ------------------------------------------------------------
> revno: 3538
> revision-id: timour(a)askmonty.org-20120605124124-akdmgdmr01h10s2w
> parent: psergey(a)askmonty.org-20120602121305-mhw0u2zppjk0erhk
> fixes bug(s): https://launchpad.net/bugs/1008773
> committer: timour(a)askmonty.org
> branch nick: 5.3-lpb1008773
> timestamp: Tue 2012-06-05 15:41:24 +0300
> message:
> Fix bug lp:1008773
>
> Analysis:
> Queries with implicit grouping (there is aggregate, but no group by)
> follow some non-obvious semantics in the case of empty result set.
> Aggregate functions produce some special "natural" value depending on
> the function. For instance MIN/MAX return NULL, COUNT returns 0.
>
> The complexity comes from non-aggregate expressions in the select list.
> If the non-aggregate expression is a constant, it can be computed, so
> we should return its value, however if the expression is non-constant,
> and depends on columns from the empty result set, then the only meaningful
> value is NULL.
>
> The cause of the wrong result was that for subqueries the optimizer didn't
> make a difference between constant and non-constant ones in the case of
> empty result for implicit grouping.
>
> Solution:
> In all implementations of Item_subselect::no_rows_in_result() check if the
> subquery predicate is constant. If it is constant, do not set it to the
> default value for implicit grouping, instead let it be evaluated.
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result 2012-02-28 14:41:55 +0000
> +++ b/mysql-test/r/subselect4.result 2012-06-05 12:41:24 +0000
> @@ -2124,6 +2124,61 @@ ERROR 21000: Subquery returns more than
> DROP TABLE t2;
> ERROR 42S02: Unknown table 't2'
> DROP TABLE t1;
> +#
> +# LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING
> +#
> +CREATE TABLE t1 (a INT) ENGINE=MyISAM;
> +CREATE TABLE t2 (b INT) ENGINE=MyISAM;
> +INSERT INTO t2 VALUES (1);
> +EXPLAIN
> +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 1
> +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
> +MAX(a) bb
> +NULL 1
> +EXPLAIN
> +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
> +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
> +MAX(a) bb
> +NULL 1
> +EXPLAIN
> +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +MAX(a) bb
> +NULL 1
> +EXPLAIN
> +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
> +MAX(a) bb
> +NULL NULL
> +EXPLAIN
> +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
> +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
> +MAX(a) bb
> +NULL NULL
> +EXPLAIN
> +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
> +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
> +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +MAX(a) bb
> +NULL NULL
> +drop table t1, t2;
> set optimizer_switch=@subselect4_tmp;
> SET optimizer_switch= @@global.optimizer_switch;
> set @@tmp_table_size= @@global.tmp_table_size;
>
> === modified file 'mysql-test/t/subselect4.test'
> --- a/mysql-test/t/subselect4.test 2012-02-28 14:41:55 +0000
> +++ b/mysql-test/t/subselect4.test 2012-06-05 12:41:24 +0000
> @@ -1761,6 +1761,40 @@ CREATE TABLE t2 AS
> DROP TABLE t2;
> DROP TABLE t1;
>
> +--echo #
> +--echo # LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING
> +--echo #
> +
> +CREATE TABLE t1 (a INT) ENGINE=MyISAM;
> +CREATE TABLE t2 (b INT) ENGINE=MyISAM;
> +INSERT INTO t2 VALUES (1);
> +
> +EXPLAIN
> +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
> +SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
> +
> +EXPLAIN
> +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
> +SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
> +
> +EXPLAIN
> +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +
> +
> +EXPLAIN
> +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
> +SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
> +
> +EXPLAIN
> +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
> +SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
> +
> +EXPLAIN
> +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
> +
> +drop table t1, t2;
>
> set optimizer_switch=@subselect4_tmp;
>
>
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc 2012-05-22 12:22:55 +0000
> +++ b/sql/item_subselect.cc 2012-06-05 12:41:24 +0000
> @@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String
>
> void Item_maxmin_subselect::no_rows_in_result()
> {
> + if (const_item())
> + return;
> value= Item_cache::get_cache(new Item_null());
> null_value= 0;
> was_values= 0;
> @@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_r
>
> void Item_singlerow_subselect::no_rows_in_result()
> {
> + if (const_item())
> + return;
> value= Item_cache::get_cache(new Item_null());
> reset();
> make_const();
> @@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_
>
> void Item_exists_subselect::no_rows_in_result()
> {
> + if (const_item())
> + return;
> value= 0;
> null_value= 0;
> make_const();
> @@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String
>
> void Item_allany_subselect::no_rows_in_result()
> {
> + if (const_item())
> + return;
> value= 0;
> null_value= 0;
> was_null= 0;
>
> _______________________________________________
> commits mailing list
> commits(a)mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
3
2
Re: [Maria-developers] [Commits] Rev 3434: MDEV-339, LP1001340 - system_time_zone is wrong on Windows in file:///H:/bzr/5.5/
by Sergei Golubchik 15 Jun '12
by Sergei Golubchik 15 Jun '12
15 Jun '12
Hi, Vladislav!
On Jun 15, Vladislav Vaintroub wrote:
> ------------------------------------------------------------
> revno: 3434
> revision-id: wlad(a)montyprogram.com-20120615152106-p4a50d0yv2qj4fxk
> parent: wlad(a)montyprogram.com-20120613093751-tv67fxuh1qcxm0by
> fixes bug: https://launchpad.net/bugs/1001340
> committer: Vladislav Vaintroub <wlad(a)montyprogram.com>
> branch nick: 5.5
> timestamp: Fri 2012-06-15 17:21:06 +0200
> message:
> MDEV-339, LP1001340 - system_time_zone is wrong on Windows
>
> On localized Windows versions, Windows uses localized time zone
> names and contain non-ASCII characters. non-ASCII characters appear
> broken when displayed by clients
> The fix is to declare system_time_zone variable to have UTF8
> encoding and to convert tzname to UTF8.
Looks good, thanks!
Did you verify that it works?
(I vaguely remember that you don't have a localized Windows version)
Ok to push, if yes.
Regards,
Sergei
1
0
Re: [Maria-developers] MDEV-339 lp:1001340 system_time_zone is wrong on Windows
by Sergei Golubchik 15 Jun '12
by Sergei Golubchik 15 Jun '12
15 Jun '12
Hi, Vladislav!
On Jun 14, Vladislav Vaintroub wrote:
> At file:///H:/bzr/5.5/
>
> revno: 3434
> revision-id: wlad(a)montyprogram.com-20120614122958-bq20w9xbqjw032nk
> parent: wlad(a)montyprogram.com-20120613093751-tv67fxuh1qcxm0by
> fixes bug: https://launchpad.net/bugs/1001340
> committer: Vladislav Vaintroub <wlad(a)montyprogram.com>
> branch nick: 5.5
> timestamp: Thu 2012-06-14 14:29:58 +0200
> message:
> MDEV-339, LP1001340 - system_time_zone is wrong on Windows
>
> On localized Windows versions, Windows uses localized time zone
> names. Since characters in timezone name can be outside of ASCII
> range, thus not every client would be able to read variable without
> loosing information, and even server misinterprets the encoding (it
> does not try to interpret the encoding).
>
> The fix is to use the UTC offset format "+/-hh:mm" for
> system_time_zone on Windows, compatible with time_zone values
> everywhere else. UTC offset format is however not used if TZ
> environment variable is defined - MySQL documents to handle TZ
> environment variable in OS-dependent way.
I am not sure I like this idea. UTC offset format specifies a
*different* time zone. Not the system one. Timezone that surely produces
different results for the historical dates.
Original bug report says that system_time_zone value is not a valid
MySQL (or POSIX) time zone name. But is it a problem?
When time_zone is set to SYSTEM, MariaDB will use OS functions for
date/time conversion, and not the data from timezone tables. So it will
use the system time zone *exactly*, not UTC offset approximation.
And it won't try to look up system_time_zone value in the timezone
tables.
So, I think, it's quite ok for system_time_zone to have "Westeuropäische
Sommerzeit" value. You only need to fix the charset/truncation issue and
that's all.
Regards,
Sergei
3
2
Re: [Maria-developers] [Commits] Rev 3543: Fix bug lp:1008686 in file:///home/tsk/mprog/src/5.3/
by Sergei Petrunia 14 Jun '12
by Sergei Petrunia 14 Jun '12
14 Jun '12
Hi Timour,
How does this relate to fix for lp:1008773?
In this bug, you've added:
> + if (parsing_place != SELECT_LIST)
> + return;
and in that one:
> + if (const_item())
> + return;
How will the fixes work together?
Generally, I'm wondering if we're taking the right approach here: from an
out-of-context view, it seems that approach "equip every Item with ability
to evaluate to NULL under some special circumnstances" is very confusing, and
one should just ignore the non-group-by items and produce NULLs instead..
Let's discuss it on irc.
On Wed, Jun 13, 2012 at 08:52:19AM +0000, timour(a)askmonty.org wrote:
> At file:///home/tsk/mprog/src/5.3/
>
> ------------------------------------------------------------
> revno: 3543
> revision-id: timour(a)askmonty.org-20120613085100-1a3zlttyiebfvzuy
> parent: psergey(a)askmonty.org-20120610100611-0l0qf7ov53h1elr9
> fixes bug(s): https://launchpad.net/bugs/1008686
> committer: timour(a)askmonty.org
> branch nick: 5.3
> timestamp: Wed 2012-06-13 11:51:00 +0300
> message:
> Fix bug lp:1008686
>
> Analysis:
> The fix for bug lp:985667 implements the method Item_subselect::no_rows_in_result()
> for all main kinds of subqueries. The purpose of this method is to be called from
> return_zero_rows() and set Items to some default value in the case when a query
> returns no rows. Aggregates and subqueries require special treatment in this case.
>
> Every implementation of Item_subselect::no_rows_in_result() called
> Item_subselect::make_const() to set the subquery predicate to its default value
> irrespective of where the predicate was located in the query. Once the predicate
> was set to a constant it was never executed.
>
> At the same time, the JOIN object of the fake select for UNIONs (the one used for
> the final result of the UNION), was set after all subqueries in the union were
> executed. Since we set the subquery as constant, it was never executed, and the
> corresponding JOIN was never created.
>
> In order to decide whether the result of NOT IN is NULL or FALSE, Item_in_optimizer
> needs to check if the subquery result was empty or not. This is where we got the
> crash, because subselect_union_engine::no_rows() checks for
> unit->fake_select_lex->join->send_records, and the join object was NULL.
>
> Solution:
> If a subquery is in the HAVING clause it must be evaluated in order to know its
> result, so that we can properly filter the result records. Once subqueries in the
> HAVING clause are executed even in the case of no result rows, this specific
> crash will be solved, because the UNION will be executed, and its JOIN will be
> constructed. Therefore the fix for this crash is to narrow the fix for lp:985667,
> and to apply Item_subselect::no_rows_in_result() only when the subquery predicate
> is in the SELECT clause.
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2012-06-05 14:25:10 +0000
> +++ b/mysql-test/r/subselect.result 2012-06-13 08:51:00 +0000
> @@ -6112,5 +6112,30 @@ NULL UNION RESULT <union2,3> ALL NULL NU
> SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
> f1 f2
> drop table t1,t2;
> +#
> +# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
> +# WHERE and UNION in HAVING
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +EXPLAIN
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 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
> +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
> +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
> +min_a a
> +EXPLAIN
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 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
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
> +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
> +min_a a
> +drop table t1;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'mysql-test/t/subselect.test'
> --- a/mysql-test/t/subselect.test 2012-05-22 12:22:55 +0000
> +++ b/mysql-test/t/subselect.test 2012-06-13 08:51:00 +0000
> @@ -5190,5 +5190,23 @@ SELECT SUM(a) AS f1, a AS f2 FROM (t1, t
>
> drop table t1,t2;
>
> +--echo #
> +--echo # LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
> +--echo # WHERE and UNION in HAVING
> +--echo #
> +
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +
> +EXPLAIN
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
> +
> +EXPLAIN
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
> +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
> +
> +drop table t1;
> +
> --echo # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc 2012-05-22 12:22:55 +0000
> +++ b/sql/item_subselect.cc 2012-06-13 08:51:00 +0000
> @@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String
>
> void Item_maxmin_subselect::no_rows_in_result()
> {
> + if (parsing_place != SELECT_LIST)
> + return;
> value= Item_cache::get_cache(new Item_null());
> null_value= 0;
> was_values= 0;
> @@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_r
>
> void Item_singlerow_subselect::no_rows_in_result()
> {
> + if (parsing_place != SELECT_LIST)
> + return;
> value= Item_cache::get_cache(new Item_null());
> reset();
> make_const();
> @@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_
>
> void Item_exists_subselect::no_rows_in_result()
> {
> + if (parsing_place != SELECT_LIST)
> + return;
> value= 0;
> null_value= 0;
> make_const();
> @@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String
>
> void Item_allany_subselect::no_rows_in_result()
> {
> + if (parsing_place != SELECT_LIST)
> + return;
> value= 0;
> null_value= 0;
> was_null= 0;
>
> _______________________________________________
> commits mailing list
> commits(a)mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
1
0
Re: [Maria-developers] Please review: [Commits] Rev 3406: Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer in file:///home/tsk/mprog/src/5.5-lpb944706/
by Sergei Petrunia 14 Jun '12
by Sergei Petrunia 14 Jun '12
14 Jun '12
Hi Timour,
Ok to push.
I was wondering what are your plans with regards to pushing 5.5-timour to main?
Is this the last issue?
On Wed, May 30, 2012 at 12:22:06AM +0300, Timour Katchaounov wrote:
> Sergey,
>
> Please review the following bug fix that we discussed today.
>
>
> Timour
>
>
> ------------------------------------------------------------
> revno: 3406
> revision-id: timour(a)askmonty.org-20120529211853-hww47vl7d4u4ae23
> parent: timour(a)askmonty.org-20120524110828-r0mm8sm1vn8a095e
> committer: timour(a)askmonty.org
> branch nick: 5.5-lpb944706
> timestamp: Wed 2012-05-30 00:18:53 +0300
> message:
> Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer
>
> Analysis:
>
> The fix for lp:944706 introduces early subquery optimization.
> While a subquery is being optimized some of its predicates may be
> removed. In the test case, the EXISTS subquery is constant, and is
> evaluated to TRUE. As a result the whole OR is TRUE, and thus the
> correlated condition "b = alias1.b" is optimized away. The subquery
> becomes non-correlated.
>
> The subquery cache is designed to work only for correlated subqueries.
> If constant subquery optimization is disallowed, then the constant
> subquery is not evaluated, the subquery remains correlated, and its
> execution is cached. As a result execution is fast.
>
> However, when the constant subquery was optimized away, it was neither
> cached by the subquery cache, nor it was cached by the internal subquery
> caching. The latter was due to the fact that the subquery still appeared
> as correlated to the subselect_XYZ_engine::exec methods, and they
> re-executed the subquery on each call to Item_subselect::exec.
>
> Solution:
>
> The solution is to update the correlated status of the subquery after it has
> been optimized. This status consists of:
> - st_select_lex::is_correlated
> - Item_subselect::is_correlated
> - SELECT_LEX::uncacheable
> - SELECT_LEX_UNIT::uncacheable
> The status is updated by st_select_lex::update_correlated_cache(), and its
> caller st_select_lex::optimize_unflattened_subqueries. The solution relies
> on the fact that the optimizer already called
> st_select_lex::update_used_tables() for each subquery. This allows to
> efficiently update the correlated status of each subquery without walking
> the whole subquery tree.
>
> Notice that his patch is an improvement over MySQL 5.6 and older, where
> subqueries are not pre-optimized, and the above analysis is not possible.
> === modified file 'mysql-test/r/derived_opt.result'
> --- a/mysql-test/r/derived_opt.result 2012-02-14 14:52:56 +0000
> +++ b/mysql-test/r/derived_opt.result 2012-05-29 21:18:53 +0000
> @@ -161,24 +161,24 @@ prepare stmt1 from @stmt ;
> execute stmt1 ;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> execute stmt1 ;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> deallocate prepare stmt1;
> drop tables t1,t2;
> set @@optimizer_switch=@save_optimizer_switch;
>
> === modified file 'mysql-test/r/explain.result'
> --- a/mysql-test/r/explain.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/explain.result 2012-05-29 21:18:53 +0000
> @@ -225,10 +225,10 @@ INSERT INTO t2 VALUES (NULL), (0);
> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
> Warnings:
> Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
> -Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL))) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual
> +Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual
> DROP TABLE t1, t2;
> #
> # Bug#30302: Tables that were optimized away are printed in the
>
> === modified file 'mysql-test/r/index_merge_innodb.result'
> --- a/mysql-test/r/index_merge_innodb.result 2012-02-21 19:51:56 +0000
> +++ b/mysql-test/r/index_merge_innodb.result 2012-05-29 21:18:53 +0000
> @@ -654,7 +654,7 @@ EXPLAIN SELECT t1.f1 FROM t1
> WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
> -2 DEPENDENT SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index
> +2 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index
> DROP TABLE t1,t2;
> #
> # BUG#56862/640419: Wrong result with sort_union index merge when one
>
> === modified file 'mysql-test/r/index_merge_myisam.result'
> --- a/mysql-test/r/index_merge_myisam.result 2012-02-15 17:08:08 +0000
> +++ b/mysql-test/r/index_merge_myisam.result 2012-05-29 21:18:53 +0000
> @@ -1487,7 +1487,7 @@ EXPLAIN SELECT t1.f1 FROM t1
> WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
> -2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where
> +2 SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where
> DROP TABLE t1,t2;
> create table t0 (a int);
> insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
>
> === modified file 'mysql-test/r/join_outer.result'
> --- a/mysql-test/r/join_outer.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/join_outer.result 2012-05-29 21:18:53 +0000
> @@ -1872,7 +1872,7 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON (6)
> 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 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
> +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
> Warnings:
> Note 1003 select NULL AS `a` from `test`.`t2` where 1
> DROP TABLE t1,t2,t3;
>
> === modified file 'mysql-test/r/join_outer_jcl6.result'
> --- a/mysql-test/r/join_outer_jcl6.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/join_outer_jcl6.result 2012-05-29 21:18:53 +0000
> @@ -1883,7 +1883,7 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON (6)
> 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 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
> +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
> Warnings:
> Note 1003 select NULL AS `a` from `test`.`t2` where 1
> DROP TABLE t1,t2,t3;
>
> === modified file 'mysql-test/r/ps.result'
> --- a/mysql-test/r/ps.result 2012-04-10 06:28:13 +0000
> +++ b/mysql-test/r/ps.result 2012-05-29 21:18:53 +0000
> @@ -160,26 +160,26 @@ execute stmt1 ;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> execute stmt1 ;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> deallocate prepare stmt1;
> set optimizer_switch=@tmp_optimizer_switch;
> drop tables t1,t2;
>
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/r/subselect.result 2012-05-29 21:18:53 +0000
> @@ -51,12 +51,12 @@ ERROR 42S22: Reference 'a' not supported
> EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> Warnings:
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1)
> +Note 1003 select 1 AS `1` from dual having ((select 1) = 1)
> SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> 1
> 1
> @@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2)
> explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> Warnings:
> Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
> CREATE TABLE t3 (a int(11) default '0');
> @@ -1317,7 +1317,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> INSERT INTO t1 (pseudo) VALUES ('test1');
> @@ -1327,7 +1327,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> drop table t1;
> @@ -1605,25 +1605,25 @@ a3 1
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
> drop table t1,t2;
> @@ -3097,7 +3097,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
> @@ -3109,7 +3109,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
> @@ -3160,7 +3160,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
> EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 ALL NULL NULL NULL NULL 3
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> SELECT a, a IN (SELECT a FROM t1) FROM t2;
> a a IN (SELECT a FROM t1)
> 1 1
> @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
> -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> SELECT * FROM t1,t2
> WHERE t1.t = (SELECT t1.t FROM t1
> WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
> @@ -4577,7 +4577,7 @@ FROM t1
> WHERE a = 230;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
> FROM t1
> WHERE a = 230;
> @@ -5713,7 +5713,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP
> GROUP BY b;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> SELECT b FROM t1
> WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
> GROUP BY b;
> @@ -6530,7 +6530,7 @@ EXPLAIN
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 NULL
> @@ -6538,7 +6538,7 @@ EXPLAIN
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 0
> @@ -6573,7 +6573,7 @@ WHERE alias1.a = alias2.a OR alias1.a =
> HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a ) AS field
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR alias1.a = 'y'
> @@ -6587,7 +6587,7 @@ id select_type table type possible_keys
> 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index
> 1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index
> 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join)
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a )
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
> @@ -6615,5 +6615,90 @@ SELECT * FROM t1
> WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2));
> a1
> drop table t1, t2;
> +#
> +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +# inner joins takes hundreds times longer
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +set @@expensive_subquery_limit= 0;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 6
> +Subquery_cache_miss 2
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 8
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 22
> +set @@expensive_subquery_limit= default;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 16
> +drop table t1, t2, t3;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/subselect4.result 2012-05-29 21:18:53 +0000
> @@ -226,10 +226,10 @@ NULL
> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
> -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual
> +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual
> first equivalent variant
> SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ;
> RESULT
> @@ -237,10 +237,10 @@ NULL
> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
> -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL
> +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL
> second equivalent variant
> SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
> RESULT
> @@ -248,10 +248,10 @@ NULL
> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
> -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL
> +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL
> DROP TABLE t1,t2;
> #
> # BUG#45928 "Differing query results depending on MRR and
> @@ -649,7 +649,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -671,7 +671,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -679,7 +679,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
> not_in
> 1
> @@ -687,7 +687,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
> not_in
> NULL
> @@ -695,7 +695,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -703,7 +703,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
> not_in
> NULL
> @@ -719,28 +719,28 @@ EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) 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 t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) 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 t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) 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 t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
> f1 f2
> EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -748,21 +748,21 @@ EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) 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 t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) 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 t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
> f1 f2
> EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -770,7 +770,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
> not_in
> 1
> @@ -778,7 +778,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
> not_in
> NULL
> @@ -786,7 +786,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
> not_in
> NULL
> @@ -794,7 +794,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
> not_in
> NULL
> @@ -835,7 +835,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -857,7 +857,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -865,7 +865,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in;
> not_in
> 1
> @@ -873,7 +873,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in;
> not_in
> NULL
> @@ -881,7 +881,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -889,7 +889,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -905,28 +905,28 @@ EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
> f1 f2
> EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -934,21 +934,21 @@ EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
> f1 f2
> EXPLAIN
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
> f1 f2
> EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -956,7 +956,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in;
> not_in
> 1
> @@ -964,7 +964,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in;
> not_in
> NULL
> @@ -972,7 +972,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -980,7 +980,7 @@ EXPLAIN
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan
> SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in;
> not_in
> NULL
> @@ -1240,7 +1240,7 @@ EXPLAIN
> SELECT i FROM t1 WHERE (1) NOT IN (SELECT i 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 t2 index_subquery k k 5 const 2 Using index
> +2 SUBQUERY t2 index_subquery k k 5 const 2 Using index
> DROP TABLE t2;
> DROP TABLE t1;
> #
> @@ -1258,8 +1258,8 @@ 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 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
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(t2.f3),
> (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
> FROM t2 JOIN t1 ON t1.f3
> @@ -1274,8 +1274,8 @@ 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 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
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(t2.f3),
> (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
> FROM t2 JOIN t1 ON t1.f3
> @@ -1291,8 +1291,8 @@ 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 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
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(t2.f3),
> (SELECT t2.f1 FROM t1 limit 1) AS f9
> FROM t2 JOIN t1
> @@ -1307,8 +1307,8 @@ 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 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
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(t2.f3),
> (SELECT t2.f1 FROM t1 limit 1) AS f9
> FROM t2 JOIN t1
> @@ -1390,7 +1390,7 @@ EXPLAIN
> SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
> 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1400,7 +1400,7 @@ EXPLAIN
> SELECT ( 5 ) IN ( SELECT * FROM v1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
> 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1411,7 +1411,7 @@ EXPLAIN
> SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED t1 system NULL NULL NULL NULL 1
> 4 UNION t2 system NULL NULL NULL NULL 1
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1431,7 +1431,7 @@ EXPLAIN
> SELECT ( 5 ) IN ( SELECT * FROM v2 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED t1 system NULL NULL NULL NULL 1
> 4 UNION t2 system NULL NULL NULL NULL 1
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1443,7 +1443,7 @@ EXPLAIN
> SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
> 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1453,7 +1453,7 @@ EXPLAIN
> SELECT ( 5 ) IN ( SELECT * FROM v1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
> 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1464,7 +1464,7 @@ EXPLAIN
> SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED t1 system NULL NULL NULL NULL 1
> 4 UNION t2 system NULL NULL NULL NULL 1
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1474,7 +1474,7 @@ EXPLAIN
> SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED t1 system NULL NULL NULL NULL 1
> 4 UNION t2 system NULL NULL NULL NULL 1
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1484,7 +1484,7 @@ EXPLAIN
> SELECT ( 5 ) IN ( SELECT * FROM v2 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where
> 3 DERIVED t1 system NULL NULL NULL NULL 1
> 4 UNION t2 system NULL NULL NULL NULL 1
> NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
> @@ -1635,8 +1635,8 @@ 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 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
> +2 SUBQUERY t3 system NULL NULL NULL NULL 1
> +2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index
> SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
> f4
> drop table t1,t2,t3;
>
> === modified file 'mysql-test/r/subselect_extra_no_semijoin.result'
> --- a/mysql-test/r/subselect_extra_no_semijoin.result 2012-01-13 14:50:02 +0000
> +++ b/mysql-test/r/subselect_extra_no_semijoin.result 2012-05-29 21:18:53 +0000
> @@ -46,7 +46,7 @@ select * from t1
> where id in (select id from t1 as x1 where (t1.cur_date is null));
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> Warnings:
> Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
> Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0)))
> @@ -58,7 +58,7 @@ select * from t2
> where id in (select id from t2 as x1 where (t2.cur_date is null));
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> Warnings:
> Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
> Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0)))
>
> === modified file 'mysql-test/r/subselect_mat.result'
> --- a/mysql-test/r/subselect_mat.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/subselect_mat.result 2012-05-29 21:18:53 +0000
> @@ -1163,7 +1163,7 @@ set @@optimizer_switch='materialization=
> 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
> min(a1)
> NULL
> @@ -1182,7 +1182,7 @@ set @@optimizer_switch='materialization=
> 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select b1 from t2);
> min(a1)
> NULL
> @@ -1190,7 +1190,7 @@ NULL
> 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
> min(a1)
> NULL
> @@ -2162,7 +2162,7 @@ EXPLAIN
> SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1);
> (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1)
> @@ -2179,7 +2179,7 @@ EXPLAIN
> SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
> -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
> (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1)
> @@ -2234,7 +2234,7 @@ 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 NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
> -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
> Warnings:
> Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0
> DROP TABLE t1,t2;
>
> === modified file 'mysql-test/r/subselect_mat_cost.result'
> --- a/mysql-test/r/subselect_mat_cost.result 2012-03-01 22:22:22 +0000
> +++ b/mysql-test/r/subselect_mat_cost.result 2012-05-29 21:18:53 +0000
> @@ -496,7 +496,7 @@ from City
> where City.population > 10000000;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY City range Population Population 5 NULL 4 Using index condition; Rowid-ordered scan
> -2 DEPENDENT SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where
> +2 SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where
> select Name, City.id in (select capital from Country where capital is not null) as is_capital
> from City
> where City.population > 10000000;
>
> === modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
> --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-29 21:18:53 +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 no matching row in const table
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> SELECT t1.*
> FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1
> WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 );
>
> === modified file 'mysql-test/r/subselect_no_mat.result'
> --- a/mysql-test/r/subselect_no_mat.result 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/r/subselect_no_mat.result 2012-05-29 21:18:53 +0000
> @@ -58,12 +58,12 @@ ERROR 42S22: Reference 'a' not supported
> EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> Warnings:
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1)
> +Note 1003 select 1 AS `1` from dual having ((select 1) = 1)
> SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> 1
> 1
> @@ -911,7 +911,7 @@ a t1.a in (select t2.a from t2)
> explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> Warnings:
> Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
> CREATE TABLE t3 (a int(11) default '0');
> @@ -1324,7 +1324,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> INSERT INTO t1 (pseudo) VALUES ('test1');
> @@ -1334,7 +1334,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> drop table t1;
> @@ -1612,25 +1612,25 @@ a3 1
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
> drop table t1,t2;
> @@ -3103,7 +3103,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
> @@ -3115,7 +3115,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
> @@ -3166,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
> EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 ALL NULL NULL NULL NULL 3
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> SELECT a, a IN (SELECT a FROM t1) FROM t2;
> a a IN (SELECT a FROM t1)
> 1 1
> @@ -3700,7 +3700,7 @@ ORDER BY t1.t DESC LIMIT 1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
> -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> SELECT * FROM t1,t2
> WHERE t1.t = (SELECT t1.t FROM t1
> WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
> @@ -4526,13 +4526,13 @@ 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 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 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 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
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 0
> SET join_cache_level=@save_join_cache_level;
> @@ -4579,7 +4579,7 @@ FROM t1
> WHERE a = 230;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
> FROM t1
> WHERE a = 230;
> @@ -5714,7 +5714,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP
> GROUP BY b;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> SELECT b FROM t1
> WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
> GROUP BY b;
> @@ -6529,7 +6529,7 @@ EXPLAIN
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 NULL
> @@ -6537,7 +6537,7 @@ EXPLAIN
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 0
> @@ -6572,7 +6572,7 @@ WHERE alias1.a = alias2.a OR alias1.a =
> HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a ) AS field
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR alias1.a = 'y'
> @@ -6586,7 +6586,7 @@ id select_type table type possible_keys
> 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index
> 1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index
> 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join)
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a )
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
> @@ -6613,6 +6613,91 @@ SELECT * FROM t1
> WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2));
> a1
> drop table t1, t2;
> +#
> +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +# inner joins takes hundreds times longer
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +set @@expensive_subquery_limit= 0;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 6
> +Subquery_cache_miss 2
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 8
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 22
> +set @@expensive_subquery_limit= default;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 16
> +drop table t1, t2, t3;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subselect_no_opts.result'
> --- a/mysql-test/r/subselect_no_opts.result 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/r/subselect_no_opts.result 2012-05-29 21:18:53 +0000
> @@ -54,7 +54,7 @@ ERROR 42S22: Reference 'a' not supported
> EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> Warnings:
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> @@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2)
> explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> Warnings:
> Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
> CREATE TABLE t3 (a int(11) default '0');
> @@ -1320,7 +1320,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> INSERT INTO t1 (pseudo) VALUES ('test1');
> @@ -1330,7 +1330,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> drop table t1;
> @@ -1608,25 +1608,25 @@ a3 1
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
> drop table t1,t2;
> @@ -3099,7 +3099,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
> @@ -3111,7 +3111,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
> @@ -3162,7 +3162,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
> EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 ALL NULL NULL NULL NULL 3
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> SELECT a, a IN (SELECT a FROM t1) FROM t2;
> a a IN (SELECT a FROM t1)
> 1 1
> @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
> -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> SELECT * FROM t1,t2
> WHERE t1.t = (SELECT t1.t FROM t1
> WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
> @@ -4522,13 +4522,13 @@ 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 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 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 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
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 0
> SET join_cache_level=@save_join_cache_level;
> @@ -4575,7 +4575,7 @@ FROM t1
> WHERE a = 230;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
> FROM t1
> WHERE a = 230;
> @@ -5710,7 +5710,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP
> GROUP BY b;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> SELECT b FROM t1
> WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
> GROUP BY b;
> @@ -6525,7 +6525,7 @@ EXPLAIN
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 NULL
> @@ -6533,7 +6533,7 @@ EXPLAIN
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 0
> @@ -6568,7 +6568,7 @@ WHERE alias1.a = alias2.a OR alias1.a =
> HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a ) AS field
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR alias1.a = 'y'
> @@ -6582,7 +6582,7 @@ id select_type table type possible_keys
> 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index
> 1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index
> 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join)
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a )
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
> @@ -6610,6 +6610,91 @@ SELECT * FROM t1
> WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2));
> a1
> drop table t1, t2;
> +#
> +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +# inner joins takes hundreds times longer
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +set @@expensive_subquery_limit= 0;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 6
> +Subquery_cache_miss 2
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 8
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 22
> +set @@expensive_subquery_limit= default;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 16
> +drop table t1, t2, t3;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set @optimizer_switch_for_subselect_test=null;
>
> === modified file 'mysql-test/r/subselect_no_scache.result'
> --- a/mysql-test/r/subselect_no_scache.result 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/r/subselect_no_scache.result 2012-05-29 21:18:53 +0000
> @@ -57,7 +57,7 @@ ERROR 42S22: Reference 'a' not supported
> EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> Warnings:
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> @@ -910,7 +910,7 @@ a t1.a in (select t2.a from t2)
> explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
> Warnings:
> Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
> CREATE TABLE t3 (a int(11) default '0');
> @@ -1323,7 +1323,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> INSERT INTO t1 (pseudo) VALUES ('test1');
> @@ -1333,7 +1333,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> drop table t1;
> @@ -1611,25 +1611,25 @@ a3 1
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
> explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
> -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key
> Warnings:
> Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
> drop table t1,t2;
> @@ -3103,7 +3103,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
> @@ -3115,7 +3115,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
> @@ -3166,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3);
> EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 ALL NULL NULL NULL NULL 3
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key
> SELECT a, a IN (SELECT a FROM t1) FROM t2;
> a a IN (SELECT a FROM t1)
> 1 1
> @@ -3702,7 +3702,7 @@ ORDER BY t1.t DESC LIMIT 1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
> -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> SELECT * FROM t1,t2
> WHERE t1.t = (SELECT t1.t FROM t1
> WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
> @@ -4583,7 +4583,7 @@ FROM t1
> WHERE a = 230;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
> FROM t1
> WHERE a = 230;
> @@ -5719,7 +5719,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP
> GROUP BY b;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> SELECT b FROM t1
> WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
> GROUP BY b;
> @@ -6536,7 +6536,7 @@ EXPLAIN
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 NULL
> @@ -6544,7 +6544,7 @@ EXPLAIN
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 0
> @@ -6579,7 +6579,7 @@ WHERE alias1.a = alias2.a OR alias1.a =
> HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a ) AS field
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR alias1.a = 'y'
> @@ -6593,7 +6593,7 @@ id select_type table type possible_keys
> 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index
> 1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index
> 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join)
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a )
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
> @@ -6621,6 +6621,91 @@ SELECT * FROM t1
> WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2));
> a1
> drop table t1, t2;
> +#
> +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +# inner joins takes hundreds times longer
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +set @@expensive_subquery_limit= 0;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 58
> +set @@expensive_subquery_limit= default;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 16
> +drop table t1, t2, t3;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subselect_no_semijoin.result'
> --- a/mysql-test/r/subselect_no_semijoin.result 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/r/subselect_no_semijoin.result 2012-05-29 21:18:53 +0000
> @@ -54,12 +54,12 @@ ERROR 42S22: Reference 'a' not supported
> EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
> -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> Warnings:
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
> -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1)
> +Note 1003 select 1 AS `1` from dual having ((select 1) = 1)
> SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
> 1
> 1
> @@ -1320,7 +1320,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> INSERT INTO t1 (pseudo) VALUES ('test1');
> @@ -1330,7 +1330,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a);
> EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> Warnings:
> Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
> drop table t1;
> @@ -3099,7 +3099,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
> @@ -3111,7 +3111,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
> 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 const PRIMARY PRIMARY 4 const 1
> -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition
> +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition
> SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
> ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
> ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
> @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
> -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
> SELECT * FROM t1,t2
> WHERE t1.t = (SELECT t1.t FROM t1
> WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
> @@ -4522,13 +4522,13 @@ 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 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 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 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
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary
> Warnings:
> Note 1003 select 1 AS `1` from `test`.`t1` where 0
> SET join_cache_level=@save_join_cache_level;
> @@ -4575,7 +4575,7 @@ FROM t1
> WHERE a = 230;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
> FROM t1
> WHERE a = 230;
> @@ -5710,7 +5710,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP
> GROUP BY b;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> SELECT b FROM t1
> WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a )
> GROUP BY b;
> @@ -6525,7 +6525,7 @@ EXPLAIN
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 NULL
> @@ -6533,7 +6533,7 @@ EXPLAIN
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3;
> COUNT(f1) f4
> 0 0
> @@ -6568,7 +6568,7 @@ WHERE alias1.a = alias2.a OR alias1.a =
> HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a ) AS field
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR alias1.a = 'y'
> @@ -6582,7 +6582,7 @@ id select_type table type possible_keys
> 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index
> 1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index
> 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join)
> -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where
> SELECT MAX( alias2.a )
> FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
> WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 );
> @@ -6610,6 +6610,91 @@ SELECT * FROM t1
> WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2));
> a1
> drop table t1, t2;
> +#
> +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +# inner joins takes hundreds times longer
> +#
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +set @@expensive_subquery_limit= 0;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
> +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 6
> +Subquery_cache_miss 2
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 8
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 22
> +set @@expensive_subquery_limit= default;
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2
> +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2
> +flush status;
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +show status like "subquery_cache%";
> +Variable_name Value
> +Subquery_cache_hit 0
> +Subquery_cache_miss 0
> +show status like '%Handler_read%';
> +Variable_name Value
> +Handler_read_first 0
> +Handler_read_key 0
> +Handler_read_last 0
> +Handler_read_next 0
> +Handler_read_prev 0
> +Handler_read_rnd 0
> +Handler_read_rnd_deleted 0
> +Handler_read_rnd_next 16
> +drop table t1, t2, t3;
> # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set @optimizer_switch_for_subselect_test=null;
>
> === modified file 'mysql-test/r/subselect_partial_match.result'
> --- a/mysql-test/r/subselect_partial_match.result 2011-12-04 21:31:42 +0000
> +++ b/mysql-test/r/subselect_partial_match.result 2012-05-29 21:18:53 +0000
> @@ -879,7 +879,7 @@ EXPLAIN
> SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
> SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2);
> c
> 0
> @@ -888,7 +888,7 @@ EXPLAIN
> SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
> SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2);
> c
> 0
> @@ -913,7 +913,7 @@ set @@optimizer_switch='in_to_exists=on,
> EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
> SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
> f3
> 5
>
> === modified file 'mysql-test/r/subselect_sj_mat.result'
> --- a/mysql-test/r/subselect_sj_mat.result 2012-05-17 10:46:05 +0000
> +++ b/mysql-test/r/subselect_sj_mat.result 2012-05-29 21:18:53 +0000
> @@ -1198,7 +1198,7 @@ set @@optimizer_switch='materialization=
> 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
> min(a1)
> NULL
> @@ -1207,7 +1207,7 @@ 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select b1 from t2);
> min(a1)
> NULL
> @@ -1224,7 +1224,7 @@ NULL
> 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 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
> +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
> select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
> min(a1)
> NULL
>
> === modified file 'mysql-test/t/subselect.test'
> --- a/mysql-test/t/subselect.test 2012-05-18 11:52:01 +0000
> +++ b/mysql-test/t/subselect.test 2012-05-29 21:18:53 +0000
> @@ -5580,5 +5580,49 @@ WHERE a1 = (SELECT COUNT(*) FROM t1 WHER
>
> drop table t1, t2;
>
> +--echo #
> +--echo # MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS,
> +--echo # inner joins takes hundreds times longer
> +--echo #
> +
> +CREATE TABLE t1 (a INT);
> +INSERT INTO t1 VALUES (1),(7);
> +
> +CREATE TABLE t2 (b INT);
> +INSERT INTO t2 VALUES (4),(5);
> +
> +CREATE TABLE t3 (c INT);
> +INSERT INTO t3 VALUES (8),(3);
> +
> +set @@expensive_subquery_limit= 0;
> +
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +
> +flush status;
> +
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +
> +show status like "subquery_cache%";
> +show status like '%Handler_read%';
> +
> +set @@expensive_subquery_limit= default;
> +
> +EXPLAIN
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +
> +flush status;
> +
> +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3)))
> +FROM t2 alias1, t1 alias2, t1 alias3;
> +
> +show status like "subquery_cache%";
> +show status like '%Handler_read%';
> +
> +drop table t1, t2, t3;
> +
> --echo # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'sql/sql_lex.cc'
> --- a/sql/sql_lex.cc 2012-05-17 10:46:05 +0000
> +++ b/sql/sql_lex.cc 2012-05-29 21:18:53 +0000
> @@ -3443,6 +3443,7 @@ bool st_select_lex::optimize_unflattened
> }
>
> bool empty_union_result= true;
> + bool is_correlated_unit= false;
> /*
> If the subquery is a UNION, optimize all the subqueries in the UNION. If
> there is no UNION, then the loop will execute once for the subquery.
> @@ -3467,6 +3468,8 @@ bool st_select_lex::optimize_unflattened
> inner_join->select_options|= SELECT_DESCRIBE;
> }
> res= inner_join->optimize();
> + sl->update_correlated_cache();
> + is_correlated_unit|= sl->is_correlated;
> inner_join->select_options= save_options;
> un->thd->lex->current_select= save_select;
> if (empty_union_result)
> @@ -3482,6 +3485,9 @@ bool st_select_lex::optimize_unflattened
> }
> if (empty_union_result)
> subquery_predicate->no_rows_in_result();
> + if (!is_correlated_unit)
> + un->uncacheable&= ~UNCACHEABLE_DEPENDENT;
> + subquery_predicate->is_correlated= is_correlated_unit;
> }
> }
> return FALSE;
> @@ -3850,6 +3856,61 @@ void SELECT_LEX::update_used_tables()
> }
>
>
> +/**
> + @brief
> + Update is_correlated cache for this select
> +
> + @details
> +*/
> +
> +void st_select_lex::update_correlated_cache()
> +{
> + TABLE_LIST *tl;
> + List_iterator<TABLE_LIST> ti(leaf_tables);
> +
> + is_correlated= false;
> +
> + while ((tl= ti++))
> + {
> + if (tl->on_expr)
> + is_correlated|= test(tl->on_expr->used_tables() & OUTER_REF_TABLE_BIT);
> + for (TABLE_LIST *embedding= tl->embedding ; embedding ;
> + embedding= embedding->embedding)
> + {
> + if (embedding->on_expr)
> + is_correlated|= test(embedding->on_expr->used_tables() &
> + OUTER_REF_TABLE_BIT);
> + }
> + }
> +
> + if (join->conds)
> + is_correlated|= test(join->conds->used_tables() & OUTER_REF_TABLE_BIT);
> +
> + if (join->having)
> + is_correlated|= test(join->having->used_tables() & OUTER_REF_TABLE_BIT);
> +
> + if (join->tmp_having)
> + is_correlated|= test(join->tmp_having->used_tables() & OUTER_REF_TABLE_BIT);
> +
> + Item *item;
> + List_iterator_fast<Item> it(join->fields_list);
> + while ((item= it++))
> + is_correlated|= test(item->used_tables() & OUTER_REF_TABLE_BIT);
> +
> + for (ORDER *order= group_list.first; order; order= order->next)
> + is_correlated|= test((*order->item)->used_tables() & OUTER_REF_TABLE_BIT);
> +
> + if (!master_unit()->is_union())
> + {
> + for (ORDER *order= order_list.first; order; order= order->next)
> + is_correlated|= test((*order->item)->used_tables() & OUTER_REF_TABLE_BIT);
> + }
> +
> + if (!is_correlated)
> + uncacheable&= ~UNCACHEABLE_DEPENDENT;
> +}
> +
> +
> /**
> Set the EXPLAIN type for this subquery.
> */
>
> === modified file 'sql/sql_lex.h'
> --- a/sql/sql_lex.h 2012-05-17 10:46:05 +0000
> +++ b/sql/sql_lex.h 2012-05-29 21:18:53 +0000
> @@ -1018,6 +1018,7 @@ class st_select_lex: public st_select_le
> void mark_as_belong_to_derived(TABLE_LIST *derived);
> void increase_derived_records(ha_rows records);
> void update_used_tables();
> + void update_correlated_cache();
> void mark_const_derived(bool empty);
>
> bool save_leaf_tables(THD *thd);
>
> _______________________________________________
> commits mailing list
> commits(a)mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
1
0
Re: [Maria-developers] MDEV-181: XID crash recovery across binlog boundaries
by Sergei Golubchik 12 Jun '12
by Sergei Golubchik 12 Jun '12
12 Jun '12
Hi, Kristian!
Here's my review.
Only few comments and questions, see below.
On May 14, Kristian Nielsen wrote:
> At http://bazaar.launchpad.net/~maria-captains/maria/5.5
>
> ------------------------------------------------------------
> revno: 3372
> revision-id: knielsen(a)knielsen-hq.org-20120514095140-n685kn0n4vtronn5
> timestamp: Mon 2012-05-14 11:51:40 +0200
> message:
> MDEV-181: XID crash recovery across binlog boundaries
> === added file 'mysql-test/suite/binlog/t/binlog_xa_recover.test'
> --- mysql-test/suite/binlog/t/binlog_xa_recover.test 1970-01-01 00:00:00 +0000
> +++ mysql-test/suite/binlog/t/binlog_xa_recover.test 2012-05-14 09:51:40 +0000
> @@ -0,0 +1,172 @@
> +--source include/have_innodb.inc
> +--source include/have_debug.inc
> +--source include/have_debug_sync.inc
> +--source include/have_binlog_format_row.inc
> +
> +SET GLOBAL max_binlog_size= 4096;
> +
> +CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
> +CREATE TABLE t2 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Myisam;
> +
> +# Transactions are not guaranteed stored durably on disk in the engine until
> +# they are fsync()ed, which normally happens during commit(). But there is no
> +# guarantee that they will _not_ be durable, in particular loosing results
> +# of a write(2) system call normally requires a kernel crash (as opposed to
> +# just mysqld crash), which is inconvenient to do in a test suite.
> +# So instead we do an error insert to prevent commit_ordered() from being
> +# called in the engine - so nothing will be written to disk at all, and crash
> +# recovery is sure to be needed.
Are these assumptions reasonable? You seem to assume that without
commit_ordered the engine will still work normally. but simply won't
write changes to disk. Is it always the case?
> +SET @@global.debug_dbug='+d,skip_commit_ordered';
I don't see where you restore the old value of debug_dbug
> +
> +INSERT INTO t1 VALUES (0, REPEAT("x", 4100));
how comes this insert is not affected by skip_commit_ordered ?
> +
> +# Now start a bunch of transactions that span multiple binlog
> +# files. Leave then in the state prepared-but-not-committed in the engine
> +# and crash the server. Check that crash recovery is able to recover all
> +# of them.
> +
> +connect(con1,localhost,root,,);
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con1_ready WAIT_FOR _ever";
> +send INSERT INTO t1 VALUES (1, REPEAT("x", 4100));
> +
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
> +INSERT INTO t2 VALUES (1, "force binlog rotation");
> +
> +connect(con2,localhost,root,,);
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con2_ready WAIT_FOR _ever";
> +send INSERT INTO t1 VALUES (2, NULL);
> +
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con2_ready";
> +
> +connect(con3,localhost,root,,);
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con3_ready WAIT_FOR _ever";
> +send INSERT INTO t1 VALUES (3, REPEAT("x", 4100));
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con3_ready";
> +INSERT INTO t2 VALUES (2, "force binlog rotation");
> +# So we won't get warnings about t2 being crashed.
> +FLUSH TABLES t2;
> +
> +# Check that everything is committed in binary log.
> +--source include/show_binary_logs.inc
> +--let $binlog_file= master-bin.000001
> +--let $binlog_start= 4
> +--source include/show_binlog_events.inc
> +--let $binlog_file= master-bin.000002
> +--source include/show_binlog_events.inc
> +--let $binlog_file= master-bin.000003
> +--source include/show_binlog_events.inc
> +--let $binlog_file= master-bin.000004
> +--source include/show_binlog_events.inc
> +
> +# Check that transactions really are not yet committed in engine.
> +# (This works because of debug_dbug='+d,skip_commit_ordered').
> +--echo We should see only one entry here, a=0:
> +SELECT a FROM t1 ORDER BY a;
> +
> +
> +# Check that server will not purge too much.
> +PURGE BINARY LOGS TO "master-bin.000004";
> +--source include/show_binary_logs.inc
> +
> +# Now crash the server with one more transaction in prepared state.
> +system echo wait-binlog_xa_recover.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
better use --write_file directive
> +SET SESSION debug_dbug="+d,crash_commit_after_log";
> +--error 2006,2013
> +INSERT INTO t1 VALUES (4, NULL);
> +
> +system echo restart-group_commit_binlog_pos.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
same
> +
> +connection default;
> +--enable_reconnect
> +--source include/wait_until_connected_again.inc
> +
> +# Check that all transactions are recovered.
> +SELECT a FROM t1 ORDER BY a;
> +
> +
> +--echo *** Test that RESET MASTER waits for pending XIDs to be unlogged.
Ah, good point
> +
> +SET @old_max_binlog_size= @@global.max_binlog_size;
> +SET GLOBAL max_binlog_size= 4096;
> +# con10 will hang with a pending XID, blocking RESET MASTER.
> +connect(con10,localhost,root,,);
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con10_ready WAIT_FOR con10_go";
> +send INSERT INTO t1 VALUES (10, NULL);
> +
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con10_ready";
> +# Let's add a few binlog rotations just for good measure.
> +INSERT INTO t2 VALUES (10, REPEAT("x", 4100));
> +INSERT INTO t2 VALUES (11, REPEAT("x", 4100));
> +--source include/show_binary_logs.inc
> +SET DEBUG_SYNC= "execute_command_after_close_tables SIGNAL reset_master_done";
> +send RESET MASTER;
> +
> +connect(con11,localhost,root,,);
> +--echo This will timeout, as RESET MASTER is blocked
> +SET DEBUG_SYNC= "now WAIT_FOR reset_master_done TIMEOUT 1";
> +# Wake up transaction to allow RESET MASTER to complete.
> +SET DEBUG_SYNC= "now SIGNAL con10_go";
> +
> +connection con10;
> +reap;
> +
> +connection default;
> +reap;
> +--source include/show_binary_logs.inc
> +
> +
> +--echo *** Test that binlog N is active, and last pending trx in (N-1) is
> +--echo unlogged while there is still a pending trx in (N-2).
> +
> +connection con10;
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con10_ready WAIT_FOR con10_continue";
> +send INSERT INTO t1 VALUES (20, REPEAT("x", 4100));
> +
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con10_ready";
> +INSERT INTO t2 VALUES (3, "force binlog rotation");
> +
> +connection con11;
> +SET DEBUG_SYNC= "ha_commit_trans_after_log_and_order SIGNAL con11_ready WAIT_FOR con11_continue";
> +send INSERT INTO t1 VALUES (21, REPEAT("x", 4100));
> +
> +connection default;
> +SET DEBUG_SYNC= "now WAIT_FOR con11_ready";
> +INSERT INTO t2 VALUES (4, "force binlog rotation");
> +--source include/show_binary_logs.inc
> +--let $binlog_file= master-bin.000001
> +--source include/show_binlog_events.inc
> +--let $binlog_file= master-bin.000002
> +--source include/show_binlog_events.inc
> +--let $binlog_file= master-bin.000003
> +--source include/show_binlog_events.inc
> +
> +SET DEBUG_SYNC= "now SIGNAL con11_continue";
> +
> +connection con11;
> +reap;
> +
> +connection default;
> +--echo con10 is still pending, no new binlog checkpoint should have been logged.
> +--let $binlog_file= master-bin.000003
> +--source include/show_binlog_events.inc
> +
> +SET DEBUG_SYNC= "now SIGNAL con10_continue";
> +
> +connection con10;
> +reap;
> +
> +connection default;
> +--echo No XIDs are pending, a new binlog checkpoint should have been logged.
Could you also test recovery with multiple checkpoints in a binlog?
E.g. binlog, like the above, then crash, and see that it uses
the *last* checkpoint in the binlog.
> +--let $binlog_file= master-bin.000003
> +--source include/show_binlog_events.inc
> +
> +
> +# Cleanup
> +connection default;
> +DROP TABLE t1, t2;
> +SET GLOBAL max_binlog_size= @old_max_binlog_size;
>
> === modified file 'sql/log.h'
> --- sql/log.h 2012-01-16 19:16:35 +0000
> +++ sql/log.h 2012-05-14 09:51:40 +0000
> @@ -348,6 +348,15 @@
> time_t last_time;
> };
>
> +/*
> + We assign each binlog file an internal ID, used to identify them for unlog().
> + Ids start from BINLOG_COOKIE_START; the value BINLOG_COOKIE_DUMMY is special
> + meaning "no binlog" (we cannot use zero as that is reserved for error return
> + from log_and_order).
> +*/
> +#define BINLOG_COOKIE_DUMMY 1
> +#define BINLOG_COOKIE_START 2
Are these values completely internal and can be changed in any minor release without
any compatibility concerns? Or they're written to binlog or something?
> +
> class binlog_cache_mngr;
> class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG
> {
> @@ -388,10 +397,40 @@
> bool all;
> };
>
> + /*
> + A list of struct xid_count_per_binlog is used to keep track of how many
> + XIDs are in preared, but not committed, state in each binlog.
"prepared"
> +
> + When count drops to zero in a binlog after rotation, it means that there
> + are no more XIDs in prepared state, so that binlog is no longer needed
> + for XA crash recovery, and we can log a new binlog checkpoint event.
> +
> + The list is protected against simultaneous access from multiple
> + threads by LOCK_xid_list.
> + */
> + struct xid_count_per_binlog : public ilink {
> + char *binlog_name;
> + uint binlog_name_len;
> + ulong binlog_id;
> + long xid_count;
> + xid_count_per_binlog(); /* Give link error if constructor used. */
> + };
> + ulong current_binlog_id;
> + I_List<xid_count_per_binlog> binlog_xid_count_list;
Why I_List?
> + /*
> + When this is set, a RESET MASTER is in progress.
> +
> + Then we should not write any binlog checkpoints into the binlog (that
> + could result in deadlock on LOCK_log, and we will delete all binlog files
> + anyway). Instead we should signal COND_prep_xids whenever a new binlog
COND_xid_list?
> + checkpoint arrives - when all have arrived, RESET MASTER will complete.
> + */
> + bool reset_master_pending;
> +
> /* LOCK_log and LOCK_index are inited by init_pthread_objects() */
> mysql_mutex_t LOCK_index;
> - mysql_mutex_t LOCK_prep_xids;
> - mysql_cond_t COND_prep_xids;
> + mysql_mutex_t LOCK_xid_list;
> + mysql_cond_t COND_xid_list;
> mysql_cond_t update_cond;
> ulonglong bytes_written;
> IO_CACHE index_file;
> @@ -415,7 +454,6 @@
It'd be great, if your diff would include function names (diff -p).
An easy way of doing it is to use my diff_p plugin, see the last section in
http://kb.askmonty.org/en/how-to-get-more-out-of-bzr-when-working-on-mariadb
it's the most flexible, robust and universal solution that I have, and it
puts function names in all bzr-generated diffs, not only in "bzr diff" command.
> fix_max_relay_log_size).
> */
> ulong max_size;
> - long prepared_xids; /* for tc log - number of xids to remember */
> // current file sequence number for load data infile binary logging
> uint file_id;
> uint open_count; // For replication
> === modified file 'sql/log_event.cc'
> --- sql/log_event.cc 2012-05-09 07:35:46 +0000
> +++ sql/log_event.cc 2012-05-14 09:51:40 +0000
> @@ -5842,6 +5848,84 @@
>
>
> /**************************************************************************
> + Binlog_checkpoint_log_event methods
> +**************************************************************************/
> +
> +#if defined(HAVE_REPLICATION) && !defined(MYSQL_CLIENT)
> +void Binlog_checkpoint_log_event::pack_info(Protocol *protocol)
> +{
> + protocol->store(binlog_file_name, binlog_file_len, &my_charset_bin);
> +}
> +#endif
> +
> +
> +#ifdef MYSQL_CLIENT
> +void Binlog_checkpoint_log_event::print(FILE *file,
> + PRINT_EVENT_INFO *print_event_info)
> +{
> + Write_on_release_cache cache(&print_event_info->head_cache, file,
> + Write_on_release_cache::FLUSH_F);
> +
> + if (print_event_info->short_form)
> + return;
> + print_header(&cache, print_event_info, FALSE);
> + my_b_printf(&cache, "\tBinlog checkpoint ");
> + my_b_write(&cache, (uchar*)binlog_file_name, binlog_file_len);
> + my_b_printf(&cache, "\n");
> +}
> +#endif /* MYSQL_CLIENT */
> +
> +
> +#ifdef MYSQL_SERVER
> +Binlog_checkpoint_log_event::Binlog_checkpoint_log_event(
> + const char *binlog_file_name_arg,
> + uint binlog_file_len_arg)
> + :Log_event(),
> + binlog_file_name(my_strndup(binlog_file_name_arg, binlog_file_len_arg,
> + MYF(MY_WME))),
> + binlog_file_len(binlog_file_len_arg)
> +{
> + cache_type= EVENT_NO_CACHE;
> +}
> +#endif /* MYSQL_SERVER */
> +
> +
> +Binlog_checkpoint_log_event::Binlog_checkpoint_log_event(
> + const char *buf, uint event_len,
> + const Format_description_log_event *description_event)
> + :Log_event(buf, description_event), binlog_file_name(0)
> +{
> + uint8 header_size= description_event->common_header_len;
> + uint8 post_header_len=
> + description_event->post_header_len[BINLOG_CHECKPOINT_EVENT-1];
> + if (event_len < header_size + post_header_len ||
> + post_header_len < 4)
I'd replace this 4 with BINLOG_CHECKPOINT_HEADER_LEN
> + return;
> + buf+= header_size;
and here I'd add
compile_time_assert(BINLOG_CHECKPOINT_HEADER_LEN == 4); // see uint4korr and int4store below
> + binlog_file_len= uint4korr(buf);
> + if (event_len - (header_size + post_header_len) < binlog_file_len)
> + return;
> + binlog_file_name= my_strndup(buf + post_header_len, binlog_file_len,
> + MYF(MY_WME));
> + return;
> +}
> +
> +
> +#ifndef MYSQL_CLIENT
> +bool Binlog_checkpoint_log_event::write(IO_CACHE *file)
> +{
> + uchar buf[BINLOG_CHECKPOINT_HEADER_LEN];
> + int4store(buf, binlog_file_len);
> + return write_header(file, BINLOG_CHECKPOINT_HEADER_LEN + binlog_file_len) ||
> + wrapper_my_b_safe_write(file, buf, BINLOG_CHECKPOINT_HEADER_LEN) ||
> + wrapper_my_b_safe_write(file, (const uchar *)binlog_file_name,
> + binlog_file_len) ||
> + write_footer(file);
> +}
> +#endif /* MYSQL_CLIENT */
> +
> +
> +/**************************************************************************
> Intvar_log_event methods
> **************************************************************************/
>
> === modified file 'sql/log.cc'
> --- sql/log.cc 2012-04-10 06:28:13 +0000
> +++ sql/log.cc 2012-05-14 09:51:40 +0000
> @@ -2912,13 +2929,30 @@
> DBUG_ENTER("cleanup");
> if (inited)
> {
> + xid_count_per_binlog *b;
> +
> inited= 0;
> close(LOG_CLOSE_INDEX|LOG_CLOSE_STOP_EVENT);
> delete description_event_for_queue;
> delete description_event_for_exec;
> +
> + while ((b= binlog_xid_count_list.get()))
'get' - what a bad name. I checked the implementation and now I know
what it's doing, but absoletely not what one would expect :(
It's not your fault, of course, just ranting...
> + {
> + /*
> + There should be no pending XIDs at shutdown, and only one entry (for
> + the active binlog file) in the list.
> + */
> + DBUG_ASSERT(b->xid_count == 0);
> + DBUG_ASSERT(!binlog_xid_count_list.head());
> + my_free(b);
> + }
> +
> mysql_mutex_destroy(&LOCK_log);
> mysql_mutex_destroy(&LOCK_index);
> + mysql_mutex_destroy(&LOCK_xid_list);
> mysql_cond_destroy(&update_cond);
> + mysql_cond_destroy(&COND_queue_busy);
> + mysql_cond_destroy(&COND_xid_list);
> }
> DBUG_VOID_RETURN;
> }
> @@ -3506,6 +3587,42 @@
> mysql_mutex_lock(&LOCK_log);
> mysql_mutex_lock(&LOCK_index);
>
> + if (!is_relay_log)
> + {
> + /*
> + We are going to nuke all binary log files.
> + So first wait until all pending binlog checkpoints have completed.
> + */
> + mysql_mutex_lock(&LOCK_xid_list);
> + xid_count_per_binlog *b;
> + reset_master_pending= true;
> + for (;;)
> + {
> + I_List_iterator<xid_count_per_binlog> it(binlog_xid_count_list);
> + while ((b= it++))
> + {
> + if (b->xid_count > 0)
> + break;
> + }
> + if (!b)
> + break; /* No more pending XIDs */
> + /*
> + Wait until signalled that one more binlog dropped to zero, then check
> + again.
> + */
> + mysql_cond_wait(&COND_xid_list, &LOCK_xid_list);
> + }
I would've done it simpler, like
while (b= binlog_xid_count_list.head())
{
while (b->xid_count > 0)
mysql_cond_wait(&COND_xid_list, &LOCK_xid_list);
my_free(binlog_xid_count_list.get());
}
> +
> + /*
> + Now all XIDs are fully flushed to disk, and we are holding LOCK_log so
> + no new ones will be written. So we can proceed to delete the logs.
> + */
> + while ((b= binlog_xid_count_list.get()))
> + my_free(b);
> + reset_master_pending= false;
> + mysql_mutex_unlock(&LOCK_xid_list);
> + }
> +
> /*
> The following mutex is needed to ensure that no threads call
> 'delete thd' as we would then risk missing a 'rollback' from this
> @@ -5761,6 +5877,37 @@
> DBUG_RETURN(error);
> }
>
> +void
> +MYSQL_BIN_LOG::write_binlog_checkpoint_event_already_locked(const char *name,
> + uint len)
> +{
> + Binlog_checkpoint_log_event ev(name, len);
> + /*
> + Note that we must sync the binlog checkpoint to disk.
> + Otherwise a subsequent log purge could delete binlogs that XA recovery
> + thinks are needed (even though they are not really).
> + */
> + if (!ev.write(&log_file) && !flush_and_sync(0))
> + {
> + bool check_purge= false;
> + signal_update();
> + rotate(false, &check_purge);
> + if (check_purge)
> + purge();
why "write binlog checkpoint event" implies signal_update(), rotate(), and purge() ?
> + return;
> + }
> +
> + /*
> + If we fail to write the checkpoint event, something is probably really
> + bad with the binlog. We complain in the error log.
> + Note that failure to write binlog checkpoint does not compromise the
> + ability to do crash recovery - crash recovery will just have to scan a
> + bit more of the binlog than strictly necessary.
> + */
> + sql_print_error("Failed to write binlog checkpoint event to binary log\n");
> +}
> +
> +
> /**
> Write a cached log entry to the binary log.
> - To support transaction over replication, we wrap the transaction
> @@ -6061,11 +6207,11 @@
> {
> /*
> If we fail to rotate, which thread should get the error?
> - We give the error to the *last* transaction thread; that seems to
> - make the most sense, as it was the last to write to the log.
> + We give the error to the leader, as any my_error() thrown inside
> + rotate() will have been registered for the leader THD.
> */
> - last_in_queue->error= ER_ERROR_ON_WRITE;
> - last_in_queue->commit_errno= errno;
> + leader->error= ER_ERROR_ON_WRITE;
> + leader->commit_errno= errno;
Where did that change happen? Is it related to MDEV-181 at all, or it's an unrelated bugfix?
> check_purge= false;
> }
> }
> @@ -6082,9 +6228,6 @@
> */
> mysql_mutex_unlock(&LOCK_log);
>
> - if (check_purge)
> - purge();
Why was it moved down?
> -
> DEBUG_SYNC(leader->thd, "commit_after_release_LOCK_log");
> ++num_group_commits;
>
> @@ -7427,11 +7560,23 @@
>
> cache_mngr->using_xa= TRUE;
> cache_mngr->xa_xid= xid;
> +#ifndef DBUG_OFF
> + cache_mngr->cookie= 0;
> +#endif
> err= binlog_commit_flush_xid_caches(thd, cache_mngr, all, xid);
>
> DEBUG_SYNC(thd, "binlog_after_log_and_order");
>
> - DBUG_RETURN(!err);
> + if (err)
> + DBUG_RETURN(0);
> + /*
> + If using explicit user XA, we will not have XID. We must still return a
> + non-zero cookie (as zero cookie signals error).
> + */
> + if (!xid)
> + DBUG_RETURN(1);
s/1/BINLOG_COOKIE_DUMMY/
> + DBUG_ASSERT(cache_mngr->cookie != 0);
> + DBUG_RETURN(cache_mngr->cookie);
> }
>
> /*
> @@ -7446,40 +7591,135 @@
> binary log.
> */
> void
> -TC_LOG_BINLOG::mark_xids_active(uint xid_count)
> +TC_LOG_BINLOG::mark_xids_active(ulong cookie, uint xid_count)
> {
> + xid_count_per_binlog *b;
> +
> DBUG_ENTER("TC_LOG_BINLOG::mark_xids_active");
> - DBUG_PRINT("info", ("xid_count=%u", xid_count));
> - mysql_mutex_lock(&LOCK_prep_xids);
> - prepared_xids+= xid_count;
> - mysql_mutex_unlock(&LOCK_prep_xids);
> + DBUG_PRINT("info", ("cookie=%lu xid_count=%u", cookie, xid_count));
> + DBUG_ASSERT(cookie != 0 && cookie != BINLOG_COOKIE_DUMMY);
> +
> + mysql_mutex_lock(&LOCK_xid_list);
> + I_List_iterator<xid_count_per_binlog> it(binlog_xid_count_list);
> + while ((b= it++))
> + {
> + if (b->binlog_id == cookie)
> + {
> + b->xid_count += xid_count;
> + break;
> + }
> + }
> + /*
> + As we do not delete elements until count reach zero, elements should always
> + be found.
> + */
> + DBUG_ASSERT(b);
> + mysql_mutex_unlock(&LOCK_xid_list);
> DBUG_VOID_RETURN;
> }
>
> /*
> - Once an XID is committed, it is safe to rotate the binary log, as it can no
> - longer be needed during crash recovery.
> + Once an XID is committed, it can no longer be needed during crash recovery,
> + as it has been durably recorded on disk as "committed".
>
> This function is called to mark an XID this way. It needs to decrease the
> - count of pending XIDs, and signal the log rotator thread when it reaches zero.
> + count of pending XIDs in the corresponding binlog. When the count reaches
> + zero (for an "old" binlog that is not the active one), that binlog file no
> + longer need to be scanned during crash recovery, so we can log a new binlog
> + checkpoint.
> */
> void
> -TC_LOG_BINLOG::mark_xid_done()
> +TC_LOG_BINLOG::mark_xid_done(ulong cookie)
> {
> - my_bool send_signal;
> + xid_count_per_binlog *b;
> + bool first;
> + ulong current;
>
> DBUG_ENTER("TC_LOG_BINLOG::mark_xid_done");
> - mysql_mutex_lock(&LOCK_prep_xids);
> - // prepared_xids can be 0 if the transaction had ignorable errors.
> - DBUG_ASSERT(prepared_xids >= 0);
> - if (prepared_xids > 0)
> - prepared_xids--;
> - send_signal= (prepared_xids == 0);
> - mysql_mutex_unlock(&LOCK_prep_xids);
> - if (send_signal) {
> - DBUG_PRINT("info", ("prepared_xids=%lu", prepared_xids));
> - mysql_cond_signal(&COND_prep_xids);
> - }
> + if (cookie == BINLOG_COOKIE_DUMMY)
> + DBUG_VOID_RETURN; /* Nothing to do. */
> +
> + mysql_mutex_lock(&LOCK_xid_list);
> + current= current_binlog_id;
> + I_List_iterator<xid_count_per_binlog> it(binlog_xid_count_list);
> + first= true;
> + while ((b= it++))
> + {
> + if (b->binlog_id == cookie)
> + {
> + --b->xid_count;
> + break;
> + }
> + first= false;
> + }
> + /* Binlog is always found, as we do not remove until count reaches 0 */
> + DBUG_ASSERT(b);
> + if (likely(cookie == current && !reset_master_pending) ||
> + b->xid_count != 0 || !first)
> + {
> + /* No new binlog checkpoint reached yet. */
> + mysql_mutex_unlock(&LOCK_xid_list);
> + DBUG_VOID_RETURN;
> + }
> +
> + /*
> + Now log a binlog checkpoint for the first binlog file with a non-zero count.
> +
> + Note that it is possible (though perhaps unlikely) that when count of
> + binlog (N-2) drops to zero, binlog (N-1) is already at zero. So we may
> + need to skip several entries before we find the one to log in the binlog
> + checkpoint event.
> +
> + We chain the locking of LOCK_xid_list and LOCK_log, so that we ensure that
> + Binlog_checkpoint_events are logged in order. This simplifies recovery a
> + bit, as it can just take the last binlog checkpoint in the log, rather
> + than compare all found against each other to find the one pointing to the
> + most recent binlog.
> +
> + Note also that we need to first release LOCK_xid_list, then aquire
> + LOCK_log, then re-aquire LOCK_xid_list. If we were to take LOCK_log while
> + holding LOCK_xid_list, we might deadlock with other threads that take the
> + locks in the opposite order.
> +
> + If a RESET MASTER is pending, we are about to remove all log files, and
> + the RESET MASTER thread is waiting for all pending unlog() calls to
> + complete while holding LOCK_log. In this case we should not log a binlog
> + checkpoint event (it would be deleted immediately anywat and we would
> + deadlock on LOCK_log) but just signal the thread.
> + */
> + if (!reset_master_pending)
> + {
> + mysql_mutex_unlock(&LOCK_xid_list);
> + mysql_mutex_lock(&LOCK_log);
> + mysql_mutex_lock(&LOCK_xid_list);
> + }
> + for (;;)
> + {
> + /* Remove initial element(s) with zero count. */
> + b= binlog_xid_count_list.get();
Eh. You need to check whether
binlog_xid_count_list.head()->xid_count == 0 before removing it like that.
When you released LOCK_xid_list another thread could've taken over and
deleted all zero count elements. So now you could find that
binlog_xid_count_list.head() has non-zero count.
> + my_free(b);
> + b= binlog_xid_count_list.head();
> + /*
> + Normally, we must not remove all elements in the list.
> + Only if a RESET MASTER is in progress may we delete everything - RESET
> + MASTER has LOCK_log held, and will create a new initial element before
> + releasing the lock.
> + */
> + DBUG_ASSERT(b || reset_master_pending);
> + if (unlikely(!b) || likely(b->binlog_id == current) || b->xid_count > 0)
> + break;
> + }
> + if (reset_master_pending)
> + {
> + mysql_cond_signal(&COND_xid_list);
> + mysql_mutex_unlock(&LOCK_xid_list);
> + DBUG_VOID_RETURN;
> + }
> +
> + mysql_mutex_unlock(&LOCK_xid_list);
> + write_binlog_checkpoint_event_already_locked(b->binlog_name,
> + b->binlog_name_len);
> + mysql_mutex_unlock(&LOCK_log);
> DBUG_VOID_RETURN;
> }
>
> @@ -7507,19 +7755,104 @@
>
> fdle->flags&= ~LOG_EVENT_BINLOG_IN_USE_F; // abort on the first error
>
> - while ((ev= Log_event::read_log_event(log, 0, fdle,
> - opt_master_verify_checksum))
> - && ev->is_valid())
> + /*
> + Scan the binlog for XIDs that need to be committed if still in the
> + prepared stage.
> +
> + Start with the latest binlog file, then continue with any other binlog
> + files if the last found binlog checkpoint indicates it is needed.
> + */
> +
> + first_round= true;
> + for (;;)
> {
> - if (ev->get_type_code() == XID_EVENT)
> - {
> - Xid_log_event *xev=(Xid_log_event *)ev;
> - uchar *x= (uchar *) memdup_root(&mem_root, (uchar*) &xev->xid,
> - sizeof(xev->xid));
> - if (!x || my_hash_insert(&xids, x))
> + while ((ev= Log_event::read_log_event(first_round ? first_log : &log,
> + 0, fdle, opt_master_verify_checksum))
> + && ev->is_valid())
> + {
> + switch (ev->get_type_code())
> + {
> + case XID_EVENT:
> + {
> + Xid_log_event *xev=(Xid_log_event *)ev;
> + uchar *x= (uchar *) memdup_root(&mem_root, (uchar*) &xev->xid,
> + sizeof(xev->xid));
> + if (!x || my_hash_insert(&xids, x))
> + {
> + delete ev;
> + goto err2;
> + }
> + break;
> + }
> + case BINLOG_CHECKPOINT_EVENT:
> + if (first_round)
> + {
> + Binlog_checkpoint_log_event *cev= (Binlog_checkpoint_log_event *)ev;
> + if (cev->binlog_file_len >= FN_REFLEN)
> + {
> + sql_print_error("Incorrect binlog checkpoint event with too long "
> + "file name found. Aborting.");
This is a recovery. After a crash. You can be more tolerant here.
In this particular case there is no need to abort, you can safely ignore
incorrect checkpoints. This only means that the recovery will start from the
previous checkpoint and it'll take longer.
> + delete ev;
> + goto err2;
> + }
> + binlog_checkpoint_len= cev->binlog_file_len;
> + memcpy(binlog_checkpoint_name, cev->binlog_file_name,
> + binlog_checkpoint_len);
> + binlog_checkpoint_name[binlog_checkpoint_len]= '\0';
> + break;
> + }
> + default:
> + /* Nothing. */
> + break;
> + }
> + delete ev;
> + }
> +
> + /*
> + If the last binlog checkpoint event points to an older log, we have to
> + scan all logs from there also, to get all possible XIDs to recover.
> +
> + If there was no binlog checkpoint event at all, this means the log was
> + written by an older version of MariaDB (or MySQL) - these always have an
> + (implicit) binlog checkpoint event at the start of the last binlog file.
> + */
> + if (first_round)
> + {
> + if (!binlog_checkpoint_len)
> + break;
> + first_round= false;
> + if (find_log_pos(linfo, binlog_checkpoint_name, 1))
> + {
> + sql_print_error("Binlog file '%s' not found in binlog index, needed "
> + "for recovery. Aborting.", binlog_checkpoint_name);
> goto err2;
> - }
> - delete ev;
> + }
> + }
> + else
> + {
> + end_io_cache(&log);
> + mysql_file_close(file, MYF(MY_WME));
> + file= -1;
> + }
> +
> + if (0 == strcmp(linfo->log_file_name, last_log_name))
> + break; // No more files to do
> + if ((file= open_binlog(&log, linfo->log_file_name, &errmsg)) < 0)
> + {
> + sql_print_error("%s", errmsg);
> + goto err2;
> + }
> + /*
> + We do not need to read the Format_description_log_event of other binlog
> + files. It is not possible for a binlog checkpoint to span multiple
> + binlog files written by different versions of the server. So we can use
> + the first one read for reading from all binlog files.
> + */
> + if (find_next_log(linfo, 1))
> + {
> + sql_print_error("Error reading binlog files during recovery. Aborting.");
> + goto err2;
> + }
> }
>
> if (ha_recover(&xids))
> === modified file 'sql/sql_repl.cc'
> --- sql/sql_repl.cc 2012-04-27 08:20:38 +0000
> +++ sql/sql_repl.cc 2012-05-14 09:51:40 +0000
> @@ -624,6 +624,30 @@
> }
>
> /*
> + Do not send binlog checkpoint events to a slave that does not understand it.
> + */
> + if (unlikely(event_type == BINLOG_CHECKPOINT_EVENT) &&
> + mariadb_slave_capability < MARIA_SLAVE_CAPABILITY_BINLOG_CHECKPOINT)
> + {
> + if (mariadb_slave_capability >= MARIA_SLAVE_CAPABILITY_TOLERATE_HOLES)
> + {
> + /* This slave can tolerate events omitted from the binlog stream. */
> + return NULL;
> + }
> + else
> + {
> + /*
> + The slave does not understand BINLOG_CHECKPOINT_EVENT. Send a dummy
> + event instead, with same length so slave does not get confused about
> + binlog positions.
> + */
Hm. Above and below the code that you've added events are simply skipped,
without checks and dummy events. Why do you bother to replace with dummies?
> + if (Query_log_event::dummy_event(packet, ev_offset, current_checksum_alg))
> + return "Failed to replace binlog checkpoint event with dummy: "
> + "too small event.";
> + }
> + }
> +
> + /*
> Skip events with the @@skip_replication flag set, if slave requested
> skipping of such events.
> */
Regards,
Sergei
2
1
[Maria-developers] Fwd: crash in slave replication with XA transaction
by Zardosht Kasheff 11 Jun '12
by Zardosht Kasheff 11 Jun '12
11 Jun '12
Hello all,
We see this on Maria 5.2 and Maria 5.5 as well. Does the following fix work?
-Zardosht
---------- Forwarded message ----------
From: Rich Prohaska <prohaska(a)tokutek.com>
Date: Mon, Jun 4, 2012 at 1:23 PM
Subject: crash in slave replication with XA transaction
To: internals(a)lists.mysql.com
Hello,
We are running MySQL 5.5.21 with both InnoDB and TokuDB as XA storage
engines. Â When one runs a transaction that inserts into an InnoDB
table and a TokuDB table, the commit crashes the replication slave at
log.cc line 6602 because cache_mngr == NULL. Â We found a pattern in
various log functions in log.cc that calls binlog_setup_trx_data to
initialize the txn state.
This change fixes our immediate problem.
$ diff log.cc.orig log.cc
6595a6596,6597
> Â if (thd_get_ha_data(thd, binlog_hton) == NULL)
> Â Â thd->binlog_setup_trx_data();
Is this a correct fix? Â If so, are there other places that need a
similar change?
Thanks
Rich Prohaska
The transaction is:
create table t1 (a int primary key) engine=innodb;
create table t2 (a int primary key) engine=tokudb;
set autocommit=0;
insert into t1 values (1);
insert into t2 values (2);
commit;
the slave crashes while handling the commit binlog entry.
--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: Â Â http://lists.mysql.com/internals
3
5
Re: [Maria-developers] [Commits] Rev 3426: LP1008334 : Speedup specific datetime queries that got slower with introduction of microseconds in file:///H:/bzr/5.5/
by Sergei Golubchik 08 Jun '12
by Sergei Golubchik 08 Jun '12
08 Jun '12
Hi, Vladislav!
On Jun 08, Vladislav Vaintroub wrote:
> ------------------------------------------------------------
> revno: 3426
> revision-id: wlad(a)montyprogram.com-20120608000841-ieo4vumio11pc8p9
> parent: wlad(a)montyprogram.com-20120530182054-g7sll9u4gsitvgqe
> fixes bug(s): https://launchpad.net/bugs/1008334
> committer: Vladislav Vaintroub <wlad(a)montyprogram.com>
> branch nick: 5.5
> timestamp: Fri 2012-06-08 02:08:41 +0200
> message:
> LP1008334 : Speedup specific datetime queries that got slower with introduction of microseconds
>
> - Item::get_seconds() now skips decimal arithmetic, if decimals is 0. This significantly speeds up from_unixtime() if no fractional part is passed.
> - replace sprintfs used to format temporal values by hand-coded formatting
Looks ok.
Would be good to mention the ~25% speedup in the comment above
fmt_number. Like "Gives 25% speedup when converting temporal values to
strings, as compared to sprintf".
So that one wouldn't need to dig up bzr history every time.
> === modified file 'sql/item.cc'
> --- a/sql/item.cc 2012-05-21 18:54:41 +0000
> +++ b/sql/item.cc 2012-06-08 00:08:41 +0000
> @@ -1265,7 +1265,7 @@
>
> bool Item::get_seconds(ulonglong *sec, ulong *sec_part)
> {
> - if (result_type() == INT_RESULT)
> + if (decimals == 0 || result_type() == INT_RESULT)
I'd expect that checking for decimals==0 alone should be enough.
Could you try to run the test suite with the
DBUG_ASSERT(result_type() != INT_RESULT || decimals == 0);
please?
> { // optimize for an important special case
> longlong val= val_int();
> bool neg= val < 0 && !unsigned_flag;
Regards,
Sergei
1
0