- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 2971: MBug#643463: Slow XtraDB shutdown: Fix more sleeps delaying shutdown. in http://bazaar.launchpad.net/~maria-captains/maria/5.1
by Kristian Nielsen 10 Nov '10

10 Nov '10

Hi Codership, Can you do a review of this patch for InnoDB (XtraDB)? It is https://bugs.launchpad.net/percona-server/+bug/643463, it fixes a problem that a number of threads in the InnoDB code used sleep() for several seconds, which unnecessarily delays shutdown (this is especially annoying when doing testing, eg. the test suite). - Kristian. knielsen(a)knielsen-hq.org writes: > At http://bazaar.launchpad.net/~maria-captains/maria/5.1 > > ------------------------------------------------------------ > revno: 2971 > revision-id: knielsen(a)knielsen-hq.org-20101109140357-2obql1onnmjg6to9 > parent: monty(a)askmonty.org-20101107122529-sl5sup1m9gjxlzte > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-5.1 > timestamp: Tue 2010-11-09 15:03:57 +0100 > message: > MBug#643463: Slow XtraDB shutdown: Fix more sleeps delaying shutdown. > > This patch removes most remaining delays due to uninteruptible sleep() > during shutdown, as found using PMP. This makes standard test run very > close in speed to with --loose-innodb-fast-shutdown=2, and greatly > speeds up running the test suite. > === modified file 'storage/xtradb/include/srv0srv.h' > --- a/storage/xtradb/include/srv0srv.h 2010-11-03 21:40:53 +0000 > +++ b/storage/xtradb/include/srv0srv.h 2010-11-09 14:03:57 +0000 > @@ -57,8 +57,8 @@ extern const char srv_mysql50_table_name > thread starts running */ > extern os_event_t srv_lock_timeout_thread_event; > > -/* This event is set to tell the purge thread to shut down */ > -extern os_event_t srv_purge_thread_event; > +/* This event is set at shutdown to wakeup threads from sleep */ > +extern os_event_t srv_shutdown_event; > > /* If the last data file is auto-extended, we add this many pages to it > at a time */ > > === modified file 'storage/xtradb/log/log0log.c' > --- a/storage/xtradb/log/log0log.c 2010-11-03 21:40:53 +0000 > +++ b/storage/xtradb/log/log0log.c 2010-11-09 14:03:57 +0000 > @@ -3102,7 +3102,7 @@ logs_empty_and_mark_files_at_shutdown(vo > algorithm only works if the server is idle at shutdown */ > > srv_shutdown_state = SRV_SHUTDOWN_CLEANUP; > - os_event_set(srv_purge_thread_event); > + os_event_set(srv_shutdown_event); > loop: > os_thread_sleep(100000); > > > === modified file 'storage/xtradb/srv/srv0srv.c' > --- a/storage/xtradb/srv/srv0srv.c 2010-11-03 21:40:53 +0000 > +++ b/storage/xtradb/srv/srv0srv.c 2010-11-09 14:03:57 +0000 > @@ -704,7 +704,7 @@ UNIV_INTERN srv_slot_t* srv_mysql_table > > UNIV_INTERN os_event_t srv_lock_timeout_thread_event; > > -UNIV_INTERN os_event_t srv_purge_thread_event; > +UNIV_INTERN os_event_t srv_shutdown_event; > > UNIV_INTERN srv_sys_t* srv_sys = NULL; > > @@ -1011,7 +1011,7 @@ srv_init(void) > } > > srv_lock_timeout_thread_event = os_event_create(NULL); > - srv_purge_thread_event = os_event_create(NULL); > + srv_shutdown_event = os_event_create(NULL); > > for (i = 0; i < SRV_MASTER + 1; i++) { > srv_n_threads_active[i] = 0; > @@ -2239,7 +2239,7 @@ loop: > /* Wake up every 5 seconds to see if we need to print > monitor information. */ > > - os_thread_sleep(5000000); > + os_event_wait_time(srv_shutdown_event, 5000000); > > current_time = time(NULL); > > @@ -2381,7 +2381,7 @@ loop: > /* When someone is waiting for a lock, we wake up every second > and check if a timeout has passed for a lock wait */ > > - os_thread_sleep(1000000); > + os_event_wait_time(srv_shutdown_event, 1000000); > > srv_lock_timeout_active = TRUE; > > @@ -2546,7 +2546,7 @@ loop: > > fflush(stderr); > > - os_thread_sleep(1000000); > + os_event_wait_time(srv_shutdown_event, 1000000); > > if (srv_shutdown_state < SRV_SHUTDOWN_CLEANUP) { > > @@ -2590,7 +2590,7 @@ srv_LRU_dump_restore_thread( > last_dump_time = time(NULL); > > loop: > - os_thread_sleep(5000000); > + os_event_wait_time(srv_shutdown_event, 5000000); > > if (srv_shutdown_state >= SRV_SHUTDOWN_CLEANUP) { > goto exit_func; > @@ -2754,7 +2754,7 @@ loop: > > if (!skip_sleep) { > > - os_thread_sleep(1000000); > + os_event_wait_time(srv_shutdown_event, 1000000); > srv_main_sleeps++; > > /* > @@ -3340,10 +3340,10 @@ loop: > mutex_exit(&kernel_mutex); > > sleep_ms = 10; > - os_event_reset(srv_purge_thread_event); > + os_event_reset(srv_shutdown_event); > } > > - os_event_wait_time(srv_purge_thread_event, sleep_ms * 1000); > + os_event_wait_time(srv_shutdown_event, sleep_ms * 1000); > > history_len = trx_sys->rseg_history_len; > if (history_len > 1000) > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] Is it possible to have RQG look for crashes only?
by Sergey Petrunya 09 Nov '10

09 Nov '10

Hi Philip, I've been fixing bugs in maria-5.3-mwl128-dsmrr-cpk tree, fixed two of the four problems so far. My general impression is that crashes are easier to fix than wrong results. Is it possible to run RQG in a mode that will cause it to ignore the difference in query results, and stop only on crashes? if yes, I'd like to get a command/grammar that were used to test the -mwl128-dsmrr-cpk tree. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 2845: Fixed LP bug #668644. in file:///home/igor/maria/maria-5.3-bug668644/
by Sergey Petrunya 08 Nov '10

08 Nov '10

Ok to push. On Mon, Nov 08, 2010 at 08:36:33PM -0800, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-bug668644/ > > ------------------------------------------------------------ > revno: 2845 > revision-id: igor(a)askmonty.org-20101109043632-s053dbydv48cr9nz > parent: monty(a)askmonty.org-20101105103751-09kb6rx5tvpyywen > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-bug668644 > timestamp: Mon 2010-11-08 20:36:32 -0800 > message: > Fixed LP bug #668644. > The pushdown condition for the sorted table in a query can be complemented > by the conditions from HAVING. This transformation is done in JOIN::exec > pretty late after the original pushdown condition have been saved in the > field pre_idx_push_select_cond for the sorted table. So this field must > be updated after the inclusion of the condition from HAVING. > === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' > --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-10-28 17:04:23 +0000 > +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-11-09 04:36:32 +0000 > @@ -2609,5 +2609,41 @@ > rows 3 > Extra Using index > DROP TABLE t1; > -# > End of 5.1 tests > +# > +# Bug#668644: HAVING + ORDER BY > +# > +CREATE TABLE t1 ( > +pk int NOT NULL PRIMARY KEY, i int DEFAULT NULL, > +INDEX idx (i) > +) ENGINE=INNODB; > +INSERT INTO t1 VALUES > +(6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400); > +CREATE TABLE t2 ( > +i int DEFAULT NULL, > +pk int NOT NULL PRIMARY KEY, > +INDEX idx (i) > +) ENGINE= INNODB; > +INSERT INTO t2 VALUES > +(-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16), > +(-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17), > +(5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7), > +(576061440,3); > +EXPLAIN > +SELECT t1 .i AS f FROM t1, t2 > +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 > +HAVING f > 7 > +ORDER BY f; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort > +1 SIMPLE t2 ref idx idx 5 test.t1.pk 1 Using index > +SELECT t1 .i AS f FROM t1, t2 > +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 > +HAVING f > 7 > +ORDER BY f; > +f > +1148715008 > +1541734400 > +1541734400 > +DROP TABLE t1, t2; > +End of 5.3 tests > > === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test' > --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-10-19 13:58:35 +0000 > +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2010-11-09 04:36:32 +0000 > @@ -840,7 +840,41 @@ > > DROP TABLE t1; > > ---echo # > - > - > --echo End of 5.1 tests > + > +--echo # > +--echo # Bug#668644: HAVING + ORDER BY > +--echo # > + > +CREATE TABLE t1 ( > + pk int NOT NULL PRIMARY KEY, i int DEFAULT NULL, > + INDEX idx (i) > +) ENGINE=INNODB; > +INSERT INTO t1 VALUES > + (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400); > + > +CREATE TABLE t2 ( > + i int DEFAULT NULL, > + pk int NOT NULL PRIMARY KEY, > + INDEX idx (i) > +) ENGINE= INNODB; > +INSERT INTO t2 VALUES > + (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16), > + (-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17), > + (5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7), > + (576061440,3); > + > +EXPLAIN > +SELECT t1 .i AS f FROM t1, t2 > + WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 > + HAVING f > 7 > + ORDER BY f; > +SELECT t1 .i AS f FROM t1, t2 > + WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224 > + HAVING f > 7 > + ORDER BY f; > + > +DROP TABLE t1, t2; > + > + > +--echo End of 5.3 tests > > === modified file 'sql/opt_index_cond_pushdown.cc' > --- a/sql/opt_index_cond_pushdown.cc 2009-12-22 12:49:15 +0000 > +++ b/sql/opt_index_cond_pushdown.cc 2010-11-09 04:36:32 +0000 > @@ -318,7 +318,7 @@ > if (idx_cond) > { > Item *idx_remainder_cond= 0; > - tab->pre_idx_push_select_cond= tab->select_cond; > + tab->pre_idx_push_select_cond= tab->select->cond; > /* > For BKA cache we store condition to special BKA cache field > because evaluation of the condition requires additional operations > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-28 17:04:23 +0000 > +++ b/sql/sql_select.cc 2010-11-09 04:36:32 +0000 > @@ -2213,6 +2213,14 @@ > DBUG_VOID_RETURN; > curr_table->select->cond->fix_fields(thd, 0); > } > + if (curr_table->pre_idx_push_select_cond) > + { > + if (!(curr_table->pre_idx_push_select_cond= > + new Item_cond_and(curr_table->pre_idx_push_select_cond, > + sort_table_cond))) > + DBUG_VOID_RETURN; > + curr_table->pre_idx_push_select_cond->fix_fields(thd, 0); > + } > curr_table->set_select_cond(curr_table->select->cond, __LINE__); > curr_table->select_cond->top_level_item(); > DBUG_EXECUTE("where",print_where(curr_table->select->cond, > @@ -6355,6 +6363,7 @@ > 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; > bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); > temp_table->status=0; > temp_table->null_row=0; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] LOCK_open contention for high-concurrency workloads
by MARK CALLAGHAN 07 Nov '10

07 Nov '10

We discussed this in Istanbul but were vague. Domas has provided more details on the problem. Fixing this will make high-concurrency results much better. http://bugs.mysql.com/bug.php?id=58037&thanks=4 -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] WL#166 New (by Dreas): Add server hostname/IP when access is denied
by worklog-noreply＠askmonty.org 07 Nov '10

07 Nov '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add server hostname/IP when access is denied CREATION DATE..: Sun, 07 Nov 2010, 16:17 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 166 (http://askmonty.org/worklog/?tid=166) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: If you're trying to access MariaDB without correct grants, it returns "Access denied for user 'user'@'ip' to database 'database'". It would be VERY useful to have the actual server hostname/IP that is trying to connect to included in the message, so it can be seen which server is missing the grant. Often when called from a script, handling a large MySQL cluster this is not directly obvious. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Tree with DS-MRR improvements ready for testing
by Sergey Petrunya 05 Nov '10

05 Nov '10

Hi Philip, The combined tree with DS-MRR improvements and MWL#128 is ready for testing. It is located at lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk/ . I don't know how hard that is for you, but it would be useful to check the failures - against 5.3-main tree (to see whether the problem is in the new code or not) - against lp:~maria-captains/maria-5.3-mwl128 (to see whether the problem is in MWL#128 or in the DS-MRR code) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Transactional INFORMATION_SCHEMA / status variables?
by Kristian Nielsen 05 Nov '10

05 Nov '10

Would it make sense to have transactional behaviour for status variables, and/or information_schema tables? Or does this break the code and/or user expectations too much? The motivation is to follow up on MWL#116, group commit, which introduces consistent commit order between storage engines and binary log. I want to use this to get a consistent binlog position for START TRANSACTION WITH CONSISTENT SNAPSHOT, without taking any additional locks. Currently, I believe it is common (eg. mysqlbinlog --master-data --single-transaction) to do something like this: FLUSH TABLES WITH READ LOCK; START TRANSACTION WITH CONSISTENT SNAPSHOT; UNLOCK TABLES <take consistent backup by dumping tables using the consistent snapshot> and this takes a lock that can depending on circumstances severely affect the server: http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-wit… One idea is to let the binlog storage engine participate in START TRANSACTION WITH CONSISTENT SNAPSHOT, by installing a start_consistent_snapshot() method in its handlerton. And then do something like this: START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT variable_value FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE variable_name IN ('binlog_master_file', 'binlog_master_position'); <dump other transactional tables> If the SELECT of binlog_master_file and binlog_master_position could be transactional, then the binlog engine could return the correct values associated with the consistent snapshot, without blocking any other transactions. I like the simplicity of this idea, but I do not understand server transaction handling enough to be sure it will work well, some concerns: - Using SHOW STATUS / INFORMATION_SCHEMA.SESSION_STATUS like this could be surprising to people that do not expect different parts of the results to display different kinds of transactional behaviour. This could be helped by using instead a separate INFORMATION_SCHEMA table for the binlog position. - But this raises another concern, if an INFORMATION_SCHEMA can be transactional in this respect? - Also, it seems to me that merely selecting from such transactional INFORMATION_SCHEMA table would then start a new transaction inside the binlog engine. I wonder if this would cause any unpleasant side effects? Any suggestions or comments? ---- If this does not work, I have another idea, which I think is more general, but also more complicated to implement. The idea is that every transaction has a local transaction ID, assigned at the start (we already have this, in thd->transaction.xid_state.xid). Each engine will transactionally store the local transaction ID of the last transaction committed. The binary log will similarly store this ID along with every transaction that is binlogged. Then START TRANSACTION WITH CONSISTENT SNAPSHOT could optionally return the local transaction ID of the last committed transaction visible in the snapshot obtained. This local transaction ID could then be mapped to binlog position (with eg. mysqldump), and more generally any binlog plugin could provide a way to map such local transaction ID into its own global transaction ID. Similarly, after restore of InnoDB hot backup or LVM snapshot, one could ask the engine for last committed local transaction ID, and map this to binlog position / global transaction ID to be able to use the restored backup to provision a new slave. This would work with any storage engine and any binlog/replication implementation, without any need for FLUSH TABLES WITH READ LOCK. - Kristian.

3 3

Re: [Maria-developers] [Commits] Rev 2845: Fixed LP bug #664594 and other bugs leading to invalid execution in file:///home/igor/maria/maria-5.3-mwl128/
by Sergey Petrunya 03 Nov '10

03 Nov '10

Hello Igor, Ok to push. On Wed, Nov 03, 2010 at 12:26:19PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128/ > > ------------------------------------------------------------ > revno: 2845 > revision-id: igor(a)askmonty.org-20101103192618-17ii8dyn1h2qzdy8 > parent: igor(a)askmonty.org-20101102235032-vh451jmuugv1gsr2 > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mwl128 > timestamp: Wed 2010-11-03 12:26:18 -0700 > message: > Fixed LP bug #664594 and other bugs leading to invalid execution > plans or wrong results due to the fact that JOIN_CACHE functions > ignored the possibility of interleaving materialized semijoin > tables with tables whose records were stored in join buffers. > This fixes would become mostly unnecessary if the new code of > mwl 90 was merged into 5.3 right now. > Yet the fix the code of optimize_wo_join_buffering was needed > in any case. > === modified file 'mysql-test/r/explain.result' > --- a/mysql-test/r/explain.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/explain.result 2010-11-03 19:26:18 +0000 > @@ -195,16 +195,16 @@ > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where > -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR) > +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary > +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); > dt > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where > -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR) > +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary > +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > dt > > === modified file 'mysql-test/r/group_by.result' > --- a/mysql-test/r/group_by.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/group_by.result 2010-11-03 19:26:18 +0000 > @@ -1543,7 +1543,8 @@ > (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index > -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 144 > CREATE TABLE t2 (a INT, b INT, KEY(a)); > INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); > EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect.result 2010-11-03 19:26:18 +0000 > @@ -2831,9 +2831,10 @@ > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 1.00 > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where > Warnings: > -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N')) > explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 > @@ -4203,8 +4204,8 @@ > CREATE INDEX I2 ON t1 (b); > EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > a b > CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); > @@ -4213,15 +4214,15 @@ > CREATE INDEX I2 ON t2 (b); > EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition > +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2) > SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); > a b > EXPLAIN > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); > a b > DROP TABLE t1,t2; > > === modified file 'mysql-test/r/subselect3.result' > --- a/mysql-test/r/subselect3.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect3.result 2010-11-03 19:26:18 +0000 > @@ -103,7 +103,7 @@ > 1 1 > show status like '%Handler_read_rnd_next'; > Variable_name Value > -Handler_read_rnd_next 5 > +Handler_read_rnd_next 11 > delete from t2; > insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); > set optimizer_switch='subquery_cache=off'; > @@ -1112,7 +1112,8 @@ > explain select * from (select a from t0) X where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1 > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 > 2 DERIVED t0 ALL NULL NULL NULL NULL 11 > drop table t0, t1; > create table t0 (a int); > @@ -1124,16 +1125,18 @@ > insert into t3 select A.a + 10*B.a from t0 A, t0 B; > explain select * from t3 where a in (select kp1 from t1 where kp1<20); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan > -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index > create table t4 (pk int primary key); > insert into t4 select a from t3; > explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 > and t4.pk=t1.c); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan > -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) > -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR > +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index > drop table t1, t3, t4; > create table t1 (a int) as select * from t0 where a < 5; > set @save_max_heap_table_size=@@max_heap_table_size; > @@ -1261,12 +1264,14 @@ > create table t2 as select * from t1; > explain select * from t2 where a in (select b from t1 where a=3); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan > -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index > explain select * from t2 where (b,a) in (select a,b from t1 where a=3); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan > -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index > drop table t1,t2; > create table t1 (a int, b int); > insert into t1 select a,a from t0; > @@ -1295,7 +1300,8 @@ > explain select * from t0 where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 10.24 > @@ -1308,7 +1314,8 @@ > explain select * from t0 where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 2008-01-01 > > === modified file 'mysql-test/r/subselect3_jcl6.result' > --- a/mysql-test/r/subselect3_jcl6.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect3_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -110,7 +110,7 @@ > 1 1 > show status like '%Handler_read_rnd_next'; > Variable_name Value > -Handler_read_rnd_next 5 > +Handler_read_rnd_next 11 > delete from t2; > insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); > set optimizer_switch='subquery_cache=off'; > @@ -1031,7 +1031,7 @@ > t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort > -1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join) > +1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join) > 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join) > 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where > 2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) > @@ -1039,7 +1039,6 @@ > t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; > a b c > 256 67 NULL > -256 67 NULL > drop table t1, t11, t12, t21, t22; > create table t1(a int); > insert into t1 values (0),(1); > @@ -1120,7 +1119,8 @@ > explain select * from (select a from t0) X where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1 > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 > 2 DERIVED t0 ALL NULL NULL NULL NULL 11 > drop table t0, t1; > create table t0 (a int); > @@ -1132,16 +1132,18 @@ > insert into t3 select A.a + 10*B.a from t0 A, t0 B; > explain select * from t3 where a in (select kp1 from t1 where kp1<20); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan > -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index > create table t4 (pk int primary key); > insert into t4 select a from t3; > explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 > and t4.pk=t1.c); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan > -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) > -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR > +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index > drop table t1, t3, t4; > create table t1 (a int) as select * from t0 where a < 5; > set @save_max_heap_table_size=@@max_heap_table_size; > @@ -1269,12 +1271,14 @@ > create table t2 as select * from t1; > explain select * from t2 where a in (select b from t1 where a=3); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan > -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index > explain select * from t2 where (b,a) in (select a,b from t1 where a=3); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan > -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index > drop table t1,t2; > create table t1 (a int, b int); > insert into t1 select a,a from t0; > @@ -1303,7 +1307,8 @@ > explain select * from t0 where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 10.24 > @@ -1316,7 +1321,8 @@ > explain select * from t0 where a in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 2008-01-01 > @@ -1404,7 +1410,7 @@ > ); > id select_type table type possible_keys key key_len ref rows filtered Extra > 1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 > -1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join) > +1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where > 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join) > Warnings: > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2010-11-03 19:26:18 +0000 > @@ -4207,8 +4207,8 @@ > CREATE INDEX I2 ON t1 (b); > EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > a b > CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); > @@ -4217,15 +4217,15 @@ > CREATE INDEX I2 ON t2 (b); > EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition > +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2) > SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); > a b > EXPLAIN > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); > id select_type table type possible_keys key key_len ref rows Extra > -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); > a b > DROP TABLE t1,t2; > > === modified file 'mysql-test/r/subselect_sj.result' > --- a/mysql-test/r/subselect_sj.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_sj.result 2010-11-03 19:26:18 +0000 > @@ -1062,8 +1062,10 @@ > WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 > -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1) > -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1 > +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where > SELECT * > FROM t1 > WHERE t1.val IN (SELECT t2.val FROM t2 > > === modified file 'mysql-test/r/subselect_sj2.result' > --- a/mysql-test/r/subselect_sj2.result 2010-10-18 20:33:05 +0000 > +++ b/mysql-test/r/subselect_sj2.result 2010-11-03 19:26:18 +0000 > @@ -52,7 +52,8 @@ > explain select * from t3 where b in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t3 ALL b NULL NULL NULL 10 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 > select * from t3 where b in (select a from t1); > a b pk1 pk2 pk3 > 1 1 1 1 1 > > === modified file 'mysql-test/r/subselect_sj2_jcl6.result' > --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-10-18 20:33:05 +0000 > +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -59,7 +59,8 @@ > explain select * from t3 where b in (select a from t1); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t3 ALL b NULL NULL NULL 10 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 > select * from t3 where b in (select a from t1); > a b pk1 pk2 pk3 > 1 1 1 1 1 > > === modified file 'mysql-test/r/subselect_sj_jcl6.result' > --- a/mysql-test/r/subselect_sj_jcl6.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -1069,8 +1069,10 @@ > WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 > -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) > -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1 > +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where > SELECT * > FROM t1 > WHERE t1.val IN (SELECT t2.val FROM t2 > > === modified file 'mysql-test/r/type_datetime.result' > --- a/mysql-test/r/type_datetime.result 2010-06-26 10:05:41 +0000 > +++ b/mysql-test/r/type_datetime.result 2010-11-03 19:26:18 +0000 > @@ -537,8 +537,8 @@ > select * from t1 > where id in (select id from t1 as x1 where (t1.cur_date is null)); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where > -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1) > +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary > +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 > Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0)) > @@ -549,8 +549,8 @@ > select * from t2 > where id in (select id from t2 as x1 where (t2.cur_date is null)); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where > -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary > +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 > Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0)) > > === modified file 'sql/sql_join_cache.cc' > --- a/sql/sql_join_cache.cc 2010-10-27 23:37:33 +0000 > +++ b/sql/sql_join_cache.cc 2010-11-03 19:26:18 +0000 > @@ -137,7 +137,44 @@ > *descr_ptr= copy_ptr; > return len; > } > - > + > +/* > + Get the next table whose records are stored in the join buffer of this cache > + > + SYNOPSIS > + get_next_table() > + tab the table for which the next table is to be returned > + > + DESCRIPTION > + For a given table whose records are stored in this cache the function > + returns the next such table if there is any. > + The function takes into account that the tables whose records are > + are stored in the same cache now can interleave with tables from > + materialized semijoin subqueries. > + > + TODO > + This function should be modified/simplified after the new code for > + materialized semijoins is merged. > + > + RETURN > + The next join table whose records are stored in the buffer of this cache > + if such table exists, 0 - otherwise > +*/ > + > +JOIN_TAB *JOIN_CACHE::get_next_table(JOIN_TAB *tab) > +{ > + > + if (++tab == join_tab) > + return NULL; > + if (join_tab->first_sjm_sibling) > + return tab; > + uint i= tab-join->join_tab; > + while (sj_is_materialize_strategy(join->best_positions[i].sj_strategy) && > + i < join->tables) > + i+= join->best_positions[i].n_sj_tables; > + return join->join_tab+i < join_tab ? join->join_tab+i : NULL; > +} > + > > /* > Determine different counters of fields associated with a record in the cache > @@ -159,7 +196,9 @@ > void JOIN_CACHE::calc_record_fields() > { > JOIN_TAB *tab = prev_cache ? prev_cache->join_tab : > - join->join_tab+join->const_tables; > + (join_tab->first_sjm_sibling ? > + join_tab->first_sjm_sibling : > + join->join_tab+join->const_tables); > tables= join_tab-tab; > > fields= 0; > @@ -169,7 +208,7 @@ > data_field_ptr_count= 0; > referenced_fields= 0; > > - for ( ; tab < join_tab ; tab++) > + for ( ; tab ; tab= get_next_table(tab)) > { > tab->calc_used_field_length(FALSE); > flag_fields+= test(tab->used_null_fields || tab->used_uneven_bit_fields); > @@ -222,7 +261,8 @@ > cache= this; > do > { > - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++) > + for (tab= cache->join_tab-cache->tables; tab ; > + tab= cache->get_next_table(tab)) > { > uint key_args; > bitmap_clear_all(&tab->table->tmp_set); > @@ -338,7 +378,7 @@ > &copy); > > /* Create fields for all null bitmaps and null row flags that are needed */ > - for (tab= join_tab-tables; tab < join_tab; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > TABLE *table= tab->table; > > @@ -425,7 +465,8 @@ > while (ext_key_arg_cnt) > { > cache= cache->prev_cache; > - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++) > + for (tab= cache->join_tab-cache->tables; tab; > + tab= cache->get_next_table(tab)) > { > CACHE_FIELD *copy_end; > MY_BITMAP *key_read_set= &tab->table->tmp_set; > @@ -475,7 +516,7 @@ > > /* Now create local fields that are used to build ref for this key access */ > copy= field_descr+flag_fields; > - for (tab= join_tab-tables; tab < join_tab ; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > length+= add_table_data_fields_to_join_cache(tab, &tab->table->tmp_set, > &data_field_count, &copy, > @@ -531,7 +572,7 @@ > CACHE_FIELD *copy= field_descr+flag_fields+data_field_count; > CACHE_FIELD **copy_ptr= blob_ptr+data_field_ptr_count; > > - for (tab= join_tab-tables; tab < join_tab; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > MY_BITMAP *rem_field_set; > TABLE *table= tab->table; > @@ -1341,6 +1382,7 @@ > end_pos= pos= cp; > *is_full= last_record; > > + last_written_is_null_compl= 0; > if (!join_tab->first_unmatched && join_tab->on_precond) > { > join_tab->found= 0; > @@ -1351,8 +1393,6 @@ > last_written_is_null_compl= 1; > } > } > - else > - last_written_is_null_compl= 0; > > return (uint) (cp-init_pos); > } > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-30 22:14:36 +0000 > +++ b/sql/sql_select.cc 2010-11-03 19:26:18 +0000 > @@ -7441,6 +7441,7 @@ > join join for which the check is performed > options options of the join > no_jbuf_after don't use join buffering after table with this number > + prev_tab previous join table > icp_other_tables_ok OUT TRUE if condition pushdown supports > other tables presence > idx_cond_fact_out OUT TRUE if condition pushed to the index is factored > @@ -7568,6 +7569,7 @@ > uint check_join_cache_usage(JOIN_TAB *tab, > JOIN *join, ulonglong options, > uint no_jbuf_after, > + JOIN_TAB *prev_tab, > bool *icp_other_tables_ok, > bool *idx_cond_fact_out) > { > @@ -7587,7 +7589,7 @@ > > *icp_other_tables_ok= TRUE; > *idx_cond_fact_out= TRUE; > - if (cache_level == 0 || i == join->const_tables) > + if (cache_level == 0 || i == join->const_tables || !prev_tab) > return 0; > > if (options & SELECT_NO_JOIN_CACHE) > @@ -7633,7 +7635,7 @@ > if (tab->first_sj_inner_tab && tab->first_sj_inner_tab != tab && > !tab->first_sj_inner_tab->use_join_cache) > goto no_join_cache; > - if (!tab[-1].use_join_cache) > + if (!prev_tab->use_join_cache) > { > /* > Check whether table tab and the previous one belong to the same nest of > @@ -7655,7 +7657,7 @@ > } > > if (!force_unlinked_cache) > - prev_cache= tab[-1].cache; > + prev_cache= prev_tab->cache; > > switch (tab->type) { > case JT_ALL: > @@ -7807,6 +7809,12 @@ > return TRUE; /* purecov: inspected */ > tab->sorted= TRUE; > } > + > + /* > + SJ-Materialization > + */ > + if (!(i >= first_sjm_table && i < last_sjm_table)) > + tab->first_sjm_sibling= NULL; > if (sj_is_materialize_strategy(join->best_positions[i].sj_strategy)) > { > /* This is a start of semi-join nest */ > @@ -7819,23 +7827,52 @@ > > if (setup_sj_materialization(tab)) > return TRUE; > + for (uint j= first_sjm_table; j != last_sjm_table; j++) > + join->join_tab[j].first_sjm_sibling= join->join_tab + first_sjm_table; > } > table->status=STATUS_NO_RECORD; > pick_table_access_method (tab); > > + /* > + This loop currently can be executed only once as the function > + check_join_cache_usage does not change the value of tab->type. > + It won't be true for the future code. > + */ > + for ( ; ; ) > + { > + enum join_type tab_type= tab->type; > + switch (tab->type) { > + case JT_SYSTEM: > + case JT_CONST: > + case JT_EQ_REF: > + case JT_REF: > + case JT_REF_OR_NULL: > + case JT_ALL: > + if ((jcl= check_join_cache_usage(tab, join, options, > + no_jbuf_after, > + i == last_sjm_table ? > + join->join_tab+first_sjm_table : > + tab-1, > + &icp_other_tables_ok, > + &idx_cond_fact_out))) > + { > + tab->use_join_cache= TRUE; > + tab[-1].next_select=sub_select_cache; > + } > + break; > + default: > + ; > + } > + if (tab->type == tab_type) > + break; > + } > + > switch (tab->type) { > case JT_SYSTEM: // Only happens with left join > case JT_CONST: // Only happens with left join > /* Only happens with outer joins */ > tab->read_first_record= tab->type == JT_SYSTEM ? > join_read_system :join_read_const; > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > { > @@ -7849,13 +7886,6 @@ > case JT_EQ_REF: > tab->read_record.unlock_row= join_read_key_unlock_row; > /* fall through */ > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > { > @@ -7875,13 +7905,6 @@ > } > delete tab->quick; > tab->quick=0; > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > table->enable_keyread(); > @@ -7896,12 +7919,6 @@ > Also don't use cache if this is the first table in semi-join > materialization nest. > */ > - if (check_join_cache_usage(tab, join, options, no_jbuf_after, > - &icp_other_tables_ok, &idx_cond_fact_out)) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > /* These init changes read_record */ > if (tab->use_quick == 2) > { > @@ -9563,6 +9580,11 @@ > Item_equal *upper= item_field->find_item_equal(upper_levels); > Item_field *item= item_field; > TABLE_LIST *field_sjm= embedding_sjm(item_field); > + if (!field_sjm) > + { > + current_sjm= NULL; > + current_sjm_head= NULL; > + } > > /* > Check if "item_field=head" equality is already guaranteed to be true > @@ -10629,7 +10651,7 @@ > { > /* Find the best access method that would not use join buffering */ > best_access_path(join, rs, reopt_remaining_tables, i, > - test(i < no_jbuf_before), rec_count, > + TRUE, rec_count, > &pos, &loose_scan_pos); > } > else > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2010-10-30 22:14:36 +0000 > +++ b/sql/sql_select.h 2010-11-03 19:26:18 +0000 > @@ -306,6 +306,8 @@ > */ > uint sj_strategy; > > + struct st_join_table *first_sjm_sibling; > + > void cleanup(); > inline bool is_using_loose_index_scan() > { > @@ -1035,6 +1037,8 @@ > buff= 0; > } > > + JOIN_TAB *get_next_table(JOIN_TAB *tab); > + > friend class JOIN_CACHE_HASHED; > friend class JOIN_CACHE_BNL; > friend class JOIN_CACHE_BKA; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 2953: Mess in types fixed. in file:///home/bell/maria/bzr/work-maria-5.1-itemtype-bugs2/
by Michael Widenius 03 Nov '10

03 Nov '10

>>>>> "sanja" == sanja <sanja(a)askmonty.org> writes: sanja> At file:///home/bell/maria/bzr/work-maria-5.1-itemtype-bugs2/ sanja> ------------------------------------------------------------ sanja> revno: 2953 sanja> revision-id: sanja(a)askmonty.org-20101019101449-g36e3lwnqy7qbmos sanja> parent: igor(a)askmonty.org-20101014214738-zvj7tbol145ohz2z sanja> committer: sanja(a)askmonty.org sanja> branch nick: work-maria-5.1-itemtype-bugs2 sanja> timestamp: Tue 2010-10-19 13:14:49 +0300 sanja> message: sanja> Mess in types fixed. > === modified file 'sql/item.h' > --- a/sql/item.h 2010-09-21 04:22:00 +0000 > +++ b/sql/item.h 2010-10-19 10:14:49 +0000 > @@ -488,8 +488,7 @@ public: > FIELD_VARIANCE_ITEM, INSERT_VALUE_ITEM, > SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER, > PARAM_ITEM, TRIGGER_FIELD_ITEM, DECIMAL_ITEM, > - XPATH_NODESET, XPATH_NODESET_CMP, > - VIEW_FIXER_ITEM}; > + XPATH_NODESET, XPATH_NODESET_CMP}; > > enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE }; > > @@ -2908,7 +2907,8 @@ public: > { > return Item_field::save_in_field(field_arg, no_conversions); > } > - /* > + enum Type type() const { return INSERT_VALUE_ITEM; } > + /* > We use RAND_TABLE_BIT to prevent Item_insert_value from > being treated as a constant and precalculated before execution > */ ok. (I assume this is only for Item_insert_value::eq()). Regards, Monty

1 0

[Maria-developers] DS-MRR improvements patch r3 ready for review
by Sergey Petrunya 03 Nov '10

03 Nov '10

Hello Igor, Please find attached the combined patch that addresses all of the review feedback provided so far. The tree is in launchpad and buildbot also: https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 3