Hello Igor, On Thu, Nov 18, 2010 at 10:04:24PM -0800, Igor Babaev wrote: > Sergey, > > Please review this fix. > Ok to push after the irc feedback (lack of comments about JOIN_CACHE format) is addressed.
> > -------- Original Message -------- > Subject: [Commits] Rev 2860: Fixed LP bug #675922. in > file:///home/igor/maria/maria-5.3-mwl128-bug675922/ > Date: Thu, 18 Nov 2010 22:02:41 -0800 (PST) > From: Igor Babaev <[email protected]> > Reply-To: [email protected] > To: <[email protected]> > > > > At file:///home/igor/maria/maria-5.3-mwl128-bug675922/ > > ------------------------------------------------------------ > revno: 2860 > revision-id: [email protected] > parent: [email protected] > committer: Igor Babaev <[email protected]> > branch nick: maria-5.3-mwl128-bug675922 > timestamp: Thu 2010-11-18 22:02:40 -0800 > message: > Fixed LP bug #675922. > The bug happened when BKA join algorithm used an incremental buffer > and some of the fields over which access keys were constructed > - were allocated in the previous join buffers > - were non-nullable > - belonged to inner tables of outer joins. > For such fields an offset to the field value in the record is saved > in the postfix of the record, and a zero offset indicates that the value > is null. Before the key using the field value is constructed the > value is read into the corresponding field of the record buffer and > the null bit is set for the field if the offset is 0. However if > the field is non-nullable the table->null_row must be set to 1 > for null values and to 0 for non-null values to ensure proper reading > of the value from the record buffer. > > === modified file 'mysql-test/r/join_cache.result' > --- a/mysql-test/r/join_cache.result 2010-11-18 22:13:57 +0000 > +++ b/mysql-test/r/join_cache.result 2010-11-19 06:02:40 +0000 > @@ -6004,4 +6004,56 @@ > SET SESSION optimizer_switch = 'outer_join_with_cache=off'; > SET SESSION join_cache_level = DEFAULT; > DROP TABLE t1,t2,t3; > +# > +# Bug #675922: incremental buffer for BKA with access from previous > +# buffers from non-nullable columns whose values may be null > +# > +CREATE TABLE t1 (a1 varchar(32)) ; > +INSERT INTO t1 VALUES ('s'),('k'); > +CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ; > +INSERT INTO t2 VALUES (7,'s'); > +CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ; > +INSERT INTO t3 VALUES (7,'s'); > +CREATE TABLE t4 (a4 int) ; > +INSERT INTO t4 VALUES (9); > +CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ; > +INSERT INTO t5 VALUES (7,0); > +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; > +SET SESSION join_cache_level = 0; > +EXPLAIN > +SELECT t4.a4, t5.b5 > +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +id select_type table type possible_keys key key_len ref > rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using > where > +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 > Using index > +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using > where > +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 > Using where > +SELECT t4.a4, t5.b5 > +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +a4 b5 > +9 0 > +9 NULL > +SET SESSION join_cache_level = 6; > +EXPLAIN > +SELECT t4.a4, t5.b5 > +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +id select_type table type possible_keys key key_len ref > rows Extra > +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 > +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using > where > +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 > Using index > +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using > where; Using join buffer (flat, BNL join) > +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 > Using where; Using join buffer (incremental, BKA join) > +SELECT t4.a4, t5.b5 > +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +a4 b5 > +9 0 > +9 NULL > +SET SESSION optimizer_switch = 'outer_join_with_cache=off'; > +SET SESSION join_cache_level = DEFAULT; > +DROP TABLE t1,t2,t3,t4,t5; > set @@optimizer_swit...@save_optimizer_switch; > > === modified file 'mysql-test/t/join_cache.test' > --- a/mysql-test/t/join_cache.test 2010-11-18 22:13:57 +0000 > +++ b/mysql-test/t/join_cache.test 2010-11-19 06:02:40 +0000 > @@ -2655,5 +2655,50 @@ > > DROP TABLE t1,t2,t3; > > +--echo # > +--echo # Bug #675922: incremental buffer for BKA with access from previous > +--echo # buffers from non-nullable columns whose values may be null > +--echo # > + > +CREATE TABLE t1 (a1 varchar(32)) ; > +INSERT INTO t1 VALUES ('s'),('k'); > + > +CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ; > +INSERT INTO t2 VALUES (7,'s'); > + > +CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ; > +INSERT INTO t3 VALUES (7,'s'); > + > +CREATE TABLE t4 (a4 int) ; > +INSERT INTO t4 VALUES (9); > + > +CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ; > +INSERT INTO t5 VALUES (7,0); > + > +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; > + > +SET SESSION join_cache_level = 0; > +EXPLAIN > +SELECT t4.a4, t5.b5 > + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +SELECT t4.a4, t5.b5 > + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > + > +SET SESSION join_cache_level = 6; > +EXPLAIN > +SELECT t4.a4, t5.b5 > + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > +SELECT t4.a4, t5.b5 > + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) > + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; > + > +SET SESSION optimizer_switch = 'outer_join_with_cache=off'; > +SET SESSION join_cache_level = DEFAULT; > + > +DROP TABLE t1,t2,t3,t4,t5; > + > # this must be the last command in the file > set @@optimizer_swit...@save_optimizer_switch; > > === modified file 'sql/sql_join_cache.cc' > --- a/sql/sql_join_cache.cc 2010-11-13 15:47:43 +0000 > +++ b/sql/sql_join_cache.cc 2010-11-19 06:02:40 +0000 > @@ -1805,14 +1805,21 @@ > size_of_fld_ofs* > (referenced_fields+1-copy->referenced_field_no)); > bool is_null= FALSE; > + Field *field= copy->field; > if (offset == 0 && flag_fields) > is_null= TRUE; > if (is_null) > - copy->field->set_null(); > + { > + field->set_null(); > + if (!field->real_maybe_null()) > + field->table->null_row= 1; > + } > else > { > uchar *save_pos= pos; > - copy->field->set_notnull(); > + field->set_notnull(); > + if (!field->real_maybe_null()) > + field->table->null_row= 0; > pos= rec_ptr+offset; > read_record_field(copy, blob_data_is_in_rec_buff(rec_ptr)); > pos= save_pos; > > _______________________________________________ > commits mailing list > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
