maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Ok to push.

On Mon, Nov 08, 2010 at 08:36:33PM -0800, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-bug668644/
> 
> ------------------------------------------------------------
> revno: 2845
> revision-id: igor@xxxxxxxxxxxx-20101109043632-s053dbydv48cr9nz
> parent: monty@xxxxxxxxxxxx-20101105103751-09kb6rx5tvpyywen
> committer: Igor Babaev <igor@xxxxxxxxxxxx>
> branch nick: maria-5.3-bug668644
> timestamp: Mon 2010-11-08 20:36:32 -0800
> message:
>   Fixed LP bug #668644.
>   The pushdown condition for the sorted table in a query can be complemented
>   by the conditions from HAVING. This transformation is done in JOIN::exec
>   pretty late after the original pushdown condition have been saved in the
>   field pre_idx_push_select_cond for the sorted table. So this field must
>   be updated after the inclusion of the condition from HAVING.

> === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
> --- a/mysql-test/suite/innodb/r/innodb_mysql.result	2010-10-28 17:04:23 +0000
> +++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2010-11-09 04:36:32 +0000
> @@ -2609,5 +2609,41 @@
>  rows	3
>  Extra	Using index
>  DROP TABLE t1;
> -#
>  End of 5.1 tests
> +#
> +# Bug#668644: HAVING + ORDER BY
> +#
> +CREATE TABLE t1 (
> +pk int  NOT NULL PRIMARY KEY, i int DEFAULT NULL,
> +INDEX idx (i)
> +) ENGINE=INNODB;
> +INSERT INTO t1 VALUES
> +(6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
> +CREATE TABLE t2 (
> +i int DEFAULT NULL,
> +pk int NOT NULL PRIMARY KEY,
> +INDEX idx (i)
> +) ENGINE= INNODB;
> +INSERT INTO t2 VALUES
> +(-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
> +(-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
> +(5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
> +(576061440,3);
> +EXPLAIN
> +SELECT t1 .i AS f FROM t1, t2
> +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +HAVING f > 7
> +ORDER BY f;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using filesort
> +1	SIMPLE	t2	ref	idx	idx	5	test.t1.pk	1	Using index
> +SELECT t1 .i AS f FROM t1, t2
> +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +HAVING f > 7
> +ORDER BY f;
> +f
> +1148715008
> +1541734400
> +1541734400
> +DROP TABLE t1, t2;
> +End of 5.3 tests
> 
> === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
> --- a/mysql-test/suite/innodb/t/innodb_mysql.test	2010-10-19 13:58:35 +0000
> +++ b/mysql-test/suite/innodb/t/innodb_mysql.test	2010-11-09 04:36:32 +0000
> @@ -840,7 +840,41 @@
>  
>  DROP TABLE t1;
>  
> ---echo #
> -
> -
>  --echo End of 5.1 tests
> +
> +--echo #
> +--echo # Bug#668644: HAVING + ORDER BY
> +--echo #
> +
> +CREATE TABLE t1 (
> +  pk int  NOT NULL PRIMARY KEY, i int DEFAULT NULL,
> +  INDEX idx (i)
> +) ENGINE=INNODB;
> +INSERT INTO t1 VALUES
> +  (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
> +
> +CREATE TABLE t2 (
> +  i int DEFAULT NULL,
> +  pk int NOT NULL PRIMARY KEY,
> +  INDEX idx (i)
> +) ENGINE= INNODB;
> +INSERT INTO t2 VALUES
> +  (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
> +  (-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
> +  (5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
> +  (576061440,3);
> +
> +EXPLAIN
> +SELECT t1 .i AS f FROM t1, t2
> +  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +  HAVING f > 7
> +  ORDER BY f;
> +SELECT t1 .i AS f FROM t1, t2
> +  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +  HAVING f > 7
> +  ORDER BY f;
> +
> +DROP TABLE t1, t2;
> +
> +
> +--echo End of 5.3 tests
> 
> === modified file 'sql/opt_index_cond_pushdown.cc'
> --- a/sql/opt_index_cond_pushdown.cc	2009-12-22 12:49:15 +0000
> +++ b/sql/opt_index_cond_pushdown.cc	2010-11-09 04:36:32 +0000
> @@ -318,7 +318,7 @@
>      if (idx_cond)
>      {
>        Item *idx_remainder_cond= 0;
> -      tab->pre_idx_push_select_cond= tab->select_cond;
> +      tab->pre_idx_push_select_cond= tab->select->cond;
>        /*
>          For BKA cache we store condition to special BKA cache field
>          because evaluation of the condition requires additional operations
> 
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2010-10-28 17:04:23 +0000
> +++ b/sql/sql_select.cc	2010-11-09 04:36:32 +0000
> @@ -2213,6 +2213,14 @@
>  	    DBUG_VOID_RETURN;
>  	  curr_table->select->cond->fix_fields(thd, 0);
>  	}
> +        if (curr_table->pre_idx_push_select_cond)
> +	{
> +          if (!(curr_table->pre_idx_push_select_cond= 
> +                new Item_cond_and(curr_table->pre_idx_push_select_cond,
> +                                  sort_table_cond)))
> +            DBUG_VOID_RETURN;            
> +          curr_table->pre_idx_push_select_cond->fix_fields(thd, 0);
> +        }
>          curr_table->set_select_cond(curr_table->select->cond, __LINE__);
>  	curr_table->select_cond->top_level_item();
>  	DBUG_EXECUTE("where",print_where(curr_table->select->cond,
> @@ -6355,6 +6363,7 @@
>    join_tab->do_firstmatch= NULL;
>    join_tab->loosescan_match_tab= NULL;
>    join_tab->emb_sj_nest= NULL;
> +  join_tab->pre_idx_push_select_cond= NULL;
>    bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
>    temp_table->status=0;
>    temp_table->null_row=0;
> 

> _______________________________________________
> 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