- developers - lists.mariadb.org

[Maria-developers] MDEV-83: pushdown of expensive predicates
by Sergei Petrunia 21 May '12

21 May '12

Hi Timour, Some thoughts about MDEV-83. The jira entry says: > The main difference in these two plans is that with the first execution plan > the dependent subquery: ... is evaluated before the table part is accessed > while many rows where (p_name like 'forest%') is not true are filtered out > and evaluation of the dependent subquery is not needed for them. The way I read this, the only way one could figure out that the subquery should be evaluated at later phase (after table part) is to figure out that selectivity of table `part`, together with its condition of p_name like 'forest%' is less than one. That way, subquery is evaluated fewer times, and query execution will be cheaper. I don't see any index that covers p_name column. This means that current optimizer has no clue about selectivity of conditions over p_name column, and hence is unable to make the correct decision. If I add an index on p_name, then I get a different query plan (I didn't check if is slower or faster than the original). I think, one of the first things this MDEV needs is to figure out how exactly we will get selectivities for non-indexed columns. If we get them, and start to take them into account in join optimizer(*), the example provided in this MDEV/ bug may just go away :-). The general problem will remain, though. (*) { Currently, the optimizer has "quick_condition_rows", which may cause join fanout to be less than 1 (check out examples with EXPLAIN EXTENDED and filtered != 100% in the testsuite). The problem is that quick_condition_rows is not used consistently for all ways to access the table, for example it is not taken into account when considering ref access. I do not know whether Igor's work in best_access_path() includes making it consistently make use of quick_condition_rows estimates. } == Subquery predicate attachment considerations == Some thoughts about the problem. There are three factors we may want/need to take into account when attaching subquery predicate to some join_tab: 1. Fanout of join tabs 2. Cost of the subquery predicate evaluation 3. Selectivity of the subquery predicate. The example used in this MDEV item can be resolved by taking into account #1 and #2. For the first step, we only need to know - accurate join tab fanout (#1), - that subquery predicate evaluation has some cost ( #2 != 0) these two allow us to set/resolve a task of attaching the subquery predicate to the join_tab where total cost of its evaluations is minimized. The exact value of subquery evaluation cost is irrelevant. It will only become relevant when/if we start to make a choice between evaluating subquery predicate, or preforming join with the next join_tab. In order for that choice to be meaningful, we will need to have an idea about subquery predicate's selectivity. That way, we will be able to make a choice between 1. evaluate subquery, then make join with next join tab cost(subquery_pred) + selectivity(subquery_pred) * cost(join_tab) and 2. make join with next join_tab, then evaluate the subquery: cost(join_tab) + selectivity(join_tab) * cost(join_tab) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] MDEV-136 Non-blocking "set read_only"
by Sergei Golubchik 20 May '12

20 May '12

Hi, Holyfoot! Looks good to me! See a couple of comments below. Please push after fixing all that. On May 17, holyfoot(a)askmonty.org wrote: > ------------------------------------------------------------ > revno: 3518 > revision-id: holyfoot(a)askmonty.org-20120517131405-y12erfkgta2ym2pn > parent: sanja(a)montyprogram.com-20120507181437-1zl1v1uxje7v6994 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-136 > timestamp: Thu 2012-05-17 18:14:05 +0500 > message: > MDEV-136 Non-blocking "set read_only". > Handle the 'set read_only=1' in lighter way, than the FLUSH TABLES READ LOCK; > For the transactional engines we don't wait for operations on that tables to finish. > > === modified file 'mysql-test/t/read_only_innodb.test' > --- a/mysql-test/t/read_only_innodb.test 2008-04-08 05:20:58 +0000 > +++ b/mysql-test/t/read_only_innodb.test 2012-05-17 13:14:05 +0000 > @@ -75,7 +75,28 @@ > SELECT * FROM t1; > COMMIT; > > -connection default; > +# > +# Tests that LOCK TABLE doesn't block the SET READ_ONLY=1 for the InnoDB tables > +# > +connection default; > +UNLOCK TABLES; > + > +--echo connection con1; > +connection con1; > +lock table t1 write; See whether you can directly test that the table was not reopened. Perhaps by examining the corresponding SHOW STATUS variables you could do that. > + > +--echo connection default; > +connection default; > +set global read_only=1; > + > +--echo connection con1; > +connection con1; > +unlock tables; > + > +--echo connection default; > +connection default; > +SET GLOBAL read_only=0; > + > UNLOCK TABLES; > DROP TABLE t1; > DROP USER test@localhost; > === modified file 'sql/sql_base.cc' > --- a/sql/sql_base.cc 2012-04-19 06:16:30 +0000 > +++ b/sql/sql_base.cc 2012-05-17 13:14:05 +0000 > @@ -933,7 +938,14 @@ > for (uint idx=0 ; idx < open_cache.records ; idx++) > { > TABLE *table=(TABLE*) hash_element(&open_cache,idx); > - if (table->in_use) > + /* We don't increment the refresh_version when set_readonly_mode, */ > + /* but we still need non-transactional tables to be reopened. */ > + /* So we set their versions as 'refresh_version - 1', which marks */ > + /* them for the 'needs_reopen_or_table_lock()' */ please, use the standard mysql comment style as you can see everywhere in the sources > + if (set_readonly_mode && !table->file->has_transactions()) > + table->s->version= refresh_version-1; better set it to 0, that's what tdc_remove_table() does. And we won't need to think about possible race conditions. > + if (table->in_use && > + (!set_readonly_mode || !table->file->has_transactions())) > table->in_use->some_tables_deleted= 1; > } > } Regards, Sergei

1 0

[Maria-developers] On the DBUG_ASSERT in create_ref_for_key()
by Sergei Petrunia 20 May '12

20 May '12

Hi Sergei, Re the DBUG_ASSERT(length > 0 && keyparts != 0); line in create_ref_for_key() in 5.3: I've looked - the assert can be commented out, because it's valid to have length=0 && !keyparts for a variant of LooseScan algorithm. Some more details: I've tried to come up another assert statement, that would allow length==0 only when LooseScan is used. Unfortunately, it is not easy - the query plan is encoded in join->best_positions, while create_ref_for_key() has access only to the JOIN_TAB. Since SJ-Materialization, the query plan is represented with multiple JOIN_TAB arrays, while there is only one POSITION array, which means that one needs to loop to find the POSITION which corresponds to the JOIN_TAB. EXPLAIN and execution use JOIN_TAB::loosescan_match_tab, but it is set by setup_semijoin_dups_elimination() which is invoked after create_ref_for_key() is. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] MDEV-225: Replace with dummy events an event that is not understood by a slave to which it should be sent
by Sergei Golubchik 19 May '12

19 May '12

Hi, Kristian! On Apr 27, knielsen(a)knielsen-hq.org wrote: > revno: 3368 > revision-id: knielsen(a)knielsen-hq.org-20120427082038-frqt8ykqy77x3r8e > parent: sergii(a)pisem.net-20120410063020-fm6a5ds0iggihq02 > committer: knielsen(a)knielsen-hq.org > branch nick: work-5.5-mdev225 > timestamp: Fri 2012-04-27 10:20:38 +0200 > message: > MDEV-225: Replace with dummy events an event that is not understood > by a slave to which it should be sent > > Add function to replace arbitrary event with dummy event. > > Add code which uses this to fix the bug that enabling row_annotate > events on the master breaks slaves which do not request such events. > > Add that slaves set a variable @mariadb_slave_capability to inform > the master in a robust way about which events it can, and cannot, > handle. I'm not sure I like the idea of magic user variables. Why wouldn't you create a session server variable @@mariadb_slave_capability? Also: Why would "a later release of a slave change what an earlier release of a master believes about its capabilities"? See comments to the code below. > === added file 'mysql-test/suite/rpl/t/rpl_mariadb_slave_capability.test' > --- a/mysql-test/suite/rpl/t/rpl_mariadb_slave_capability.test 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/suite/rpl/t/rpl_mariadb_slave_capability.test 2012-04-27 08:20:38 +0000 > @@ -0,0 +1,96 @@ > +--source include/master-slave.inc > +--source include/have_debug.inc > +--source include/have_binlog_format_row.inc > + > +connection master; > + > +set @old_master_binlog_checksum= @@global.binlog_checksum; > +CREATE TABLE t1 (a INT PRIMARY KEY); > +INSERT INTO t1 VALUES (0); > + > +sync_slave_with_master; > +connection slave; > + > +--echo # Test slave with no capability gets dummy event, which is ignored. > +--source include/stop_slave.inc > +SET @@global.debug_dbug='d,simulate_slave_capability_none'; Don't do that please, it'll stop any debug trace for ./mtr --debug. Instead, write SET @@global.debug_dbug='+d,simulate_slave_capability_old_53'; ... your tests SET @@global.debug_dbug='-d,simulate_slave_capability_old_53'; or SET @debug_old=@@global.debug_dbug; SET @@global.debug_dbug='+d,simulate_slave_capability_old_53'; ... your tests SET @@global.debug_dbug=@debug_old; > +--source include/start_slave.inc > +connection master; > +sync_slave_with_master; > +connection slave; > +let $relaylog_start= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1); > + > +connection master; > +SET SESSION binlog_annotate_row_events = ON; > +let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); > +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); > +# A short event, to test when we need to use user_var_event for dummy event. ... > === modified file 'sql/log_event.cc' > --- a/sql/log_event.cc 2012-04-10 06:28:13 +0000 > +++ b/sql/log_event.cc 2012-04-27 08:20:38 +0000 > @@ -3277,6 +3277,114 @@ Query_log_event::Query_log_event(const c > } > > > +/* > + Replace a binlog event read into a packet with a dummy event. Either a > + Query_log_event that has just a comment, or if that will not fit in the > + space used for the event to be replaced, then a NULL user_var event. > + > + This is used when sending binlog data to a slave which does not understand > + this particular event and which is too old to support informational events > + or holes in the event stream. > + > + This allows to write such events into the binlog on the master and still be > + able to replicate against old slaves without them breaking. > + > + Clears the flag LOG_EVENT_THREAD_SPECIFIC_F and set LOG_EVENT_SUPPRESS_USE_F. > + Overwrites the type with QUERY_EVENT (or USER_VAR_EVENT), and replaces the > + body with a minimal query / NULL user var. > + > + Returns zero on success, -1 if error due to too little space in original > + event. A minimum of 25 bytes (19 bytes fixed header + 6 bytes in the body) > + is needed in any event to be replaced with a dummy event. > +*/ > +int > +Query_log_event::dummy_event(String *packet, ulong ev_offset, > + uint8 checksum_alg) > +{ > + uchar *p= (uchar *)packet->ptr() + ev_offset; > + size_t data_len= packet->length() - ev_offset; > + uint16 flags; > + static const size_t min_user_var_event_len= > + LOG_EVENT_HEADER_LEN + UV_NAME_LEN_SIZE + 1 + UV_VAL_IS_NULL; // 25 > + static const size_t min_query_event_len= > + LOG_EVENT_HEADER_LEN + QUERY_HEADER_LEN + 1 + 1; // 34 > + > + if (checksum_alg == BINLOG_CHECKSUM_ALG_CRC32) > + data_len-= BINLOG_CHECKSUM_LEN; > + else > + DBUG_ASSERT(checksum_alg == BINLOG_CHECKSUM_ALG_UNDEF || > + checksum_alg == BINLOG_CHECKSUM_ALG_OFF); > + > + if (data_len < min_user_var_event_len) > + /* Cannot replace with dummy, event too short. */ > + return -1; > + > + flags= uint2korr(p + FLAGS_OFFSET); > + flags&= ~LOG_EVENT_THREAD_SPECIFIC_F; > + flags|= LOG_EVENT_SUPPRESS_USE_F; > + int2store(p + FLAGS_OFFSET, flags); > + > + if (data_len < min_query_event_len) > + { > + /* > + Have to use dummy user_var event for such a short packet. > + > + This works, but the event will be considered part of an event group with > + the following event, so things like START SLAVE UNTIL may behave slightly > + unexpected. Why? > + > + We write a NULL user var with the name @`!dummyvar` (or as much > + as that as will fit within the size of the original event - so > + possibly just @`!`). > + */ > + static const char var_name[]= "!dummyvar"; > + uint name_len= data_len - (min_user_var_event_len - 1); > + > + p[EVENT_TYPE_OFFSET]= USER_VAR_EVENT; > + int4store(p + LOG_EVENT_HEADER_LEN, name_len); > + memcpy(p + LOG_EVENT_HEADER_LEN + UV_NAME_LEN_SIZE, var_name, name_len); > + p[LOG_EVENT_HEADER_LEN + UV_NAME_LEN_SIZE + name_len]= 1; // indicates NULL > + } > + else > + { > + /* > + Use a dummy query event, just a comment. > + */ > + static const char message[]= > + "# Dummy event replacing event type %u that slave cannot handle."; > + char buf[sizeof(message)+1]; /* +1, as %u can expand to 3 digits. */ > + uchar old_type= p[EVENT_TYPE_OFFSET]; > + uchar *q= p + LOG_EVENT_HEADER_LEN; > + size_t comment_len, len; > + > + p[EVENT_TYPE_OFFSET]= QUERY_EVENT; > + int4store(q + Q_THREAD_ID_OFFSET, 0); > + int4store(q + Q_EXEC_TIME_OFFSET, 0); > + q[Q_DB_LEN_OFFSET]= 0; > + int2store(q + Q_ERR_CODE_OFFSET, 0); > + int2store(q + Q_STATUS_VARS_LEN_OFFSET, 0); > + q[Q_DATA_OFFSET]= 0; /* Zero terminator for empty db */ > + q+= Q_DATA_OFFSET + 1; > + len= my_snprintf(buf, sizeof(buf), message, old_type); > + comment_len= data_len - (min_query_event_len - 1); > + if (comment_len <= len) > + memcpy(q, buf, comment_len); > + else > + { > + memcpy(q, buf, len); > + memset(q+len, ' ', comment_len - len); > + } > + } > + > + if (checksum_alg == BINLOG_CHECKSUM_ALG_CRC32) > + { > + ha_checksum crc= my_checksum(0L, p, data_len); > + int4store(p + data_len, crc); > + } > + return 0; > +} > + > + > #ifdef MYSQL_CLIENT > /** > Query_log_event::print(). > === modified file 'sql/log_event.h' > --- a/sql/log_event.h 2012-04-10 06:28:13 +0000 > +++ b/sql/log_event.h 2012-04-27 08:20:38 +0000 > @@ -566,6 +566,43 @@ enum enum_binlog_checksum_alg { > #define BINLOG_CHECKSUM_LEN CHECKSUM_CRC32_SIGNATURE_LEN > #define BINLOG_CHECKSUM_ALG_DESC_LEN 1 /* 1 byte checksum alg descriptor */ > > +/* > + These are cabability numbers for MariaDB slave servers. Typo. capability. > + > + Newer MariaDB slaves set this to inform the master about their capabilities. > + This allows the master to decide which events it can send to the slave > + without breaking replication on old slaves that maybe do not understand > + all events from newer masters. > + > + As new releases are backwards compatible, a given capability implies also > + all capabilities with smaller number. > + > + Older MariaDB slaves and other MySQL slave servers do not set this, so they > + are recorded with capability 0. > +*/ > + > +/* MySQL or old MariaDB slave with no announced capability. */ > +#define MARIA_SLAVE_CAPABILITY_UNKNOWN 0 > +/* MariaDB >= 5.3, which understands ANNOTATE_ROWS_EVENT. */ > +#define MARIA_SLAVE_CAPABILITY_ANNOTATE 1 > +/* > + MariaDB >= 5.5. This version has the capability to tolerate events omitted > + from the binlog stream without breaking replication (MySQL slaves fail > + because they mis-compute the offsets into the master's binlog). > +*/ > +#define MARIA_SLAVE_CAPABILITY_TOLERATE_HOLES 2 > +/* MariaDB > 5.5, which knows about binlog_checkpoint_log_event. */ > +#define MARIA_SLAVE_CAPABILITY_BINLOG_CHECKPOINT 3 > +/* > + MariaDB server which understands MySQL 5.6 ignorable events. This server > + can tolerate receiving any event with the LOG_EVENT_IGNORABLE_F flag set. > +*/ > +#define MARIA_SLAVE_CAPABILITY_IGNORABLE 4 > + > +/* Our capability. */ > +#define MARIA_SLAVE_CAPABILITY_MINE MARIA_SLAVE_CAPABILITY_BINLOG_CHECKPOINT > + > + > /** > @enum Log_event_type > > === modified file 'sql/slave.cc' > --- a/sql/slave.cc 2012-04-10 06:28:13 +0000 > +++ b/sql/slave.cc 2012-04-27 08:20:38 +0000 > @@ -1747,6 +1747,37 @@ past_checksum: > } > } > } > + > + /* Announce MariaDB slave capabilities. */ > + DBUG_EXECUTE_IF("simulate_slave_capability_none", goto after_set_cabability;); > + { > + int rc= DBUG_EVALUATE_IF("simulate_slave_capability_old_53", > + mysql_real_query(mysql, STRING_WITH_LEN("SET @mariadb_slave_capability=" > + STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_ANNOTATE))), > + mysql_real_query(mysql, STRING_WITH_LEN("SET @mariadb_slave_capability=" > + STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_MINE)))); Better put DBUG_EVALUATE_IF only around the STRINGIFY_ARG values: DBUG_EVALUATE_IF("...", STRINGIFY_ARG(...), STRINGIFY_ARG(...)) so that next time I (or somebody) wouldn't need to vdiff two long function calls, trying to count parentheses and commans :) > + if (rc) > + { > + err_code= mysql_errno(mysql); > + if (is_network_error(err_code)) > + { > + mi->report(ERROR_LEVEL, err_code, > + "Setting @mariadb_slave_capability failed with error: %s", > + mysql_error(mysql)); > + goto network_err; > + } > + else > + { > + /* Fatal error */ > + errmsg= "The slave I/O thread stops because a fatal error is " > + "encountered when it tries to set @mariadb_slave_capability."; > + sprintf(err_buff, "%s Error: %s", errmsg, mysql_error(mysql)); > + goto err; > + } > + } > + } > +after_set_cabability: > + > err: > if (errmsg) > { > === modified file 'sql/sql_repl.cc' > --- a/sql/sql_repl.cc 2012-03-28 17:26:00 +0000 > +++ b/sql/sql_repl.cc 2012-04-27 08:20:38 +0000 > @@ -563,14 +584,44 @@ static int send_heartbeat_event(NET* net > static const char * > send_event_to_slave(THD *thd, NET *net, String* const packet, ushort flags, > Log_event_type event_type, char *log_file_name, > - IO_CACHE *log) > + IO_CACHE *log, int mariadb_slave_capability, > + ulong ev_offset, uint8 current_checksum_alg) current_checksum_alg is part of THD, why do you bother to pass is separately? > { > my_off_t pos; > > /* Do not send annotate_rows events unless slave requested it. */ > - if (event_type == ANNOTATE_ROWS_EVENT && > - !(flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT)) > - return NULL; > + if (event_type == ANNOTATE_ROWS_EVENT && !(flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT)) > + { > + if (mariadb_slave_capability >= MARIA_SLAVE_CAPABILITY_TOLERATE_HOLES) > + { > + /* This slave can tolerate events omitted from the binlog stream. */ > + return NULL; > + } > + else if (mariadb_slave_capability >= MARIA_SLAVE_CAPABILITY_ANNOTATE) > + { > + /* > + The slave did not request ANNOTATE_ROWS_EVENT (it does not need them as > + it will not log them in its own binary log). However, it understands the > + event and will just ignore it, and it would break if we omitted it, > + leaving a hole in the binlog stream. So just send the event as-is. > + */ > + } > + else > + { > + /* > + The slave does not understand ANNOTATE_ROWS_EVENT. > + > + Older MariaDB slaves (and MySQL slaves) will break replication if there > + are holes in the binlog stream (they will miscompute the binlog offset > + and request the wrong position when reconnecting). > + > + So replace the event with a dummy event of the same size that will be > + a no-operation on the slave. > + */ > + if (Query_log_event::dummy_event(packet, ev_offset, current_checksum_alg)) > + return "Failed to replace row annotate event with dummy: too small event."; No ER_xxx error message for that? > + } > + } > > /* > Skip events with the @@skip_replication flag set, if slave requested Regards, Sergei

3 5

[Maria-developers] BUG#1001117: a problem with constants.
by Sergei Petrunia 18 May '12

18 May '12

Hi Timour, In regular maria-5.5, I get: (gdb) wher 1 #0 Item_func_like::select_optimize (this=0xa11c478) at /home/psergey/dev2/5.5-look7/sql/item_cmpfunc.cc:4739 (More stack frames follow...) (gdb) p args[1] $123 = (Item_singlerow_subselect *) 0xa1194a0 (gdb) p args[1]->used_tables() $124 = 0 (gdb) p args[1]->const_item() $125 = true In 5.5-timour tree, I get: #1 0x0841a035 in Item_func_like::select_optimize (this=0xa109820) at /home/psergey/dev2/5.5-timour/sql/item_cmpfunc.cc:4739 (gdb) p args[1] $47 = (Item_singlerow_subselect *) 0xa106848 (gdb) p args[1]->used_tables() $49 = 0 (gdb) p args[1]->const_item() $51 = false I also put a breakpoint in JOIN::get_examined_rows(), it is never hit. Can you explain this effect? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] MDEV-136 Non-blocking "set read_only"
by Sergei Golubchik 17 May '12

17 May '12

Hi, Holyfoot! On May 09, holyfoot(a)askmonty.org wrote: > ------------------------------------------------------------ > revno: 3518 > revision-id: holyfoot(a)askmonty.org-20120509185945-i1yx6d38phgoqzes > parent: sanja(a)montyprogram.com-20120507181437-1zl1v1uxje7v6994 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-136 > timestamp: Wed 2012-05-09 23:59:45 +0500 > message: > MDEV-136 Non-blocking "set read_only". > Handle the 'set read_only=1' in lighter way, than the FLUSH TABLES READ LOCK; > For the transactional engines we don't wait for operations on that tables to finish. See my comments below. > === modified file 'mysql-test/r/read_only.result' > --- a/mysql-test/r/read_only.result 2009-03-06 14:56:17 +0000 > +++ b/mysql-test/r/read_only.result 2012-05-09 18:59:45 +0000 > @@ -59,7 +59,7 @@ > connection con1; > select @@global.read_only; > @@global.read_only > -0 > +1 This is prone to race conditions. Please, fix the test to remove "send" here and below. (assuming the new result is correct) But is it correct ? Why set read_only is not blocked by a write locked myisam table? > unlock tables ; > select @@global.read_only; > @@global.read_only > @@ -80,7 +80,7 @@ > connection con1; > select @@global.read_only; > @@global.read_only > -0 > +1 > unlock tables ; > select @@global.read_only; > @@global.read_only Where are the tests for the changed functionality? > === modified file 'sql/sql_base.cc' > --- a/sql/sql_base.cc 2012-04-19 06:16:30 +0000 > +++ b/sql/sql_base.cc 2012-05-09 18:59:45 +0000 > @@ -933,7 +938,8 @@ > for (uint idx=0 ; idx < open_cache.records ; idx++) > { > TABLE *table=(TABLE*) hash_element(&open_cache,idx); > - if (table->in_use) > + if (table->in_use && > + (!set_readonly_mode || !table->file->has_transactions())) I wonder how this could work. The line below sets a flag *on a thread*. The task description tells "not wait for transactional tables", while your change means "not set a flag if all tables used in a thread are transactional". That is, if a thread uses both transactional and non-transactional tables, your change does nothing. Back to my first question - where are the tests for this new task? > table->in_use->some_tables_deleted= 1; > } > } Regards, Sergei

3 6

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

17 May '12

On Mon, May 14, 2012 at 11:22:52PM +0300, Timour Katchaounov wrote: > > > /** > > > + Estimate the number of rows that query execution will read. > > > + > > > + @todo This is a very pessimistic upper bound. Use join selectivity > > > + when available to produce a more realistic number. > > > +*/ > > > + > > > +double JOIN::get_examined_rows() > > > +{ > > > + /* Each constant table examines one row, and the result is at most one row. */ > > > + ha_rows examined_rows= const_tables; > > > + uint i= const_tables; > > > + double prev_fanout; > > > + > > > + if (table_count == const_tables) > > > + return examined_rows; > > > + > > > + examined_rows+= join_tab[i++].get_examined_rows(); > > > + for (; i < table_count ; i++) > > > + { > > > + if (join_tab[i].type == JT_EQ_REF) > > > + prev_fanout= 1; > > > + else > > > + prev_fanout= best_positions[i-1].records_read; > > This looks wrong. Declaration of POSITION::records_read has this comment: > > /* > The "fanout": number of output rows that will be produced (after > pushed down selection condition is applied) per each row combination of > previous tables. > */ > > note the "PER EACH ROW COMBINATION .." part. I would expect that this function > would calculate a product of records_read values. > > timour: > > In this function we want to estimate how many rows will be *examined*, not > produced by each JOIN operator. For the estimate I assume a simple nested loops > model, where the JOIN read every row of its right table as many times as many > rows there are in the left operand. The partial join that serves as left > operand, contains records_read rows. This is the multiplier of the number of > rows that will be examined in the right table. > > Of course, for each partial join the join condition will filter a subset of > these rows. > > I agree that blocking algorithms may examine a lot less rows, but it doesn't > make sense to have a very tight bound here. We use an upper bound, because it > is better to miss some constant optimizations, rather than execute very > expensive subqueries here. I was talking about something much more simpler than that. Consider this example: create table ten (a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k (a int); insert into one_k select A.a + 10*B.a + 100*C.a from ten A, ten B, ten C; create table ti1 ( a int); insert into ti1 select a from ten limit 4; alter table ti1 add b int; create table ti2 (a int primary key, b int); create table ti3 (a int primary key, b int); insert into ti2 select a, a from one_k; insert into ti3 select a, a from one_k; MariaDB [test]> explain select * from ten where 3 in (select ti2.b + ti3.b from ti1, ti2, ti3 where ti2.a=ti1.a and ti3.a=ti1.a) or a <5; +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | 1 | PRIMARY | ten | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 2 | DEPENDENT SUBQUERY | ti1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | | 2 | DEPENDENT SUBQUERY | ti3 | eq_ref | PRIMARY | PRIMARY | 4 | test.ti1.a | 1 | | | 2 | DEPENDENT SUBQUERY | ti2 | eq_ref | PRIMARY | PRIMARY | 4 | test.ti1.a | 1 | Using where | +------+--------------------+-------+--------+---------------+---------+---------+------------+------+-------------+ For this example, JOIN::get_examined_rows() produces 6. The number comes from 4 rows examined in table ti1 (correct) 1 row examined in table ti2 (incorrect, should be 4) 1 row examined in table ti3 (incorrect, should be 4) Do you agree that the number of 6 is incorrect and needs to be fixed? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

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

15 May '12

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

1 0

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

14 May '12

On Sun, May 13, 2012 at 02:11:15PM +0300, Timour Katchaounov wrote: > Sergey, > > At the optimizer call this Tuesday Igor suggested that you > should review the patch for this bug. The commit message > contains a description of the patch. Let me know if you believe > a more detailed description is needed. > > The patch is also pushed into the following tree: > lp:~maria-captains/maria/5.5-timour > > > Thanks, > Timour > > ------------------------------------------------------------ > revno: 3403 > revision-id: timour(a)askmonty.org-20120511152503-zbjewctjclx0sajt > parent: knielsen(a)knielsen-hq.org-20120508122744-v5okh33kuolot3kb > fixes bug(s): https://launchpad.net/bugs/944706 > committer: timour(a)askmonty.org > branch nick: 5.5-lpb944706 > timestamp: Fri 2012-05-11 18:25:03 +0300 > message: > Fix for bug lp:944706, task MDEV-193 > > The patch enables back constant subquery execution during > query optimization after it was disabled during the development > of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION). > > The main idea is that constant subqueries are allowed to be executed > during optimization if their execution is not expensive. > > The approach is as follows: > - Constant subqueries are recursively optimized in the beginning of > JOIN::optimize of the outer query. This is done by the new method > JOIN::optimize_constant_subqueries(). This is done so that the cost > of executing these queries can be estimated. > - Optimization of the outer query proceeds normally. During this phase > the optimizer may request execution of non-expensive constant subqueries. > Each place where the optimizer may potentially execute an expensive > expression is guarded with the predicate Item::is_expensive(). > - The implementation of Item_subselect::is_expensive has been extended > to use the number of examined rows (estimated by the optimizer) as a > way to determine whether the subquery is expensive or not. > - The new system variable "expensive_subquery_limit" controls how many > examined rows are considered to be not expensive. The default is 100. > > In addition, multiple changes were needed to make this solution work > in the light of the changes made by MWL#89. These changes were needed > to fix various crashes and wrong results, and legacy bugs discovered > during development. > === modified file 'mysql-test/r/explain.result' > --- a/mysql-test/r/explain.result 2012-01-13 14:50:02 +0000 > +++ b/mysql-test/r/explain.result 2012-05-11 15:25:03 +0000 > @@ -260,7 +260,7 @@ FLUSH TABLES; > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > DROP TABLE t1, t2; > # > # Bug #48573: difference of index selection between rpm binary and > @@ -287,7 +287,7 @@ WHERE t1.f1 GROUP BY t1.f1)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 2 SUBQUERY a system NULL NULL NULL NULL 1 > -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where > +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 > PREPARE stmt FROM > 'EXPLAIN SELECT 1 FROM t1 > WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a > @@ -297,12 +297,12 @@ EXECUTE stmt; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 2 SUBQUERY a system NULL NULL NULL NULL 1 > -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where > +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 > EXECUTE stmt; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 2 SUBQUERY a system NULL NULL NULL NULL 1 > -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where > +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 > DEALLOCATE PREPARE stmt; > PREPARE stmt FROM > 'EXPLAIN SELECT 1 FROM t1 > @@ -313,12 +313,12 @@ EXECUTE stmt; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 2 SUBQUERY a system NULL NULL NULL NULL 1 > -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where > +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 > EXECUTE stmt; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 2 SUBQUERY a system NULL NULL NULL NULL 1 > -2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where > +2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > End of 5.1 tests. > > === modified file 'mysql-test/r/group_min_max.result' > --- a/mysql-test/r/group_min_max.result 2012-03-01 22:22:22 +0000 > +++ b/mysql-test/r/group_min_max.result 2012-05-11 15:25:03 +0000 > @@ -2398,12 +2398,12 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index > -2 SUBQUERY t1 index NULL a 10 NULL 1 Using index > +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index > EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index > -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index > EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE > a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); > id select_type table type possible_keys key key_len ref rows Extra > @@ -2419,9 +2419,9 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JO > ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > AND t1_outer1.b = t1_outer2.b; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1_outer1 ref a a 5 const 2 Using where; Using index > +1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index > 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) > -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by > +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index > EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x > FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; > id select_type table type possible_keys key key_len ref rows Extra > @@ -2756,8 +2756,8 @@ NULL > EXPLAIN > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > id select_type table type possible_keys key key_len ref rows Extra > -x x x x x x x x x Using where; Using index > -x x x x x x x x x Using where; Using index > +x x x x x x x x x Impossible WHERE noticed after reading const tables > +x x x x x x x x x Using index > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > MIN( a ) > NULL > @@ -2828,8 +2828,8 @@ NULL > EXPLAIN > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > id select_type table type possible_keys key key_len ref rows Extra > -x x x x x x x x x Using where; Using index > -x x x x x x x x x Using where; Using index > +x x x x x x x x x Impossible WHERE noticed after reading const tables > +x x x x x x x x x Using index > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > MIN( a ) > NULL > @@ -2907,8 +2907,8 @@ NULL > EXPLAIN > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > id select_type table type possible_keys key key_len ref rows Extra > -x x x x x x x x x Using where; Using index > -x x x x x x x x x Using where; Using index > +x x x x x x x x x Impossible WHERE noticed after reading const tables > +x x x x x x x x x Using index > SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); > MIN( a ) > NULL > > === modified file 'mysql-test/r/join_outer.result' > --- a/mysql-test/r/join_outer.result 2012-04-07 13:58:46 +0000 > +++ b/mysql-test/r/join_outer.result 2012-05-11 15:25:03 +0000 > @@ -1871,10 +1871,10 @@ EXPLAIN EXTENDED > SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 > -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where > +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 > 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1 > +Note 1003 select NULL AS `a` from `test`.`t2` where 1 > DROP TABLE t1,t2,t3; > # > # LP bug #817384 Wrong result with outer join + subquery in ON > > === modified file 'mysql-test/r/join_outer_jcl6.result' > --- a/mysql-test/r/join_outer_jcl6.result 2012-04-07 13:58:46 +0000 > +++ b/mysql-test/r/join_outer_jcl6.result 2012-05-11 15:25:03 +0000 > @@ -1882,10 +1882,10 @@ EXPLAIN EXTENDED > SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 > -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where > +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 > 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1 > +Note 1003 select NULL AS `a` from `test`.`t2` where 1 > DROP TABLE t1,t2,t3; > # > # LP bug #817384 Wrong result with outer join + subquery in ON > > === modified file 'mysql-test/r/key.result' > --- a/mysql-test/r/key.result 2011-10-19 19:45:18 +0000 > +++ b/mysql-test/r/key.result 2012-05-11 15:25:03 +0000 > @@ -598,8 +598,8 @@ VALUES > EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1_outer index NULL a 10 NULL 15 Using index > -2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index > SELECT 1 as RES FROM t1 AS t1_outer WHERE > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; > RES > > === modified file 'mysql-test/r/limit_rows_examined.result' > --- a/mysql-test/r/limit_rows_examined.result 2012-03-11 22:45:18 +0000 > +++ b/mysql-test/r/limit_rows_examined.result 2012-05-11 15:25:03 +0000 > @@ -679,7 +679,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary > 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Distinct > 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 > -2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 > SELECT DISTINCT a AS field1 FROM t1, t2 > WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) > HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20; > > === modified file 'mysql-test/r/myisam_mrr.result' > --- a/mysql-test/r/myisam_mrr.result 2012-02-25 15:13:24 +0000 > +++ b/mysql-test/r/myisam_mrr.result 2012-05-11 15:25:03 +0000 > @@ -349,10 +349,10 @@ WHERE t2.int_key IS NULL > GROUP BY t2.pk > ); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition > Warnings: > -Note 1003 select min(1) AS `MIN(t1.pk)` from dual where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`)) > +Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 > DROP TABLE t1, t2; > # > # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation > > === modified file 'mysql-test/r/mysqld--help.result' > --- a/mysql-test/r/mysqld--help.result 2012-04-10 06:28:13 +0000 > +++ b/mysql-test/r/mysqld--help.result 2012-05-11 15:25:03 +0000 > @@ -159,6 +159,9 @@ > Enable the event scheduler. Possible values are ON, OFF, > and DISABLED (keep the event scheduler completely > deactivated, it cannot be activated run-time) > + --expensive-subquery-limit=# > + The maximum number of rows a subquery examines in order > + to be considered non-expensive > --expire-logs-days=# > If non-zero, binary logs will be purged after > expire_logs_days days; possible purges happen at startup > @@ -885,6 +888,7 @@ delayed-queue-size 1000 > div-precision-increment 4 > engine-condition-pushdown FALSE > event-scheduler OFF > +expensive-subquery-limit 100 > expire-logs-days 0 > external-locking FALSE > extra-max-connections 1 > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/r/subselect.result 2012-05-11 15:25:03 +0000 > @@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j > INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); > EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index > +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where > +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 > 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > Warnings: > -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) > +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 > SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM > t8 WHERE pseudo='joce'); > ERROR 21000: Operand should contain 1 column(s) > @@ -517,6 +517,7 @@ ERROR 21000: Subquery returns more than > show warnings; > Level Code Message > Error 1242 Subquery returns more than 1 row > +Error 1028 Sort aborted: Subquery returns more than 1 row > drop table t1; > create table t1 (a int); > insert into t1 values (1),(2),(3); > @@ -547,11 +548,7 @@ SELECT numreponse, (SELECT numeropost FR > numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index > -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index > -Warnings: > -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) > +ERROR 21000: Subquery returns more than 1 row > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > ERROR 21000: Subquery returns more than 1 row > EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; > @@ -1675,34 +1672,34 @@ select * from t3 where NULL >= any (sele > a > explain extended select * from t3 where NULL >= any (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= any (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= any (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2); > a > explain extended select * from t3 where NULL >= some (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= some (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > insert into t2 values (2,2), (2,1), (3,3), (3,1); > select * from t3 where a > all (select max(b) from t2 group by a); > a > @@ -1764,7 +1761,7 @@ id select_type table type possible_keys > 3 UNION t1 system NULL NULL NULL NULL 1 100.00 > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL > Warnings: > -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) > +Note 1003 select 'e' AS `s1` from dual where 1 > drop table t1; > CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; > INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); > @@ -3099,7 +3096,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3111,7 +3108,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3727,8 +3724,8 @@ explain select * from t1 where not exist > ((select t11.i from t1 t11) union (select t12.i from t1 t12)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found > -3 UNION t12 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > DROP TABLE t1; > CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); > @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); > INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > @@ -4626,7 +4623,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); > 1 > 1 > @@ -5960,7 +5957,7 @@ id select_type table type possible_keys > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > DROP TABLE t1; > # > # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) > @@ -6121,7 +6118,7 @@ set optimizer_switch=@tmp_optimizer_swit > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > > DROP TABLE t1; > # > > === modified file 'mysql-test/r/subselect3.result' > --- a/mysql-test/r/subselect3.result 2012-04-07 13:58:46 +0000 > +++ b/mysql-test/r/subselect3.result 2012-05-11 15:25:03 +0000 > @@ -1479,7 +1479,7 @@ id select_type table type possible_keys > 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) > +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 > SELECT * FROM t1 > WHERE ( > ( SELECT a FROM t2 WHERE a = 9 ), > > === modified file 'mysql-test/r/subselect3_jcl6.result' > --- a/mysql-test/r/subselect3_jcl6.result 2012-04-07 13:58:46 +0000 > +++ b/mysql-test/r/subselect3_jcl6.result 2012-05-11 15:25:03 +0000 > @@ -1489,7 +1489,7 @@ id select_type table type possible_keys > 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) > +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 > SELECT * FROM t1 > WHERE ( > ( SELECT a FROM t2 WHERE a = 9 ), > > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2012-03-01 22:22:22 +0000 > +++ b/mysql-test/r/subselect4.result 2012-05-11 15:25:03 +0000 > @@ -562,7 +562,7 @@ WHERE f3 = ( > SELECT t1.f3 FROM t1 > WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref f3 f3 5 const 0 Using where > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 > 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 > 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 > @@ -577,7 +577,7 @@ WHERE f3 = ( > SELECT f3 FROM t1 > WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref f3 f3 5 const 0 Using where > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 > 2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 8 func,func 1 > 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 > @@ -1220,6 +1220,13 @@ id select_type table type possible_keys > 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where > 3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary > 3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) > +SELECT * FROM t1 WHERE > +(SELECT f2 FROM t2 > +WHERE f4 <= ALL > +(SELECT max(SQ1_t1.f4) > +FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 > +GROUP BY SQ1_t1.f4)); > +ERROR 21000: Subquery returns more than 1 row > drop table t1, t2, t3; > # > # BUG#52317: Assertion failing in Field_varstring::store() > @@ -1250,8 +1257,7 @@ FROM t2 JOIN t1 ON t1.f3 > WHERE ('v') IN (SELECT f4 FROM t2) > GROUP BY f9; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 system NULL NULL NULL NULL 1 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 > SELECT COUNT(t2.f3), > @@ -1267,8 +1273,7 @@ FROM t2 JOIN t1 ON t1.f3 > WHERE ('v') IN (SELECT f4 FROM t2) > ORDER BY f9; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 system NULL NULL NULL NULL 1 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 > SELECT COUNT(t2.f3), > @@ -1277,7 +1282,7 @@ FROM t2 JOIN t1 ON t1.f3 > WHERE ('v') IN (SELECT f4 FROM t2) > ORDER BY f9; > COUNT(t2.f3) f9 > -0 2 > +0 NULL > EXPLAIN > SELECT COUNT(t2.f3), > (SELECT t2.f1 FROM t1 limit 1) AS f9 > @@ -1285,8 +1290,7 @@ FROM t2 JOIN t1 > WHERE ('v') IN (SELECT f4 FROM t2) > GROUP BY f9; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 system NULL NULL NULL NULL 1 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 > SELECT COUNT(t2.f3), > @@ -1302,8 +1306,7 @@ FROM t2 JOIN t1 > WHERE ('v') IN (SELECT f4 FROM t2) > ORDER BY f9; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 system NULL NULL NULL NULL 1 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 > SELECT COUNT(t2.f3), > @@ -1325,7 +1328,7 @@ EXPLAIN > SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 > -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; > field1 > NULL > @@ -1333,7 +1336,7 @@ EXPLAIN > SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 > -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; > field1 > NULL > @@ -1631,7 +1634,7 @@ SET SESSION optimizer_switch='in_to_exis > EXPLAIN > SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 > 2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index > SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); > @@ -1852,8 +1855,8 @@ GROUP BY 1, 2; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t3 system NULL NULL NULL NULL 1 Using temporary; Using filesort > 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where > -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -3 MATERIALIZED t1 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table > PREPARE st1 FROM " > SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) > FROM t2 JOIN t3 ON t3.f4 = t2.f4 > > === modified file 'mysql-test/r/subselect_cache.result' > --- a/mysql-test/r/subselect_cache.result 2012-02-15 17:08:08 +0000 > +++ b/mysql-test/r/subselect_cache.result 2012-05-11 15:25:03 +0000 > @@ -486,9 +486,9 @@ Handler_read_key 7 > Handler_read_last 0 > Handler_read_next 0 > Handler_read_prev 0 > -Handler_read_rnd 10 > +Handler_read_rnd 0 > Handler_read_rnd_deleted 0 > -Handler_read_rnd_next 42 > +Handler_read_rnd_next 31 > set optimizer_switch='subquery_cache=off'; > flush status; > select a from t1 ORDER BY (select d from t2 where b=c); > @@ -514,9 +514,9 @@ Handler_read_key 0 > Handler_read_last 0 > Handler_read_next 0 > Handler_read_prev 0 > -Handler_read_rnd 10 > +Handler_read_rnd 0 > Handler_read_rnd_deleted 0 > -Handler_read_rnd_next 72 > +Handler_read_rnd_next 61 > set optimizer_switch='subquery_cache=on'; > #single value subquery test (distinct ORDER BY) > flush status; > > === modified file 'mysql-test/r/subselect_innodb.result' > --- a/mysql-test/r/subselect_innodb.result 2012-01-13 14:50:02 +0000 > +++ b/mysql-test/r/subselect_innodb.result 2012-05-11 15:25:03 +0000 > @@ -272,8 +272,8 @@ FROM t2 > WHERE (SELECT DISTINCT b FROM t3) > 0); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index > -2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 > -3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary > SELECT * > FROM t1 > WHERE t1.a = ( > @@ -301,7 +301,7 @@ GROUP BY 1 > ); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 > SELECT MAX( f1 ) FROM t2 > WHERE f2 >= ( > > === modified file 'mysql-test/r/subselect_mat.result' > --- a/mysql-test/r/subselect_mat.result 2012-03-17 08:26:58 +0000 > +++ b/mysql-test/r/subselect_mat.result 2012-05-11 15:25:03 +0000 > @@ -1152,8 +1152,8 @@ create table t2 (b1 int); > insert into t1 values (5); > explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > min(a1) > NULL > @@ -1162,8 +1162,8 @@ set @@optimizer_switch=@optimizer_switch > set @@optimizer_switch='materialization=off,in_to_exists=on'; > explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > min(a1) > NULL > @@ -1171,8 +1171,8 @@ set @@optimizer_switch=@optimizer_switch > set @@optimizer_switch='semijoin=off'; > explain select min(a1) from t1 where 7 in (select b1 from t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select b1 from t2); > min(a1) > NULL > @@ -1181,16 +1181,16 @@ set @@optimizer_switch='materialization= > # with MariaDB and MWL#90, this particular case is solved: > explain select min(a1) from t1 where 7 in (select b1 from t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select b1 from t2); > min(a1) > NULL > # but when we go around MWL#90 code, the problem still shows up: > explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; > min(a1) > NULL > @@ -1932,7 +1932,7 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index > -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index > +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index > 2 MATERIALIZED s1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) > 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 > SELECT a, c FROM t1, t2 > @@ -1951,8 +1951,8 @@ WHERE s2.d = s1.e AND s1.e = (SELECT MAX > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index > -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index > -2 MATERIALIZED s1 hash_ALL NULL #hash#$hj 5 test.s2.d 8 Using where; Using join buffer (flat, BNLH join) > +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index > +2 MATERIALIZED s1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) > 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 > SELECT a, c FROM t1, t2 > WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 > @@ -2153,7 +2153,7 @@ set @@optimizer_switch='materialization= > EXPLAIN > SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 > 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > @@ -2222,10 +2222,10 @@ NULL > EXPLAIN EXTENDED > SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 index NULL b 5 NULL 2 100.00 Using index > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,9 in ( <materialize> (select `test`.`t2`.`a` from `test`.`t2` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery2>`.`a`)))))) > +Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0 > set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; > SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); > max_res > @@ -2233,10 +2233,10 @@ NULL > EXPLAIN EXTENDED > SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 index NULL b 5 NULL 2 100.00 Using index > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where > Warnings: > -Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <expr_cache><9>(<in_optimizer>(9,<exists>(select `test`.`t2`.`a` from `test`.`t2` where (9 = `test`.`t2`.`a`)))) > +Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0 > DROP TABLE t1,t2; > # > # LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions > > === modified file 'mysql-test/r/subselect_mat_cost_bugs.result' > --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-01-13 14:50:02 +0000 > +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-11 15:25:03 +0000 > @@ -124,7 +124,7 @@ FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 > WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > SELECT t1.* > FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 > WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); > @@ -148,7 +148,7 @@ FROM t2 GROUP BY f1 > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 SUBQUERY t1 system NULL NULL NULL NULL 1 > -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > drop table t1, t2, t3; > # > # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2012-05-11 15:25:03 +0000 > @@ -378,12 +378,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j > INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); > EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index > +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where > +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 > 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > Warnings: > -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) > +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 > SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM > t8 WHERE pseudo='joce'); > ERROR 21000: Operand should contain 1 column(s) > @@ -524,6 +524,7 @@ ERROR 21000: Subquery returns more than > show warnings; > Level Code Message > Error 1242 Subquery returns more than 1 row > +Error 1028 Sort aborted: Subquery returns more than 1 row > drop table t1; > create table t1 (a int); > insert into t1 values (1),(2),(3); > @@ -554,11 +555,7 @@ SELECT numreponse, (SELECT numeropost FR > numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index > -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index > -Warnings: > -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) > +ERROR 21000: Subquery returns more than 1 row > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > ERROR 21000: Subquery returns more than 1 row > EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; > @@ -1682,34 +1679,34 @@ select * from t3 where NULL >= any (sele > a > explain extended select * from t3 where NULL >= any (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= any (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= any (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2); > a > explain extended select * from t3 where NULL >= some (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= some (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > insert into t2 values (2,2), (2,1), (3,3), (3,1); > select * from t3 where a > all (select max(b) from t2 group by a); > a > @@ -1771,7 +1768,7 @@ id select_type table type possible_keys > 3 UNION t1 system NULL NULL NULL NULL 1 100.00 > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL > Warnings: > -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) > +Note 1003 select 'e' AS `s1` from dual where 1 > drop table t1; > CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; > INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); > @@ -3105,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3117,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3731,8 +3728,8 @@ explain select * from t1 where not exist > ((select t11.i from t1 t11) union (select t12.i from t1 t12)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found > -3 UNION t12 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > DROP TABLE t1; > CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); > @@ -4202,8 +4199,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); > INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > @@ -4531,13 +4528,13 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 0 > SET join_cache_level=@save_join_cache_level; > DROP TABLE t1; > # > @@ -4628,7 +4625,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); > 1 > 1 > @@ -5961,7 +5958,7 @@ id select_type table type possible_keys > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > DROP TABLE t1; > # > # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) > @@ -6120,7 +6117,7 @@ set optimizer_switch=@tmp_optimizer_swit > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > > DROP TABLE t1; > # > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2012-05-11 15:25:03 +0000 > @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j > INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); > EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index > +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where > +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 > 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > Warnings: > -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) > +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 > SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM > t8 WHERE pseudo='joce'); > ERROR 21000: Operand should contain 1 column(s) > @@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than > show warnings; > Level Code Message > Error 1242 Subquery returns more than 1 row > +Error 1028 Sort aborted: Subquery returns more than 1 row > drop table t1; > create table t1 (a int); > insert into t1 values (1),(2),(3); > @@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FR > numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index > -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index > -Warnings: > -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) > +ERROR 21000: Subquery returns more than 1 row > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > ERROR 21000: Subquery returns more than 1 row > EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; > @@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (sele > a > explain extended select * from t3 where NULL >= any (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= any (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= any (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2); > a > explain extended select * from t3 where NULL >= some (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= some (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > insert into t2 values (2,2), (2,1), (3,3), (3,1); > select * from t3 where a > all (select max(b) from t2 group by a); > a > @@ -1767,7 +1764,7 @@ id select_type table type possible_keys > 3 UNION t1 system NULL NULL NULL NULL 1 100.00 > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL > Warnings: > -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) > +Note 1003 select 'e' AS `s1` from dual where 1 > drop table t1; > CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; > INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); > @@ -3101,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3113,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3727,8 +3724,8 @@ explain select * from t1 where not exist > ((select t11.i from t1 t11) union (select t12.i from t1 t12)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found > -3 UNION t12 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > DROP TABLE t1; > CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); > @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); > INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > @@ -4527,13 +4524,13 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 0 > SET join_cache_level=@save_join_cache_level; > DROP TABLE t1; > # > @@ -4624,7 +4621,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); > 1 > 1 > @@ -5957,7 +5954,7 @@ id select_type table type possible_keys > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > DROP TABLE t1; > # > # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) > @@ -6116,7 +6113,7 @@ set optimizer_switch=@tmp_optimizer_swit > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > > DROP TABLE t1; > # > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2012-05-11 15:25:03 +0000 > @@ -377,12 +377,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j > INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); > EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index > +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where > +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 > 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > Warnings: > -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) > +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 > SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM > t8 WHERE pseudo='joce'); > ERROR 21000: Operand should contain 1 column(s) > @@ -523,6 +523,7 @@ ERROR 21000: Subquery returns more than > show warnings; > Level Code Message > Error 1242 Subquery returns more than 1 row > +Error 1028 Sort aborted: Subquery returns more than 1 row > drop table t1; > create table t1 (a int); > insert into t1 values (1),(2),(3); > @@ -553,11 +554,7 @@ SELECT numreponse, (SELECT numeropost FR > numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index > -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index > -Warnings: > -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) > +ERROR 21000: Subquery returns more than 1 row > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > ERROR 21000: Subquery returns more than 1 row > EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; > @@ -1681,34 +1678,34 @@ select * from t3 where NULL >= any (sele > a > explain extended select * from t3 where NULL >= any (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= any (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= any (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2); > a > explain extended select * from t3 where NULL >= some (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= some (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > insert into t2 values (2,2), (2,1), (3,3), (3,1); > select * from t3 where a > all (select max(b) from t2 group by a); > a > @@ -1770,7 +1767,7 @@ id select_type table type possible_keys > 3 UNION t1 system NULL NULL NULL NULL 1 100.00 > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL > Warnings: > -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) > +Note 1003 select 'e' AS `s1` from dual where 1 > drop table t1; > CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; > INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); > @@ -3105,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3117,7 +3114,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3733,8 +3730,8 @@ explain select * from t1 where not exist > ((select t11.i from t1 t11) union (select t12.i from t1 t12)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found > -3 UNION t12 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > DROP TABLE t1; > CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); > @@ -4204,8 +4201,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); > INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > @@ -4632,7 +4629,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); > 1 > 1 > @@ -5966,7 +5963,7 @@ id select_type table type possible_keys > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > DROP TABLE t1; > # > # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) > @@ -6127,7 +6124,7 @@ set optimizer_switch=@tmp_optimizer_swit > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > > DROP TABLE t1; > # > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2012-05-11 15:25:03 +0000 > @@ -374,12 +374,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j > INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); > EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index > +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where > +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 > 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index > Warnings: > -Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) > +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 > SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM > t8 WHERE pseudo='joce'); > ERROR 21000: Operand should contain 1 column(s) > @@ -520,6 +520,7 @@ ERROR 21000: Subquery returns more than > show warnings; > Level Code Message > Error 1242 Subquery returns more than 1 row > +Error 1028 Sort aborted: Subquery returns more than 1 row > drop table t1; > create table t1 (a int); > insert into t1 values (1),(2),(3); > @@ -550,11 +551,7 @@ SELECT numreponse, (SELECT numeropost FR > numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index > -2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index > -Warnings: > -Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')))) > +ERROR 21000: Subquery returns more than 1 row > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > ERROR 21000: Subquery returns more than 1 row > EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; > @@ -1678,34 +1675,34 @@ select * from t3 where NULL >= any (sele > a > explain extended select * from t3 where NULL >= any (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= any (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= any (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2); > a > explain extended select * from t3 where NULL >= some (select b from t2); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > select * from t3 where NULL >= some (select b from t2 group by 1); > a > explain extended select * from t3 where NULL >= some (select b from t2 group by 1); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 > -2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table > Warnings: > -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) > +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0 > insert into t2 values (2,2), (2,1), (3,3), (3,1); > select * from t3 where a > all (select max(b) from t2 group by a); > a > @@ -1767,7 +1764,7 @@ id select_type table type possible_keys > 3 UNION t1 system NULL NULL NULL NULL 1 100.00 > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL > Warnings: > -Note 1003 select 'e' AS `s1` from dual where <nop>(<in_optimizer>('f',(<min>(select 'e' from dual union select 'e' from dual) < 'f'))) > +Note 1003 select 'e' AS `s1` from dual where 1 > drop table t1; > CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; > INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); > @@ -3101,7 +3098,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3113,7 +3110,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c > ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 > -1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where > +1 PRIMARY r const PRIMARY PRIMARY 4 const 1 > 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition > SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r > ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' > @@ -3727,8 +3724,8 @@ explain select * from t1 where not exist > ((select t11.i from t1 t11) union (select t12.i from t1 t12)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > -2 SUBQUERY t11 system NULL NULL NULL NULL 0 const row not found > -3 UNION t12 system NULL NULL NULL NULL 0 const row not found > +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > +3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table > NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL > DROP TABLE t1; > CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b)); > @@ -4198,8 +4195,8 @@ CREATE TABLE t1 (a int, b int, KEY (a)); > INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 ref a a 5 const 0 Using where; Using index > -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > +2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > @@ -4525,15 +4522,15 @@ SET join_cache_level=0; > EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary > +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 0 > SET join_cache_level=@save_join_cache_level; > DROP TABLE t1; > # > @@ -4624,7 +4621,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 > Warnings: > -Note 1003 select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1))) > +Note 1003 select 1 AS `1` from `test`.`t1` where 1 > SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); > 1 > 1 > @@ -5957,7 +5954,7 @@ id select_type table type possible_keys > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > DROP TABLE t1; > # > # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS) > @@ -6116,7 +6113,7 @@ set optimizer_switch=@tmp_optimizer_swit > EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 > -2 SUBQUERY t1 ref a a 5 const 1 Using index > +2 SUBQUERY t1 ref a a 5 const 1 > > DROP TABLE t1; > # > > === modified file 'mysql-test/r/subselect_sj_mat.result' > --- a/mysql-test/r/subselect_sj_mat.result 2012-04-07 13:58:46 +0000 > +++ b/mysql-test/r/subselect_sj_mat.result 2012-05-11 15:25:03 +0000 > @@ -1197,8 +1197,8 @@ set @@optimizer_switch=@optimizer_switch > set @@optimizer_switch='materialization=off,in_to_exists=on'; > explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); > min(a1) > NULL > @@ -1206,8 +1206,8 @@ set @@optimizer_switch=@optimizer_switch > set @@optimizer_switch='semijoin=off'; > explain select min(a1) from t1 where 7 in (select b1 from t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select b1 from t2); > min(a1) > NULL > @@ -1223,8 +1223,8 @@ NULL > # but when we go around MWL#90 code, the problem still shows up: > explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 system NULL NULL NULL NULL 1 > -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table > select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; > min(a1) > NULL > @@ -1971,7 +1971,7 @@ id select_type table type possible_keys > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 1 PRIMARY t2 index c c 5 NULL 8 Using index > 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 > -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index > +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index > 2 MATERIALIZED s1 ALL c NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) > 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 > SELECT a, c FROM t1, t2 > @@ -1991,8 +1991,8 @@ id select_type table type possible_keys > 1 PRIMARY t1 system NULL NULL NULL NULL 1 > 1 PRIMARY t2 index c c 5 NULL 8 Using index > 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 > -2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index > -2 MATERIALIZED s1 hash_ALL c #hash#$hj 10 const,test.s2.d 8 Using where; Using join buffer (flat, BNLH join) > +2 MATERIALIZED s2 ref d d 4 const 2 Using where; Using index > +2 MATERIALIZED s1 hash_ALL c #hash#$hj 5 const 8 Using where; Using join buffer (flat, BNLH join) > 3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 > SELECT a, c FROM t1, t2 > WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 > > === modified file 'mysql-test/suite/sys_vars/r/all_vars.result' > --- a/mysql-test/suite/sys_vars/r/all_vars.result 2012-03-09 07:06:59 +0000 > +++ b/mysql-test/suite/sys_vars/r/all_vars.result 2012-05-11 15:25:03 +0000 > @@ -10,5 +10,6 @@ where length(variable_name) > 50; > select distinct variable_name as `there should be *no* variables listed below:` from t2 > left join t1 on variable_name=test_name where test_name is null; > there should be *no* variables listed below: > +expensive_subquery_limit > drop table t1; > drop table t2; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2012-05-04 05:16:38 +0000 > +++ b/mysql-test/t/subselect.test 2012-05-11 15:25:03 +0000 > @@ -304,6 +304,7 @@ SELECT (SELECT numeropost FROM t1 HAVING > SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; > SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; > INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); > +-- error ER_SUBQUERY_NO_1_ROW > EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > -- error ER_SUBQUERY_NO_1_ROW > SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); > > === modified file 'mysql-test/t/subselect4.test' > --- a/mysql-test/t/subselect4.test 2012-03-01 22:22:22 +0000 > +++ b/mysql-test/t/subselect4.test 2012-05-11 15:25:03 +0000 > @@ -977,6 +977,14 @@ SELECT * FROM t1 WHERE > FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 > GROUP BY SQ1_t1.f4)); > > +--error ER_SUBQUERY_NO_1_ROW > +SELECT * FROM t1 WHERE > +(SELECT f2 FROM t2 > + WHERE f4 <= ALL > + (SELECT max(SQ1_t1.f4) > + FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 > + GROUP BY SQ1_t1.f4)); > + > drop table t1, t2, t3; > > --echo # > > === modified file 'sql/item.h' > --- a/sql/item.h 2012-04-10 06:28:13 +0000 > +++ b/sql/item.h 2012-05-11 15:25:03 +0000 > @@ -1395,21 +1395,21 @@ class Item { > { > return cmp_context == IMPOSSIBLE_RESULT || item->cmp_context == cmp_context; > } > - /* > + /** > Test whether an expression is expensive to compute. Used during > optimization to avoid computing expensive expressions during this > phase. Also used to force temp tables when sorting on expensive > functions. > - TODO: > + @todo > Normally we should have a method: > cost Item::execution_cost(), > where 'cost' is either 'double' or some structure of various cost > parameters. > > - NOTE > - This function is now used to prevent evaluation of materialized IN > - subquery predicates before it is allowed. grep for > - DontEvaluateMaterializedSubqueryTooEarly to see the uses. > + @note > + This function is now used to prevent evaluation of expensive subquery > + predicates during the optimization phase. It also prevents evaluation > + of predicates that are not computable at this moment (infinite cost). Could you elaborate more on the "infinite cost" part? (let's discuss it on irc) > */ > virtual bool is_expensive() > { > > === modified file 'sql/item_cmpfunc.cc' > --- a/sql/item_cmpfunc.cc 2012-03-28 18:25:31 +0000 > +++ b/sql/item_cmpfunc.cc 2012-05-11 15:25:03 +0000 > @@ -5539,7 +5539,8 @@ void Item_equal::add_const(Item *c, Item > else > { > Item_func_eq *func= new Item_func_eq(c, const_item); > - func->set_cmp_func(); > + if(func->set_cmp_func()) > + return; Please add space after "if", Please add a comment about what kind of condition can cause the problem. As far as I understand, this is a kind of error? > func->quick_fix_field(); > cond_false= !func->val_int(); > } > > === modified file 'sql/item_cmpfunc.h' > --- a/sql/item_cmpfunc.h 2012-05-04 05:16:38 +0000 > +++ b/sql/item_cmpfunc.h 2012-05-11 15:25:03 +0000 > @@ -370,9 +370,9 @@ class Item_bool_func2 :public Item_int_f > Item_bool_func2(Item *a,Item *b) > :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {} > void fix_length_and_dec(); > - void set_cmp_func() > + int set_cmp_func() > { > - cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE); > + return cmp.set_cmp_func(this, tmp_arg, tmp_arg+1, TRUE); > } > optimize_type select_optimize() const { return OPTIMIZE_OP; } > virtual enum Functype rev_functype() const { return UNKNOWN_FUNC; } > > === modified file 'sql/item_strfunc.h' > --- a/sql/item_strfunc.h 2012-03-06 19:46:07 +0000 > +++ b/sql/item_strfunc.h 2012-05-11 15:25:03 +0000 > @@ -829,7 +829,7 @@ class Item_func_conv_charset :public Ite > { > DBUG_ASSERT(args[0]->fixed); > conv_charset= cs; > - if (cache_if_const && args[0]->const_item() && !args[0]->with_subselect) > + if (cache_if_const && args[0]->const_item() && !args[0]->is_expensive()) > { > uint errors= 0; > String tmp, *str= args[0]->val_str(&tmp); > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/item_subselect.cc 2012-05-11 15:25:03 +0000 > @@ -522,6 +522,48 @@ void Item_subselect::recalc_used_tables( > */ > } > > + > +/** > + Determine if a subquery is expensive to execute during query optimization. > + > + @details The cost of execution of a subquery is estimated based on an > + estimate of the number of rows the subquery will access during execution. > + This measure is used instead of JOIN::read_time, because it is considered > + to be much more reliable than the cost estimate. > + > + @return true if the subquery is expensive > + @return false otherwise > +*/ > +bool Item_subselect::is_expensive() > +{ > + double examined_rows= 0; > + > + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) > + { > + JOIN *cur_join= sl->join; > + if (!cur_join) > + continue; > + > + /* If a subquery is not optimized we cannot estimate its cost. */ > + if (!cur_join->join_tab) > + return true; > + > + if (sl->first_inner_unit()) > + { > + /* > + Subqueries that contain subqueries are considered expensive. > + @todo: accumulate the cost of subqueries. > + */ > + return true; > + } > + > + examined_rows+= cur_join->get_examined_rows(); > + } > + > + return (examined_rows > thd->variables.expensive_subquery_limit); > +} > + > + > bool Item_subselect::walk(Item_processor processor, bool walk_subquery, > uchar *argument) > { > > === modified file 'sql/item_subselect.h' > --- a/sql/item_subselect.h 2012-05-04 05:16:38 +0000 > +++ b/sql/item_subselect.h 2012-05-11 15:25:03 +0000 > @@ -191,6 +191,7 @@ class Item_subselect :public Item_result > table_map used_tables() const; > table_map not_null_tables() const { return 0; } > bool const_item() const; > + virtual bool const_pred() const { return const_item(); } What is this function for? It seems to have the same semantics as const_item(), but it is a separate function, which implies there is some difference. Could you add a comment about this? > inline table_map get_used_tables_cache() { return used_tables_cache; } > Item *get_tmp_table_item(THD *thd); > void update_used_tables(); > @@ -209,7 +210,7 @@ class Item_subselect :public Item_result > */ > bool is_evaluated() const; > bool is_uncacheable() const; > - bool is_expensive() { return TRUE; } > + bool is_expensive(); > > /* > Used by max/min subquery to initialize value presence registration > @@ -235,7 +236,7 @@ class Item_subselect :public Item_result > @retval TRUE if the predicate is expensive > @retval FALSE otherwise > */ > - bool is_expensive_processor(uchar *arg) { return TRUE; } > + bool is_expensive_processor(uchar *arg) { return is_expensive(); } > > /** > Get the SELECT_LEX structure associated with this Item. > @@ -581,6 +582,7 @@ class Item_in_subselect :public Item_exi > bool fix_fields(THD *thd, Item **ref); > void fix_length_and_dec(); > void fix_after_pullout(st_select_lex *new_parent, Item **ref); > + bool const_pred() const { return const_item() && left_expr->const_item(); } > void update_used_tables(); > bool setup_mat_engine(); > bool init_left_expr_cache(); > > === modified file 'sql/opt_subselect.cc' > --- a/sql/opt_subselect.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/opt_subselect.cc 2012-05-11 15:25:03 +0000 > @@ -4872,7 +4872,43 @@ static void remove_subq_pushed_predicate > > bool JOIN::optimize_unflattened_subqueries() > { > - return select_lex->optimize_unflattened_subqueries(); > + return select_lex->optimize_unflattened_subqueries(false); > +} > + > +/** > + Optimize all constant subqueries of a query that were not flattened into > + a semijoin. > + > + @details > + Similar to other constant conditions, constant subqueries can be used in > + various constant optimizations. Having optimized constant subqueries before > + these constant optimizations, makes it possible to estimate if a subquery > + is "cheap" enough to be executed during the optimization phase. > + > + Constant subqueries can be optimized and evaluated independent of the outer > + query, therefore if const_only = true, this method can be called early in > + the optimization phase of the outer query. > + > + @return Operation status > + @retval FALSE success. > + @retval TRUE error occurred. > +*/ > + > +bool JOIN::optimize_constant_subqueries() > +{ > + ulonglong save_options= select_lex->options; > + bool res; > + /* > + Constant subqueries may be executed during the optimization phase. > + In EXPLAIN mode the optimizer doesn't initialize many of the data structures > + needed for execution. In order to make it possible to execute subqueries > + during optimization, constant subqueries must be optimized for execution, > + not for EXPLAIN. > + */ > + select_lex->options&= ~SELECT_DESCRIBE; > + res= select_lex->optimize_unflattened_subqueries(true); > + select_lex->options= save_options; > + return res; > } > > > @@ -5273,7 +5309,14 @@ bool JOIN::choose_subquery_plan(table_ma > by the IN predicate. > */ > outer_join= unit->outer_select() ? unit->outer_select()->join : NULL; > - if (outer_join && outer_join->table_count > 0) > + /* > + Get the cost of the outer join if: > + (1) It has at least one table, and > + (2) It has been already optimized (if there is no join_tab, then the > + outer join has not been optimized yet). > + */ > + if (outer_join && outer_join->table_count > 0 && // (1) > + outer_join->join_tab) // (2) > { > /* > TODO: > > === modified file 'sql/sql_class.h' > --- a/sql/sql_class.h 2012-03-11 22:45:18 +0000 > +++ b/sql/sql_class.h 2012-05-11 15:25:03 +0000 > @@ -482,6 +482,7 @@ typedef struct system_variables > ulonglong group_concat_max_len; > ha_rows select_limit; > ha_rows max_join_size; > + ha_rows expensive_subquery_limit; > ulong auto_increment_increment, auto_increment_offset; > ulong lock_wait_timeout; > ulong join_cache_level; > > === modified file 'sql/sql_delete.cc' > --- a/sql/sql_delete.cc 2012-03-24 17:21:22 +0000 > +++ b/sql/sql_delete.cc 2012-05-11 15:25:03 +0000 > @@ -120,7 +120,7 @@ bool mysql_delete(THD *thd, TABLE_LIST * > } > > /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ > - if (select_lex->optimize_unflattened_subqueries()) > + if (select_lex->optimize_unflattened_subqueries(false)) > DBUG_RETURN(TRUE); > > const_cond= (!conds || conds->const_item()); > > === modified file 'sql/sql_lex.cc' > --- a/sql/sql_lex.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/sql_lex.cc 2012-05-11 15:25:03 +0000 > @@ -3405,7 +3405,23 @@ bool st_select_lex::add_index_hint (THD > } > > > -bool st_select_lex::optimize_unflattened_subqueries() > +/** > + Optimize all subqueries that have not been flattened into semi-joins. > + > + @details > + This functionality is a method of SELECT_LEX instead of JOIN because > + SQL statements as DELETE/UPDATE do not have a corresponding JOIN object. > + > + @see JOIN::optimize_unflattened_subqueries > + > + @param const_only Restrict subquery optimization to constant subqueries > + > + @return Operation status > + @retval FALSE success. > + @retval TRUE error occurred. > +*/ > + > +bool st_select_lex::optimize_unflattened_subqueries(bool const_only) > { > for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit()) > { > @@ -3415,11 +3431,17 @@ bool st_select_lex::optimize_unflattened > { > if (subquery_predicate->substype() == Item_subselect::IN_SUBS) > { > - Item_in_subselect *in_subs=(Item_in_subselect*)subquery_predicate; > + Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate; > if (in_subs->is_jtbm_merged) > continue; > } > > + if (const_only && !subquery_predicate->const_pred()) > + { > + /* Skip non-constant subqueries if the caller asked so. */ > + continue; > + } > + > bool empty_union_result= true; > /* > If the subquery is a UNION, optimize all the subqueries in the UNION. If > > === modified file 'sql/sql_lex.h' > --- a/sql/sql_lex.h 2012-04-10 06:28:13 +0000 > +++ b/sql/sql_lex.h 2012-05-11 15:25:03 +0000 > @@ -997,12 +997,7 @@ class st_select_lex: public st_select_le > > void clear_index_hints(void) { index_hints= NULL; } > bool is_part_of_union() { return master_unit()->is_union(); } > - /* > - Optimize all subqueries that have not been flattened into semi-joins. > - This functionality is a method of SELECT_LEX instead of JOIN because > - some SQL statements as DELETE do not have a corresponding JOIN object. > - */ > - bool optimize_unflattened_subqueries(); > + bool optimize_unflattened_subqueries(bool const_only); > /* Set the EXPLAIN type for this subquery. */ > void set_explain_type(); > bool handle_derived(LEX *lex, uint phases); > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/sql_select.cc 2012-05-11 15:25:03 +0000 > @@ -986,7 +986,10 @@ JOIN::optimize() > } > > eval_select_list_used_tables(); > - > + > + if (optimize_constant_subqueries()) > + DBUG_RETURN(1); > + > table_count= select_lex->leaf_tables.elements; > > if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ > @@ -1273,6 +1276,12 @@ JOIN::optimize() > { > conds= substitute_for_best_equal_field(NO_PARTICULAR_TAB, conds, > cond_equal, map2table); > + if (thd->is_error()) > + { > + error= 1; > + DBUG_PRINT("error",("Error from substitute_for_best_equal")); > + DBUG_RETURN(1); > + } > conds->update_used_tables(); > DBUG_EXECUTE("where", > print_where(conds, > @@ -1293,6 +1302,12 @@ JOIN::optimize() > *tab->on_expr_ref, > tab->cond_equal, > map2table); > + if (thd->is_error()) > + { > + error= 1; > + DBUG_PRINT("error",("Error from substitute_for_best_equal")); > + DBUG_RETURN(1); > + } > (*tab->on_expr_ref)->update_used_tables(); > } > } > @@ -6592,6 +6607,36 @@ void JOIN::get_prefix_cost_and_fanout(ui > > > /** > + Estimate the number of rows that query execution will read. > + > + @todo This is a very pessimistic upper bound. Use join selectivity > + when available to produce a more realistic number. > +*/ > + > +double JOIN::get_examined_rows() > +{ > + /* Each constant table examines one row, and the result is at most one row. */ > + ha_rows examined_rows= const_tables; > + uint i= const_tables; > + double prev_fanout; > + > + if (table_count == const_tables) > + return examined_rows; > + > + examined_rows+= join_tab[i++].get_examined_rows(); > + for (; i < table_count ; i++) > + { > + if (join_tab[i].type == JT_EQ_REF) > + prev_fanout= 1; > + else > + prev_fanout= best_positions[i-1].records_read; This looks wrong. Declaration of POSITION::records_read has this comment: /* The "fanout": number of output rows that will be produced (after pushed down selection condition is applied) per each row combination of previous tables. */ note the "PER EACH ROW COMBINATION .." part. I would expect that this function would calculate a product of records_read values. > + examined_rows+= join_tab[i].get_examined_rows() * prev_fanout; > + } > + return examined_rows; > +} > + > + > +/** > Find a good, possibly optimal, query execution plan (QEP) by a possibly > exhaustive search. > > @@ -8011,36 +8056,15 @@ JOIN::make_simple_join(JOIN *parent, TAB > row_limit= unit->select_limit_cnt; > do_send_rows= row_limit ? 1 : 0; > > - join_tab->use_join_cache= FALSE; > - join_tab->cache=0; /* No caching */ > + bzero(join_tab, sizeof(JOIN_TAB)); > join_tab->table=temp_table; > - join_tab->cache_select= 0; > - join_tab->select=0; > - join_tab->select_cond= 0; // Avoid valgrind warning > join_tab->set_select_cond(NULL, __LINE__); > - join_tab->quick=0; > join_tab->type= JT_ALL; /* Map through all records */ > join_tab->keys.init(); > join_tab->keys.set_all(); /* test everything in quick */ > - join_tab->info=0; > - join_tab->on_expr_ref=0; > - join_tab->last_inner= 0; > - join_tab->first_unmatched= 0; > join_tab->ref.key = -1; > - join_tab->not_used_in_distinct=0; > join_tab->read_first_record= join_init_read_record; > - join_tab->preread_init_done= FALSE; > join_tab->join= this; > - join_tab->ref.key_parts= 0; > - join_tab->keep_current_rowid= FALSE; > - join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL; > - join_tab->do_firstmatch= NULL; > - join_tab->loosescan_match_tab= NULL; > - join_tab->emb_sj_nest= NULL; > - join_tab->pre_idx_push_select_cond= NULL; > - join_tab->bush_root_tab= NULL; > - join_tab->bush_children= NULL; > - join_tab->last_leaf_in_bush= FALSE; > bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); > temp_table->status=0; > temp_table->null_row=0; > @@ -10225,6 +10249,51 @@ double JOIN_TAB::scan_time() > return res; > } > > + > +/** > + Estimate the number of rows that a an access method will read from a table. > + > + @todo: why not use JOIN_TAB::found_records > +*/ > + > +ha_rows JOIN_TAB::get_examined_rows() > +{ > + ha_rows examined_rows; > + > + if (select && select->quick) > + examined_rows= select->quick->records; > + else if (type == JT_NEXT || type == JT_ALL || > + type == JT_HASH || type ==JT_HASH_NEXT) > + { > + if (limit) > + { > + /* > + @todo This estimate is wrong, a LIMIT query may examine much more rows > + than the LIMIT itself. > + */ > + examined_rows= limit; > + } > + else > + { > + if (table->is_filled_at_execution()) > + examined_rows= records; > + else > + { > + /* > + handler->info(HA_STATUS_VARIABLE) has been called in > + make_join_statistics() > + */ > + examined_rows= table->file->stats.records; > + } > + } > + } > + else > + examined_rows= (ha_rows) records_read; > + > + return examined_rows; > +} > + > + > /** > Initialize the join_tab before reading. > Currently only derived table/view materialization is done here. > @@ -11204,9 +11273,9 @@ static bool check_simple_equality(Item * > if (!item) > { > Item_func_eq *eq_item; > - if ((eq_item= new Item_func_eq(orig_left_item, orig_right_item))) > + if (!(eq_item= new Item_func_eq(orig_left_item, orig_right_item)) || > + eq_item->set_cmp_func()) > return FALSE; > - eq_item->set_cmp_func(); > eq_item->quick_fix_field(); > item= eq_item; > } > @@ -11299,9 +11368,9 @@ static bool check_row_equality(THD *thd, > if (!is_converted) > { > Item_func_eq *eq_item; > - if (!(eq_item= new Item_func_eq(left_item, right_item))) > + if (!(eq_item= new Item_func_eq(left_item, right_item)) || > + eq_item->set_cmp_func()) > return FALSE; > - eq_item->set_cmp_func(); > eq_item->quick_fix_field(); > eq_list->push_back(eq_item); > } > @@ -11987,9 +12056,8 @@ Item *eliminate_item_equal(COND *cond, C > > eq_item= new Item_func_eq(field_item->real_item(), head_item); > > - if (!eq_item) > + if (!eq_item || eq_item->set_cmp_func()) > return 0; > - eq_item->set_cmp_func(); > eq_item->quick_fix_field(); > } > current_sjm= field_sjm; > @@ -12076,7 +12144,7 @@ Item *eliminate_item_equal(COND *cond, C > Item_equal::get_first() for details. > > @return > - The transformed condition > + The transformed condition, or NULL in case of error > */ > > static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab, > @@ -18544,6 +18612,7 @@ check_reverse_order: > tab->ref.key_parts= 0; > if (select_limit < table->file->stats.records) > tab->limit= select_limit; > + table->disable_keyread(); ^^^ This change looks weird. Let's discuss it. > } > } > else if (tab->type != JT_ALL) > @@ -21269,10 +21338,17 @@ static void select_describe(JOIN *join, > } > else > { > - TABLE_LIST *real_table= table->pos_in_table_list; > - item_list.push_back(new Item_string(real_table->alias, > - strlen(real_table->alias), > - cs)); > + TABLE_LIST *real_table= table->pos_in_table_list; > + /* > + Internal temporary tables have no corresponding table reference > + object. Such a table may appear in EXPLAIN when a subquery that needs > + a temporary table has been executed, and JOIN::exec replaced the > + original JOIN with a plan to access the data in the temp table > + (made by JOIN::make_simple_join). > + */ > + const char *tab_name= real_table ? real_table->alias : > + "internal_tmp_table"; > + item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs)); > } > /* "partitions" column */ > if (join->thd->lex->describe & DESCRIBE_PARTITIONS) > @@ -21430,32 +21506,8 @@ static void select_describe(JOIN *join, > } > else > { > - ha_rows examined_rows; > - if (tab->select && tab->select->quick) > - examined_rows= tab->select->quick->records; > - else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj) > - { > - if (tab->limit) > - examined_rows= tab->limit; > - else > - { > - if (tab->table->is_filled_at_execution()) > - { > - examined_rows= tab->records; > - } > - else > - { > - /* > - handler->info(HA_STATUS_VARIABLE) has been called in > - make_join_statistics() > - */ > - examined_rows= tab->table->file->stats.records; > - } > - } > - } > - else > - examined_rows=(ha_rows)tab->records_read; > - > + ha_rows examined_rows= tab->get_examined_rows(); > + > item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, > MY_INT64_NUM_DECIMAL_DIGITS)); > > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2012-05-04 05:16:38 +0000 > +++ b/sql/sql_select.h 2012-05-11 15:25:03 +0000 > @@ -512,6 +512,7 @@ typedef struct st_join_table { > return (is_hash_join_key_no(key) ? hj_key : table->key_info+key); > } > double scan_time(); > + ha_rows get_examined_rows(); > bool preread_init(); > > bool is_sjm_nest() { return test(bush_children); } > @@ -1281,6 +1282,7 @@ class JOIN :public Sql_alloc > bool alloc_func_list(); > bool flatten_subqueries(); > bool optimize_unflattened_subqueries(); > + bool optimize_constant_subqueries(); > bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, > bool before_group_by, bool recompute= FALSE); > > @@ -1380,6 +1382,7 @@ class JOIN :public Sql_alloc > void get_prefix_cost_and_fanout(uint n_tables, > double *read_time_arg, > double *record_count_arg); > + double get_examined_rows(); > /* defined in opt_subselect.cc */ > bool transform_max_min_subquery(); > /* True if this JOIN is a subquery under an IN predicate. */ > > === modified file 'sql/sql_update.cc' > --- a/sql/sql_update.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/sql_update.cc 2012-05-11 15:25:03 +0000 > @@ -368,7 +368,7 @@ int mysql_update(THD *thd, > } > > /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ > - if (select_lex->optimize_unflattened_subqueries()) > + if (select_lex->optimize_unflattened_subqueries(false)) > DBUG_RETURN(TRUE); > > if (select_lex->inner_refs_list.elements && > > === modified file 'sql/sys_vars.cc' > --- a/sql/sys_vars.cc 2012-04-19 14:00:13 +0000 > +++ b/sql/sys_vars.cc 2012-05-11 15:25:03 +0000 > @@ -3780,4 +3780,9 @@ static Sys_var_ulong Sys_debug_binlog_fs > CMD_LINE(REQUIRED_ARG), > VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1)); > #endif > - > +static Sys_var_harows Sys_expensive_subquery_limit( > + "expensive_subquery_limit", > + "The maximum number of rows a subquery examines in order to be " > + "considered non-expensive", > + SESSION_VAR(expensive_subquery_limit), CMD_LINE(REQUIRED_ARG), > + VALID_RANGE(0, HA_POS_ERROR), DEFAULT(100), BLOCK_SIZE(1)); > > === modified file 'sql/table.cc' > --- a/sql/table.cc 2012-05-04 05:16:38 +0000 > +++ b/sql/table.cc 2012-05-11 15:25:03 +0000 > @@ -5985,7 +5985,8 @@ void TABLE::use_index(int key_to_save) > > bool TABLE::is_filled_at_execution() > { > - return test(pos_in_table_list->jtbm_subselect || > + return test(!pos_in_table_list || > + pos_in_table_list->jtbm_subselect || > pos_in_table_list->is_active_sjm()); Could you please add a comment for pos_in_table_list that it can be NULL for some tables? (I assume you still remember what kind of tables have pos_in_table_list==NULL?) > } > > A general question: was this expected to work: MariaDB [j3]> explain extended select * from t1 where 33 in (select b from five) or c > 11; +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | five | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (22 min 25.44 sec) The subuqery is constant, table `five` has 5 records, and none of them has b=33. When debugging, I see JOIN::get_examined_rows() to be invoked and it returns 5 rows, but EXPLAIN still doesn't show "Impossible WHERE"? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

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

14 May '12

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

2 4