From: Oystein Grovlen Date: August 17 2010 11:53am Subject: bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3227) Bug#52344 List-Archive: http://lists.mysql.com/commits/115928 X-Bug: 52344 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0014376144==" --===============0014376144== 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 are 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:53:45 +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:53:45 +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:53:45 +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:53:45 +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:53:45 +0000 @@ -17452,7 +17452,15 @@ 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); --===============0014376144== 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\ # erpngrr8v0yxpt65 # target_branch: file:///home/og136792/mysql/mysql-next-mr-opt-\ # backporting/ # testament_sha1: 55b35760fbb6e3f6e67ebaa738579ca0fb16f6b0 # timestamp: 2010-08-17 13:53:54 +0200 # base_revision_id: epotemkin@stripped\ # r4ghhxs82qbss3ob # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbST/xgABzrfgHBweXf//3+3 /+C////wYA2eXu+0e5ve71jO5qSugzwB03VK7uc71igS0NJSJXrDJCJjVDZQ9I9NHokZppqBoADQ AAABJQQaYgEaCn6iehPJNQNGg0BkDQ0A9QcNNMEMhppkZMIBpoAwmjTJgAQNBEkk0ACGBFPU3pTE 9I0aaMh6CAaNABoIpCaJoGiYE0yamE0yaCT0xT0mIPUNBkGgKlEJiTAQIym2lPUaBo2oPUwyah6n qeSDRtIWH42qagYDzV2P5jJFPyV/cyEKg2O9qP6jY4h/uP1H6jybxvHGraDmGfX2enfCZOff3XYo MgiOBQ9Y+6sKXCLU4lm+F2SSWIdIvGUylS7imK7wcIfaMHm2Tqqg47+OPIzOyM7zKZTQFT0e8bqb RrmNT9GIQ9uUkyoVraeNlprXwgKNEOss0o8nuu+DgA3fNyTkpbI4SysjNxlBZFMnLHw1OwcYcMaw oA6Qzb0nyJ7U6kkhCEkjv5P93ZYyTrqnkVq20pk5KRnsvtuuvuY8R4zynHUvDm2qFIpSi+gVLD3H cL97lsDUHiCs6I9h2lBfOal5eejyuXX805/l/um1L11ckqsqsXWrf0aM0/1ARDw3X2OIuNQBaYVs S7wbqJUuslX4UKXMpu1FEC9PS/ocBzRAKCaxcTAki1qaEkMlBDQphTT6K1X1kBTVwPLo1EEZlBvD 2gHtKjzBU2lhaFTcmFNGOWjQjPva8N3HSRsZClvhbQosWnLq3qqq04buB9NmIpWgpSyEjYXLnvdI JfBTT5WQZGEfYj85Y2GyV64BfAD1RFeIHL1e8B8gB6wx7fRllt9zHJyckOEvstgZZeQNQ76prTho IiA5Dcw8RT3Zh5O6voLciE/tTtdYk97KGs8wR8Ok1kvVYvJXqggFVa9OExb2FmsguRqcqXq0AWI0 FWgyqDzcRrBqwJZxuG1ZyT0PUuNGRSa4FdzSHM9A0oSEBdTQXweg28LYYF0MUTFMzPOU5AbXFrLi uoLxgvdaTHE60iCrjMdeBoJF9cxby6X9RegHmDtHkbG5wdlgUZkgSwgKjlPs7AkC2kP02D4Y2F4B pRmVHCbLjiBK2NiGo6aC/euNdGQLZcHEqLFQa315q9RYrRKZCgYK4TBJxapMOBz05U62zKlPkhmO SGGSLzM3uQRKF06hdYspWMqeArXtCjmHFALr6ienPsxtwxCO7gQ682Iz9fHQVKWk7dt/EpVnhwxq AuoIRO1dKB9T1VBSUKFDRSe0LXtALHOtGWNsJl+mPR17sBttL0ZixJtHXrwxZykspsla3QUi0tzF MbWksAtJYGwkXFY37GGbxahmqaIgIkMTFgdQ4kgqYG/YNEnCiciDYjIYm1RhNvfyCgUMiUB6qsIH isF44rsauvouBne0tUGj6QyC1aUSY9npSzbodrD+CMbyrJAh6dVCyQ9tYbBgRKIHbVRWqxhSDvhX ApN5SQpImFJ3uN9PJ3vu2b+BkhVvFsejZaw4TnLEwXPOWKeE75jICdEx3LcaFz9N7Um3zCZ5bStU 0rB6jVOll6seKYhMrJQO5d2dZXYd9mpONOKYpspwaXnNROjLTB2RJEbrLGTtpSdcblMyVihOp2Ab GAii7isqzAzLxr2vruIQtrvbbhIgVd+OxRHXrmJdSw0pTRWhew30K/A453D9jI4iyNBqeaEhqu2Z tJEltym3EWEQ4PsHwtxYJyjLMucIoSSWZNuuK6LgsGg1z450uvkuJJXxIbMVLLQJ2W4on4pDCr6x 35rzckOhN2FkpSlODnje2mQjJOsO0eA9hOsnF3+k0TZO+lYo/gPBN8woMbiUB2z1Swtuy6SaQ6iZ qhLCdwO6e5Owlg0pMw+7OeTSlQawyRKEroJsFQ7wxFZIaYI0DzhgC0MYIwGAP8JsoJUKBklBSUIz QRsHvDQDKFDfwMJ/owJq69KXE95NZPAHiLZGwlJGAYBxDTNyWW2Rt1DKF8zAuDKSwisftxJ8E8Y1 xMkEb0vg1AZhDifjx7g75SS5KTw1QulCmBGLIrSet6L+mqWowolyZr3nff+LEnS6ehc6HV152R63 KosJ2dm5rkmZfq6VzqLMXAmAq3+uR/zh7Eolk672eFMEeWPcaghLMXVOZIB/imfUzQ+mNDdwL2K9 BJisoA/LzPMDaSPYcHhyYUU/gpVmp6+RcYjAwfuOwykHvnQzlTd+jeJvG0kURfKF8VYL7mJdmflX TcnLF3Q7/OYlzDMKtntRSTHmDZOy/RWNmv1NUrlpHBDKRJXY3GlzKldvlL5MJ6vY5hDCklzDGNiA gHtTMbPZ1dVThuL2ZzJdmRZFHqZBJ/YdtKQ5UWGvnVRyOPpy2kIye3MzIi3KKiucNxoL+eW8HUho wylxXz31LUkE7SQWwkdjSXSU92F8ja9tNVW1zBGrK50iYMRm5X+J9lu8ZON83U6N/tblLZg3ZxsX KuqwcLFzxzxnltMtFa8G+GUNrN3Jps8Dc7uOHgiIg2paTr43IyHNJkyhyoxx+fQ3+D64ZMYWgc2m KytAOMODGf43UQUJLQYKLUqGxkr2JxsXikL1p2vrvG06uE+ZPuZiZ5HGesW6xLEXvMCcsB6cIxKz mr5V9jBN9QQJ65EjzP5R09WVTrEM3EdJJukSD+Bpu7EORC3gC6xdD9YUFDCMIgyDcNDStQMMbbYN Jpcf0ic698hEpPM6H351FwOCPO2toaT4NbW15dC5WIeGmszb4q3C7KvJ6HmkQnNozyA6TYlfRoZJ 0WfC10uJr9YIYI0ONol1z4vB6QCgB+IXvm7/Q9CmPAZbbx8DXAu2s6mPKFsJ3Dg9ADparelc9UD2 yyvBNHTpJXtuA0asWU2s67buzqxXi55IMXm4guIIYIILD2W11gV1JdNonWzTzWaQGUEk9ng3Pq8e pm5BytjxmBeHXAEcnG4Z/iDwHtZpIDwbse18XW6lII5hsT1sNNjR+XKlqdzkBtZu7chkAiLFYVkN 4hBgOoAkY1LGDj1Fz2tJq4wzTSwzb3V198sADbUU8KUnEtKMQvIGUAY06dY9BlyOhsolL0xnlnZ3 3jm8b4MVye5LBhHKmCVEhS1IhioKgOdlK3NA0lK01j0pQXwvfQFAWevCBfMdiHMWU/a9jrTvuE9v o7nDJfJjQnBkssIlCRZIBEIFeIrLDYZ6GkThx7BC8/boXFQZDaTAdMG0apg0ftvQfh0C3CQLqIpm NCVrZYBojm/ZnDlampNMJ2K7ANLxKPMo4SGBITdRxDtyKm+B9x8hhO/39DpbtIvqcdsJAP9WwfiD 80wfO19sYOhgXu1eQ+lNOxzZW8x+UtFrkz2iwl7uvZdD9HS63YgcmBb5l0xZFcqYx+KqImXW9Lc8 XWLecRuVL9mPVi97HpjeOZqOSFyYNwMMjdvmO69zvNmzUxwUIv0zbKb8HJ9Y1XynH5e5WmYegfuc ovbeLltoiaWs4SyOqURXWPYNcr+vogVVXRxEKgWYcLGPrdDyfI4QyYF2uLVmGIQkRXWnJdY8MSV2 t9HXDcljUdNvDGBT42JamdeiNnyXnanZM9VVVWjgJVrN1wcz0L6k4DW2JmGKc/PVsSrmAvvQzZZJ dVQlSteccHdMivm3blb8xoCHSw59lljihpMnYVGxh3V6eCXI1C8coLAwCwwA2i2WWwQlZjNJNokx /MuG5/SCpF0i4eTikPEyNir+GZKD1VEx5A8LtDIFhJzpeljpgHdbL27jamgtgopJSkz2cRqAs6xl WaLtzS56N0cO0eI9re5cDpSMiHxGETshy2EPc+1V8+0dHncr82Hc/N5G67e2DfSPqaMIxNXjzPrF pj0vpnAMUvbHcSg4pzZAz7ZUfFNnRKDLNE2WM0vTEapuT0uhXeL52geYd/lcV7gGuuRWRoYL4nRp BoPEX+2CvUrzavEXg6iETtv3O15vg//F3JFOFCQtJP/GAA== --===============0014376144==--