From: Jon Olav Hauglid Date: December 16 2010 9:55am Subject: bzr commit into mysql-5.5-bugteam branch (jon.hauglid:3202) Bug#58730 List-Archive: http://lists.mysql.com/commits/127043 X-Bug: 58730 Message-Id: <201012160956.oBG9uNYU011385@rcsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4560837788233381648==" --===============4560837788233381648== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-bugteam-bug58730/ based on revid:jonathan.perkin@stripped 3202 Jon Olav Hauglid 2010-12-16 Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table, temptable views The TABLE::key_read field indicates if the optimizer has found that row retrieval only should access the index tree. The triggered assert inside close_thread_table() checks that this field has been reset when the table is about to be closed. During normal execution, these fields are reset right before tables are closed at the end of mysql_execute_command(). But in the case of errors, tables are closed earlier. The patch for Bug#52044 refactored the open tables code so that close_thread_tables() is called immediately if opening of tables fails. At this point in the execution, it could happend that all TABLE::key_read fields had not been properly reset, therefore triggering the assert. The problematic statement in this case was EXPLAIN where the query accessed two derived tables and where the first derived table was processed successfully while the second derived table was not. Since it was an EXPLAIN, TABLE::key_read fields were not reset after successful derived table processing since the state needs to be accessible afterwards. When processing of the second derived table failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused it's TABLE::key_read fields to be reset. Since processing failed, the error path of open_and_lock_tables() was entered and close_thread_tables() was called. The assert was then triggered due to the TABLE::key_read fields set during processing of the first derived table. This patch fixes the problem by adding a new derived table processor, mysql_derived_cleanup() that is called after mysql_derived_filling(). It causes cleanup of all SELECT_LEX_UNITs to be called, resetting all relevant TABLE::key_read fields. Test case added to derived.test. modified: mysql-test/r/derived.result mysql-test/t/derived.test sql/sql_base.cc sql/sql_derived.cc sql/sql_derived.h sql/sql_update.cc === modified file 'mysql-test/r/derived.result' --- a/mysql-test/r/derived.result 2010-12-14 10:46:00 +0000 +++ b/mysql-test/r/derived.result 2010-12-16 09:55:23 +0000 @@ -412,3 +412,18 @@ MIN(i) 1 DROP TABLE t1; # End of 5.0 tests +# +# Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, +# temptable views +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, KEY (b)); +INSERT INTO t1 VALUES (1),(1); +INSERT INTO t2 VALUES (1),(1); +CREATE algorithm=temptable VIEW v1 AS +SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1); +CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2; +EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, t2; +DROP VIEW v1, v2; === modified file 'mysql-test/t/derived.test' --- a/mysql-test/t/derived.test 2010-12-14 10:46:00 +0000 +++ b/mysql-test/t/derived.test 2010-12-16 09:55:23 +0000 @@ -313,3 +313,25 @@ WHERE j = SUBSTRING('12', (SELECT * FROM DROP TABLE t1; --echo # End of 5.0 tests + + +--echo # +--echo # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, +--echo # temptable views +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, KEY (b)); +INSERT INTO t1 VALUES (1),(1); +INSERT INTO t2 VALUES (1),(1); + +CREATE algorithm=temptable VIEW v1 AS + SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1); +CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2; + +# This caused the assert to be triggered. +--error ER_SUBQUERY_NO_1_ROW +EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); + +DROP TABLE t1, t2; +DROP VIEW v1, v2; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-11-11 17:11:05 +0000 +++ b/sql/sql_base.cc 2010-12-16 09:55:23 +0000 @@ -5408,11 +5408,19 @@ bool open_and_lock_tables(THD *thd, TABL if (lock_tables(thd, tables, counter, flags)) goto err; - if (derived && - (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || - (thd->fill_derived_tables() && - mysql_handle_derived(thd->lex, &mysql_derived_filling)))) - goto err; + if (derived) + { + if (mysql_handle_derived(thd->lex, &mysql_derived_prepare)) + goto err; + if (thd->fill_derived_tables() && + mysql_handle_derived(thd->lex, &mysql_derived_filling)) + { + mysql_handle_derived(thd->lex, &mysql_derived_cleanup); + goto err; + } + if (!thd->lex->describe) + mysql_handle_derived(thd->lex, &mysql_derived_cleanup); + } DBUG_RETURN(FALSE); err: === modified file 'sql/sql_derived.cc' --- a/sql/sql_derived.cc 2010-12-14 10:46:00 +0000 +++ b/sql/sql_derived.cc 2010-12-16 09:55:23 +0000 @@ -307,13 +307,21 @@ bool mysql_derived_filling(THD *thd, LEX */ if (derived_result->flush()) res= TRUE; - - if (!lex->describe) - unit->cleanup(); } - else - unit->cleanup(); lex->current_select= save_current_select; } return res; } + + +/** + Cleans up the SELECT_LEX_UNIT for the derived table (if any). +*/ + +bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *derived) +{ + SELECT_LEX_UNIT *unit= derived->derived; + if (unit) + unit->cleanup(); + return false; +} === modified file 'sql/sql_derived.h' --- a/sql/sql_derived.h 2010-04-12 13:17:37 +0000 +++ b/sql/sql_derived.h 2010-12-16 09:55:23 +0000 @@ -26,4 +26,16 @@ bool mysql_handle_derived(LEX *lex, bool bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *t); bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *t); +/** + Cleans up the SELECT_LEX_UNIT for the derived table (if any). + + @param thd Thread handler + @param lex LEX for this thread + @param derived TABLE_LIST for the derived table + + @retval false Success + @retval true Failure +*/ +bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *derived); + #endif /* SQL_DERIVED_INCLUDED */ === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc 2010-11-16 09:05:19 +0000 +++ b/sql/sql_update.cc 2010-12-16 09:55:23 +0000 @@ -296,11 +296,17 @@ int mysql_update(THD *thd, if (lock_tables(thd, table_list, table_count, 0)) DBUG_RETURN(1); - if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) || - (thd->fill_derived_tables() && - mysql_handle_derived(thd->lex, &mysql_derived_filling))) + if (mysql_handle_derived(thd->lex, &mysql_derived_prepare)) DBUG_RETURN(1); + if (thd->fill_derived_tables() && + mysql_handle_derived(thd->lex, &mysql_derived_filling)) + { + mysql_handle_derived(thd->lex, &mysql_derived_cleanup); + DBUG_RETURN(1); + } + mysql_handle_derived(thd->lex, &mysql_derived_cleanup); + thd_proc_info(thd, "init"); table= table_list->table; @@ -1194,7 +1200,11 @@ int mysql_multi_update_prepare(THD *thd) if (thd->fill_derived_tables() && mysql_handle_derived(lex, &mysql_derived_filling)) + { + mysql_handle_derived(lex, &mysql_derived_cleanup); DBUG_RETURN(TRUE); + } + mysql_handle_derived(lex, &mysql_derived_cleanup); DBUG_RETURN (FALSE); } --===============4560837788233381648== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-bugteam-bug58730/ # testament_sha1: 3cd9f4e1656551c83f502398a13a5a21d2e7085f # timestamp: 2010-12-16 10:55:27 +0100 # source_branch: file:///export/home/x/mysql-5.1-bugteam/ # base_revision_id: jonathan.perkin@stripped\ # 2684lmfxl093zbgc # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWafjNT8ABp9fgHaQeff//3// /+C////6YA7HVT5C2YvtkAABGjM7utjIkFBVCiEVFSqDCUJI00zSmT1T9Kbyj1DeoKPRHog0AYnq aHoQ9T0mQ0OGhk00NMjQ0yMgyMjQyAxNGTQBkyMQxw0MmmhpkaGmRkGRkaGQGJoyaAMmRiGEiIjS aNT0mUNNiAhT9U8yp4k8JGIeoaHqB5IzU/UgiompqB6QGgBoAaAANAAAaAANBJICAExGgE0aaBKe 0p6p+lPUYamB6gQwjTMpGIUqCYPDvYNDDxe9b6lo1qV7k1ks0WSQWZpnB+YQ0adPd9TLfxPf3so4 ZVZoGXp/dBX4zRJVPbmeK+pWZa5Z7LcOas1rW1Nih73I8Mp0+iMNRDT8UEP+jRp2Ogd/VudBQTPe ZDbwMgd111BQtJHhPBXdgY3KPuSRvW7JX+Nyrj6K/9bezhu8YkSwSMRGli74jmGlWoEEfj+Mgyor MJmDdlJhDZ3wgFRUai08iZD7mo5huQTWLpEwbbbbY0fauX/iKcOvqnV/gMRFrVH13eamiovk8ZoG 1vzQux9jqyjwpfMOrih1hiHUhmguXr59h0h/rjQIh9/2H0NgonUYYzHFG0p6jd529MP0R+p/QYnJ sbujFK16GBMqGI207YfOt77Q84Sizxk6KfoRWj7oKuYj3ue6CniVIJI2zgzb660x7HEu61CNTtni rsYi1zQwNRuXb+Wy8vOmdAUodSzWMuNm6XffmaNdDtFf1bNM4fSJ2VWNNDx0CwmYhXbYG+y2c044 zznFK7g7jGuMndnvPmH4HlIJCSMh6S4LMPImHZ3gnP5bnYY7SMNWei1usrqm9z3MOwjInBOZp/ZO Sk0Vs9ZSKFBKRoXb28rnXFkkj2pROjLm2l4nU5MOHPnvHSUfqbMX5f1C83qypTHzpENIWz3Njbeu GfaQC4h8puflZdvmob97agJGIoMGA+GDMKRlG9KFTNecQ5ESTzKxEGfzGUKxx+c/KZmCVy7Z249h Fo81vOzy0cw64LEcjmKoO4+SBeK+Id4zEarWMUyCc5HoH1Dqmp7YukTpLeTgqFgAeKsNu27k066U clIia1ra2m6omZQgWlSpEENQluPCYgypTjkDhlSE4NSAMYLTYMZU2auykFUYtKDwcDAJ6EoUE4il EJCeeZF9UhRc+S1lciZsUsKlx9Ulhklg3pgEhosBZScYe4AsXm08ZVYWtfiYKkmsDkGlQYiAgZDM +sBwwxApjdV70xSS+TBFVx1F3whsaZUOxsPSinIxI0Mi2oSLm0fg6uFGaoNYp/xyr1Fm0CSkL01V YE7ysvIFdSQYmIxDDwN7p09LDYQJgz2MRf/OKw8i0yE5bd8hxviIduWS0a3QoZ2uHE0lxHllV8DI W9khUCo1sG8cV3UxSRWnkKbpgFzo8dDfLYTFevsmtZSV3UP3vQa8xliPLz/GGw0LaOn0HmgSHlha fMt3tpt3WmzddxIoC9bO6sTpsWBmSfAZVEh8hW8XEWNpV31RpC2g4bzQndZ3FwYEkxgYmovKxwcw 99xdA5E1XswzhqibXIYYjhOYGplEi/Ub3RbN0qi418rpLU01STKC4e8vL2mzQIpTTi43kzYRKScP 8hYdjBb9pAoOhG/AGV+C8b6omD3fky2UAG26X3g8izyzbzcTW4AYhzgPNsFyoM71zirysMSkf71B aRKsjwHj6TuNtPxK6ZaScyy2RFvMwRaahalVScTErINAtNpO213TcTM7V1wNJAQ8i44GLjIeUDY5 DzIeYEg/AYJG/KelD2hXsskDfWc+a8w2Iw17POwNp+Mv2qBmcx55rYZE7OAGipMDC6+Np1ORuOhR vxKzdCphnhx6EOCnhRRFgi8wCo0xkp5Xbs4DVUsTZtrEWNCDQV71HAuhQRWN7gRgQoFLQ6YlGJaZ YFzHcAd99Ksk8X5RXD2vpqZ63coW0W2gFYAdoUXJaEF+D9ZKYtQ9TAkbYZ+lLZ71vKZMG2MZ5xYA pTdy6Q3e0Uh0KjEvRe1ZQgKo7PakvoH5hQN4DDWGUCAED2Vs9RPVtAnAmVifZYJJQBQD2AUQgPvS VWGnBhcfQVyuKkI7xqBGkd5oIzFmfeDMAoBZfBEyGtn9Q7QWlJSHy/giia0BiJZpax5orALMO8Ki NAMXFFg+IYx9AKis/yI3hVDBK8YGK1l9RwwyLg1KcYLaQtEVBYQgqAkJfdWBWQCwqRWTGYbOmr8N oZGCMAthKlAxgLezSSZ87JWnwkzK7moZSkVSLASbSGSsmPchY45kZAVpxtWvjl9HeOEtxd85xx8S eUjIjnGJCcYA+dpi8IA/3nONjOn4ICRZZJHHJHKMVh6/3GwdSqtgg4y4AwF2+4+t2D6fpcargoK1 fzFnjQyi4s8oJE1ayREffhmv1xTiLSV1FLTZkuRXBjUIcXmrlXHFqZq4/ZYQfC5dFhwQICiWKwok ZRFcgYmPl9DTSVFdsyBjFBjoDiMd8Lx5Id0VBzRzZZ5GWikBJgD6jCNxsOS6kP3dv1HsVl80gs0X qwqi/9xtPhodCRrIFFiVbKrkUncfwQUZ1jDWmtSOMcB9xuYGRxNDV6FTsYhqm+ZQKhw+TAhHINcY hTuvKHjsSuw5idUUP0ZVVKBUZKUwOJlIYwYxKowaRuFAB2Im8a+vkazxIm8u4FwFIDUFgRSSYE9i YMMTPVUnAgY0beeMdaC5tBHn/yPeL9/MZh/KAA2WTYpNbgauBBIkcXpzL3z3yWu4kdix1A3h6vCT oBhDhUd10yKtRQnzdV1aopUil0F7JClZrSgXMmSEsrVYyUkuB0DMIOfrjmk6DyHioUmu6p/lW+88 Oo3iapi4ilLJhOdUAVIJi7+VqRQwtKKBLhnIvxO8KLDim96rB7M7N0BDQ4uehRWxz4wehMmQGdRn Gkt7VOPaDHMeewXdxTjWZFEK83xqavxcdQKNuYwHjtJDO3NFtk0oaJwGjEqeTV639SHvaO7k6HEH zSQrwuLWIg9lRMsT2H8oRbVjkLrHwONiPboFNFiJthy4YygmVOLQvPApQsV3IK6APNhkM3ARyN5i OMRjsdCIUkvHx7zWvhOR5OXUz8mE+48hg8kq4Hia0vAOZcVAfeKwTheZyTlUevYYPCCIDGxty5ls G3r3oqYQ5ycpO4nI1qriyDoDlsRNIPivaRvDxD+52PZ5R0BgDEw6lOpUutT+ZArfsEtsA4CHcq3E EUiBgHYxtCbbQ92Ho/CsDGOrbNYwqGngtISYW/K8SeLI515nrvAOPs19L/hDpy4cEJwgonICcL9i 5fixtv1D1JVRfYWpgskNDNCvA0X3FzkDzXGsjXqcOZrFLG7z5QqRQgvLFgLFB7vJtcrg8jzHJIVK qFDDqxd/I+STCdqZQOWs7fQuOprPV3YAvD4KwWo+Pita6oO2wrn4GZQ7oM+SPmtgVzKEEtkQmkh4 wl+eJsJkhhhTPdBrE02hZIFAtqoIgU/AaRpJGNXHddAi3R2NoH+o2YTPxJBm+xNidRy8bxwMsjuX DJBzjIcSW4rtUvPtrwvnOtR5IE6Z3g+jgyluMfgUilHwvJlq0lC0Iqx7VXHyDjbYleFyUfEhumf1 EzA3xYfrPyfSZGJnkZwmuKilOVuYgqnxDFVVjSJrtVFcSVL5KIGykJHtXTFqXxcksZsucuuDwByZ B5x4mLegE2BCRCZU9PLzB0I1NZvceksGkDn641pcXs2FQS+PGL5iBgNSV226jR7yTqkd5mx4wZ/g gWtA/Jnbw9Hq+i+S1oJLOyko3VanLUWFBxQrsADVqWh5PEwDKSklMyb8SY1BKqPOigPYkaMKuMUq txsDa8+Zn8y8EjcsA33beBMA7TxGkZ4IF4JF4yjKCAhWe1lXQgcLivQMlBQbVY5pJK4cKratHm42 vel2VKOV1iI3kbgYhLk4okJCnmB5gWwgzWgvMDBp85AeQYThBDcPybmZH52i9d0SNjkpETFJ0TQp otdWtfrd46o1Rwerp6cddiL9MtEZi1myC6HHIEJTfVo8iBFejBsFKGpzgyQQF4HXEDKwvHU0/aL7 WZm227sg9kwNjo5QJyQUWpmwPioA5G2lUYoOjnrvhbKjOTtOsv3wtfLdQRog5XRqoaOdME948ftq lVGd4kerETf5lV1QqfQE1AcgZMmLQYWscIorxXZ8JPmHeu459UV84eE1An1lOwpCYvefUI/B4jJk hmZkawbMX4wo4tv6G7J60JUiYr3IO7wp15mEviwkCKfcM4DLsQ7F9xiQaNx7OoxPgH02JA3sASPT oqdDQ9x5eD+f5Om7YMI7lPCz+ey4gsreK8wRD7QDWrgXMpPtrKDnWojiSSFqV9dSigmfI+zgdYEX POxIwOlIl+sA5LE1VqwZJX06ys9Dse5Na+iieQBBC1ZNYMxoOgmNrkGXU4FR7AlgXHkVjCCWhQsj SYFpfYXf7FoBbuXjmGZT/i7kinChIU/Gan4= --===============4560837788233381648==--