From: Oystein Grovlen Date: July 26 2010 12:11pm Subject: bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3217) Bug#52344 List-Archive: http://lists.mysql.com/commits/114340 X-Bug: 52344 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1464188874==" --===============1464188874== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/og136792/mysql/mysql-next-mr-opt-backporting/ based on revid:roy.lyseng@stripped 3217 Oystein Grovlen 2010-07-26 BUG#52344 - Subquery materialization: Assertion if subquery in on-clause of outer join Problem: If tables of an outer join is constant tables, the associated on-clause will be evaluated in the optimization phase. If the on-clause contains a query that is to be executed with subquery materialization, this will not work since the infrastructure for such execution is not yet set up. Solution: If a subquery is executed before subquery materialization is properly set up, revert to traditional execution of this subquery. @ mysql-test/include/subquery_mat.inc Added test case for BUG#52344. @ mysql-test/r/subquery_mat.result Updated result file with test case for BUG#52344. @ mysql-test/r/subquery_mat_all.result Updated result file with test case for BUG#52344. @ mysql-test/r/subquery_mat_none.result Updated result file with test case for BUG#52344. @ sql/item_subselect.cc If subquery materialization is to be used, but the proper execution engine has not been set up when the subquery is executed, revert to traditional exeuction based on IN=>EXISTS transformation. Moved the code to revert to IN=>EXISTS transformation to a new method, revert_to_exists_transformation(), in order to be able to re-use this code for the scenario in this bug. @ sql/item_subselect.h Added new method revert_to_exists_transformation() to be used when it is detected that one has to revert the decision to do subquery materialization for IN-subquery. IN=>EXISTS transformation will be used for the query instead. modified: mysql-test/include/subquery_mat.inc mysql-test/r/subquery_mat.result mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_mat_none.result sql/item_subselect.cc sql/item_subselect.h === modified file 'mysql-test/include/subquery_mat.inc' === modified file 'mysql-test/include/subquery_mat.inc' --- a/mysql-test/include/subquery_mat.inc 2010-06-30 06:35:23 +0000 +++ b/mysql-test/include/subquery_mat.inc 2010-07-26 12:11:18 +0000 @@ -1008,6 +1008,33 @@ --echo # DROP TABLE IF EXISTS t1,t2,t3,empty1; + +--echo # +--echo # BUG#52344 - Subquery materialization: +--echo # Assertion if subquery in on-clause of outer join +--echo # + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (10); + +CREATE TABLE t2 (j INTEGER); +INSERT INTO t2 VALUES (5); + +CREATE TABLE t3 (k INTEGER); + +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); + +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); + +DROP TABLE t1, t2, t3; + +--echo # End BUG#52344 + + # # Bug #52538 Valgrind bug: Item_in_subselect::init_left_expr_cache() # @@ -1113,3 +1140,5 @@ --echo # End BUG#54511 + + === modified file 'mysql-test/r/subquery_mat.result' --- a/mysql-test/r/subquery_mat.result 2010-06-30 06:35:23 +0000 +++ b/mysql-test/r/subquery_mat.result 2010-07-26 12:11:18 +0000 @@ -1355,6 +1355,35 @@ # Cleanup for BUG#46680 # DROP TABLE IF EXISTS t1,t2,t3,empty1; +# +# BUG#52344 - Subquery materialization: +# Assertion if subquery in on-clause of outer join +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (10); +CREATE TABLE t2 (j INTEGER); +INSERT INTO t2 VALUES (5); +CREATE TABLE t3 (k INTEGER); +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +i +10 +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +i +10 +DROP TABLE t1, t2, t3; +# End BUG#52344 CREATE TABLE t1 ( pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER, === modified file 'mysql-test/r/subquery_mat_all.result' --- a/mysql-test/r/subquery_mat_all.result 2010-06-30 06:35:23 +0000 +++ b/mysql-test/r/subquery_mat_all.result 2010-07-26 12:11:18 +0000 @@ -1354,6 +1354,35 @@ # Cleanup for BUG#46680 # DROP TABLE IF EXISTS t1,t2,t3,empty1; +# +# BUG#52344 - Subquery materialization: +# Assertion if subquery in on-clause of outer join +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (10); +CREATE TABLE t2 (j INTEGER); +INSERT INTO t2 VALUES (5); +CREATE TABLE t3 (k INTEGER); +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +i +10 +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +i +10 +DROP TABLE t1, t2, t3; +# End BUG#52344 CREATE TABLE t1 ( pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER, === modified file 'mysql-test/r/subquery_mat_none.result' --- a/mysql-test/r/subquery_mat_none.result 2010-07-13 17:29:44 +0000 +++ b/mysql-test/r/subquery_mat_none.result 2010-07-26 12:11:18 +0000 @@ -1354,6 +1354,35 @@ # Cleanup for BUG#46680 # DROP TABLE IF EXISTS t1,t2,t3,empty1; +# +# BUG#52344 - Subquery materialization: +# Assertion if subquery in on-clause of outer join +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (10); +CREATE TABLE t2 (j INTEGER); +INSERT INTO t2 VALUES (5); +CREATE TABLE t3 (k INTEGER); +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3); +i +10 +EXPLAIN +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3); +i +10 +DROP TABLE t1, t2, t3; +# End BUG#52344 CREATE TABLE t1 ( pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER, === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-07-16 12:21:31 +0000 +++ b/sql/item_subselect.cc 2010-07-26 12:11:18 +0000 @@ -326,6 +326,26 @@ bool Item_in_subselect::exec() { DBUG_ENTER("Item_in_subselect::exec"); + + if (exec_method == EXEC_MATERIALIZATION && + engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) + { + /* + We arrive here if a subquery is executed before setup_engine() + has been called. This may happen if a one tries to execute + subqueries during the optimization phase. One scenario is + evaluation of the subquery in an on-clause of an outer join + between constant tables. Since the subquery will only be + executed once in such cases, there is no reason to do + materialization and we might as well use IN=>EXISTS + transformation instead. + */ + + DBUG_RETURN( + revert_to_exists_transformation( + ((subselect_single_select_engine *)engine)->join)); + } + DBUG_ASSERT(exec_method != EXEC_MATERIALIZATION || (exec_method == EXEC_MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)); @@ -1181,7 +1201,7 @@ /** - Transofrm an IN predicate into EXISTS via predicate injection. + Transform an IN predicate into EXISTS via predicate injection. @details The transformation injects additional predicates into the subquery (and makes the subquery correlated) as follows. @@ -1883,13 +1903,7 @@ */ delete new_engine; new_engine= NULL; - exec_method= EXEC_UNSPECIFIED; - if (left_expr->cols() == 1) - trans_res= single_value_in_to_exists_transformer(old_engine->join, - &eq_creator); - else - trans_res= row_value_in_to_exists_transformer(old_engine->join); - res= (trans_res != Item_subselect::RES_OK); + res= revert_to_exists_transformation(old_engine->join); } if (new_engine) engine= new_engine; @@ -3401,3 +3415,16 @@ "" )); } + +bool Item_in_subselect::revert_to_exists_transformation(JOIN *join) +{ + exec_method= EXEC_UNSPECIFIED; + + Item_subselect::trans_res trans_res; + if (left_expr->cols() == 1) + trans_res= single_value_in_to_exists_transformer(join, &eq_creator); + else + trans_res= row_value_in_to_exists_transformer(join); + + return trans_res != Item_subselect::RES_OK; +} === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-07-16 12:21:31 +0000 +++ b/sql/item_subselect.h 2010-07-26 12:11:18 +0000 @@ -412,6 +412,19 @@ friend class Item_in_optimizer; friend class subselect_indexsubquery_engine; friend class subselect_hash_sj_engine; + +private: + + /** + Revert the decision to do subquery materialization and instead do + IN => EXISTS transformation for traditional subquery execution. + + @param join Join object of the subquery (i.e. 'child' join). + + @retval TRUE if error + @retval FALSE otherwise + */ + bool revert_to_exists_transformation(JOIN *join); }; --===============1464188874== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/oystein.grovlen@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: oystein.grovlen@stripped\ # 4w6po6kmvscypmr7 # target_branch: file:///home/og136792/mysql/mysql-next-mr-opt-\ # backporting/ # testament_sha1: 151e2b886bbd73b11ca6a2d5da5e363019803f1b # timestamp: 2010-07-26 14:11:29 +0200 # base_revision_id: roy.lyseng@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWYSQpuEACKRfgHRweff//3// //C////6YBD+T18yvrfTu3vnK7VRfbPsNy7zAAkHq23O677agFOjK2ANtkbbQBkRNTCnpqbRGnqN BkbTKNA0AAAAGgABogqfqPaSeqfkk2kGmgAAAAAAAAABkp5NCT1TQabUCYTNNRhoAjJowQAwBNMB ISggaTFMm0BPSZqJ5GUyepoZBpoGQGhoAEUkAmmmQABDQCnppPTUBqeggDTIaAaZAqSQTTQAjIIN JimRmoNBoBoZDYoeo0MnpKiqI/dJF1AwHoLsH/AyFT+gsP2hAhSDvKB/IZ2wf7D9w/cPSWDjG0Wo HIM3V+nPmeIplvMdmKQ6sklNEbFjvhylX5EX0jR9a4haxWwf0A+EqUqkmJPafs0vQSQ0u51WpMrz slXoLJqm2Zi0sc1q94NAOEJi18JhwSF7OPPnsyp502nypxOKAQf06jXFSMthS9kBCQWute2ASGOI FcDYGXGo2rclCPgq1+9lLD9fD5fM6+mvyEPAoHaUDUnjKE9LbS0/ZTu2x4t2O4XB9/icq8C8PAw8 u+93xb5U9xcgm2mMbTbZ3OcP5sw+SKM2PJcQWwscWiwKYUWbIXNu11OYy97Y9MDlDoDcNvHYG3QA Y+hRSVNVBWns7LtOJoP9nsHpaNW28LuwNYBKE/OjaHsBgYue3EGINGzayb/bu2DLNjnRZhIiTeYo t+UAO9q2PetSmqUzU1IMwjSlZcESSaTgutHYt4Az3VIi+3/E0bl1mEJdbVkrHCyimcScK/drGUyh Yb9bP4k1/iwYOFuzbldUbrmV1ksF1nNDXWVGAcUBKdjOQ1xccCMTCjhhIRVFELeKJ33X00VuitjX d0v3aM93A9ge0sOQWOJcXhY3GumvlTkTjcvbsdnK/avX4Z2tdddfhv+CrykURhjB4QwB3N2orSH+ aK24MZDMOb79bQTUxNhGdNiZetnS+edfYLLlzYdmHfBXsQtHvpsTaY17EL46N6vT2/P2d1XMBSpE eRR7xV36Xf8PG+96h8oWYkKiaabCjRVuuLi4kOBPAN/FTkU14seKdRYAywHOqJvTnoIiOth7B0sP Yg+BndwBD1u2rPFeQD5kzJPNPLyGWgMd+LG7x7uD41nXaOIMeJuwjdFdlJtdbueePcDUjGsGjEgO nLSAtSaiLFTtgnIHeOXkSq0vvvFYukI9Q35zZwpZEk3nYHG0OSZJDKcc5OhuZhkKRZIkqxPA/N8N QHCcpZ2nNQsaLLVQrS+leK1itYJS4T5LyhYi7BNd9m3JnFX40rD7HFrxu2efyTkT0E5p53M0TXLb ZqQoYBL6iaYMqWDWevgElWk9+cZJ9zyPP8G3oHANGGNuwMUqM9XK7x56dHDtzYRuxbr0hhjaWu8C ZmLrTbhXOOQ9by5J4nHAZ6TYctCLvLW9dtrPlem/k8qsGvinDcqaHPLpfoaCZTuTeOFpklMHCATV RRMFGkGuFXNQNQ7MfhspAKRm1ZpxiE3zKF+ogmeNvAvAMJZpReW34GFLX7MhvssQoMhhcdThuoOp oO+ombmwtathjza5ZZwmosGM4p1S/RFWW8mCsmWxL2EvuZ9ecTkwzXUTgVNNdIiAauRQjaYIhA0w IGhIcgFi3bFomikPJGByKihg3Ja/k9Dimhx2SS2a+eVlCTl4zgbKmHYy0Lb2LyTOF1GbxZrXppuu S7CZuamLlyXL5k21trwS+TQxZO9oZrLYy05PjqbY7uYeZo1oWqXgGNrCBvERxIAxuCEZlz9CDccO WLjGU9f5lJqmourLQig2ljOGQoN1lUG841Hq6TOc+vAymqs6Gx89z+mSkuNsGi010552RRtANSUU DWRgYRJxFZJ7aBtiU5uaLte2AJWFhYYix624yeIBe4n6gCgy9pRZXVKihHjwTMNQ1ZWQzDRNpOpv oAx1NzNl1TVqZctLS7Zu0q8dk062jCtV+A5rPKzE5FMmk8zqjAgA1YYyoSBXZKgsLuvc6uDCCNlS UChwRQW9gXKwydYJfZZRGaVRRgTFX6QkVy8lghosB8ROPa62h0ZMmLQtouc+lWnPlr43bHFi14te 9fz3O+TwA+6cZg6joK1tJDFFw20apKroOLJI5JeBLEvOitiugGIUyZIKknjyWFY0FW4rnf+nluYH E4mkl7uxLk9Bs/HEq3d7UNsbbbN8V6RBtvEHmDl+IWmPR31NKHrmc80fxD1j6/9fYPpJ85K9/je7 WdZ3Hz1aKfWH2p2mVDza2JKIZdyfwS8y5E+j+vWGi4m323wPe6WDImUDBPu3E9hPtxxGxJrcXulV W8a7HBK20DkGUSicVrvuz8X/rE7BgH+XdawVHLhvFxHHonG7upyp9n1LQasSI7h+8OcDANu/rIio ZA6E7+FhdQUFEdVRpSqqGiyWI3ERxHvQ06AYawsVxzOrNOoaibN6XxOZMtd3Em/oDxF440ciU7Lp YVOewOgau4bK5p1pprRUcxtDFOALw2kuJShbrTLC5cmj7k2jqSZERt37pph3awzQ347Nndjd6n9q 9nxW/mB84uqCgvGyNJe1BEwaaX0kA7RfbbSFckXuChaBBWet+sXe3/ra+4tPmMwHqGKejV6T1P5P ofRivevVOa9yhuozdv1dUGU20tNbc4tbVzkxhr6exTmT7zK8xwTHIup65aQudNc18AyZYSsyzhbf fn34qEwbPZv5TCRrSuZSmFWFVUavXOIvh/f5NL208Tvnjc54njsxdrJZ9PZuYqWeFGme1xXPBT1e NtdPTaeibId9Ul9ipdZUqHthEkTL+uBOfT2vJk5t52NxqOl7jcTGt2LLN6dT/Eanl4TJoY9V+/ix 9wwYY/84zGOynLCOQEIHk1nS5j9ucR4fYTr7PK4VyxECa3QANaBYFmmBFoajSpqPkZWsVtPlx9HO bTm6DYZ5eraK9vOzytpwraNHDGJaYb/N8c+JwZt7e9Dme8yGw7TgVHtvmvMhoq5t5lWsOLlf52h5 uvLNZYDYDZfzwpXKVV+1sMgVgVAu2RG0t2VYXBIFgYsKgHDTm9ZZitEkUDssBfUgCBCAwkL2Pkmw KJ5EG7hr23XQZQZ4aVDaVcUZS3DrF5ReINbtZNJPBSbIVlWbexynPrnncXwuPyRxeGwkztoR037C SXVER8bp32xsyGYz5uHgrRoterjeIVh2FBhpuEOu3ui3zlmBbvSsXFAsLQdWEo3s6BpLE09BMMSi PGJmKACg36D71pQBmXQk7LAulSRKs79eQklqL7gEb2jqjktcCsEsqSdJL4blnsDyKrVs7r9k/wgQ 5n656GPcyiDghRej2iGTqHMH1cCUI0RReEFy9R03nh1kO6dZwFxeUXHGi8mzwYzoVLloyr0vUVv5 jtc24YGQ+b6PRBwJ0MpzkVSRkerqcaVUNWqvM0JqM7oZhrqHIwJDpdNAHokFR6IF5U8XA6QoCUjz IYihmuF0dcG4pLszJXRb7SqcscQSPrgwbWdJg8nQ4fE8QS0A8AqIg9XW8Oxt1g3ZV4Fk9eMfE2Qg ROUZNsAVry1vXrBZBUHBas0BIhCFkgeNzvy/XKb8aENwGfPblwc99xRzgN3d021lYJWQBEMQMLDq azEQEQxBGmjabkPMCn6MrolJlWGZtuOtl8aGxYCfcBEXvc6xprq8fiU58FFctIHu7ZmlThVzlpAn DXAEdje8kznvB6hlCHzgHxteDyeZ3xuIglKW0CCDvDePuYfRo3ND8LHGBY1bZ9orkqjCCkLJ1MiT PpfyT0IWN5MjRI7o6rXxbqZ1bwuqKiCne8emMSYhZU7akGaeUIywRiF5B5RfCzgwhWMjSuWqgiV1 zPleNBQY7vceLdjX5Mh0vh7gDY4vMArT9KQuG8aYJPNUaqJxNijUkKgmSqG4GVpRJoWkCICxUhSI CRNahUVTVzLT0hRSBqCf74kEJBFRZMhWjBxZpUeT4NW1PCQ9Z2+DlMm4kGUCKGdKqibAai3VxTv4 ziHkREF55JaqX0xx0bY7g8i/DEb9x8OwxXeiYIynWH4QuIoe+v1hR9+fFChdAkgIRIc6TyoQ5PN4 mAcaSWpDR0oEITzGZ1yQNZQ5JL7EkkqtM1JMv42qB7j1WB7flzuV4bBDpfnaMemyQwL97Wv0o587 8R515mkA3gHbXZ4jh34WOxpPoxl0GWpqwcTja3cYmf7HWNLyb3wcHaJ2MBoaidOYmeAYhAwuhIXF EmIginJ5PJoZgDmNKBDCFVcyfqhG2jVWSPkbnuyG8cat76wUtQM3UTBKDhBHGS77m52szMDijLhc X8bKSN8AFhVzPw2NuO1t/h6IGA6hzp9b7nbo0I54TQyvm3MNNCGxsS7otouam9eOgphHGDjc8lK/ zKxjGcPPJznJ8wY6c/E1lClrLV8ETuoaxwTXclWMLxe1v01LaONsXomsyKe2Yc67YC/P1i8DzSSS STA0igw3kvqRwJYqDdEwTRaAFyCzcA6NKFqayvNg28UjHHNKFZaLth5pkV7wLME2i37ZtAb2Rmo6 YoMUBpOw3wztZYNLDwq9mITEjSFw5QWBgFggBnZ2DnUNMwzpJqGaBxjIaxrWqlva3JB5MYqG/Beq pnZtmAPm4Ipwwk3+TTI3HxzsNRcR5AtFRxtJZGGcOwtsN7fN8li1aIUhxpsRETQkrWdxGqEd4FOh Cd0jQFsf8I6Iy2DEEtGWdDhLhrtto19yaZPVN6+dSs4PzxSQ9EFl1IeD0oJQnf59w9E6vvYA4HxO t6p+LLCJ5l6Yk/Y3bRxLAZfan2uI0m44FfFnU+lukhOIagDrZMxB7QCOLTbHVHysBygMbgzNOxKO Te2jrA0Ox+lyimZgX4Dx0NXQ7mkcpPBbChKExpE4wLfqcyNYBQNSaxPhYgaBdDW7W4hHEwvZbsep v3xH/xdyRThQkISQpuE= --===============1464188874==--