- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 2935: Fixed LP bugs BUG#729067/730466. in file:///home/igor/maria/maria-5.3-bug729067/
by Oleksandr Byelkin 12 Mar '11

12 Mar '11

Hi! Ok to push. 12 марта 2011, в 10:49, Igor Babaev написал(а): > At file:///home/igor/maria/maria-5.3-bug729067/ > > ------------------------------------------------------------ > revno: 2935 > revision-id: igor(a)askmonty.org-20110312084903-b9mmi2wdgbdar3tc > parent: timour(a)askmonty.org-20110308212344-7r4ue17b3rxschlj > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-bug729067 > timestamp: Sat 2011-03-12 00:49:03 -0800 > message: > Fixed LP bugs BUG#729067/730466. > Do not reset the value of the item_equal field in the Item_field object > once it has been set. > === modified file 'mysql-test/r/join.result' > --- a/mysql-test/r/join.result 2011-02-28 21:29:59 +0000 > +++ b/mysql-test/r/join.result 2011-03-12 08:49:03 +0000 > @@ -1285,3 +1285,38 @@ > 1 1 1 1 1 > 1 1 1 1 1 > drop table t1,t2,t3; > +# > +# BUG#729067/730466: unexpected 'Range checked for each record' > +# for queries with OR in WHERE clause > +# > +CREATE TABLE t1 (f1 int, f2 int) ; > +INSERT INTO t1 VALUES (4,0),(5,1); > +CREATE TABLE t2 (f1 int, f2 int, KEY (f2)) ; > +INSERT INTO t2 VALUES (5,7), (8,9); > +EXPLAIN > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where > +1 SIMPLE t2 ALL f2 NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > +WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +f1 f2 f1 f2 > +5 1 5 7 > +DROP TABLE t1,t2; > +CREATE TABLE t1(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t1 VALUES (9,4), (10,9); > +CREATE TABLE t2(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t2 VALUES (9,4), (10,9); > +EXPLAIN > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where > +1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) > +2 SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > +WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +f1 f2 f1 f2 > +9 4 10 9 > +DROP TABLE t1,t2; > > === modified file 'mysql-test/t/join.test' > --- a/mysql-test/t/join.test 2011-02-28 21:29:59 +0000 > +++ b/mysql-test/t/join.test 2011-03-12 08:49:03 +0000 > @@ -964,3 +964,36 @@ > > drop table t1,t2,t3; > > +--echo # > +--echo # BUG#729067/730466: unexpected 'Range checked for each record' > +--echo # for queries with OR in WHERE clause > +--echo # > + > +CREATE TABLE t1 (f1 int, f2 int) ; > +INSERT INTO t1 VALUES (4,0),(5,1); > + > +CREATE TABLE t2 (f1 int, f2 int, KEY (f2)) ; > +INSERT INTO t2 VALUES (5,7), (8,9); > + > +EXPLAIN > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > + WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > +SELECT * FROM t1 STRAIGHT_JOIN t2 ON t2.f1 = t1.f1 > + WHERE t1.f1<>0 OR t1.f2<>0 AND t1.f1 = t2.f2; > + > +DROP TABLE t1,t2; > + > +CREATE TABLE t1(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t1 VALUES (9,4), (10,9); > + > +CREATE TABLE t2(f1 int PRIMARY KEY, f2 int) ; > +INSERT INTO t2 VALUES (9,4), (10,9); > + > +EXPLAIN > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > + WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > +SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t2.f2 = t1.f1 > + WHERE t1.f1 IN (SELECT f1 FROM t1) AND t1.f1 = t2.f1 OR t1.f1 = 9; > + > +DROP TABLE t1,t2; > + > > === modified file 'sql/item_cmpfunc.cc' > --- a/sql/item_cmpfunc.cc 2011-02-06 04:57:03 +0000 > +++ b/sql/item_cmpfunc.cc 2011-03-12 08:49:03 +0000 > @@ -5723,7 +5723,8 @@ > not_null_tables_cache|= tmp_table_map; > if (item->maybe_null) > maybe_null=1; > - item->item_equal= this; > + if (!item->item_equal) > + item->item_equal= this; > } > fix_length_and_dec(); > fixed= 1; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] Rev 2934: Fix LP BUG#719198 in file:///home/tsk/mprog/src/5.3/
by Sergey Petrunya 08 Mar '11

08 Mar '11

Hi Timour, On Tue, Mar 08, 2011 at 04:35:24PM +0200, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 2934 > revision-id: timour(a)askmonty.org-20110308143512-0m7uotvxp9rmegak > parent: psergey(a)askmonty.org-20110304155430-l0ad4iag9gn5zeka > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-03-08 16:35:12 +0200 > message: > Fix LP BUG#719198 > > Analysis: > The assert failed because the execution code for > partial matching is designed with the assumption that > NULLs on the left side are detected as early as possible, > and a NULL result is returned before any lookups are > performed at all. > > However, in the case of an Item_cache object on the left > side, null was not detected properly, because detection > was done via Item::is_null(), which is not implemented at > all for Item_cache, and resolved to the default Item::is_null() > which always returns FALSE. > > Solution: > Use the property Item::null_value instead of is_null(), which > is properly updated for Item_cache objects as well. > > Alternatively one could implement Item_cache:is_null(), but > its not quite clear how that will interact with the cache > update methods, so the current fix seems simpler and cleaner. Ok to push. Since it is guaranteed that cache_value() have been called for the object, it's ok to use null_value instead of is_null(). Btw, out of interest I've implemented Item_cache::is_null() and t/subselect*.test ran successfully, so it seems to be ok to have. I don't insist that it is added as part of this fix, though. > === modified file 'mysql-test/r/subselect_mat.result' > --- a/mysql-test/r/subselect_mat.result 2011-01-14 10:51:30 +0000 > +++ b/mysql-test/r/subselect_mat.result 2011-03-08 14:35:12 +0000 > @@ -1371,3 +1371,41 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELE > pk > 2 > DROP TABLE t1, t2; > +# > +# LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' > +# failed with subquery on both sides of NOT IN and materialization > +# > +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, f3b int); > +set session optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 > +2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > +f2 > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 > +2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); > +f2 > +insert into t3 values (1,1),(2,2); > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1); > +id select_type table type possible_keys key key_len ref rows Extra > +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 > +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1); > +f2 > +3 > +4 > +drop table t1, t2, t3; > > === modified file 'mysql-test/t/subselect_mat.test' > --- a/mysql-test/t/subselect_mat.test 2011-01-14 10:51:30 +0000 > +++ b/mysql-test/t/subselect_mat.test 2011-03-08 14:35:12 +0000 > @@ -1011,3 +1011,31 @@ SELECT pk FROM t1 WHERE (a) IN (SELECT a > SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); > DROP TABLE t1, t2; > > +--echo # > +--echo # LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): Assertion `!compare_pred[i]->null_value' > +--echo # failed with subquery on both sides of NOT IN and materialization > +--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, f3b int); > + > +set session optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; > + > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > + > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); > + > +insert into t3 values (1,1),(2,2); > + > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1); > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1); > + > +drop table t1, t2, t3; > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2011-03-03 21:48:31 +0000 > +++ b/sql/item_subselect.cc 2011-03-08 14:35:12 +0000 > @@ -5069,7 +5069,7 @@ bool subselect_rowid_merge_engine::parti > for (uint i= test(non_null_key); i < keys_count; i++) > { > DBUG_ASSERT(merge_keys[i]->get_column_count() == 1); > - if (merge_keys[i]->get_search_key(0)->is_null()) > + if (merge_keys[i]->get_search_key(0)->null_value) > { > ++count_nulls_in_search_key; > bitmap_set_bit(&matching_outer_cols, merge_keys[i]->get_keyid()); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MWL#90 combined patch for review
by Sergey Petrunya 08 Mar '11

08 Mar '11

Hi Monty, Please find attached the complete patch of MWL#90 for review. The tree with the code is on launchpad/buildbot: https://code.launchpad.net/~maria-captains/maria/5.3-subqueries-mwl90 http://buildbot.askmonty.org/buildbot/grid?branch=5.3-subqueries-mwl90 and it has no failures other than those also found in mainline 5.3. The merge from 5.3-main was about a week ago. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 5

[Maria-developers] Report from OpenSourceDays2011
by Kristian Nielsen 08 Mar '11

08 Mar '11

Hi, On Saturday, March 5, I gave a talk at the OpenSourceDays 2011 conference in Copenhagen, Denmark: http://opensourcedays.org/ My talk was titled "Latest news from the MariaDB (and MySQL) community". I put the slides from the talk up on my web server here: http://knielsen-hq.org/maria/osd2011.pdf This year the OpenSourceDays conference was reduced somewhat from what it usually is, only one day and I heard something like 400 tickes available (which were sold out rather quickly). Still, I had a good bunch of people at the talk, maybe 30 or so. There were many questions, people seemed interested, so I think it went well. I also talked to some people outside of the talk, again there seems to be a lot of interest in what is happening around MySQL generally and in MariaDB specifically. I think generally people are pleasantly surprised to learn the depth and dedication of the MariaDB effort, that we really are a major effort and a viable alternative. There was one good question at the talk about if there were reference customer statements or so to show that companies are deploying MariaDB with success at large scale; I guess this will be useful for people that want to sell the idea of using MariaDB at their job or institution or whatever. I did not have a very good answer to this, except that I knew there were some. I guess we already have this on one of the web sites, or if not it would be worth it to collect the different press releases and such as we get them, I think. The night before the conference there was a speaker's dinner, and I had a great talk with Magnus from Postgresql. I got the impression that things are also really moving in the Postgres space, and it seems to be picking up a lot of interest and development effort. I think that is great, I am starting to be hopeful that we are now seeing the open source databases really take off as a community effort, as opposed to just a project of whatever "acquirer of the year". That's it :-) - Kristian.

2 1

[Maria-developers] Pbxt loses rows
by Michael Widenius 08 Mar '11

08 Mar '11

Hi! This is a bug that has been around a long time but not yet fixed (at least not in 1.0.11 which is in the MariaDB 5.1 tree). Once in a while when running the pbxt.insert test, pbxt fails to find all rows in the table: pbxt.insert [ fail ] Test ended at 2011-02-25 16:44:03 CURRENT_TEST: pbxt.insert --- /home/my/maria-5.1-merge/mysql-test/suite/pbxt/r/insert.result 2011-02-10 22:57:21.000000000 +0200 +++ /home/my/maria-5.1-merge/mysql-test/suite/pbxt/r/insert.reject 2011-02-25 16:44:03.030856755 +0200 @@ -298,7 +298,7 @@ insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3; select count(*) from t2; count(*) -25500 +25495 drop table t1,t2,t3; create table t1 (a int, b int); insert into t1 (a,b) values (a,b); Rerunning the test usually works It would be really good to get this fixes ASAP as it's not good if a storage engines looses rows randomly... The above happend in my latest 5.1 tree on Linux. Regards, Monty

2 2

[Maria-developers] adutko-centos-amd64
by Adam M. Dutko 07 Mar '11

07 Mar '11

The machine running the buildbot adutko-centos-amd64 experienced a hardware failure last night around 1AM EST (UTC 0600). I believe it is the motherboard and due to funding constraints I am not certain when it will be brought back online. I will make an attempt to migrate the buildbot when I have time over the next few days. Sorry for the interruption. If someone has a spare board that supports AMD Phenom X3/X4 processors please write me off list. Thanks.

1 0

Re: [Maria-developers] RedHat Buildslaves
by Kristian Nielsen 07 Mar '11

07 Mar '11

Daniel Bartholomew <dbart(a)montyprogram.com> writes: > Daniel> I'm creating the build-slaves based on the KVM setup > Daniel> instructions you have here: > Daniel> > Daniel> http://askmonty.org/wiki/BuildBot:vm-setup#Centos_5_i386 > I now have a rhel6-amd64 vm setup on terrier with base, serial, build, > and install vms. The steps I went through to set it up are here: > > http://kb.askmonty.org/v/buildbot-setup-for-red-hat > I'm not sure what the next step should be. Probably copying over the > correct OurDelta file. Can you copy one out After that, do you want to > take a look at the VMs and see if they're ok for building and install > testing? I did this, and set up new builders in Buildbot. They fail, but the failure looks related to the .spec file and build scripts, rather than the VMs. So I would say the VMs are now ok, and the next step is to find someone to modify the ourdelta build scripts and/or spec files to work with RHEL. > Once this VM is certifed as good to use as a buildbot buildslave I'll > move on to rhel4 and rhel5. As I said, the VMs look reasonable. So if you want, you can start doing the rhel4/5 ones the same way, or if you prefer, you can wait until the .spec files are done for rhel6, in case that work turns up something else that is needed. > One thing I just realized is that > the /kvm/MySQL-OurDelta-shared-5.0.87.d10-65.el5.i386.rpm file that I > placed at /srv/shared/yum/CentOS/5/i386/RPMS/ is not the right > architecture for an amd64 system, and it's probably not at the right > path, either (what with CentOS being in the path). Yeah. I grabbed the amd64 one and copied it in there with my best guess for the path (and also put it on terrier:/kvm/). The thing is, it probably needs more than this, someone who understands (or takes the time to learn) .rpm will have to develop the right solution. The full story is like this: The Redhat rpms (original, not ours) put the `mysql` binary in the same package as libmysqlclient. Then it has other packages depend on this package to get libmysqlclient.so.15 (old version of client library). So when we replace the package with a new version of the mysql binary (from MariaDB), we also have to provide a copy of the _old_ client library (as well as a copy of the new one of course, libmysqlclient.so.16). (In contrast, Debian does this correctly, placing the client library in package libmysqlclient15, which can co-exist with new MySQL/MariaDB versions). The question is how to get this old library? The MySQL build team told me they grab it from an old binary package from MySQL 4.1 (or whatever), so the OurDelta stuff does something similar. Admittedly not a nice solution. Maybe it is not needed for RHEL 6, if it does not have dependencies on libmysqlclient15. Or maybe we need to find an old RHEL MySQL .rpm to use instead. I do not know rpm well enough to have a meaningful opinion. > I built this directly on terrier, since my local desktop can only run > with "-cpu qemu32". I tried to build an x86 version on terrier, but I > kept getting stuck in a VM reboot loop. So I'll have to upload the Yeah, there is something wierd/different with the qemu-kvm I built on terrier to fix another problem. But it works with `-cpu qemu64` (also for a 32-bit VM). Doing it on your local machine should be fine as well. Thanks, - Kristian.

1 0

[Maria-developers] Igor please review: Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/]
by Sergey Petrunya 05 Mar '11

05 Mar '11

Hello Igor, Could you please review the below? The bug page https://bugs.launchpad.net/maria/+bug/727667 has verbose explanations of how we ended up having this bug, as well as justifications for fixing it in this particular way. ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sat, 5 Mar 2011 12:56:26 +0300 (MSK) Subject: [Commits] Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/ At file:///home/psergey/dev2/5.3/ ------------------------------------------------------------ revno: 2933 revision-id: psergey(a)askmonty.org-20110305095622-wzlnwr9srfbsu3pv parent: psergey(a)askmonty.org-20110304091446-qmsezak3c0cfm9b1 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3 timestamp: Sat 2011-03-05 12:56:22 +0300 message: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 - put the code that sets HA_NULL_PART bit back - Fix test_if_ref/part_of_refkey() so that = NULL-ability of lookup columns does not prevent the equality from being removed (we now have early/late NULLs filtering which will filter out NULL values) = equality is not removed if it is ref_or_null access, and the value of the lookup column can alternate between the lookup value and NULL. === modified file 'mysql-test/r/null.result' --- a/mysql-test/r/null.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/null.result 2011-03-05 09:56:22 +0000 @@ -170,7 +170,7 @@ insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null i i 5 const 9 Using index +1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index select count(*) from t1 where i=2 or i is null; count(*) 10 === modified file 'mysql-test/r/null_key.result' --- a/mysql-test/r/null_key.result 2010-10-06 20:27:12 +0000 +++ b/mysql-test/r/null_key.result 2011-03-05 09:56:22 +0000 @@ -21,10 +21,10 @@ 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -226,7 +226,7 @@ explain select * from t2,t1 where t1.a=t2.a or t1.a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 @@ -447,3 +447,15 @@ 3 12 0 12 drop table t1, t2; End of 5.0 tests +# +# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +# +CREATE TABLE t1 ( +f3 int(11), +f10 varchar(1), +KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +f3 f10 +DROP TABLE t1; === modified file 'mysql-test/r/order_by.result' --- a/mysql-test/r/order_by.result 2011-01-27 04:45:23 +0000 +++ b/mysql-test/r/order_by.result 2011-03-05 09:56:22 +0000 @@ -643,7 +643,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -652,7 +652,7 @@ 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect.result 2011-03-05 09:56:22 +0000 @@ -906,7 +906,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2011-03-05 09:56:22 +0000 @@ -910,7 +910,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2011-03-05 09:56:22 +0000 @@ -907,7 +907,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2011-02-22 09:15:47 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2011-03-05 09:56:22 +0000 @@ -907,7 +907,7 @@ explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` === modified file 'mysql-test/t/null_key.test' --- a/mysql-test/t/null_key.test 2008-03-03 17:35:44 +0000 +++ b/mysql-test/t/null_key.test 2011-03-05 09:56:22 +0000 @@ -263,3 +263,16 @@ drop table t1, t2; -- echo End of 5.0 tests +--echo # +--echo # BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 +--echo # + +CREATE TABLE t1 ( + f3 int(11), + f10 varchar(1), + KEY (f3) +); +INSERT INTO t1 VALUES ('9','k'),(NULL,'r'); +SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL); +DROP TABLE t1; + === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-02-28 21:29:59 +0000 +++ b/sql/sql_select.cc 2011-03-05 09:56:22 +0000 @@ -6564,10 +6564,12 @@ j->ref.has_record= FALSE; j->ref.null_rejecting= 0; j->ref.disable_cache= FALSE; + j->ref.null_ref_part= (uint)-1; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; uchar *key_buff=j->ref.key_buff, *null_ref_key= 0; + uint null_ref_part= (uint)-1; bool keyuse_uses_no_tables= TRUE; if (ftkey) { @@ -6621,7 +6623,10 @@ instead of JT_REF_OR_NULL in case if field can't be null */ if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null) + { null_ref_key= key_buff; + null_ref_part= i; + } key_buff+= keyinfo->key_part[i].store_length; } } /* not ftkey */ @@ -6636,6 +6641,7 @@ /* Must read with repeat */ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; j->ref.null_ref_key= null_ref_key; + j->ref.null_ref_part= null_ref_part; } else if (keyuse_uses_no_tables) { @@ -15287,8 +15293,12 @@ *****************************************************************************/ /** + Check if "left_item=right_item" equality is guaranteed to be true by use of + [eq]ref access on left_item->field->table. + @return - 1 if right_item is used removable reference key on left_item + TRUE if right_item is used removable reference key on left_item + FALSE otherwise */ bool test_if_ref(Item *root_cond, Item_field *left_item,Item *right_item) @@ -15301,7 +15311,11 @@ (!join_tab->first_inner || *join_tab->first_inner->on_expr_ref == root_cond)) { - // Cond guards + /* + If ref access uses "Full scan on NULL key" (i.e. it actually alternates + between ref access and full table scan), then no equality can be + guaranteed to be true. + */ for (uint i = 0; i < join_tab->ref.key_parts; i++) { if (join_tab->ref.cond_guards[i]) @@ -15309,7 +15323,7 @@ return FALSE; } } - // + Item *ref_item=part_of_refkey(field->table,field); if (ref_item && ref_item->eq(right_item,1)) { @@ -15611,6 +15625,29 @@ } +/* + @brief + + Check if + - @table uses "ref"-like access + - it is based on "@field=certain_item" equality + - the equality will be true for any record returned by the access method + and return the certain_item if yes. + + @detail + + Equality won't necessarily hold if: + - the used index covers only part of the @field. + Suppose, we have a CHAR(5) field and INDEX(field(3)). if you make a lookup + for 'abc', you will get both record with 'abc' and with 'abcde'. + - The type of access is actually ref_or_null, and so @field can be either + a value or NULL. + + @return + Item that the field will be equal to + NULL if no such item +*/ + static Item * part_of_refkey(TABLE *table,Field *field) { @@ -15619,17 +15656,30 @@ return (Item*) 0; // field from outer non-select (UPDATE,...) uint ref_parts= join_tab->ref.key_parts; - if (ref_parts) + if (ref_parts) /* if it's ref/eq_ref/ref_or_null */ { - uint key= join_tab->ref.key; KEY *key_info= join_tab->get_keyinfo_by_key_no(key); KEY_PART_INFO *key_part= key_info->key_part; for (uint part=0 ; part < ref_parts ; part++,key_part++) - if (field->eq(key_part->field) && - !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART))) - return join_tab->ref.items[part]; + { + if (field->eq(key_part->field)) + { + /* + Found the field in the key. Check that + 1. ref_or_null doesn't alternate this component between a value and + a NULL + 2. index fully covers the key + */ + if (part != join_tab->ref.null_ref_part && // (1) + !(key_part->key_part_flag & HA_PART_KEY_SEG)) // (2) + { + return join_tab->ref.items[part]; + } + break; + } + } } return (Item*) 0; } === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2011-02-24 06:23:12 +0000 +++ b/sql/sql_select.h 2011-03-05 09:56:22 +0000 @@ -109,8 +109,15 @@ */ key_part_map null_rejecting; table_map depend_map; ///< Table depends on these tables. + /* null byte position in the key_buf. Used for REF_OR_NULL optimization */ uchar *null_ref_key; + /* + ref_or_null optimization: number of key part can be NULL (there's only one) + uint(-1) if no part can be NULL. + */ + uint null_ref_part; + /* The number of times the record associated with this key was used in the join. === modified file 'sql/table.cc' --- a/sql/table.cc 2011-02-22 09:15:47 +0000 +++ b/sql/table.cc 2011-03-05 09:56:22 +0000 @@ -1581,6 +1581,8 @@ #endif key_part->key_part_flag|= HA_PART_KEY_SEG; } + if (field->real_maybe_null()) + key_part->key_part_flag|= HA_NULL_PART; /* Sometimes we can compare key parts for equality with memcmp. But not always. _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB 5.2.5
by Daniel Bartholomew 03 Mar '11

03 Mar '11

Here are the download, release notes, and changelog pages for 5.2.5: * http://downloads.askmonty.org/mariadb/5.2.5 * http://kb.askmonty.org/v/mariadb-525-release-notes * http://kb.askmonty.org/v/mariadb-525-changelog If any of you have a moment, please check over the above pages to see if there are any mistakes or omissions and let me know (or jump in and fix if you have editing rights). Community team: I tested a sample of the mirrors and they all had the files so please announce the release on the announce list, blogs, and so on. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 2

Re: [Maria-developers] [Commits] Rev 2929: Fix LP BUG#718763 in file:///home/tsk/mprog/src/5.3/
by Oleksandr Byelkin 03 Mar '11

03 Mar '11

Hi! Everything looks OK, except one small style error. Also it would be nice to put somewhere 'TODO:' to bring in order 'uncachable' flags. On 03.03.2011 14:07, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 2929 > revision-id: timour(a)askmonty.org-20110303120726-la5o40rldim2t93y > parent: psergey(a)askmonty.org-20110301072222-1rmapkelx9l1kw8u > committer: timour(a)askmonty.org > branch nick: 5.3 > timestamp: Thu 2011-03-03 14:07:26 +0200 > message: > Fix LP BUG#718763 > > Analysis: > The reason for the crash was that the inner subquery was executed > via a scan on a final temporary table applied after all other > operations. This final operation is implemented by changing the > contents of the JOIN object of the subquery to represent a table > scan over the temp table. At the same time query optimization of > the outer subquery required evaluation of the inner subquery, which > happened before the actual EXPLAIN. The evaluation left the JOIN > object of the inner subquery in the changed state, where it represented > a table scan over a temp table, and EXPLAIN crashed because the temp > table is not associated with any table reference (TABLE_LIST object). > The reason the JOIN was not restored was because its saving/restoration > was controlled by the join->select_lex->uncacheable flag, which was > not set in the case of materialization. > > Solution: > In the methods Item_in_subselect::[single | row]_value_transformer() set: > select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; > In addition, for symmetry, change: > master_unit->uncacheable|= UNCACHEABLE_EXPLAIN; > instead of UNCACHEABLE_DEPENDENT because if a subquery was not > dependent initially, the changed methods do not change this > fact. The subquery may later become correlated if it is transformed > to an EXISTS query, but it may stay uncorrelated if executed via > materialization. [skip] > > > @@ -1742,7 +1752,15 @@ Item_in_subselect::row_value_transformer > optimizer->keep_top_level_cache(); > > thd->lex->current_select= current; > - master_unit->uncacheable|= UNCACHEABLE_DEPENDENT; > + /* > + The uncacheable property controls a number of actions, e.g. whether to > + save/restore (via init_save_join_tab/restore_tmp) the original JOIN for > + plans with a temp table where the original JOIN was overriden by > + make_simple_join. The UNCACHEABLE_EXPLAIN is ignored by EXPLAIN, thus > + non-correlated subqueries will not appear as such to EXPLAIN. > + */ > + master_unit->uncacheable|=UNCACHEABLE_EXPLAIN; > + select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; > > if (!abort_on_null&& left_expr->maybe_null&& !pushed_cond_guards) > { put space (' ') after '=' [skip] > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0