From: Ole John Aske Date: March 17 2011 8:02am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3446) List-Archive: http://lists.mysql.com/commits/133170 Message-Id: <20110317080206.BD687223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3763727488763678579==" --===============3763727488763678579== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped 3446 Ole John Aske 2011-03-17 SPJ-scan-scan Fixed a problem where parameters for a pushed lookup query might be specified in incorrect order. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test sql/ha_ndbcluster.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-02-28 12:25:52 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-03-17 08:02:01 +0000 @@ -2188,6 +2188,42 @@ pk a3 b3 c3 d3 pk a3 b3 c3 d3 1001 31 47 1 31 1001 31 47 1 31 1002 47 63 2 47 1002 47 63 2 47 1003 63 31 3 63 1003 63 31 3 63 +explain extended +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const PRIMARY PRIMARY 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref PRIMARY PRIMARY 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +a3 b3 c3 d3 a3 b3 c3 d3 +47 63 2 47 47 63 2 47 +explain extended +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const PRIMARY PRIMARY 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref PRIMARY PRIMARY 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3_hash` `x` join `test`.`t3_hash` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +a3 b3 c3 d3 a3 b3 c3 d3 +47 63 2 47 47 63 2 47 +explain extended +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const b3 b3 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref b3 b3 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`pk` AS `pk`,`test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`pk` AS `pk`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3_unq` `x` join `test`.`t3_unq` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +pk a3 b3 c3 d3 pk a3 b3 c3 d3 +1002 47 63 2 47 1002 47 63 2 47 drop table t3, t3_hash, t3_unq; create table t3 (a3 int, b3 int, c3 int, d3 int, primary key(a3), unique key(d3)) engine = ndb; @@ -4856,7 +4892,7 @@ LOCAL_TABLE_SCANS_SENT 228 PRUNED_RANGE_SCANS_RECEIVED 17 RANGE_SCANS_RECEIVED 718 READS_NOT_FOUND 404 -READS_RECEIVED 49 +READS_RECEIVED 52 SCAN_ROWS_RETURNED 78728 TABLE_SCANS_RECEIVED 228 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT' @@ -4865,7 +4901,7 @@ where spj_counts_at_end.counter_name = s and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT' or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT'); LOCAL+REMOTE READS_SENT -28863 +28871 drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count @@ -4875,9 +4911,9 @@ pruned_scan_count sorted_scan_count 9 pushed_queries_defined -354 +360 pushed_queries_dropped 11 pushed_queries_executed -526 +529 set ndb_join_pushdown = @save_ndb_join_pushdown; === modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-02-28 11:46:27 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-03-17 08:02:01 +0000 @@ -1114,6 +1114,7 @@ insert into t3_unq values (1001, 0x1f, 0 insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f); insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f); +## Table scans (ALL) as pushed root explain extended select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3; --sorted_result @@ -1129,6 +1130,25 @@ select * from t3_unq x, t3_unq y where y --sorted_result select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3; +## Lookup (eq_ref/const) as pushed root +explain extended +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + +explain extended +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + +explain extended +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + drop table t3, t3_hash, t3_unq; ########### === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-03-01 08:01:47 +0000 +++ b/sql/ha_ndbcluster.cc 2011-03-17 08:02:01 +0000 @@ -910,6 +910,12 @@ ha_ndbcluster::check_if_pushable(const N const NdbQueryOperationTypeWrapper& query_def_type= root_operation->getType(); + if (m_disable_pushed_join) + { + DBUG_PRINT("info", ("Push disabled (HA_EXTRA_KEYREAD)")); + return FALSE; + } + if (query_def_type != type) { DBUG_PRINT("info", @@ -920,12 +926,6 @@ ha_ndbcluster::check_if_pushable(const N return FALSE; } - if (m_disable_pushed_join) - { - DBUG_PRINT("info", ("Push disabled (HA_EXTRA_KEYREAD)")); - return FALSE; - } - const NdbDictionary::Index* const expected_index= root_operation->getIndex(); // Check that we still use the same index as when the query was prepared. @@ -4521,6 +4521,9 @@ ha_ndbcluster::pk_unique_index_read_key_ Uint32 offset= 0; NdbQueryParamValue paramValues[ndb_pushed_join::MAX_KEY_PART + ndb_pushed_join::MAX_REFERRED_FIELDS]; + uint map[ndb_pushed_join::MAX_KEY_PART]; + ndbcluster_build_key_map(m_table, m_index[idx], &table->key_info[idx], map); + // Bind key values defining root of pushed join for (i = 0, key_part= key_def->key_part; i < key_def->key_parts; i++, key_part++) { @@ -4531,11 +4534,11 @@ ha_ndbcluster::pk_unique_index_read_key_ DBUG_ASSERT(idx != table_share->primary_key); // PK can't be nullable DBUG_ASSERT(*(key+offset)==0); // Null values not allowed in key // Value is imm. after NULL indicator - paramValues[i]= NdbQueryParamValue(key+offset+1,shrinkVarChar); + paramValues[map[i]]= NdbQueryParamValue(key+offset+1,shrinkVarChar); } else // Non-nullable column { - paramValues[i]= NdbQueryParamValue(key+offset,shrinkVarChar); + paramValues[map[i]]= NdbQueryParamValue(key+offset,shrinkVarChar); } offset+= key_part->store_length; } --===============3763727488763678579== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # 8s8enpv2oll1i1og # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: b6afa198b37b090b4fe993d9a25a2c44df9bc2a4 # timestamp: 2011-03-17 09:02:06 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0/ # base_revision_id: ole.john.aske@stripped\ # 8o5igkebvx2ryok3 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQ7A8ckABhHfgFAwWX///3// /+r////6YAp+Z4fUdSdGrANzGzuuaK0lodOh0UPHMJoyNDQyGEaGQ00aADEZMgGEAwCSghommTaa E0CnoSeU8j1MoaNDTTI0aHpNAaGhzCaMjQ0MhhGhkNNGgAxGTIBhAMAiUQQUzQaTaQ2oGmTIBoAA DRoZBpoAiiJNTRP0k3pTaaExBkybSDTQaAGjRoADQCKSACNJgATRpoKn6p+mmRT0mmjTT1PUAaaD RspUMb93GOXkajkShDuSPkKwRleRoGE9hVzx5i0LLJB/b1Bked/Iwzov1bWjc1Htju/6LN3dzVk0 2uMpYQnn/iY9pRAOzR8HTmkT3N8LavE1d6oU7BgxGYyifknYKBQKlARYLdWxsmDGVKaeuTXtJn6q aLsbr2YcFmOW6U0JWVlZWFa06QNyu8aTs2icmr0MIhtvG6jJJFJDo94fuhry+7DZrVa4mlYwuy61 a3y+t0hsS6XQNihmwX27F9C26mXjFDSadjTJz3gEeS6STKXDMA5uXOhnHMDzRCpf+cSGR2d7d0Hf EL8dc7Jesuq6mqZoYyX0PPY5WWByoCwqNA4hgYMGDYPM23I25BDWHKPKOMBlayUpQlGJRErhCcQs N+/lmg0HPDl+VUz+N1/Dx4pT8JMQD2H5jKffAtDBYRv69F7ueph0fy987xs7Cu4Qr4n/KscveIYl Sl4QGZn5kdJWHYAf+N3tC0SjzJuzQauI2jctK+/nnJ53czsVxrxUacM5RVMgOEQz0CRWQhW5UCZE lJAnHI7nL2k9dLNaOXEe6xDNoyUhGK+i0ayNADBqF5gkLMNaCWBABYGCm2FhUkSpLAsYYOIt0KFh UgLRLKjOM7iMnCm0qp8fEHIMA/sHkOLcUHxYUwi2c+6QmjHQd1wxmC+YP05ADSWhzOr0mANgX7Pl LXFqTj2wpgUUTUBropH1wR5UYlYTf6VByswJhSYRE2kWnDEa7jsqJ7SwN7keJg6JRhrnKDGdAk4m xwIavpftjazT9I1CQATm3LuJmvNUV0YPCk2VUkTFefZcUHCqa0312m24aT6+06O5XZ5fATjOGY0M D0kkGkMDuDTK4qjfVkZMlBDRB0i0LZJEp7Mhlm01k9M/0jgbOOOysilvNmQcoMCsCGIFhkYQgbxv GpA0asNWhRo0aXGmZ+2XtgyzFJUt4TZFtW9OZc/FXjluYWvGk7AprKBQHp7eIZDBPCghQE7AiTG8 JYG0MiY83CHcb7oGUOLA5pr8ZTswYt7J6OPQKVJuMCOvIypv3uJzuC0hzDuHPXwv4Qz4vYyM7mtc TBaRskbBNLtjCFb63yDKlGSVtF2bMi7iRf5M/GUgrINCMThrJax4jCgkbXomI50aMoIjDG0yObqN otV0WUmml1TmPuEpDzVpLCBzV7CtNoYQY4ZKsiy9hWVncYHQI2lxmHFgim3HBtbx2pgTExePHOva NU9ugmKWGBE8EiJWSKDDM46Jnz5SyRGEsdkVbJBrhaJkiqKAtGquPdZ1yIlJTf9dWxDE7gZluIO1 lbWa8k1WiYQV3Pg7We4b0DRPHw/j7AZBGcXJXAapJGHnxG2bzT68M9Q+vQVM9/u+H8mhWn+YQAqE yyU9JpcKwf/QHYxJpJOoqxFYA7zDVv+Q9n7VUWILEgLaRadn9/9KPlYpc4eotWVkAGnnPV6PoHOM eR6Sk1a/fw6bvGJdJ2dqa/n8hziY9CYIPsahlQfEKXR91oFFsm4QdxRXMSlJ0uJoNsEkUq1YxjGM YMGLLhB4Ac4xWDBgwYxjCEGDusOKjGjBgSywwJLRBhhQvZNADxnlOW86xvBhFXeIOXZ6fuJFLvvL FZp2yXxMNxo0Ex04IB7vB5z7SE48iG9xhQNti69npg64wgFDrpdpmE4i9hhD5tZQLwQK3Wet3fGm XUJUAPUiTlBm6/lv47j9xp61OPykQQEGjoLaqh4MHrz+vKHSIZmnSOgFqU48LjnR6yJrUS6s9md9 b63VjUBAPDUgL+tyvygb7wW5hSSEIVsEhEy97ozzZjXjbhgQ0q8MXAvTxy4kmtKVwMkOYz/qKkNR AZ4Q8KJcfPvDj109w4coPFqHobx9sglHRIQ6+RPUTjRIMD2z7MtvscdTzrzsJHavEooDrIMItuFq E4bfYar8haGBorRM8MOnchNu4XKY2/GR2Y8UdOJcJ5S/HmjL+JMW+A4wlC7YEuX4MJ2XiwOU2ZNR BhujdyTfxnAWLgpJMl0W96FK9qHwNqNRXoG7ueYMMOl4lG3nsM3kcnPV12FdoNhV1zhPpwDRNjVp zuq5XZb+g0c2SUTEbBW4wO9K7yxclpY6W0ju5CJE4NY5TgFqOKFPig8ZY4hCpms1Be5xCdXD6HbY xmrcmRYpM92y0M/b7g6ze9tgNm4irFbkGpok7qFc/c3BSWBp7OjhTQK8xJC7Skx76Fy7+7pN+z0Q NbpIGBqKL42BqJwvEOQYeDrPbJuE53G44YiDzMwaBMYxIMEI6g835yZNyBagTDMFADBieVAnHTqg K3GYqguCtEugA093GakNV8Y5dxGhoeWDKJ9vbbx8aqws8pYHQ3+FNUwuKhYgbI1sjBkHRbrqxQYP h3SQ09RL8aFNVmWZDFcyNZUpsB1QUe+3jcjZWJEJD4Vazd6B45J6DoH8D2Cf69RA9QwOA7jcEMvT iJeiaQhjHvyjzdiwL7vsgcI9KrnazwWMN5xsb43PqdZ1G+1MxM4/yI1QwlIZRqv5y9nr9gfUhmmc Lb78niMyEElTvCD3mIPAu7yufozttqzbpybazqkye6U6D8DbJrczFQMNH4ZdWMPIsq9TWYnTr543 tn1Z9CJjL58zdjhiPaecpoILg9XqcWvA6iksE7lSXdBvsCM2YguFswMrRKDwxT0B9hoXuWkUz/Ox AwTv+F76PpGaY2u3RvZDKFgBcoZhwJgllGk832CHRDytCU9JXDEM9kmYxA/UQD5EAwGktuseY2F7 Ag7m/5zYPgVXHY6JkKXbGZvhvTiIgV4m+i+zTicqxYK1MmYi62SkrziZ58Inb9CR4eWrKXXSC8XM S3fKxAtDv8/zxOJpezuNQ9O0j25w9KKNulBvOKGOzp4NIheQD+IhAPNkIfFWoIVxcTmC5+W8oQwI APvcsjBhzGAhIlIEI7SCZLhTKTB2dI0OpwkZbMgTabyQXckU4UJAOwPHJA== --===============3763727488763678579==--