- developers - lists.mariadb.org

Re: [Maria-developers] Proposal: change SUBQUERY to MATERIALIZE
by Sergei Petrunia 01 Dec '11

01 Dec '11

1 0

Re: [Maria-developers] Proposal: change SUBQUERY to MATERIALIZE
by Sergei Petrunia 01 Dec '11

01 Dec '11

1 0

[Maria-developers] MYSQL_THDVAR and SET GLOBAL
by MARK CALLAGHAN 30 Nov '11

30 Nov '11

Is there an easy way to prevent SET GLOBAL ... from being done for plugin variables declared via MYSQL_THDVAR_...? For the following variable I can do SET GLOBAL innodb_fake_changes=1 and that sets a global variable. static MYSQL_THDVAR_BOOL(fake_changes, PLUGIN_VAR_OPCMDARG, "In the transaction after enabled, UPDATE, INSERT and DELETE only move the cursor to the records " "and do nothing other operations (no changes, no ibuf, no undo, no transaction log) in the transaction. " "This is to cause replication prefetch IO. ATTENTION: the transaction started after enabled is affected.", check_fake_changes, NULL, FALSE); -- Mark Callaghan mdcallag(a)gmail.com

3 2

[Maria-developers] MariaDB 5.5 binary tarball packages and libaio
by Kristian Nielsen 30 Nov '11

30 Nov '11

Hi Wlad, I got some progress with Buildbot and producing binary tarball packages for MariaDB 5.5, using your suggestions. But I encountered a problem. I used -DBUILD_CONFIG=mysql-release and installed libaio-dev. But then the server fails like this: ./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory Of course - as libaio.so must be installed by users. I do not think it is appropriate that users need to install libaio.so to use the binary tarballs (as those are supposed to work anywhere - on the other hand, for .deb for example we _should_ use libaio of course, and dependencies will take care that it is installed). So what to do? For now I've tried to build without libaio for the generic tarballs (-DIGNORE_AIO_CHECK=1). So binaries will work; to get aio, users can build themselves or install real packages. Or do you have another idea? Do you know what MySQL does? One option could be statically linking libaio. Now in general, static linking is a can of worms with many pitfalls, however for just libaio it might work (libaio.so is statically linked on my Debian Wheezy). However, I am not sure if this is safe in general, nor do I know how to make cmake link just libaio statically? Any ideas? - Kristian.

4 4

Re: [Maria-developers] [Commits] Rev 3322: Fixed bug lp:825051 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 30 Nov '11

30 Nov '11

29.11.2011 23:06, timour(a)askmonty.org пишет: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3322 > revision-id: timour(a)askmonty.org-20111129210639-bjcn2pfocub6uwba > parent: holyfoot(a)askmonty.org-20111129112752-6cy8wy7h5q58l6je > fixes bug(s): https://launchpad.net/bugs/825051 > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-11-29 23:06:39 +0200 > message: > Fixed bug lp:825051 > > The cause of the wrong result was that Item_ref_null_helper::get_date() > didn't use a method of the *_result() family, and fetched the data > for the field from the current row instead of result_field. Changed to > use the correct *_result() method, like to all other similar methods > of Item_ref_null_helper. OK to push. Do not forget that we agreed to fix this typo in 5.2 at least.

1 0

[Maria-developers] Alternate fix for BUG#833777 and BUG#894397
by Sergei Petrunia 28 Nov '11

28 Nov '11

Hi Timour, Please find it below. Any objections? === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2011-11-25 19:54:36 +0000 +++ sql/sql_select.cc 2011-11-28 11:40:42 +0000 @@ -14776,14 +14776,10 @@ do_select(JOIN *join,List<Item> *fields, /* HAVING will be checked after processing aggregate functions, But WHERE should checkd here (we alredy have read tables). - If there is join->exec_const_cond, and all tables are constant, then it - is equivalent to join->conds. exec_const_cond is already checked in the - beginning of JOIN::exec. If it is false, JOIN::exec returns zero - result already there, therefore execution reaches this point only if - exec_const_cond is TRUE. Since it is equvalent to join->conds, then - join->conds is also TRUE. + + (TODO new comment here) */ - if (!join->conds || join->exec_const_cond || join->conds->val_int()) + if (!join->outer_ref_cond || join->outer_ref_cond->val_int()) { error= (*end_select)(join, 0, 0); if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3308: Fixed LP BUG#747278 in file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/
by Sergei Petrunia 28 Nov '11

28 Nov '11

Hi Sanja, Ok to push. On Wed, Nov 23, 2011 at 01:46:22AM +0200, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/ > > ------------------------------------------------------------ > revno: 3308 > revision-id: sanja(a)askmonty.org-20111122234621-b1t47i7xgzsg1hwo > parent: holyfoot(a)askmonty.org-20111122135733-vgsg077x9rbn08r6 > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-lpb747278 > timestamp: Wed 2011-11-23 01:46:21 +0200 > message: > Fixed LP BUG#747278 > > The problem was that when we have single row subquery with no rows > Item_cache(es) which represent result row was not null and being > requested via element_index() returned random value. > > The fix is setting all Item_cache(es) in NULL before executing the > query (reset() method) which guaranty NULL value of whole query > or its elements requested in any way if no rows was found. > > set_null() method was added to Item_cache to guaranty correct NULL > value in case of reseting the cache. > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect.result 2011-11-22 23:46:21 +0000 > @@ -5681,4 +5681,85 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2011-11-22 23:46:21 +0000 > @@ -5680,6 +5680,87 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2011-11-21 20:01:47 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2011-11-22 23:46:21 +0000 > @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2011-11-22 23:46:21 +0000 > @@ -5685,6 +5685,87 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-22 10:06:46 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-22 23:46:21 +0000 > @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr > a > 1 > drop table t1; > +# > +# LP BUG#747278 incorrect values of the NULL (no rows) single > +# row subquery requested via element_index() interface > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +set @@session.optimizer_switch=@old_optimizer_switch; > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) > +NULL > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +NULL > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) > +NULL > +select (null, null) = (null, null); > +(null, null) = (null, null) > +NULL > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0) > +NULL > +drop tables t1,t2,t3; > +# return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2011-11-21 20:16:01 +0000 > +++ b/mysql-test/t/subselect.test 2011-11-22 23:46:21 +0000 > @@ -4778,4 +4778,47 @@ set @@optimizer_switch='in_to_exists=on, > select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); > drop table t1; > > +--echo # > +--echo # LP BUG#747278 incorrect values of the NULL (no rows) single > +--echo # row subquery requested via element_index() interface > +--echo # > + > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int default 1, f3b int default 2); > +INSERT INTO t3 VALUES (1,1),(2,2); > + > +# check different IN with switches where the bug was found > +set @old_optimizer_switch = @@session.optimizer_switch; > +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; > + > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > + > +set @@session.optimizer_switch=@old_optimizer_switch; > + > +# check different IN with default switches > +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); > +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; > +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; > +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); > + > +# other row operation with NULL single row subquery also should work > +select (null, null) = (null, null); > +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); > + > +drop tables t1,t2,t3; > +--echo # return optimizer switch changed in the beginning of this test > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/item.cc' > --- a/sql/item.cc 2011-11-02 08:05:07 +0000 > +++ b/sql/item.cc 2011-11-22 23:46:21 +0000 > @@ -8201,6 +8201,20 @@ void Item_cache::print(String *str, enum > str->append(')'); > } > > +/** > + Assign to this cache NULL value if it is possible > +*/ > + > +void Item_cache::set_null() > +{ > + if (maybe_null) > + { > + null_value= TRUE; > + value_cached= TRUE; > + } > +} > + > + > bool Item_cache_int::cache_value() > { > if (!example) > @@ -8671,6 +8685,20 @@ void Item_cache_row::bring_value() > } > > > +/** > + Assign to this cache NULL value if it is possible > +*/ > + > +void Item_cache_row::set_null() > +{ > + Item_cache::set_null(); > + if (!values) > + return; > + for (uint i= 0; i < item_count; i++) > + values[i]->set_null(); > +}; > + > + > Item_type_holder::Item_type_holder(THD *thd, Item *item) > :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item)) > { > > === modified file 'sql/item.h' > --- a/sql/item.h 2011-11-14 17:24:36 +0000 > +++ b/sql/item.h 2011-11-22 23:46:21 +0000 > @@ -3651,6 +3651,7 @@ public: > return false; > return example->is_expensive_processor(arg); > } > + virtual void set_null(); > }; > > > @@ -3825,6 +3826,7 @@ public: > DBUG_VOID_RETURN; > } > bool cache_value(); > + virtual void set_null(); > }; > > > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2011-11-21 14:56:32 +0000 > +++ b/sql/item_subselect.cc 2011-11-22 23:46:21 +0000 > @@ -893,7 +893,10 @@ void Item_singlerow_subselect::reset() > { > Item_subselect::reset(); > if (value) > - value->null_value= TRUE; > + { > + for(uint i= 0; i < engine->cols(); i++) > + row[i]->set_null(); > + } > } > > > @@ -1004,6 +1007,11 @@ void Item_singlerow_subselect::fix_lengt > */ > if (engine->no_tables()) > maybe_null= engine->may_be_null(); > + else > + { > + for (uint i= 0; i < max_columns; i++) > + row[i]->maybe_null= TRUE; > + } > } > > > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#244 New (by Knielsen): Reduced table/row locking for SELECT statement with read-only stored function
by worklog-noreply＠askmonty.org 25 Nov '11

25 Nov '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Reduced table/row locking for SELECT statement with read-only stored function CREATION DATE..: Thu, 24 Nov 2011, 10:55 SUPERVISOR.....: Monty IMPLEMENTOR....: Monty COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 244 (http://askmonty.org/worklog/?tid=244) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: When statement (or mixed) binlogging is used, the server currently is overly conservative in its use of table/row locks for SELECT statements that call stored functions. For example: delimiter // CREATE FUNCTION `test_f`(x CHAR(8)) RETURNS INT READS SQL DATA BEGIN DECLARE output INT DEFAULT NULL; SELECT id FROM t1 WHERE a = x INTO output; RETURN output; END // delimiter ; SELECT test_f("foo"); In general, it is possible for a SELECT statement that calls a stored function to modify tables, and thus to need to be binlogged. In statement-based binlogging, this requires taking extra read locks to ensure that replication will apply events from concurrent queries on the master in the correct order, just like normal UPDATE statements. However, most uses of stored functions in SELECT do _not_ modify data, as in the above example. Thus there is no need for extra read locks in statement-based binlog mode, as nothing will be logged to the binlog anyway for the SELECT statement. However, the server currently takes the pessimistic safe route and _always_ takes extra read locks in statement-based binlogging when stored functions are used. There is no analysis to detect that stored functions used are read-only, and hence that no extra locking is needed. This worklog is about improving this so that in common cases where no table modification is done in stored functions, no extra locking is done, while still preserving correct operation for statement-based replication when modification _is_ done. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

2 1

Re: [Maria-developers] Fwd: [Commits] Rev 3296: Fix bug lp:833777 in file:///home/tsk/mprog/src/5.3-lpb833777/
by Sergei Petrunia 21 Nov '11

21 Nov '11

Hi Timour, On Fri, Nov 18, 2011 at 11:16:27PM +0200, Timour Katchaounov wrote: > Sergey, > > Please review the following patch. > Do you have any idea about the change in > func_compress.result ? I've seen that before, > but don't remember what was the issue. I think in this case it is fairly clear - a warning is produced every time a constant expression is evaluated. Your patch makes one less evaluation, so one fewer warning is emitted. The patch is ok to push after the below notes are addressed. > ------------------------------------------------------------ > revno: 3296 > revision-id: timour(a)askmonty.org-20111118201920-fac3c9870u7eh898 > parent: igor(a)askmonty.org-20111118173551-78xd3uuu5h25756u > fixes bug(s): https://launchpad.net/bugs/833777 > committer: timour(a)askmonty.org > branch nick: 5.3-lpb833777 > timestamp: Fri 2011-11-18 22:19:20 +0200 > message: > Fix bug lp:833777 > > Analysis: > The optimizer distinguishes two kinds of 'constant' conditions: > expensive ones, and non-expensive ones. The non-expensive conditions > are evaluated inside make_join_select(), and if false, already the > optimizer detects empty query results. > > In order to avoid arbitrarily expensive optimization, the evaluation of > expensive constant conditions is delayed until execution. These conditions > are attached to JOIN::exec_const_cond and evaluated in the beginning of > JOIN::exec. The relevant execution logic is: > > JOIN::exec() > { > if (! join->exec_const_cond->val_int()) > { > produce an empty result; > stop execution > } > continue execution > execute the original WHERE clause (that contains exec_const_cond) > ... > } > > As a result, when an expensive constant condition is > TRUE, it is evaluated twice - once through > JOIN::exec_const_cond, and once through JOIN::cond. > When the expensive constant condition is a subquery, > predicate, the subquery is evaluated twice. If we have > many levels of subqueries, this logic results in a chain > of recursive subquery executions that walk a perfect > binary tree. The result is that for subquries with depth N, > JOIN::exec is executed O(2^N) times. > > Solution: > Notice that the second execution of the constant conditions > happens inside do_select(), in the branch: > if (join->table_count == join->const_tables) { ... } > In this case exec_const_cond is equivalent to the whole WHERE > clause, therefore the WHERE clause has already been checked in > the beginnig of JOIN::exec, and has been found to be true. > The bug is addressed by not evaluating the WHERE clause if there > was exec_const_conds, and it was TRUE. > === modified file 'mysql-test/r/func_compress.result' > --- a/mysql-test/r/func_compress.result 2011-02-03 15:00:28 +0000 > +++ b/mysql-test/r/func_compress.result 2011-11-18 20:19:20 +0000 > @@ -103,7 +103,6 @@ foo > Warnings: > Error 1259 ZLIB: Input data corrupted > Error 1259 ZLIB: Input data corrupted > -Error 1259 ZLIB: Input data corrupted > explain select *, uncompress(a) from t1; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t1 system NULL NULL NULL NULL 1 > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect.result 2011-11-18 20:19:20 +0000 > @@ -5665,4 +5665,14 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 Why use 'benchmark' function? It seems totally redundant. Please change the test to look like: select (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in ... (select a from t1) ))))))))))))))))))))))))))))))))) AS foo; to make it less cluttered. > +drop table t1; > set optimizer_switch=@subselect_tmp; > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2011-11-18 20:19:20 +0000 > @@ -5670,6 +5670,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%materialization=on%'; > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2011-11-18 20:19:20 +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2011-11-18 20:19:20 +0000 > @@ -5669,6 +5669,16 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set optimizer_switch=default; > select @@optimizer_switch like '%subquery_cache=on%'; > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-18 20:19:20 +0000 > @@ -5666,5 +5666,15 @@ ERROR 21000: Subquery returns more than > SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > +# > +# LP BUG#833777 Performance regression with deeply nested subqueries > +# > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a > +0 > +drop table t1; > set optimizer_switch=@subselect_tmp; > set @optimizer_switch_for_subselect_test=null; > > === modified file 'mysql-test/t/subselect.test' > --- a/mysql-test/t/subselect.test 2011-11-15 21:03:00 +0000 > +++ b/mysql-test/t/subselect.test 2011-11-18 20:19:20 +0000 > @@ -4768,4 +4768,14 @@ SET SESSION sql_mode=@old_sql_mode; > DEALLOCATE PREPARE stmt; > DROP TABLE t1; > > +--echo # > +--echo # LP BUG#833777 Performance regression with deeply nested subqueries > +--echo # > + > +create table t1 (a int not null, b char(10) not null); > +insert into t1 values (1, 'a'); > +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; > +select benchmark(1, (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))))))))); > +drop table t1; > + > set optimizer_switch=@subselect_tmp; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2011-11-15 21:03:00 +0000 > +++ b/sql/sql_select.cc 2011-11-18 20:19:20 +0000 > @@ -14782,9 +14782,15 @@ do_select(JOIN *join,List<Item> *fields, > { > /* > HAVING will be checked after processing aggregate functions, > - But WHERE should checkd here (we alredy have read tables) > + But WHERE should checkd here (we alredy have read tables). > + If there is join->exec_const_cond, and all tables are constant, then it > + is equivalent to join->conds. exec_const_cond is already checked in the > + beginning of JOIN::exec. If it is false, JOIN::exec returns zero > + result already there, therefore execution reaches this point only if > + exec_const_cond is TRUE. Since it is equvalent to join->conds, then > + join->conds is also TRUE. > */ > - if (!join->conds || join->conds->val_int()) > + if (!join->conds || join->exec_const_cond || join->conds->val_int()) > { > error= (*end_select)(join, 0, 0); > if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] feedback plugin and 5.1 debs
by Kristian Nielsen 16 Nov '11

16 Nov '11

Hi Serg, If you remember, in Athens, we discovered that the feedback plugin is missing from 5.1 .debs. I fixed and pushed this (in the ourdelta scripts). I restarted latest 5.1 buildbot for this, and it seems to work. So just a FYI ... - Kristian.

3 3