From: Ole John Aske Date: November 19 2010 10:51am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3374) Bug#58134 List-Archive: http://lists.mysql.com/commits/124413 X-Bug: 58134 Message-Id: <20101119105136.E44A8222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6427675574369730780==" --===============6427675574369730780== 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 3374 Ole John Aske 2010-11-19 SPJ-scan-scan: Cherry picked proposed fix for bug#58134: Incorrectly condition pushdown inside subquery to NDB engine into SPJ branch. Testblocker, prevented extensive RQG testing with subqueries. 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-15 10:26:48 +0000 +++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2010-11-19 10:51:30 +0000 @@ -2192,5 +2192,37 @@ select * from t where x not like 'ye%' o x no drop table t; +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-11 08:21:34 +0000 +++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2010-11-19 10:51:30 +0000 @@ -2284,6 +2284,32 @@ explain select * from t where x not like select * from t where x not like 'ye%' order by x; drop table t; +# 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-11-16 13:26:19 +0000 +++ b/sql/sql_select.cc 2010-11-19 10:51:30 +0000 @@ -6455,7 +6455,7 @@ make_join_select(JOIN *join,SQL_SELECT * if (thd->variables.engine_condition_pushdown) { COND *push_cond= - make_cond_for_table(tmp, current_map, current_map); + make_cond_for_table(tmp, tab->table->map, tab->table->map); if (push_cond) { /* Push condition to handler */ @@ -13072,7 +13072,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(); --===============6427675574369730780== 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\ # txftnw99wxm8p02o # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: e0e62ede46b5a4525ae03b1779933068913b6928 # timestamp: 2010-11-19 11:51:36 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # ic60ihx8q8mpn35b # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfp2i7IABGNfgEAwWPf//3+3 /qD////wYAm/aCgBEpVUCpKVRQAUUVrDShNMQNMQAyaA0NG1AA0aNBoDQJKAgATU2I1PJTaZET00 IeUPU9QM1BoAcNNMjEYTTAQwCaYRgmJkNMjQ0Bw00yMRhNMBDAJphGCYmQ0yNDQHDTTIxGE0wEMA mmEYJiZDTI0NAFURAgBME0AGpiZAjJlT0ZqamGp6YapXRZyPxKvIq/b9Zna/u4MV4YN5+z/KX9Mz 9X6NCk8wgFNDDktai/trg0Yxn8f9/XycPm5zTUsW+rFwSu/V/pqYHr28/HztejHYlDImZiL5pwsp hpaPRlswstuUkpXVVf2qsk49GOuWZiTEj/B4dl5tPMjkYmvi0MsSHjH+47YlMzMTLjzotswop0Tw lwlS67b70c9llys4SorTX/tEmVQndrizsTY8FeD/0w/6a3S6hQ/Lw63Sp87ybldCGiF1Il9u32Y9 zQ+8DrCVTmYShRUH0ycVqD8oDh05dbIrZoVEPmSDV5xzMzlWpmZqL5KliRRiGNQ/h9caM0afHSnJ xf9b1olaKsifY47Ph8MT0/roxYZ4v756IVmIj2ugvXYOFgj6JiPnN6Uupr9mJ4JchRRHuNx6q9cq JmTIFxF538L2BgYGI52DOy8G47g/lTJqcHwq645MudMuV7Tw9P5ver+zuwwctUObH0/Gw+MucRlD TG/cFHjBIoFiieyJ1WRwjEb9945ZJmE8nz60NOEzdhaXNSWI5r1AqkmBd8acrtQuVZl19h2WoYkm mXzTpdfG6rIi2ORgx17uvCPfky3cTcj8EuSZl8DyOHv/saUHNroxISbWrtc/pqXBPTE/KYKEcibe FZXPpDIzuEYF0dBq3ouDZrdfUxXytsZKxUBj2xVBARAr1Lnli9ZTankMN27ZEXv4FUmMZ+A1ZWgj YGDiITK9imL2um2PIRKYjcSWbB4txOerfFzHVvsZ2atXswQtaqvksZ+p8BrmqO8oO85CL2ptww2z 0xfpexVssjs2+Tansz6GWO3fW+1qZE9naZt1jkE8vSXsZTSOsILRhIhjxgP56jDYtzJy5yXEsEv6 enZ0MW9s1sbJpQmjdnvy+GhgixbZDLb5YKOLPvYs0cF+SFhVZ7Xk4dOF7ov6MtacTvOcVxWiwmgd kHk8OCa2+LjCFVkk+WSGilypJPd39BJYayyxXOod2ONdzq2a/Sup4tbUu0tlkMjS4utcxd+53VZq Y27trjPN2W0tJshhQNyZI/oq2tGlpOc1sEfQujs1NfYdhkq1W8lbCLKRVVqcKbpbVU21+yG7a4Y6 Mk48pBDD1rQsi1qgylrbtjIFrQoYKDkMkZAMEX4Tw+5M5JJk6CXtPVCPDrZplK+OyIp0wPV+cd3a XXB6H6ssP9LM6ZmJmf5f8tkn9jBd/k+p+7GYn53n8fyyK3qYiGUwD9j+FqwvDQeVWdYyexlYXMaK Qwl+5yMmRoVDKWMpybuool92/o6otiH1SssF1Hn+HujAW+2juH5KUeCiqhQh+Q3pTEoifkTEvwUY PHn8M4Pl3nwPd4jRx4eA2NqFJ51AH2mBnTOeJvkFvRwz8jqi3RyRRyoUvZdPSmD+ln4rzqCvCdZP t1OH0qPJw7pM895HDzd6FZzBUEqGnkfmewsSNTR4gOWR3C+LA4KXAuYTp/4llvWGiuxJDEu6DkVj yqxh9aB0ZYG37NxIwbT3OaIRsT4JZkwuSMiaCdkamLb4aWT9GTHDlYQpjWRGWrKkje4KCJwgh1ev aDwXIiRw5iLCujf1BQDzEptGmDizysQpFZUHKoYFVEQbdD18fVZhvW2c2L2dfS6cdGD0jnjWHP1S 1zSuxshrRc59Jgtgijpk/aDCOtG7cVSRNmlnXextRLONIEntI9XCvFnL+WlnFXoim5nUh3iaG0mH Jt57d4iXBj8MVtthxtRUUGkUgZ82uUg9qUxWkdIRSaKwUbjabhdxdvmNDwUv6UvN18viuO9c7Uxj NnDy/iN8bo+SXxamfjYwl5dyhDxQrnuh59zU5YiwbUtjwvdkaW12OavcLZQ0THwEf+d0CkYckcHM Nxj9yW3r0vQH7iOyw9qbjGgfolEb/pvlq6SoHZLiRSIly0hTaj3DRnLNSpfER8+PRZ2r4wSkkX5k WIanPHIRb53MNSY7+Xjg9bdn9T1uoLcjuY3GTy1+Sywq79UDqt2TxO9g5KI5fFijt8HqaIzlqOah WMkOCsa55X2xxSDUFoVRVbBGhubOvddMXszPcY3f0KEHHY2cnahbq0e93+EYM0V00np2Y/PTdeek Y++v2ytDN1vfSIozGRYFbJs6olbKI8oy4LH6JQwRcuMZ8XWwhaRcLr61RYJX4ll3NXxuRf4uNRWE wmJmFFI2oed9zl3LY6ZhQocaR97o3d2O3U/fyDQ7+qH/znJinu2shDSgwiMiSKIjTWyGvomr8Kdo bkUjBjbLImPH8zq4IzPgq7XP6kJyRDS2zEwqai/z4PS/DzfcfE83eileOFX1FMONVzNBMGDF8Ekb 9qaBzNToAremz545ZU9eWYrfPUlELsErRGnrOlVralF0FGR8cuvmtL7pZo2d2Nsrq2EMOxmaGNkR KcdGmqj3uxZkFpipxwRRZsfT3LOb3l3yz9438sRRyNkS3YhAaTsILNCpInoF4mwXx6ADS8vRh4Sm 0sPdHumZme5V0K0UXXcqUQ2I1IbnKQ/5/h5orG5hGF771rrDfJQudhmtsQu59AqnJijgsFr0wvj0 RYvaIKIi9nSjH9FjMYzFrZNDb9LRdOwvvwHGwSuSbSm/hXZXgMukOpgj34nL2CKTBE4IpGxNBhLH WFdyHBwVZvNggd3wjafX3XJIlrhZAjHNvECRwLh7YZSoBQWFtMpEoOZQiCpi0NudQExIR+LRSJ7x qv1Idmq1timdknyQ2qIY0JPRiEwuabVxN7Acci/1UA82hUr+y0JiNmRkf+LuSKcKEh9O0XZA --===============6427675574369730780==--