List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:September 1 2011 8:39am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3428 to 3429) Bug#12822678
View as plain text  
 3429 Olav Sandstaa	2011-09-01
      Fix for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
      
      The cause for the wrong result was that we pushed down index conditions
      for a table with join type CONST. This table was the second table in
      the join order. The pushed index condition contained a field from the
      first table. Due to optimization of "const" tables the single row from
      these are only read once from the storage engine and then re-used
      multiple times during the join. This caused the pushed index condition
      to only be evaluated once for the first access instead of on all
      accesses to the row. This causes the pushed index condition to not
      be evaluated for all records and can result in either more or zero
      rows from the join.
      
      The fix for this is to not use ICP when the join type is CONST
      or SYSTEM. An alternative fix that also would solve this problem
      is to only allow index conditions that only accessed fields from
      the current table during evaluation. This would avoid index
      conditions that referred to other tables to be pushed down.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
     @ mysql-test/r/innodb_icp.result
        Test case for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
        
        Note that this change also changes one existing test from using
        ICP for a const table to not use ICP.
     @ mysql-test/r/innodb_icp_none.result
        Test case for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
     @ mysql-test/r/innodb_mrr_cost_icp.result
        Test case change caused by fix for Bug#12822678: 
        2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN.
        
        Change in explain output due to no longer pushing index
        conditions on tables with join type CONST.
     @ mysql-test/r/innodb_mrr_icp.result
        Test case change caused by fix for Bug#12822678: 
        2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN.
        
        Change in explain output due to no longer pushing index
        conditions on tables with join type CONST.
     @ mysql-test/r/myisam_icp.result
        Test case for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
     @ mysql-test/r/myisam_icp_none.result
        Test case for Bug#12822678: 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
     @ sql/sql_select.cc
        Disable use of index condition pushdown (ICP) for tables that
        have join type CONST and SYSTEM. The reason for this is that these
        will return max one record and the access is optimized so that
        this record is only read once from the storage engine and then
        re-used multiple times in the join.
        
        The fix also adds DBUG statements for tracing calls to join_read_const().

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/innodb_mrr_cost_icp.result
      mysql-test/r/innodb_mrr_icp.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_none.result
      sql/sql_select.cc
 3428 Tor Didriksen	2011-09-01
      Bug#12856915 VALGRIND FAILURE IN FILESORT/CREATE_SORT_INDEX
      
      Post-push fix:
      Enable filesort pattern three, main.index_merge_myisam failed.

    modified:
      mysql-test/valgrind.supp
=== modified file 'mysql-test/include/icp_tests.inc'

=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-08-03 07:21:17 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-09-01 08:38:18 +0000
@@ -1000,3 +1000,32 @@
 
 }
 
+--echo #
+--echo # BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
+--echo #
+
+CREATE TABLE t1 (
+  i1 INTEGER NOT NULL,
+  d1 DOUBLE,
+  KEY k1 (d1)
+);
+
+INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
+
+CREATE TABLE t2 (
+  pk INTEGER NOT NULL,
+  i1 INTEGER NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (4,1);
+
+let query=
+SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-09-01 08:38:18 +0000
@@ -928,7 +928,7 @@
 WHERE alias2.pk = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	alias1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	alias2	const	PRIMARY	PRIMARY	4	const	1	Using index condition
+1	SIMPLE	alias2	const	PRIMARY	PRIMARY	4	const	1	Using where
 SELECT alias2.i1
 FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2
 ON alias2.pk AND alias2.pk <= alias1.c1
@@ -936,6 +936,34 @@
 i1
 Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'j'
+Warning	1292	Truncated incorrect DOUBLE value: 'e'
 DROP TABLE t1;
+#
+# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+d1 DOUBLE,
+KEY k1 (d1)
+);
+INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (4,1);
+EXPLAIN SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	k1	9	NULL	3	Using index
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+d1	pk	i1
+1	4	1
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2011-09-01 08:38:18 +0000
@@ -937,5 +937,32 @@
 Warning	1292	Truncated incorrect DOUBLE value: 'j'
 Warning	1292	Truncated incorrect DOUBLE value: 'e'
 DROP TABLE t1;
+#
+# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+d1 DOUBLE,
+KEY k1 (d1)
+);
+INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (4,1);
+EXPLAIN SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	k1	9	NULL	3	Using index
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+d1	pk	i1
+1	4	1
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result	2011-09-01 08:38:18 +0000
@@ -537,7 +537,7 @@
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
 WHERE t2.i1 > 5

=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result	2011-09-01 08:38:18 +0000
@@ -537,7 +537,7 @@
 ORDER BY i1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using index condition; Using where
+1	SIMPLE	t2	const	PRIMARY,k1	PRIMARY	4	const	1	Using where
 SELECT i1
 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2
 WHERE t2.i1 > 5

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-09-01 08:38:18 +0000
@@ -910,4 +910,31 @@
 #
 # BUG#12601961 "SEGFAULT IN HANDLER::COMPARE_KEY2"
 #
+#
+# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+d1 DOUBLE,
+KEY k1 (d1)
+);
+INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (4,1);
+EXPLAIN SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	k1	9	NULL	3	Using index
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+d1	pk	i1
+1	4	1
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-08-08 14:16:20 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2011-09-01 08:38:18 +0000
@@ -909,4 +909,31 @@
 #
 # BUG#12601961 "SEGFAULT IN HANDLER::COMPARE_KEY2"
 #
+#
+# BUG#12822678 - 2 MORE ROWS WHEN ICP=ON W/ STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+i1 INTEGER NOT NULL,
+d1 DOUBLE,
+KEY k1 (d1)
+);
+INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (4,1);
+EXPLAIN SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	k1	9	NULL	3	Using index
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+SELECT t1.d1, t2.pk, t2.i1
+FROM t1 STRAIGHT_JOIN t2 ON t2.i1
+WHERE t2.pk <> t1.d1 AND t2.pk = 4;
+d1	pk	i1
+1	4	1
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-30 10:04:45 +0000
+++ b/sql/sql_select.cc	2011-09-01 08:38:18 +0000
@@ -11388,6 +11388,12 @@
        @see subselect_single_select_engine::exec()
        @see TABLE_REF::cond_guards
        @see setup_join_buffering
+    5. The join type is not CONST or SYSTEM. The reason for excluding
+       these join types, is that these are optimized to only read the
+       record once from the storage engine and later re-use it. In a
+       join where a pushed index condition evaluates fields from
+       tables earlier in the join sequence, the pushed condition would
+       only be evaluated the first time the record value was needed.
   */
   if (tab->condition() &&
       tab->table->file->index_flags(keyno, 0, 1) &
@@ -11395,7 +11401,8 @@
       tab->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN) &&
       tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI &&
       tab->join->thd->lex->sql_command != SQLCOM_DELETE_MULTI &&
-      !tab->has_guarded_conds())
+      !tab->has_guarded_conds() &&
+      tab->type != JT_CONST && tab->type != JT_SYSTEM)
   {
     DBUG_EXECUTE("where", print_where(tab->condition(), "full cond",
                  QT_ORDINARY););
@@ -19467,6 +19474,8 @@
 {
   int error;
   TABLE *table= tab->table;
+  DBUG_ENTER("join_read_const");
+
   if (table->status & STATUS_GARBAGE)		// If first read
   {
     table->status= 0;
@@ -19485,8 +19494,11 @@
       mark_as_null_row(tab->table);
       empty_record(table);
       if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
-	return report_error(table, error);
-      return -1;
+      {
+        const int ret= report_error(table, error);
+        DBUG_RETURN(ret);
+      }
+      DBUG_RETURN(-1);
     }
     store_record(table,record[1]);
   }
@@ -19496,7 +19508,7 @@
     restore_record(table,record[1]);			// restore old record
   }
   table->null_row=0;
-  return table->status ? -1 : 0;
+  DBUG_RETURN(table->status ? -1 : 0);
 }
 
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3428 to 3429) Bug#12822678Olav Sandstaa1 Sep