[Commits] Rev 2845: Fixed LP bug #668644. in file:///home/igor/maria/maria-5.3-bug668644/

Igor Babaev igor at askmonty.org
Tue Nov 9 06:36:33 EET 2010


At file:///home/igor/maria/maria-5.3-bug668644/

------------------------------------------------------------
revno: 2845
revision-id: igor at askmonty.org-20101109043632-s053dbydv48cr9nz
parent: monty at askmonty.org-20101105103751-09kb6rx5tvpyywen
committer: Igor Babaev <igor at 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.
-------------- next part --------------
=== 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;



More information about the commits mailing list