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: [email protected]
> parent: [email protected]
> committer: Igor Babaev <[email protected]>
> 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
> [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
