From 4cfd5f014fe1be1d05d6b322a1ad193f4073c7da Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 8 Nov 2010 20:36:32 -0800 Subject: 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. --- mysql-test/suite/innodb/r/innodb_mysql.result | 38 ++++++++++++++++++++++++++- 1 file changed, 37 insertions(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb/r/innodb_mysql.result') diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index e6383fb97fa..4e7ca8b4d8f 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2609,5 +2609,41 @@ ref NULL 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 -- cgit v1.2.1