Hi Timour, Could you please review the below: ----- Forwarded message from Sergey Petrunya <psergey@askmonty.org> ----- From: Sergey Petrunya <psergey@askmonty.org> To: commits@mariadb.org 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@askmonty.org-20110713204306-tsxnddr8v3v0i6bg parent: psergey@askmonty.org-20110709123340-1qe0558i8p352p2v committer: Sergey Petrunya <psergey@askmonty.org> 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@mariadb.org 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