- developers - lists.mariadb.org

[Maria-developers] Review of mysql-test-run speedup patch
by Kristian Nielsen 31 Jul '11

31 Jul '11

Hi Serg, Can you review below patch? (sorry for no commit mail, I forgot to configure outgoing mail on my new laptop before committing). I was for a long time annoyed that mtr takes 20 seconds starting up before it starts running the first test. So being bored one evening I ran a Perl profiler on the code and fixed the culprit. (BTW, I haven't forgotten your suggestion to change include/long_test.inc information to be in a separate file instead; I just haven't gotten around to it yet.) - Kristian.

2 2

Re: [Maria-developers] [Commits] Rev 3135: Subquery cache going on disk management fix: Do not go on disk if hit rate is not good. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hi Sanja, Ok to push. On Thu, Jul 28, 2011 at 05:10:29PM +0300, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/ > > ------------------------------------------------------------ > revno: 3135 > revision-id: sanja(a)askmonty.org-20110728141029-adkbj1vedh86m3uc > parent: igor(a)askmonty.org-20110723064728-j2sgq39rp528eaol > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-subquerycachedisk > timestamp: Thu 2011-07-28 17:10:29 +0300 > message: > Subquery cache going on disk management fix: Do not go on disk if hit rate is not good. <skip> BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Is it time to change 5.3 bugs status from Fix Committed to Fix Released?
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hello, The bug database has 197 bugs with status="Fix committed" and target_milestone=5.3. Since we've released 5.3, is it time to change status on these bugs to "Fix released"? 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 3135: Subquery cache going on disk management fix: Do not go on disk if hit/miss ration less than 2. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hi Sanja A summary of our discussion on irc/skype: Please change EXPCACHE_DISK_HITMISS_RATIO to be a "hit ratio", i.e "fraction of accesses that result in cache hits", as that's this is the most common number people use when they think about cache performance. Please change one limit into two: 1. A very poor hit ratio that will cause cache to stop working altogether (like the below patch does) 2. A moderately poor ratio that will cause the in-memory table to be emptied (instead of being converted to on-disk table). Suggested names: EXPCACHE_MIN_HIT_RATIO_FOR_DISK_TABLE, EXPCACHE_MIN_HIT_RATIO_FOR_MEM_TABLE. Please supply both definitions with comments about their meaning. On Thu, Jul 28, 2011 at 01:18:56PM +0300, sanja(a)askmonty.org wrote: > === modified file 'sql/sql_expression_cache.cc' > --- a/sql/sql_expression_cache.cc 2011-07-19 20:19:10 +0000 > +++ b/sql/sql_expression_cache.cc 2011-07-28 10:18:55 +0000 > @@ -16,6 +16,8 @@ > #include "mysql_priv.h" > #include "sql_select.h" > > +#define EXPCACHE_DISK_HITMISS_RATIO 2 > /* > Expression cache is used only for caching subqueries now, so its statistic > variables we call subquery_cache*. > @@ -26,7 +28,7 @@ Expression_cache_tmptable::Expression_ca > List<Item> &dependants, > Item *value) > :cache_table(NULL), table_thd(thd), items(dependants), val(value), > - inited (0) > + hit(0), miss(0), inited (0) > { > DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable"); > DBUG_VOID_RETURN; > @@ -180,10 +182,12 @@ Expression_cache::result Expression_cach > if (res) > { > subquery_cache_miss++; > + miss++; > DBUG_RETURN(MISS); > } > > subquery_cache_hit++; > + hit++; > *value= cached_result; > DBUG_RETURN(Expression_cache::HIT); > } > @@ -224,12 +228,26 @@ my_bool Expression_cache_tmptable::put_v > if ((error= cache_table->file->ha_write_tmp_row(cache_table->record[0]))) > { > /* create_myisam_from_heap will generate error if needed */ > - if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && > - create_internal_tmp_table_from_heap(table_thd, cache_table, > - cache_table_param.start_recinfo, > - &cache_table_param.recinfo, > - error, 1)) > + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP)) > goto err; > + else > + { > + if ((hit/miss) < EXPCACHE_DISK_HITMISS_RATIO) > + { > + DBUG_PRINT("info", ("hit/miss ratio is not so good to go to disk")); > + free_tmp_table(table_thd, cache_table); > + cache_table= NULL; > + DBUG_RETURN(FALSE); > + } > + else > + { > + if (create_internal_tmp_table_from_heap(table_thd, cache_table, > + cache_table_param.start_recinfo, > + &cache_table_param.recinfo, > + error, 1)) > + goto err; > + } > + } > } > cache_table->status= 0; /* cache_table->record contains an existed record */ > ref.has_record= TRUE; /* the same as above */ > > === modified file 'sql/sql_expression_cache.h' > --- a/sql/sql_expression_cache.h 2011-07-19 20:19:10 +0000 > +++ b/sql/sql_expression_cache.h 2011-07-28 10:18:55 +0000 > @@ -85,6 +85,8 @@ private: > List<Item> &items; > /* Value Item example */ > Item *val; > + /* hit/miss counters */ > + uint hit, miss; Please change to be ulong (same type as subquery_cache_hit/miss) > /* Set on if the object has been succesfully initialized with init() */ > bool inited; > }; > -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#233 New (by Aag): Partial Range Indx
by worklog-noreply＠askmonty.org 28 Jul '11

28 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Partial Range Indx CREATION DATE..: Thu, 28 Jul 2011, 10:44 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Maria-RawIdeaBin TASK ID........: 233 (http://askmonty.org/worklog/?tid=233) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: A way to make Indices significantly useful in some usage scenarios: Scoping the index to a specific range of values. This can be useful if some queries are only performed on a specific subset of the rows. Example: A process that implements a state machine for each row. All finished tasks have the state -1. To lookup the next task to perform only states > -1 have to be considered. Creating an index for the criteria to find the next task quickly would also index all the finished tasks. Which can make index creation expensive and waste several Gigabytes of diskspace (assuming millions of entries in the DB). While partitioning promises similar effects (a second axis for query optimizations) it is rendered utterly useless by its restriction that the partioning index must occur in *all* primary and unique keys which means one cannot define a large natural key via unique and a small artificial ID as primary key and then partition by an unrelated aspect (such as the state in my example). Range-restricted indices would allow similar optimizations: specify a range-restricted column as first column and then add the remaining criteria as other columns. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#232 New (by ColinCharles): Add missing SHUTDOWN statement
by worklog-noreply＠askmonty.org 28 Jul '11

28 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add missing SHUTDOWN statement CREATION DATE..: Thu, 28 Jul 2011, 03:26 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: Monty CATEGORY.......: Server-RawIdeaBin TASK ID........: 232 (http://askmonty.org/worklog/?tid=232) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: >From http://www.jebriggs.com/blog/2011/07/mysqls-missing-shutdown-statement/ Databases are more easily managed if all operations and logging are available to DBAs and scripts at the SQL client command line. So an additional improvement would be to add a SQL command like Oracles SHUTDOWN. See the blog post for a comparison table Reference from Oracle: http://www.dba-oracle.com/concepts/shutdown_commands.htm http://dev.mysql.com/doc/refman/5.5/en/mysql-shutdown.html http://dev.mysql.com/doc/refman/5.5/en/server-shutdown.html http://dev.mysql.com/doc/refman/5.5/en/kill.html http://dev.mysql.com/doc/refman/5.5/en/stop-slave.html USD$100 committed from James Briggs at the MariaDB BoF at OSCON 2011 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#231 New (by Monty): Faster UNIQUE key generation with ALTER TABLE
by worklog-noreply＠askmonty.org 27 Jul '11

27 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Faster UNIQUE key generation with ALTER TABLE CREATION DATE..: Wed, 27 Jul 2011, 16:49 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 231 (http://askmonty.org/worklog/?tid=231) VERSION........: Server-5.5 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 30 (hours remain) ORIG. ESTIMATE.: 30 PROGRESS NOTES: DESCRIPTION: Faster UNIQUE key generation with ALTER TABLE Generate also unique keys by sorting (as we do with non unique keys) to speed up alter tables with unique keys significantly. This worklog is for doing this for MyISAM and Aria tables. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Semi-join processing with Duplicate Elimination
by Sergey Petrunya 21 Jul '11

21 Jul '11

Hello, The below really should be in documentation, but for now, I'm posting it here: Semi-join processing with Duplicate Elimination =============================================== There are three cases: Semi-join next to outer join ---------------------------- ot1 left join (ot10, ot11) semi-join (it1, it2) interesting possible join orders: ot1 {ot10 ot11} it1 it2 <-- here we have just a "tail" so no problem. it1 ot1 {ot10, ot11} it2 Here the range fully covers the inner join with all its NULL-complemented record generation, etc. We need to take care about - NULL rowids are provided for NULL-complemented rows - being able to jump over all this when doing weedout (can we have a problem with some OJ's match flag not being reset?) it1 ot1 it2 {ot10 ot11} | | +------------------+ dups-w range This is a special case of the previous case. Need to - check dups-w for NULL-complemented record. (in case of nested outer joins: check for all NULL-complemented records generated at this point? well they all call evaluate_null_complemented_record, do they?) - check if this works with Not-exists optimization. it1 ot1 it2 {ot10 ot11} | | +------------+ dups-w range This case is the most interesting. The question is: should we first check dups-w, or care about outer joins? - dups-w check can be moved to the right. if we take a record combination of {ot1.row it2.row ot10.row} we must not discard it, because we'll need to know if this record combination extends to {ot1.row, it2.row ot10.row ot11.row } If it does, outer join will not have NULL-complemented record, otherwise it will. this means: no dups-w checks inside outer joins. Move them to the right until we've got the table that's last for all inner joins we're in. Outer join inside semi-join --------------------------- ot1 semi join (it1 left join (iit2, iit3)) Possible join orders: 1) ot1 it1 {iit2, iit3} 2) it1 ot1 {iit2, iit3} 3) it1 {iit2, iit3} ot1 4) ot1 {iit2, iit3} it1 #1 and #2 have an "outside" prefix followed by "tail" of SJ-inner OJ-inner tables. For iit3, we must first form a record for which both parts of WHERE and ON have been checked (or the record could be NULL-complemented) for #3 and #3, dups-w check is done after OJ processing. Semi-join inside outer join --------------------------- - Not possible in current scheme. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#230 New (by Mitchh): Extended Aggregates Functionality
by worklog-noreply＠askmonty.org 19 Jul '11

19 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Extended Aggregates Functionality CREATION DATE..: Tue, 19 Jul 2011, 13:43 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 230 (http://askmonty.org/worklog/?tid=230) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This idea is really an extension to SQL. It was implemented at my suggestion for the SQL like EPL (Event Processing language) over at Esper project (http://esper.codehaus.org) Basically the idea is to extend all aggregates (count(), avg() etc) so that they can accept an optional boolean filter expression as a second argument. Example use case: select count(*, color='black'), count(*) as percentBlack from Marbles; This is more expressive and more efficient than using subselects. original suggestion can be found at http://old.nabble.com/Feature-suggestion--filtering-inside-aggregates-to319… Mitch ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 3119: Fixed LP BUG#800696. in file:///home/bell/maria/bzr/work-maria-5.3-lpb800696/
by Timour Katchaounov 19 Jul '11

19 Jul '11

Sanja, These are some general notes about the patch: - Change Item::add_if_unique() to a method of base_list, and the List template class. - There is no need in subselect_engine::nest_level() because a subquery predicate is always at the same nest level irrespective of the chosen engine. The class Item_subselect has a member Item_subselect::unit, which should be sufficient to check the nest_level of the subquery. IMO this will simplify the patch visibly. I am pretty sure that if you add an assert, you will see that any engine will return the same nest_level as Item_subselect::unit->first_select(). - The commit comment is hard to understand. - Please first describe what was the problem, why there was a crash, and what is the generic problem with the current implementation. - Then describe what is the solution. - Fix the English of the commit comment. There are few more comments below marked with "timour:". Timour === modified file 'mysql-test/r/subselect_cache.result' --- a/mysql-test/r/subselect_cache.result 2011-02-03 15:00:28 +0000 +++ b/mysql-test/r/subselect_cache.result 2011-07-19 09:15:34 +0000 @@ -3272,6 +3272,7 @@ FROM t2 ) AND table1 .`col_varchar_key` col_varchar_nokey drop table t1,t2; set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; # LP BUG#615378 (incorrect NULL result returning in Item_cache) CREATE TABLE `t1` ( `pk` int(11) NOT NULL AUTO_INCREMENT, @@ -3310,3 +3311,25 @@ GROUP BY field3 HAVING (field3 <= 'h' AND field2 != 4) ; field2 field3 drop tables t1, t2, t3; +# +# Aggregate function in parameters test +# +CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b)); +INSERT INTO t1 VALUES +( 1, 1, 1 ), +( 1, 2, 2 ), +( 1, 3, 3 ), +( 1, 4, 6 ), +( 1, 5, 5 ), +( 1, 9, 13 ), +( 2, 1, 6 ), +( 2, 2, 7 ), +( 2, 3, 8 ); +SELECT a, AVG(t1.b), +(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c +FROM t1 GROUP BY a; +a AVG(t1.b) t11c +1 4.0000 6 +2 2.0000 7 +DROP TABLE t1; +set @@optimizer_switch= default; === modified file 'mysql-test/r/subselect_scache.result' --- a/mysql-test/r/subselect_scache.result 2011-07-15 08:36:36 +0000 +++ b/mysql-test/r/subselect_scache.result 2011-07-19 09:15:34 +0000 @@ -232,7 +232,7 @@ id select_type table type possible_keys 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` +Note 1003 select `test`.`t4`.`b` AS `b`,<expr_cache><`test`.`t4`.`a`>((select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) from `test`.`t2`)) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` select * from t3 where exists (select * from t2 where t2.b=t3.a); a 7 @@ -2835,7 +2835,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT 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`,<expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` 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 @@ -2847,7 +2847,7 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); === modified file 'mysql-test/t/subselect_cache.test' --- a/mysql-test/t/subselect_cache.test 2010-11-28 13:02:12 +0000 +++ b/mysql-test/t/subselect_cache.test 2011-07-19 09:15:34 +0000 @@ -1567,6 +1567,7 @@ FROM t2 ) AND table1 .`col_varchar_key` drop table t1,t2; set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; # --echo # LP BUG#615378 (incorrect NULL result returning in Item_cache) # @@ -1614,3 +1615,6 @@ GROUP BY field3 HAVING (field3 <= 'h' AND field2 != 4) ; --enable_warnings drop tables t1, t2, t3; + +--echo # +--echo # Aggregate function in parameters test timour: Change the above to: "Test aggregate functions as parameters to ... (to what?)" +--echo # + +CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b)); + +INSERT INTO t1 VALUES + ( 1, 1, 1 ), + ( 1, 2, 2 ), + ( 1, 3, 3 ), + ( 1, 4, 6 ), + ( 1, 5, 5 ), + ( 1, 9, 13 ), + + ( 2, 1, 6 ), + ( 2, 2, 7 ), + ( 2, 3, 8 ); + +SELECT a, AVG(t1.b), +(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c +FROM t1 GROUP BY a; + +DROP TABLE t1; + + +set @@optimizer_switch= default; === modified file 'sql/item.cc' --- a/sql/item.cc 2011-07-09 09:47:41 +0000 +++ b/sql/item.cc 2011-07-19 09:15:34 +0000 @@ -651,14 +651,14 @@ Item* Item::transform(Item_transformer t A pointer to created wrapper item if successful, NULL - otherwise */ -Item* Item::set_expr_cache(THD *thd, List<Item *> &depends_on) +Item* Item::set_expr_cache(THD *thd) { DBUG_ENTER("Item::set_expr_cache"); Item_cache_wrapper *wrapper; if ((wrapper= new Item_cache_wrapper(this)) && !wrapper->fix_fields(thd, (Item**)&wrapper)) { - if (wrapper->set_cache(thd, depends_on)) + if (wrapper->set_cache(thd)) DBUG_RETURN(NULL); DBUG_RETURN(wrapper); } @@ -666,6 +666,26 @@ Item* Item::set_expr_cache(THD *thd, Lis } +/** + Add this item to the items list if there is no such item in the list + + @param list The list of the Items to add to +*/ + timour: This method implements a set, which is a very generic functionality. Please change this into a method of base_list and List template class. +void Item::add_if_unique(List<Item> &list) +{ + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (eq(item, FALSE)) + return; + } + list.push_back(this); + return; +} + + Item_ident::Item_ident(Name_resolution_context *context_arg, const char *db_name_arg,const char *table_name_arg, const char *field_name_arg) @@ -742,6 +762,15 @@ bool Item_ident::remove_dependence_proce } +bool Item_ident::collect_outer_ref_processor(uchar *param) +{ + Collect_deps_prm *prm= (Collect_deps_prm *)param; + if (depended_from && depended_from->nest_level < prm->nest_level) + add_if_unique(*prm->parameters); + return FALSE; +} + + /** Store the pointer to this item field into a list if not already there. @@ -4357,9 +4386,6 @@ Item_field::fix_outer_field(THD *thd, Fi ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); /* A reference to a view field had been found and we substituted it instead of this Item (find_field_in_tables @@ -4460,9 +4486,6 @@ Item_field::fix_outer_field(THD *thd, Fi mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, rf, rf); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); return 0; } @@ -4471,9 +4494,6 @@ Item_field::fix_outer_field(THD *thd, Fi mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, (Item_ident*)*reference); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -6304,9 +6324,6 @@ bool Item_ref::fix_fields(THD *thd, Item refer_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); /* view reference found, we substituted it instead of this Item, so can quit @@ -6357,9 +6374,6 @@ bool Item_ref::fix_fields(THD *thd, Item thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, fld, fld); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6383,9 +6397,6 @@ bool Item_ref::fix_fields(THD *thd, Item DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); - context->select_lex-> - register_dependency_item(last_checked_context->select_lex, - reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6400,12 +6411,6 @@ bool Item_ref::fix_fields(THD *thd, Item } else if (ref_type() != VIEW_REF) { - if (depended_from && reference) - { - DBUG_ASSERT(context->select_lex != get_depended_from()); - context->select_lex->register_dependency_item(get_depended_from(), - reference); - } /* It could be that we're referring to something that's in ancestor selects. We must make an appropriate mark_as_dependent() call for each such @@ -6901,6 +6906,7 @@ Item_cache_wrapper::~Item_cache_wrapper( } + Item_cache_wrapper::Item_cache_wrapper(Item *item_arg) :orig_item(item_arg), expr_cache(NULL), expr_value(NULL) { @@ -6914,6 +6920,7 @@ Item_cache_wrapper::Item_cache_wrapper(I unsigned_flag= orig_item->unsigned_flag; name= item_arg->name; name_length= item_arg->name_length; + with_subselect= orig_item->with_subselect; if ((expr_value= Item_cache::get_cache(orig_item))) expr_value->setup(orig_item); @@ -6922,11 +6929,28 @@ Item_cache_wrapper::Item_cache_wrapper(I } +/** + Initialize the cache if it is needed +*/ + timour: It seems that the cache is not initialized "if needed". Insted it is intialized on-demand, if not yet initialized. Isn't it so? If yes, please fix the comment. I also don't like the method name - better something like init_on_demand(). +void Item_cache_wrapper::check_initialization() +{ + if (!expr_cache->is_inited()) + { + orig_item->get_cache_parameters(parameters); + expr_cache->init(); + } +} + + void Item_cache_wrapper::print(String *str, enum_query_type query_type) { str->append(func_name()); if (expr_cache) + { + check_initialization(); expr_cache->print(str, query_type); + } else str->append(STRING_WITH_LEN("<<DISABLED>>")); str->append('('); @@ -6962,6 +6986,7 @@ void Item_cache_wrapper::cleanup() expr_cache= 0; /* expr_value is Item so it will be destroyed from list of Items */ expr_value= 0; + parameters.empty(); DBUG_VOID_RETURN; } @@ -6982,11 +7007,11 @@ void Item_cache_wrapper::cleanup() @retval TRUE Error */ -bool Item_cache_wrapper::set_cache(THD *thd, List<Item*> &depends_on) +bool Item_cache_wrapper::set_cache(THD *thd) { DBUG_ENTER("Item_cache_wrapper::set_cache"); DBUG_ASSERT(expr_cache == 0); - expr_cache= new Expression_cache_tmptable(thd, depends_on, expr_value); + expr_cache= new Expression_cache_tmptable(thd, parameters, expr_value); DBUG_RETURN(expr_cache == NULL); } @@ -7011,6 +7036,7 @@ Item *Item_cache_wrapper::check_cache() { Expression_cache_tmptable::result res; Item *cached_value; + check_initialization(); res= expr_cache->check_value(&cached_value); if (res == Expression_cache_tmptable::HIT) DBUG_RETURN(cached_value); === modified file 'sql/item.h' --- a/sql/item.h 2011-07-03 21:59:01 +0000 +++ b/sql/item.h 2011-07-19 09:15:34 +0000 @@ -1155,6 +1155,15 @@ public: { return FALSE; } + struct Collect_deps_prm + { + int nest_level; + List<Item> *parameters; + }; + /** + Collect outer references + */ + virtual bool collect_outer_ref_processor(uchar *arg) {return FALSE; } /** Find a function of a given type @@ -1249,7 +1258,7 @@ public: { return Field::GEOM_GEOMETRY; }; String *check_well_formed_result(String *str, bool send_error= 0); bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); - Item* set_expr_cache(THD *thd, List<Item*> &depends_on); + Item* set_expr_cache(THD *thd); virtual Item *get_cached_item() { return NULL; } virtual Item_equal *get_item_equal() { return NULL; } @@ -1273,6 +1282,23 @@ public: walk(&Item::view_used_tables_processor, 0, (uchar *) view); return view->view_used_tables; } + + /** + Collect and add to the list cache parameters for this Item. + + @note Now implemented only for subqueries and in_optimizer, + if we need it for general function then this method should + be defined for Item_func. + */ + virtual void get_cache_parameters(List<Item> &parameters) { }; + + /** + Add this item to the items list if there is no such item in the list + + @param list The list of the Items to add to + */ + + void add_if_unique(List<Item> &list); }; @@ -1677,6 +1703,10 @@ public: virtual void print(String *str, enum_query_type query_type); virtual bool change_context_processor(uchar *cntx) { context= (Name_resolution_context *)cntx; return FALSE; } + /** + Collect outer references + */ + virtual bool collect_outer_ref_processor(uchar *arg); friend bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, List_iterator<Item> *it, @@ -2739,8 +2769,11 @@ private: */ Item_cache *expr_value; + List<Item> parameters; + Item *check_cache(); - inline void cache(); + void cache(); + void check_initialization(); public: Item_cache_wrapper(Item *item_arg); @@ -2750,7 +2783,7 @@ public: enum Type type() const { return EXPR_CACHE_ITEM; } enum Type real_type() const { return orig_item->type(); } - bool set_cache(THD *thd, List<Item*> &depends_on); + bool set_cache(THD *thd); bool fix_fields(THD *thd, Item **it); void fix_length_and_dec() {} @@ -2832,6 +2865,9 @@ public: if (result_type() == ROW_RESULT) orig_item->bring_value(); } + virtual bool is_expensive() { return orig_item->is_expensive(); } + bool is_expensive_processor(uchar *arg) + { return orig_item->is_expensive_processor(arg); } bool check_vcol_func_processor(uchar *arg) { return trace_unsupported_by_check_vcol_func_processor("cache"); === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2011-07-11 19:48:35 +0000 +++ b/sql/item_cmpfunc.cc 2011-07-19 09:15:34 +0000 @@ -1464,32 +1464,40 @@ Item *Item_in_optimizer::expr_cache_inse DBUG_ENTER("Item_in_optimizer::expr_cache_insert_transformer"); if (args[1]->type() != Item::SUBSELECT_ITEM) DBUG_RETURN(this); // MAX/MIN transformed => do nothing - List<Item*> &depends_on= ((Item_subselect *)args[1])->depends_on; if (expr_cache) DBUG_RETURN(expr_cache); + if (args[1]->expr_cache_is_needed(thd) && + (expr_cache= set_expr_cache(thd))) + DBUG_RETURN(expr_cache); + + DBUG_RETURN(this); +} + + +/** + Collect and add to the list cache parameters for this Item. + + @param parameters The list where to add parameters +*/ + +void Item_in_optimizer::get_cache_parameters(List<Item> &parameters) +{ /* Add left expression to the list of the parameters of the subquery */ if (args[0]->cols() == 1) - depends_on.push_front((Item**)args); + args[0]->add_if_unique(parameters); else { for (uint i= 0; i < args[0]->cols(); i++) { - depends_on.push_front(args[0]->addr(i)); + args[0]->element_index(i)->add_if_unique(parameters); } } - - if (args[1]->expr_cache_is_needed(thd) && - (expr_cache= set_expr_cache(thd, depends_on))) - DBUG_RETURN(expr_cache); - - /* no cache => return list in original state just to be safe */ - for (uint i= 0; i < args[0]->cols(); i++) - depends_on.pop(); - DBUG_RETURN(this); + args[1]->get_cache_parameters(parameters); } + /* The implementation of optimized \<outer expression\> [NOT] IN \<subquery\> predicates. The implementation works as follows. === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2011-07-13 18:05:33 +0000 +++ b/sql/item_cmpfunc.h 2011-07-19 09:15:34 +0000 @@ -259,6 +259,7 @@ public: bool is_expensive(); void set_join_tab_idx(uint join_tab_idx_arg) { args[1]->set_join_tab_idx(join_tab_idx_arg); } + virtual void get_cache_parameters(List<Item> &parameters); }; class Comp_creator === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2011-07-18 20:45:38 +0000 +++ b/sql/item_subselect.cc 2011-07-19 09:15:34 +0000 @@ -128,7 +128,6 @@ void Item_subselect::cleanup() } if (engine) engine->cleanup(); - depends_on.empty(); reset(); value_assigned= 0; expr_cache= 0; @@ -520,6 +519,11 @@ bool Item_subselect::walk(Item_processor { for (SELECT_LEX *lex= unit->first_select(); lex; lex= lex->next_select()) { + /* + List_iterator<Item> li(lex->join ? + lex->join->all_fields : + lex->item_list); + */ List_iterator<Item> li(lex->item_list); Item *item; ORDER *order; @@ -583,6 +587,12 @@ bool Item_subselect::exec() } +void Item_subselect::get_cache_parameters(List<Item> &parameters) +{ + Collect_deps_prm prm= { engine->nest_level(), &parameters }; + walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm); +} + int Item_in_subselect::optimize(double *out_rows, double *cost) { int res; @@ -647,7 +657,7 @@ int Item_in_subselect::optimize(double * bool Item_subselect::expr_cache_is_needed(THD *thd) { - return (depends_on.elements && + return ((engine->uncacheable() & UNCACHEABLE_DEPENDENT) && engine->cols() == 1 && optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && !(engine->uncacheable() & (UNCACHEABLE_RAND | @@ -675,8 +685,7 @@ bool Item_subselect::expr_cache_is_neede bool Item_in_subselect::expr_cache_is_needed(THD *thd) { - return (depends_on.elements && - optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + return (optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && !(engine->uncacheable() & (UNCACHEABLE_RAND | UNCACHEABLE_SIDEEFFECT))); } @@ -1009,7 +1018,7 @@ Item* Item_singlerow_subselect::expr_cac DBUG_RETURN(expr_cache); if (expr_cache_is_needed(thd) && - (expr_cache= set_expr_cache(thd, depends_on))) + (expr_cache= set_expr_cache(thd))) DBUG_RETURN(expr_cache); DBUG_RETURN(this); } @@ -1270,7 +1279,7 @@ Item* Item_exists_subselect::expr_cache_ DBUG_RETURN(expr_cache); if (substype() == EXISTS_SUBS && expr_cache_is_needed(thd) && - (expr_cache= set_expr_cache(thd, depends_on))) + (expr_cache= set_expr_cache(thd))) DBUG_RETURN(expr_cache); DBUG_RETURN(this); } @@ -4263,7 +4272,9 @@ subselect_hash_sj_engine::make_unique_en tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE); DBUG_RETURN(new subselect_uniquesubquery_engine(thd, tab, item, - semi_join_conds)); + semi_join_conds, + materialize_engine-> + nest_level())); } @@ -5725,3 +5736,15 @@ end: void subselect_table_scan_engine::cleanup() { } + timour: Make the two methods below inline. + +int subselect_single_select_engine::nest_level() +{ + return select_lex->nest_level; +}; + + +int subselect_union_engine::nest_level() +{ + return unit->first_select()->nest_level; +}; === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2011-07-18 20:45:38 +0000 +++ b/sql/item_subselect.h 2011-07-19 09:15:34 +0000 @@ -102,14 +102,6 @@ public: List<Ref_to_outside> upper_refs; st_select_lex *parent_select; - /** - List of references on items subquery depends on (externally resolved); - - @note We can't store direct links on Items because it could be - substituted with other item (for example for grouping). - */ - List<Item*> depends_on; - /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) @@ -225,6 +217,7 @@ public: @retval FALSE otherwise */ bool is_expensive_processor(uchar *arg) { return TRUE; } + /** Get the SELECT_LEX structure associated with this Item. @return the SELECT_LEX structure associated with this Item @@ -232,6 +225,7 @@ public: st_select_lex* get_select_lex(); const char *func_name() const { DBUG_ASSERT(0); return "subselect"; } virtual bool expr_cache_is_needed(THD *); + virtual void get_cache_parameters(List<Item> &parameters); friend class select_result_interceptor; friend class Item_in_optimizer; @@ -646,6 +640,7 @@ public: virtual bool no_rows() = 0; virtual enum_engine_type engine_type() { return ABSTRACT_ENGINE; } virtual int get_identifier() { DBUG_ASSERT(0); return 0; } + virtual int nest_level()= 0; protected: void set_row(List<Item> &item_list, Item_cache **row); }; @@ -679,6 +674,7 @@ public: bool no_rows(); virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; } int get_identifier(); + virtual int nest_level(); friend class subselect_hash_sj_engine; friend class Item_in_subselect; @@ -708,6 +704,7 @@ public: bool is_executed() const; bool no_rows(); virtual enum_engine_type engine_type() { return UNION_ENGINE; } + virtual int nest_level(); }; @@ -736,6 +733,7 @@ class subselect_uniquesubquery_engine: p protected: st_join_table *tab; Item *cond; /* The WHERE condition of subselect */ + int save_nest_level; /* TRUE<=> last execution produced empty set. Valid only when left expression is NULL. @@ -746,8 +744,9 @@ public: // constructor can assign THD because it will be called after JOIN::prepare subselect_uniquesubquery_engine(THD *thd_arg, st_join_table *tab_arg, - Item_subselect *subs, Item *where) - :subselect_engine(thd_arg, subs, 0), tab(tab_arg), cond(where) + Item_subselect *subs, Item *where, int lvl) + :subselect_engine(thd_arg, subs, 0), tab(tab_arg), cond(where), + save_nest_level(lvl) {} ~subselect_uniquesubquery_engine(); void cleanup(); @@ -769,6 +768,7 @@ public: int copy_ref_key_simple(); /* TIMOUR: this method needs refactoring. */ bool no_rows() { return empty_result_set; } virtual enum_engine_type engine_type() { return UNIQUESUBQUERY_ENGINE; } + virtual int nest_level() { return save_nest_level;}; }; @@ -811,8 +811,8 @@ public: // constructor can assign THD because it will be called after JOIN::prepare subselect_indexsubquery_engine(THD *thd_arg, st_join_table *tab_arg, Item_subselect *subs, Item *where, - Item *having_arg, bool chk_null) - :subselect_uniquesubquery_engine(thd_arg, tab_arg, subs, where), + Item *having_arg, bool chk_null, int lvl) + :subselect_uniquesubquery_engine(thd_arg, tab_arg, subs, where, lvl), check_null(chk_null), having(having_arg) {} @@ -905,6 +905,8 @@ public: bool temp= FALSE); bool no_tables();//=>base class + virtual int nest_level() { return materialize_engine->nest_level();}; + protected: /* The engine used to compute the IN predicate. */ subselect_engine *lookup_engine; @@ -1185,6 +1187,7 @@ public: return !(((Item_in_subselect *) item)->null_value); } void print(String*, enum_query_type); + virtual int nest_level() { return lookup_engine->nest_level(); } friend void subselect_hash_sj_engine::cleanup(); }; === modified file 'sql/item_sum.cc' --- a/sql/item_sum.cc 2011-06-27 16:07:24 +0000 +++ b/sql/item_sum.cc 2011-07-19 09:15:34 +0000 @@ -319,7 +319,6 @@ bool Item_sum::register_sum_func(THD *th if (aggr_level >= 0) { ref_by= ref; - thd->lex->current_select->register_dependency_item(aggr_sel, ref); /* Add the object to the list of registered objects assigned to aggr_sel */ if (!aggr_sel->inner_sum_func_list) next= this; @@ -355,6 +354,16 @@ bool Item_sum::register_sum_func(THD *th return FALSE; } + +bool Item_sum::collect_outer_ref_processor(uchar *param) +{ + Collect_deps_prm *prm= (Collect_deps_prm *)param; + SELECT_LEX *ds; + if ((ds= depended_from()) && ds->nest_level < prm->nest_level) + add_if_unique(*prm->parameters); + return FALSE; +} + Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements), forced_const(FALSE) === modified file 'sql/item_sum.h' --- a/sql/item_sum.h 2011-05-10 15:17:43 +0000 +++ b/sql/item_sum.h 2011-07-19 09:15:34 +0000 @@ -374,6 +374,7 @@ public: virtual Field *create_tmp_field(bool group, TABLE *table, uint convert_blob_length); bool walk(Item_processor processor, bool walk_subquery, uchar *argument); + virtual bool collect_outer_ref_processor(uchar *param); bool init_sum_func_check(THD *thd); bool check_sum_func(THD *thd, Item **ref); bool register_sum_func(THD *thd, Item **ref); === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2011-07-04 11:51:16 +0000 +++ b/sql/opt_range.cc 2011-07-19 09:15:34 +0000 @@ -11731,17 +11731,19 @@ check_group_min_max_predicates(Item *con Disallow loose index scan if the MIN/MAX argument field is referenced by a subquery in the WHERE clause. */ + if (cond_type == Item::SUBSELECT_ITEM) { Item_subselect *subs_cond= (Item_subselect*) cond; if (subs_cond->is_correlated) { - DBUG_ASSERT(subs_cond->depends_on.elements > 0); - List_iterator_fast<Item*> li(subs_cond->depends_on); - Item **dep; + DBUG_ASSERT(subs_cond->upper_refs.elements > 0); + List_iterator_fast<Item_subselect::Ref_to_outside> + li(subs_cond->upper_refs); + Item_subselect::Ref_to_outside *dep; while ((dep= li++)) { - if ((*dep)->eq(min_max_arg_item, FALSE)) + if (dep->item->eq(min_max_arg_item, FALSE)) DBUG_RETURN(FALSE); } } === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2011-07-19 07:45:46 +0000 +++ b/sql/opt_subselect.cc 2011-07-19 09:15:34 +0000 @@ -4167,7 +4167,10 @@ int rewrite_to_index_subquery_engine(JOI timour: Here you could introduce one variable 'nest_level' in the beginning of the method. subselect_uniquesubquery_engine(thd, join_tab, unit->item, - where))); + where, + join-> + select_lex-> + nest_level))); } else if (join_tab[0].type == JT_REF && join_tab[0].ref.items[0]->name == in_left_expr_name) @@ -4183,7 +4186,10 @@ int rewrite_to_index_subquery_engine(JOI unit->item, where, NULL, - 0))); + 0, + join-> + select_lex-> + nest_level))); } } else if (join_tab[0].type == JT_REF_OR_NULL && join_tab[0].ref.items[0]->name == in_left_expr_name && @@ -4199,7 +4205,10 @@ int rewrite_to_index_subquery_engine(JOI unit->item, join->conds, join->having, - 1))); + 1, + join-> + select_lex-> + nest_level))); } } === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2011-07-11 21:00:44 +0000 +++ b/sql/sql_base.cc 2011-07-19 09:15:34 +0000 @@ -6454,11 +6454,6 @@ find_field_in_tables(THD *thd, Item_iden { mark_select_range_as_dependent(thd, last_select, current_sel, found, *ref, item); - if (item->can_be_depended) - { - DBUG_ASSERT((*ref) == (Item*)item); - current_sel->register_dependency_item(last_select, ref); - } } } return found; === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2011-07-17 07:52:07 +0000 +++ b/sql/sql_class.h 2011-07-19 09:15:34 +0000 @@ -74,6 +74,22 @@ public: /** + Item iterator over List_iterator_fast for Items +*/ + +class Item_iterator_list: public Item_iterator +{ + List_iterator<Item> list; +public: + Item_iterator_list(List_iterator<Item> &arg_list): + list(arg_list) {} + void open() { list.rewind(); } + Item *next() { return (list++); } + void close() {} +}; + + +/** Item iterator over Item interface for rows */ === modified file 'sql/sql_expression_cache.cc' --- a/sql/sql_expression_cache.cc 2011-06-27 16:07:24 +0000 +++ b/sql/sql_expression_cache.cc 2011-07-19 09:15:34 +0000 @@ -23,9 +23,9 @@ ulong subquery_cache_miss, subquery_cache_hit; Expression_cache_tmptable::Expression_cache_tmptable(THD *thd, - List<Item*> &dependants, - Item *value) - :cache_table(NULL), table_thd(thd), list(&dependants), val(value), + List<Item> &dependants, + Item *value) + :cache_table(NULL), table_thd(thd), items(dependants), val(value), inited (0) { DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable"); @@ -60,50 +60,32 @@ static uint field_enumerator(uchar *arg) void Expression_cache_tmptable::init() { - List_iterator<Item*> li(*list); - Item_iterator_ref_list it(li); - Item **item; + List_iterator<Item> li(items); + Item_iterator_list it(li); uint field_counter; DBUG_ENTER("Expression_cache_tmptable::init"); DBUG_ASSERT(!inited); inited= TRUE; cache_table= NULL; - while ((item= li++)) - { - DBUG_ASSERT(item); - if (*item) - { - DBUG_ASSERT((*item)->fixed); - items.push_back((*item)); - } - else - { - /* - This is possible when optimizer already executed this subquery and - optimized out the condition predicate. - */ - li.remove(); - } - } - - if (list->elements == 0) + if (items.elements == 0) { DBUG_PRINT("info", ("All parameters were removed by optimizer.")); DBUG_VOID_RETURN; } + /* add result field */ + items.push_front(val); + cache_table_param.init(); /* dependent items and result */ - cache_table_param.field_count= list->elements + 1; + cache_table_param.field_count= items.elements; /* postpone table creation to index description */ cache_table_param.skip_create_table= 1; - items.push_front(val); - if (!(cache_table= create_tmp_table(table_thd, &cache_table_param, items, (ORDER*) NULL, - FALSE, FALSE, + FALSE, TRUE, ((table_thd->options | TMP_TABLE_ALL_COLUMNS) & ~(OPTION_BIG_TABLES | @@ -122,16 +104,13 @@ void Expression_cache_tmptable::init() goto error; } - /* This list do not contain result field */ - it.open(); - - field_counter=1; + field_counter= 1; if (cache_table->alloc_keys(1) || cache_table->add_tmp_key(0, items.elements - 1, &field_enumerator, (uchar*)&field_counter, TRUE) || ref.tmp_table_index_lookup_init(table_thd, cache_table->key_info, it, - TRUE)) + TRUE, 1 /* skip result field*/)) { DBUG_PRINT("error", ("creating index failed")); goto error; @@ -192,13 +171,6 @@ Expression_cache::result Expression_cach int res; DBUG_ENTER("Expression_cache_tmptable::check_value"); - /* - We defer cache initialization to get item references that are - used at the execution phase. - */ - if (!inited) - init(); - if (cache_table) { DBUG_PRINT("info", ("status: %u has_record %u", @@ -274,16 +246,17 @@ err: void Expression_cache_tmptable::print(String *str, enum_query_type query_type) { - List_iterator<Item*> li(*list); - Item **item; + List_iterator<Item> li(items); + Item *item; bool is_first= TRUE; str->append('<'); + li++; // skip result field while ((item= li++)) { if (!is_first) str->append(','); - (*item)->print(str, query_type); + item->print(str, query_type); is_first= FALSE; } str->append('>'); === modified file 'sql/sql_expression_cache.h' --- a/sql/sql_expression_cache.h 2010-10-27 03:03:59 +0000 +++ b/sql/sql_expression_cache.h 2011-07-19 09:15:34 +0000 @@ -37,6 +37,15 @@ public: Print cache parameters */ virtual void print(String *str, enum_query_type query_type)= 0; + + /** + Is this cache initialized + */ + virtual bool is_inited()= 0; + /** + Initialize this cache + */ + virtual void init()= 0; }; struct st_table_ref; @@ -51,15 +60,16 @@ class Item_field; class Expression_cache_tmptable :public Expression_cache { public: - Expression_cache_tmptable(THD *thd, List<Item*> &dependants, Item *value); + Expression_cache_tmptable(THD *thd, List<Item> &dependants, Item *value); virtual ~Expression_cache_tmptable(); virtual result check_value(Item **value); virtual my_bool put_value(Item *value); void print(String *str, enum_query_type query_type); + bool is_inited() { return inited; }; + void init(); private: - void init(); /* tmp table parameters */ TMP_TABLE_PARAM cache_table_param; @@ -71,10 +81,8 @@ private: struct st_table_ref ref; /* Cached result */ Item_field *cached_result; - /* List of references to the parameters of the expression */ - List<Item*> *list; - /* List of items */ - List<Item> items; + /* List of parameter items */ + List<Item> &items; /* Value Item example */ Item *val; /* Set on if the object has been succesfully initialized with init() */ === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2011-07-19 03:05:33 +0000 +++ b/sql/sql_lex.cc 2011-07-19 09:15:34 +0000 @@ -1879,59 +1879,6 @@ void st_select_lex_unit::exclude_tree() } -/** - Register reference to an item which the subqueries depends on - - @param def_sel select against which the item is resolved - @param dependency reference to the item - - @details - This function puts the reference dependency to an item that is either an - outer field or an aggregate function resolved against an outer select into - the list 'depends_on'. It adds it to the 'depends_on' lists for each - subquery between this one and 'def_sel' - the subquery against which the - item is resolved. -*/ - -void st_select_lex::register_dependency_item(st_select_lex *def_sel, - Item **dependency) -{ - SELECT_LEX *s= this; - DBUG_ENTER("st_select_lex::register_dependency_item"); - DBUG_ASSERT(this != def_sel); - DBUG_ASSERT(*dependency); - //psergey-add-stupid: - while (def_sel->merged_into) - def_sel= def_sel->merged_into; - //:eof - do - { - /* check duplicates */ - List_iterator_fast<Item*> li(s->master_unit()->item->depends_on); - Item **dep; - while ((dep= li++)) - { - if ((*dep)->eq(*dependency, FALSE)) - { - DBUG_PRINT("info", ("dependency %s already present", - ((*dependency)->name ? - (*dependency)->name : - "<no name>"))); - DBUG_VOID_RETURN; - } - } - - s->master_unit()->item->depends_on.push_back(dependency); - DBUG_PRINT("info", ("depends_on: Select: %d added: %s", - s->select_number, - ((*dependency)->name ? - (*dependency)->name : - "<no name>"))); - } while ((s= s->outer_select()) != def_sel); - DBUG_VOID_RETURN; -} - - /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2011-07-17 06:57:43 +0000 +++ b/sql/sql_lex.h 2011-07-19 09:15:34 +0000 @@ -793,7 +793,6 @@ public: inline bool is_subquery_function() { return master_unit()->item != 0; } bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency); - void register_dependency_item(st_select_lex *last, Item **dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-07-18 06:12:31 +0000 +++ b/sql/sql_select.cc 2011-07-19 09:15:34 +0000 @@ -9613,6 +9613,7 @@ bool JOIN_TAB::preread_init() @param thd Thread handle @param tmp_key The temporary table key @param it The iterator of items for lookup in the key + @param skip Number of fields from the beginning to skip @details Build TABLE_REF object for lookup in the key 'tmp_key' using items @@ -9625,9 +9626,11 @@ bool JOIN_TAB::preread_init() bool TABLE_REF::tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, - bool value) + bool value, + uint skip) { uint tmp_key_parts= tmp_key->key_parts; + uint i; DBUG_ENTER("TABLE_REF::tmp_table_index_lookup_init"); key= 0; /* The only temp table index. */ @@ -9648,7 +9651,8 @@ bool TABLE_REF::tmp_table_index_lookup_i uchar *cur_ref_buff= key_buff; it.open(); - for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + for (i= 0; i < skip; i++) it.next(); + for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) { Item *item= it.next(); DBUG_ASSERT(item); === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2011-07-17 07:52:07 +0000 +++ b/sql/sql_select.h 2011-07-19 09:15:34 +0000 @@ -135,7 +135,7 @@ typedef struct st_table_ref bool disable_cache; bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, - bool value); + bool value, uint skip= 0); } TABLE_REF;

1 0