From: Oystein Grovlen Date: August 17 2010 11:50am Subject: bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3227) Bug#52344 List-Archive: http://lists.mysql.com/commits/115924 X-Bug: 52344 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0045517334==" --===============0045517334== 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 3227 Oystein Grovlen 2010-08-17 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-08-17 11:49:48 +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-08-17 11:49:48 +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-08-17 11:49:48 +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-08-17 11:49:48 +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-08-13 19:19:23 +0000 +++ b/sql/sql_select.cc 2010-08-17 11:49:48 +0000 @@ -17452,7 +17452,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); --===============0045517334== 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\ # 0lfbjvv2m3e1jdom # target_branch: file:///home/og136792/mysql/mysql-next-mr-opt-\ # backporting/ # testament_sha1: 1cd103a656517884f9a9dbe6daff8c0430831866 # timestamp: 2010-08-17 13:50:06 +0200 # base_revision_id: epotemkin@stripped\ # r4ghhxs82qbss3ob # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWee7M30ABznfgHBweXf//3+3 /+C////wYA2ffb77ter6fW7ejdbQ2Prp5tA8g2x11wJ1lJKFkkMrRCaSZDQZGmmTTQAPUAAAAAAA ElGkE02hNNqnpkSDJkyaAAGgDQAADSQTaammmgNBoAAAAAAAAAG0kRDTE0FPBGUxAyaGgBkABtQA AikQQ0T00JiaMCaCnin6hNNlAA9JBk0xqBFIjTQgBNMIyp6T1T9RlHhT1PUbaBJ5R5IPQ1PKWnuu E2AwH8ldr8xkin6K+tkIUBtd7QfkNLfH/Y+o+o7HAOAbFaweYZ7eHb0QmHd2cb1FhAKjNIOq+5sC O+BiM2t4cqxV0MCoKYsiyJHaJIt4DPAdyGjLneSIS16cWCodg2PWOBwQkofRsWiCThMyRKNmIEFE oDBGWBMpOrCGRJPJXBO1V5YQ2q51e2IwIq2x2RMTQECdIYRpghIq3J/dp+YGoAv50xikklrSnoE+ 6LcLeE200022aOE/U8LG3shPUSkbIirXCWzropprpL+0OgOwOmcwrAOG0gISIgugAkEw+YfECuz7 pgGgA0gEn0J7A9QgCt4RWFYefYbu19vH+I2DUUyzs5xImU0FH8NjNP2gIh4bsLW8qbAC40UtSlfw bEoVslTzWFzOnt3xaQLlBL8jAcSNATEVexECtFj08K0RkvSQUvpTzVa8y8crnCwz43F6bC03h8gD 9AkB/EAkHMJhYASDFrGa/CDMJl2aa6785GKwgR0wOqBCkc2RqTSuoqYHJyhq6omGYgqxxCyTMItI gDwypo87EDEMEPxR/oTNptiXtgCkCp9QOzd4gO8AesNXB3L90vq5kyZIYS57NBfftDcHWnRgjIL0 5CIgNY32HpFOVo63TVuK8SE/enBnOKJR2hh4e7I638uD115IXhdV8+nKPCDlkDdoTS4pRSEkpJkJ RJocQl4t5EUXUCkaS9KLU3zlewMEYhSMmqQT2elmhsdoyUIggFqyC6sh3RdAlIqx6CTUGcLTqnML hgWhIcG/oZQFHqNJWd+U5dYvF1A8wekbDY6nM7LQsZkgSkgKhxHs3hIFrIfTnH4zaAwMT5R1HDle Fk2xVVEt7ibPOWnbcgtVZuSeoKY3CW26fBRFEeUjaMCzwtkpHQdlx37qteWtu3JGW5JkikypjMxX bpN9EMTaLa9nIeY9GbLFbAnAJulILHSN8AlF9+MSpUCc5CfmOWuEbyJAaYaqyevIoU3LRjTnvAoY ZoPA90ziTafUXI52/Bw6HVotmFkAFrqXS6G0kZOEdnLUOYoXiw0H7+y27MWSyXkrmsFkXHTuLml8 tAXEtAXHULTXaXvHew2vJqFIEwEzEMOo5mewzIZYDq8BMcgXxjExdRUXcxfil38ApG/lLxVQ1nyb n5Y3PJ3bnJizGIv9oUvXiHtSkhvalJOXoQqsSlQXAPWUpivOD1UDDgrKoOKjhooKKVOP2dtZlzE5 MUFJyKlefqrJf7TliZuHtjJa52YTzVJ0B1syvGHrJ0+gbp1lOxRxNhjlOk+6wLXgXChNJkhg5liO 0CYBAjHDtcKzSmwFjoFJNwIhPyWDwdyuKNby841Wdd0pFxhBTTFqI6tOl1umdJoSqGMFe25cpbqF 0UMNIDrL9PFwKwajaaSyLKlZV0XYyzXwIk/JOUklUWrfVjJwpwu6mCtuN1ciEHaKrhTCWokMsjYO pT8MJqKisqUtcX1Y4EGDNdoXHDRFcrmkrxLHEECiwWmt047kqETRTbVKd+qAwCipwijBOi8kpUXs DlyCYwDlRqtqzCaWMWkSkNt5+ZNnMKAlH2AHwA+gHrB9gPD5fIMzqflEkg+gB+xSClwGiYE4APev KmCEzrQVoBzgsZADMH4gB8X5j7wZgZhcQP54h15hkAGgAojAMswOkCQB8gC8CSoZ0BMwHkAKgBYA XoCVAqAH7A6oAZAEAFGAIYBMiAmoD9wDMAGABAbKhV+oFQdHtzjaD+4OkH6ABuCxTUDClQCoBvAz uLOyac9AGABc5AAtAMAZgkk+Oof7HoAuRqQEzNaGMDCIaj11fWHEkkUJJO2UhbUJMBDCwkofiedf 2yGwSsDaOS48hcsoT1H5eg8x5yn54nrPScyCYNvwxzq0s9JYd+XvvOgbwJGz1qfY2/BLEtnTcz/P Sjyx3nQEJff1TmSAfemfazQ78UlvB6nQhkhwtA+PbYaTidMGU5HGRaHOD3dt/vbShge6854GS41G l7ztLSDyzsO04/AX9XETrG4kWouyF8LgPtclxb/Csk9Y7S8wUXGsMOhHQVjFBc2mBf8lMpsORVJV iojqQxESV528aW1Uq0eEvRhO/k8whjokuYYxWEdqcOHQUeR5JVWCr1RVey2D7qKTbbjh4W42ngPJ 6OOwt/hliyXgLjAdzg1e6/wi7zUY9mvEHahq05ipTfaUSQeO4kF0JH3tkuJZ9rnzM6M40xLI6GRI CwTFRfVexBCp3zl617gWHfvNmvpVzsmX1Nb1l54imjE6XAn5U8qc22MYJS6doBgBK8za3ZMPKeyN Euy5kViCSW9ZHO5GsZ4zk1nj2Yl/NdPfr1xBmA7pGp15wDdfWL/pLqEKK7SBySWw6GSu9MFK6khc ycHywDWdHRPqJ9bMS2RsnXQlCLxLScrRy2xeVnNXxq5ME3uCBPKRENz8YenpsU1iGHUNpEaIhIH9 C2jehsIJ/McjUVdxIkd58xqF9ryO7A7hyXDvd+5+Sa3jtSEknsbHfGwoBphyM54mScbWXO9eRbGE O6TLC1+Cuql3OV4JEJwaGdgOQzpV13Mky0++tub7V6wQwRocWxK1fE8XgAWAHuDB7Hd2utTHQMuG A/idEC9NJ0McoXQnaOh1gOpouFICM8MoA8MTQ1Ct3sScbppK26/hWce2vRxb+ClBa20MOAwJVEEM EEFp47qdAFNiVm2JzZp+CzSAygknh3N55ePiLraHEPM1IGyYFgboAja4W7H8AdY8WaSA729ZpfBz vOpBHANCZ2GjO0PntorTi5Abmbu3IZAIi1WFZDgIQaB2AEjgLhUqYNfUX3i0TVwhbNKS3U8+3slc rSRpkgHcrRROJZUYhdoMoAsSnpzjlMWFuaaEosS48WdnhgOTxvdWxPskwuHEbYlAQSWUQhWQSgHK xJWxkDJJKyZX50kFIKPqCQE3bWBVF+lCJcnrcydtYnLfxbbMEmMaamSytiUJFMgiEO/sZtD4urZY mo1v5l+mq3ZPBsbmuNzmG57t42P9cQMRSEiIPRrFvEgXYRZaaUpS2kC6Nr7rQ2VpmTJCb1c4GR1l D4lDjkMCQmmhvjowqnRA+09BhOz25XI3sgve2VwkA/4aR8wfqTG+VbyjG3PHN4D2pkzvNicBZ4Su rcVo4HTgZZXzbW550DewE/cvNCxCtiXx8JSISzG8Wx4N51jcqX7dd3mY7o3jkcw7YXJoagwyOndJ GmoxHASJAsLU06skiieq4r+DzVRJ9PMBO1GVHQcdVdrFiIsSchFzhMDGjiRHDTtzMURE2XyCUBNg 3zL+Tlex8rcGFq7WujIMIQREU2JyXYPG9KbXCxOiKpa0HVdxxAs/talyZ12QzealcYuJ2REREW6k KJSJzSyGYPSLUiM7FWhgsuWJXwCtJU0iVeFtziGnDjHYijndQHcL+kHDMaQh1MOfbba3w2TJ2lBt Yd1OHFKo0DAcoLAwCwwA2WUghLZjNJNBJj8y4bn9YKEVkVHk3yHrMjaq+3Mlg9VBL8A7naGATEjW lEmcYA754/j0m1MxO2RJIkpGWbU0ATbhiU2RRoZLikaIa846h72pspciRhQ+AwicIcVJDxeSr5dY 3eV5X0YdL6Ow0UaWYaZI+pkXDCaPHkcBZPteL643hfFzM7xgDg+CgBzbX0FmBrc3wYAsbXNMxNY4 AMjhbHmV+A0jGjR4C9rKERtbAbIQ0HIW4kiaNiDdcrrV3NTqcpCJ139Dndz3P/xdyRThQkOe7M30 --===============0045517334==--