← Back to team overview

maria-developers team mailing list archive

  1. maria-developers team
  2. Mailing list archive
  3. Message #04250

Timour please review: [Commits] Rev 3096: BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89

  Thread Previous • Date Previous • • Thread Next 
Hi Timour,

Could you please review the below:

----- Forwarded message from Sergey Petrunya <psergey@xxxxxxxxxxxx> -----

From: Sergey Petrunya <psergey@xxxxxxxxxxxx>
To: commits@xxxxxxxxxxx
X-Mailer: mail (GNU Mailutils 1.2)
Date: Thu, 14 Jul 2011 00:43:12 +0400 (MSD)
Subject: [Commits] Rev 3096: BUG#778434 Wrong result with in_to_exists=on in
	maria-5.3-mwl89 in file:///home/psergey/dev2/5.3-push7/

At file:///home/psergey/dev2/5.3-push7/

------------------------------------------------------------
revno: 3096
revision-id: psergey@xxxxxxxxxxxx-20110713204306-tsxnddr8v3v0i6bg
parent: psergey@xxxxxxxxxxxx-20110709123340-1qe0558i8p352p2v
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: 5.3-push7
timestamp: Thu 2011-07-14 00:43:06 +0400
message:
  BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
  - Make {ha_myisam,ha_maria}::index_read_idx_map check pushed index condition.
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-02-09 03:17:12 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-07-13 20:43:06 +0000
@@ -225,3 +225,32 @@
 
 DROP PROCEDURE insert_data;
 DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+--echo #
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+
+set @tmp_778434=@@optimizer_switch;
+SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+        SELECT t3.f1, t3.f1
+        FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-07-08 14:46:47 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-07-13 20:43:06 +0000
@@ -202,5 +202,28 @@
 12
 DROP PROCEDURE insert_data;
 DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+set @tmp_778434=@@optimizer_switch;
+SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+f11	f10
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
 set optimizer_switch=@innodb_icp_tmp;
 set storage_engine= @save_storage_engine;

=== modified file 'mysql-test/r/maria_icp.result'
--- a/mysql-test/r/maria_icp.result	2011-07-08 14:46:47 +0000
+++ b/mysql-test/r/maria_icp.result	2011-07-13 20:43:06 +0000
@@ -202,5 +202,28 @@
 12
 DROP PROCEDURE insert_data;
 DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+set @tmp_778434=@@optimizer_switch;
+SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+f11	f10
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
 set storage_engine= @save_storage_engine;
 set optimizer_switch=@maria_icp_tmp;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-07-08 14:46:47 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-07-13 20:43:06 +0000
@@ -198,6 +198,29 @@
 12
 DROP PROCEDURE insert_data;
 DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+set @tmp_778434=@@optimizer_switch;
+SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+f11	f10
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
 set @myisam_icp_tmp=@@optimizer_switch;
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 drop table if exists t0, t1, t1i, t1m;

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2011-07-01 12:08:30 +0000
+++ b/sql/sql_class.h	2011-07-13 20:43:06 +0000
@@ -3633,6 +3633,7 @@
                                           enum ha_rkey_function find_flag)
 {
   increment_statistics(&SSV::ha_read_key_count);
+  DBUG_ASSERT(inited==NONE);
   int error= index_read_idx_map(buf, index, key, keypart_map, find_flag);
   if (!error)
   {

=== modified file 'storage/maria/ha_maria.cc'
--- a/storage/maria/ha_maria.cc	2011-07-01 12:16:10 +0000
+++ b/storage/maria/ha_maria.cc	2011-07-13 20:43:06 +0000
@@ -2259,7 +2259,14 @@
 				 key_part_map keypart_map,
 				 enum ha_rkey_function find_flag)
 {
-  int error= maria_rkey(file, buf, index, key, keypart_map, find_flag);
+  int error;
+  /* Use the pushed index condition if it matches the index we're scanning */
+  if (index == pushed_idx_cond_keyno)
+    ma_set_index_cond_func(file, index_cond_func_maria, this);
+  
+  error= maria_rkey(file, buf, index, key, keypart_map, find_flag);
+   
+  ma_set_index_cond_func(file, NULL, 0);
   table->status= error ? STATUS_NOT_FOUND : 0;
   return error;
 }

=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc	2011-06-24 17:43:31 +0000
+++ b/storage/myisam/ha_myisam.cc	2011-07-13 20:43:06 +0000
@@ -1790,7 +1790,13 @@
                                   key_part_map keypart_map,
                                   enum ha_rkey_function find_flag)
 {
-  return mi_rkey(file, buf, index, key, keypart_map, find_flag);
+  int res;
+  /* Use the pushed index condition if it matches the index we're scanning */
+  if (index == pushed_idx_cond_keyno)
+    mi_set_index_cond_func(file, index_cond_func_myisam, this);
+  res= mi_rkey(file, buf, index, key, keypart_map, find_flag);
+  mi_set_index_cond_func(file, NULL, 0);
+  return res;
 }
 
 int ha_myisam::index_next(uchar *buf)

_______________________________________________
commits mailing list
commits@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

----- End forwarded message -----

-- 
BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
  Thread Previous • Date Previous • • Thread Next