From: Oystein Grovlen Date: July 27 2010 11:05am Subject: bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3219) Bug#52344 List-Archive: http://lists.mysql.com/commits/114430 X-Bug: 52344 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0548655886==" --===============0548655886== 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:epotemkin@stripped 3219 Oystein Grovlen 2010-07-27 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: Do not evaluate on-clause in optimization phase if is_expensive() returns true for this clause. This is how the problem is currently avoided for where-clauses. This works because, Item_in_subselect::is_expensive_processor returns true if query is to be executed with subquery materialization. @ 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/sql_select.cc Do not evaluate on-clause in optimization phase if is_expensive() returns true for this clause. This prevents executing materialized subqueries in optimization phase. (Proper setup for such execution has not been done at this stage.) 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/sql_select.cc === 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-27 11:05:20 +0000 @@ -1008,6 +1008,33 @@ FROM t2); --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 @@ DROP TABLE t1,t2,t3; --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-27 11:05:20 +0000 @@ -1355,6 +1355,35 @@ id select_type table type possible_keys # 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 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +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 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-27 11:05:20 +0000 @@ -1354,6 +1354,35 @@ id select_type table type possible_keys # 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 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-27 11:05:20 +0000 @@ -1354,6 +1354,35 @@ id select_type table type possible_keys # 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/sql_select.cc' --- a/sql/sql_select.cc 2010-07-26 11:34:07 +0000 +++ b/sql/sql_select.cc 2010-07-27 11:05:20 +0000 @@ -17517,7 +17517,14 @@ join_read_const_table(JOIN_TAB *tab, POS DBUG_RETURN(error); } } - if (*tab->on_expr_ref && !table->null_row) + /* We will evaluate on-expressions here only if it is not considered + expensive. This also prevents executing materialized subqueries + in optimization phase. This is necessary since proper setup for + such execution has not been done at this stage. (See comment in + internal_remove_eq_conds() tagged DontEvaluateMaterializedSubqueryTooEarly). + */ + if (*tab->on_expr_ref && !table->null_row && + !(*tab->on_expr_ref)->is_expensive()) { if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0))) mark_as_null_row(table); --===============0548655886== 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\ # z6so1citzuwpxb2v # target_branch: file:///home/og136792/mysql/mysql-next-mr-opt-\ # backporting/ # testament_sha1: fb64f0b8d179972a578db0a64a68122385657967 # timestamp: 2010-07-27 13:05:29 +0200 # base_revision_id: epotemkin@stripped\ # 97mj9zm97haxsf6t # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSR/G6oABz1fgHBweXf//3+3 /+C////wYA2eXvfbDX3vb33NlR327dyG72t27d3eV7x13T1xSqu713ea26lezykQRJkNA0aaaGQ0 Bk0AGgAAAABJQQ0Bo0mUno2qZGh6j0jTTQAABoAAJIgFJtT0QmZR6NCYgGmhk0M0g0AMIBEkk1E9 pPSp4Ez1KfpTDQJ6mnqPSDMQA0AjJoIpI0TEmZTKbJgSYJgmiMNIANHqaAHqBFIgJpiBoaJkFT/U noKfqn6pkehNAz1QAAEz7axM4MB3K8T/AyRT9FfqZCFQM3jah8hm2D+o/EfiO5tG0blawcg0b+Xp 44THydHPiqKuAmLRg6qe95GPABWTLjeWHZCldUUhRFYVhMdcyBuADTAfgMHb3ptVQcO/u9bOzYZG ZMplNAVOjUb9NpUGdRyMhEX1cKagn4ZtbaNMpQVow2yuvK/9XcOQS8vR6RMTIIEzDSNMEJFXen/M T2A4gFvFNQWAPxC7jSfqnwThSSEISSPX7D9bsISGogsw7g0szIdPCEdornDKG+HehAKAG5nGBkMz FGAHCAdQfUCurtwAMYBkAHXIjQHzBgKLdagUDl3zb4Fxb3W20KZJ9dQgzkCUSPWoqCOxgZl3cmCS sVNYAoYpyROvgUUTKxefHEpCKseg3Aamhf9EwPcloUl7smXgaUwkyDSjYbIcJ2nPz1Vc5YVyvCJf pULI1iRlD7AH3BoHIANDhBwPAGhWoMupqYXCLdWEMaYjK7xAx0QOmBhQOWVdirWbbem0UidTEgcJ ooLwhaBaIhqAQfEKfhzMgyMI9SPiLG8b0rzQC+Cp/wHZ5+QDnAPYGHHzS0y+zHJyckOEvRdSSlsD QOvOjUjnrIiA6jYw9BT1wHq/YGdfkWXoT+admc4olHSF3b1cD8NTtr3IYgrdovtw4B1wUlwZEyti TVoAsRoKtBlUH85DWbHMlnvutatgk53DiXOLIBxQtbZgXC3Tb3OBzIU3DShIQFrZBXA9uecp2SkB awr8zKbaDRdQE0GdY1EDNmMRSxBiKDYYGKc8Zv1OAPIDxDuM7icjmmFLAJMgKhxnr5QkC1EPwzD+ dDSksBfWXceWJ62i6gopuuMqTicLLwwQveJmsmk43/ReNxgVbByDThwXQGk6JnXyb7ijkrc80ieZ IL8DjF3JrMjOaHUK7HoPUd2GiOFlIDZTIKVbNQxALvra8qh1wjynzJOWF5pCZOuYtJTblXjHItbP jnZfaBSW3Hi/Rga5tzXa8RbMmvCQTxkAVOoUcFFqoaUNeX4lGOyQpycJhKpycLqcVVkqmKZulk+n ZWwOrFri5Sg+IJj4i62AoSLLKEFmUhLStDQuuGokbAwoKUN0Bn3kx3GBoscm22+Xn4BMeG60sK5u I8m98uyHd6HLsuK+kKHluHN0pTIgWqvXOhR1CaimWxjiPQMoxQYgWKl0dyT1UJyHnKszMoHlZSbV QvNtWa/1/AcTDW7bJTRkD75hTGBQRNqJEnOCPC42cHtpOJfnUssOYXhPUayl2C5htpdMY32kiiJb A2umQ1A1CysOVpjB2ce1YgptN0Il0ztDUqDGYVNd52U28oNpArRKNdTLNU6TlSRAkqVCyfN2rAVM dduJJhNIjmTrIsJ6pNjjAuJ+lCrNNNi0T3JhUxhWEXDslcWmzLMlRXgJ9BvDlvF2RBM6wawvzzhB 5EtW9le2csTGHAuMO4KNFsYvE0IFVqNF0PcjLstPUxhIURTvwQjXA6VkPgiQzspnWBROuyKOpIYV e0dmS3jiAhwTYhQySb/Km5kF4Je9IB6gPqB0A9IOXV1Bi7T1M6GPwAdou4FWAtIEwAdK9igEZNeC vAOgHCgBsD7QA9r8x9INgMRcAObANWIyAC8AqRgGWQHACQB4gFgElQigEXAcwBAAHgFKARACAAfk FJgDQBAC9gEYCaEBNoD/QDEAM4Awa1Qqv6BUFi0XikC7AWMF2gBkCKRIExIgAQANYEVWnPcjhvAq AKFYAEwC0GgEkn5ah/0eMDBGpATO1oXgYxDI9WXOHGUkuSk7tULpQpgRixKj7DlpzuKIirCkLDQ4 SgLk5DmOY9Zxkg0EzouKEzvIJg1HtoxV/RjBMqNo0gSNn61P/G72ShKZ1b2ey9HndvNoQltuc5kg H2THJmh9dyS4A+9YkGFlgkBpB8N2RjMhcaDpEohnYn8lM+J0bJMrNZqfEvIPyTPAoz/w2ich+4kU IvwhbR/VC+XBxLNNW2kukZa9RQ0aYhj8gyZtIjyTaXGroUymz7DUlXALhONSGQiSva2Hi5VSrV2S 97Cc/pcohhTJcgxcsI5U27dHEVniRrIsWO+LXfFqz5Sakmmo7GZ5z24GqzolLzmUsHibHW9Ogvs3 4WA5kMeLCVJ7EiaHDzUHCjIbcUXyEf9RcShNQbXeGNpQyoV4EhJ4i2fofHl3luqqLJOmw7WhrKHW VnWTrN54HA9U9U6tMXQSlvyANAErDOD5F8tJbx+KaBBilv2Zb8USHNHMTqvFcb27OaEqWSLQO20w UpwDK2yLf9y5iFSuRA6ZLQbmSvinKlbkheieT+PQNZnnPuJ/iZiYyOs66EoRexiTliO3GLFbWV5L +RhZ7wQTkokdl9keHhwU1CGjIdJI4RCQPsY0eKHUgn8Dw9DmV1HmTN4UHwOnq6GQ32ZR3u/a+Sa3 l2pCST2uN3xsKQZw3tFGRknLey4XN3Lcwh0yZVdauml3OZ5EiE5GlotBwOJKvUHLg0Jmq9mJsZ/G CGCL25pSut7HUAUAH7AtfR4e52KaMBlnoH3m2Bd9U6i68K4TsOLsAcGpbaoCMsMoA64oV0pk1ZSJ fKkDJfiuOZjbj0c2uyoXJEQMLyNgWsEMEEEzvrq8wFWdLMxaGlOrQnos0gMAST297a/GsPl0aG8c Gp3UAXBlgCNzjbvcDsHmaEkB1Nduh7HO5lII8QyT5MNGTQ/X30Vp2dINbN4cENIERSrCshtEILx1 gEi5SbBt2GJ52mhW4MlCUmGbu7/dLUAcKhSf5QonE9iMQvUGUAXJyyFiW2LBTPRuCatFhyXp1dYr eVbYPR/kOCgVg5pUCFLUiGvQVAcWUrc0DSUrTVsSHCbE17gcCCmBJqxQM/Eu+tZHBQRxbe8scM9M 0pk0tYyokzUEiHg42zc/o8HFgnAan0ltNUhes7cWiPB2jU+7iND87UHy1i2CQLsIfmXonnmmAvZo ueIaUIxRJiOFJZASWwetw9XyGBITRS2DoxqmmB9Z4DCc3rzPC4uEX2N1cJAPizH3g/BL3ure+L3B 5s/WPQnDxOTgbS7rlhW34joeQudzP0cXW70DxYCfquELEK3JYPXKRCW3uxpcRuGpUxebhq9TBoG1 xjnhbsjWCYmmWbhZVK5ZpyckVsHjKtHKZ+kVZ72Y1Nczo5UtGA7R+589GnCExZXhE0dUSIV1j4jL wq6boSqq6OuQqBZh+djraHmcwa/5LuutpwGIQklW1Oi7R5WpVk+QXU5QlqVNY7LOekCj50pWmpdy Nnl8QuseBO3MaqqqtOQlWs3XBvL0pkNb8TQMU29urayrgBfehhnkl1VCVK2dA5vJM6vo2Zq6MDUE Oxhx300tsNEydJUNLDxqz7ksRvDOOwCwYCxgDddfCJmsNkpvEgL9lBUXUxMarlRZFnWCAtkuUppJ aL0SFmoJouDzeAXBMSNyVJM7oAvnd7+BvTAnTIkkSUi+bYagJusYlNkUcGS3yOENe8eQ+9qQLpvC kY0PcMInnh4JkPO96r3cw4d2JXwYdL4O40U6WgNMkfrZFYwl/pxvgLpRofsdj9Maw0RVY0FgwBza gC/qyA7nZ+dgC1wdky0W5xDaBe8zjF8HIrMIBwHR6nFqZjLdEKxDIYF6nLeDSO0X5Xq7FeRqdTfm FiSJ7A53VC5vB8Hzf/i7kinChIEj+N1Q --===============0548655886==--