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@askmonty.org-20101109043632-s053dbydv48cr9nz parent: monty@askmonty.org-20101105103751-09kb6rx5tvpyywen committer: Igor Babaev <igor@askmonty.org> 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@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