maria-developers team mailing list archive

Thread
Date
Re: Fwd: [Commits] Rev 2860: Fixed LP bug #675922. in file:///home/igor/maria/maria-5.3-mwl128-bug675922/

To: Igor Babaev <igor@xxxxxxxxxxxx>
From: Sergey Petrunya <psergey@xxxxxxxxxxxx>
Date: Fri, 19 Nov 2010 17:33:33 +0300
Cc: maria-developers@xxxxxxxxxxxxxxxxxxx
In-reply-to: <4CE61368.1020006@askmonty.org>
User-agent: Mutt/1.5.18 (2008-05-17)
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 <igor@xxxxxxxxxxxx>
> Reply-To: 	maria-developers@xxxxxxxxxxxxxxxxxxx
> To: 	<commits@xxxxxxxxxxx>
> 
> 
> 
> At file:///home/igor/maria/maria-5.3-mwl128-bug675922/
> 
> ------------------------------------------------------------
> revno: 2860
> revision-id: igor@xxxxxxxxxxxx-20101119060240-gzh1k5gxl3aazk45
> parent: igor@xxxxxxxxxxxx-20101118221357-zg55d3erru07ugzy
> committer: Igor Babaev <igor@xxxxxxxxxxxx>
> 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_switch=@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_switch=@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
> commits@xxxxxxxxxxx
> 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