From: Ole John Aske Date: January 14 2011 10:42am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4107) Bug#58134 List-Archive: http://lists.mysql.com/commits/128716 X-Bug: 58134 Message-Id: <20110114104301.28D561818C@loki43.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8519173690013451156==" --===============8519173690013451156== 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/ based on revid:ole.john.aske@stripped 4107 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58134: 'Incorrectly condition pushdown inside subquery to NDB engine' An incorrect 'table_map' containing both the table itself, and possible any outer-refs if this was the last table in the subquery, was presented to make_cond_for_table(). As a pushed condition is only able to refer column from the table the condition is pushed to, nothing else than columns from the table itself (tab->table->map) may be refered in the pushed condition constructed by 'push_cond= make_cond_for_table()'. Also fix a minor 'copy and paste' bug in a comment in make_cond_for_table(). modified: mysql-test/suite/ndb/r/ndb_condition_pushdown.result mysql-test/suite/ndb/t/ndb_condition_pushdown.test sql/sql_select.cc === modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2010-11-29 15:43:51 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2011-01-14 10:42:53 +0000 @@ -2247,5 +2247,37 @@ id select_type table type possible_keys Warnings: Note 1003 select `test`.`t2`.`c` AS `c`,count(distinct `test`.`t2`.`a`) AS `count(distinct t2.a)` from `test`.`tx` join `test`.`tx` `t2` where ((`test`.`tx`.`b` = `test`.`t2`.`d`) and (`test`.`tx`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`a` = 4)) group by `test`.`t2`.`c` drop table tx; +set engine_condition_pushdown = on; +create table t (pk int, i int) engine = ndb; +insert into t values (1,3), (3,6), (6,9), (9,1); +create table subq (pk int, i int) engine = ndb; +insert into subq values (1,3), (3,6), (6,9), (9,1); +explain extended +select * from t where exists +(select * from t as subq where subq.i=3 and t.i=3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 50.00 Using where +2 DEPENDENT SUBQUERY subq ALL NULL NULL NULL NULL 4 100.00 Using where with pushed condition: (`test`.`subq`.`i` = 3) +Warnings: +Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` where exists(select 1 from `test`.`t` `subq` where ((`test`.`subq`.`i` = 3) and (`test`.`t`.`i` = 3))) +explain extended +select * from t where exists +(select * from subq where subq.i=3 and t.i=3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 Using where +2 DEPENDENT SUBQUERY subq ALL NULL NULL NULL NULL 4 50.00 Using where with pushed condition: (`test`.`subq`.`i` = 3) +Warnings: +Note 1276 Field or reference 'test.t.i' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`i` AS `i` from `test`.`t` where exists(select 1 from `test`.`subq` where ((`test`.`subq`.`i` = 3) and (`test`.`t`.`i` = 3))) +select * from t where exists +(select * from t as subq where subq.i=3 and t.i=3); +pk i +1 3 +select * from t where exists +(select * from subq where subq.i=3 and t.i=3); +pk i +1 3 +drop table t,subq; set engine_condition_pushdown = @old_ecpd; DROP TABLE t1,t2,t3,t4,t5; === modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2010-11-29 15:43:51 +0000 +++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2011-01-14 10:42:53 +0000 @@ -2320,6 +2320,32 @@ group by t2.c; drop table tx; +# Bug#58134: Incorrectly condition pushdown inside subquery to NDB engine +set engine_condition_pushdown = on; + +create table t (pk int, i int) engine = ndb; +insert into t values (1,3), (3,6), (6,9), (9,1); +create table subq (pk int, i int) engine = ndb; +insert into subq values (1,3), (3,6), (6,9), (9,1); + +# 'Explain extended' to verify that only 'subq.i=3' is pushed +explain extended +select * from t where exists + (select * from t as subq where subq.i=3 and t.i=3); +explain extended + select * from t where exists + (select * from subq where subq.i=3 and t.i=3); + +--sorted_result +select * from t where exists + (select * from t as subq where subq.i=3 and t.i=3); +--sorted_result +select * from t where exists + (select * from subq where subq.i=3 and t.i=3); + +drop table t,subq; + + set engine_condition_pushdown = @old_ecpd; DROP TABLE t1,t2,t3,t4,t5; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-12 11:54:35 +0000 +++ b/sql/sql_select.cc 2011-01-14 10:42:53 +0000 @@ -6361,7 +6361,11 @@ make_join_select(JOIN *join,SQL_SELECT * if (thd->variables.engine_condition_pushdown) { COND *push_cond= +#ifndef MCP_BUG58134 + make_cond_for_table(tmp, tab->table->map, tab->table->map); +#else make_cond_for_table(tmp, current_map, current_map); +#endif if (push_cond) { /* Push condition to handler */ @@ -12860,7 +12864,7 @@ make_cond_for_table(COND *cond, table_ma new_cond->argument_list()->push_back(fix); } /* - Item_cond_and do not need fix_fields for execution, its parameters + Item_cond_or do not need fix_fields for execution, its parameters are fixed or do not need fix_fields, too */ new_cond->quick_fix_field(); --===============8519173690013451156== 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\ # ojmo06ia9rjdvqkp # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: cc7b45a7d6177573aed8c6475cc7a6810f87e4ed # timestamp: 2011-01-14 11:43:00 +0100 # base_revision_id: ole.john.aske@stripped\ # lpa53iyi8klji1g4 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfy5Z4IABKlfgFBwWPf//3+3 /qD////wYAqV8NAoAAoJUAAAAAAUA1QEDyQAGTINDT1ANBkMgBoGmgFVCASbITGVND1NHqGnqAAG Q0Boep6mj1BxkyaGIxNGARgJhAGAmmjTI0AxxkyaGIxNGARgJhAGAmmjTI0AxxkyaGIxNGARgJhA GAmmjTI0AwkkE0AmJkABDQaJkNTKPUGjJo0BiUURND3md4CZu2xjg53WYqgrcRudCLetbXMWAUbg EwCkPF44oumUSQEciCPn9D1hCZsJGTMHsJHIz1qMdMwPj/o/wZEg7e1uVLeLesww8tqSOHG0ezJU VPjJ2RVlXwdwqjU+csxcGUoPPXoPJ2txqYeLVf6TwesXAqxH4h56ePQorDeFYKYrNXiSLF0cAea/ NbkwzMyZlhzFtmjS4dqe5bmNUoNv+YnvaIzbmU/CUYX/lCJckR6t8+fYoSdd0FfSaFP+1djWjnFt RN/vy7ObVeT55DiaaX8KgakhOIpQyz0W2/GzUYH0AI094OqpVzRXAKgzA/BqJ1xfdnxd3KIUPnoI ELbDDF9CgOrLHV/8HWQnHk4d8CJrIjMzPQnA8YBxSBmHWMj1KBOtlRa6zfEqpnvf4wGtt6Fmk1kg GJAPKTp6d2j4/Gk86OH6z7F52HAT2EvgcAoHKJHvpNXOcRMmEH7hkvq1AwxqHR+FJOMYBXTAgL2D KTtk6mMkYxMQdqshYdzs1tfAYjEYzjWF5rKQ7gD9E4zrcuNZYXVWHJOcazEOmAjnj9iaYrI6Tv5h YXDqtQCuErlksRJx3IGAcJQHDbU2EFtVIaOjpAfJgZhOz5nMz/mE10xeScQDW04w9MgGQDoDDigc DhHKTgNwiYfMig4FTxST4m86lUZPOQZhfMZW31vZzjqHaFxSef2C1FqE3uxUIYPl2ndyG6xBLZNC MJBSXFRVGO/BjOqiS0IYQ42b7ITjRtnMwV5+kDad57UmZVmbMnF7GTxMtw/LXNfO24zTWpZgZCtR IgK2coEaDKPAnJmOTKE5i8WOgmHGa82nlcYOPkUFhvqZj1QPFzBwHMKxFNVBLg2c8yrNhBg1LAy3 yRZBUqbviFJmzdB71utyCaieoqMygMGts4whgrre8Q5TPzj1wKShZzsI2bii4ro4KYiSeDHqRa/9 Sk68byVRe2g0D6TORH6zARZioYKdd+VBOZYmccHheSWDpmj/boUWnkVE4VvNZ7HkbDhzm1Fdx0Du CwLOto7U0OaLzIhq1x27d46rynrpgPrJzfvQ8Hs6VSDTv8eDV1Ks0NadpyZatDIcnBfkZmxTHM4V WVdRe8xxQ7AuOpQd9xRNjPPdZfJTmk1X0qgYjsIKctHGy+zDMOKCM/jRxV06fxDiEC6ojQYjjOYV lUxC8z0DiKvezr685eV1GAintKPIaLQC5rL3V3NNMMzgiwRehpmiEhSk3b8RqXIZguBj4ByS1sLt 7LWZmYnUUnYoDd/NcO4U2gkJeYfmWBam/sRJkYxRjH+N/J+Kq5G4xLPsc50rTG91RdN/VtFErCDG UhtP2ohOUhYZZkzd4VydYhWhQF5cfoTvAKAqLgq984a+YOGPnsDjzUUj7xlPOKZNVnlhWKPaSdsf FKTvJJkiRByjiGGTCTfQGTH1HE4y9z3LAPxsXqfwUX/XK8RivmqL0lYwHxKuVwlTZ+8/f931gP2y FtRXxDD8Q4KaOp2C7WQThXhxGQfqQ/ZTA1RyEs0pq2yBjRErXA+wxS20/A5GzWcuIJxu/jxm0fge BcuLIBht8YiA0HyZ59m4i/Jkd66GDFRafePMioMeS17DTqzwIZWMyS3GcoQDZfxFi8TgHO+1CeHw LhCsY4DbBi0YUhkFg4Q5bllT5bKYn9CmhEkMOSpcik1kngtcw4WXB3QGSXcbiOjoItLaf63COoVh Ex0FtMMiB1iECMBDhOOLh4HYQeDFIUPia8KJoiQQh4d5tLX34NMdp4bSXotiyEs0/BkzwvL0UiaY jjnDxOu8uIToExwYkawpcqb/QmjGZxn4ab7HMTksHIvJt9vJsP9uLTYuCQvx7IaSwciEANsAm0Jy NssNztQCYgpSe4L+5K2lVTJAzlMOcKRWkagmScjQKa8ovdwHv2C5Dzu2ktev8PJ3Bj0GcdBjWE4V Blr6/9WpaV9Rj7joV090SgGL9Q4SLXzTHFg83GxTGKUQM4x5ntOcldkcjX2QYCIwhkrnC6gLfA5p J5IbWbEGgJvYYtWPh6ruJkB/gR0EeF6bVYenJKI7y/Q335sOQ4PSzwJJEiSOaLXPbgL2AhfbcDif 0iE4l9evOHxLBUjDAwElVmFERnOJikplZMYZN0Va8utBwNQi7xJl8jvEcxLQWDzyPJgbL0Hg4uSW 4MvEO0mGFr6ryJ6RDFXb6HeRF1CYWTgeq0cdpAM7al+zVJkBYJREnieooFeMaDK5dunBTH3vVJeY UBYHgbxwkHjYV54WYrNf7rfyUaC1cI4PaMMqzE2iO3PRSFfOp/6q0wK8xyPFyqZF+2JMQYZiYSe9 n7mAK0xbSUy/AcCcKIpgrD3NJOjQ0ySkBJzkngMSakgOibYd98Re53T0wAYHM4GTMh45dc1EizUS Y4MkwdpWI+g085cftwEs4jMdm9fmagZO9rS8sEhyQTiKxgVQ9CM0BxEG6Z9jRPucHMS0CcpLrYNB MqP6Bydvq2CtPY0kRg3BucmsSOqoNTJkNAM4TT+bjzPmH0DxPdEcqvketj7ftvE8YYWbUkjVYOA9 TeeIjSkZoodzoPJjYw99jJ6OIwIjIYgAuQcE5ZF4eDyZBZAqPfLdAJRb9wi1aOlRofhoEijtM15S q0MmpdOz0x7HYYxAiHxpss8ZqnDJM40nsQH7ae86AadSTjOZJiclrzBI7Hm5idPAK1MfkF4orG4S 34FqMvnSY3lx6r1ZmZuwftQ9w4lNvVrgQ2QoYCNJrBHT7zqJ60lKokXlO+QwBWwOCRxC2O18RE75 wHjVULWQAieFEl1FAkZkDhLlOZhjkyVYfcsaq8xaSt0rqeFg9VMGvjvYYZEy9CGdCVzNNDRFGJzf bO+7WNJFrCPhia6UZAxoE5YjOQSYocPQ2s1jirxJCA7J/VYh6H85EjfeeT1IbzQGiPjgef00BQQT pjv+B2CyOGgQ2Pt8aQcwC+pa4a8C6O/aQNg60wbEYS/JgTF7jNkaOxxvo2ORI5las8AgaEp1hVIJ ybPrNR/8XckU4UJD8uWeCA== --===============8519173690013451156==--