From: Jon Olav Hauglid Date: December 10 2010 2:59pm Subject: bzr commit into mysql-5.5-bugteam branch (jon.hauglid:3184) Bug#58730 List-Archive: http://lists.mysql.com/commits/126533 X-Bug: 58730 Message-Id: <201012101500.oBAF0sPZ027879@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8338058586193825173==" --===============8338058586193825173== 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:dmitry.shulga@stripped 3184 Jon Olav Hauglid 2010-12-10 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 2009-11-10 18:48:46 +0000 +++ b/mysql-test/r/derived.result 2010-12-10 14:59:14 +0000 @@ -401,3 +401,19 @@ SELECT 0 FROM 0 0 # End of 5.0 tests +# +# Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, +# temptable views +# +DROP TABLE IF EXISTS t1, t2, t3; +DROP VIEW IF EXISTS v1, v2; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, KEY (b)) engine=innodb; +INSERT INTO t1 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 2009-11-10 18:48:46 +0000 +++ b/mysql-test/t/derived.test 2010-12-10 14:59:14 +0000 @@ -302,3 +302,29 @@ SELECT 0 FROM (SELECT 0) t61; # 61 == MAX_TABLES --echo # End of 5.0 tests + + +--echo # +--echo # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, +--echo # temptable views +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3; +DROP VIEW IF EXISTS v1, v2; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, KEY (b)) engine=innodb; +INSERT INTO t1 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-10 14:59:14 +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-06-11 01:30:49 +0000 +++ b/sql/sql_derived.cc 2010-12-10 14:59:14 +0000 @@ -306,13 +306,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 *table_list) +{ + SELECT_LEX_UNIT *unit= table_list->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-10 14:59:14 +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 db LEX for this thread + @param table_list TABLE_LIST for the upper SELECT + + @retval false Success + @retval true Failure +*/ +bool mysql_derived_cleanup(THD *thd, LEX *lex, TABLE_LIST *table_list); + #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-10 14:59:14 +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); } --===============8338058586193825173== 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: 55206c8a5abb883cb873bb0a1f70d4c0d31b7b6a # timestamp: 2010-12-10 15:59:18 +0100 # source_branch: file:///export/home/x/mysql-5.1-bugteam/ # base_revision_id: dmitry.shulga@stripped\ # 5kuldljyahvu390t # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWbcUeBAABs7fgHeQeff//3// /+C////6YA8nX1e27ou3evShlV3cHZYZ2bt7ZrRp72NA9sPQPXtYe7a16rxYShQmaT0jSZGnqamz VPyT0k8hqepkMRpoGQ0HqA0ZBJSZEwNGo9TRNPUxCekzUGgaADQAAAAEoBIaQmpPZNTyiH6TUeUD CMQGAgAAGmQSmiICaj0ak2mQ0h6IDJ6gaaAANAAAA5o0aGmEA0wJpoAyGhiANGI0MEZABIoIAEAN TJo0T0SaYQJkYhoNNMjJo0GSQQ2lRDFRTh5WL10bvX8qWhlMTreRWyWSCytLAGuv49vZZ1QFm/le /vZSAZVZqzN++zYKPEe12O5afMLNwKzLRbuaKtHveanOmiqe6zrcUaNd4V1z1sk2K2Msgdcqa7BS DurxwVKCU3WQ3NpkDtlgxmBcJHgpXFK8rgc0zmjmaV3Z9/Dfh3T/Jt6eOe9CIsQi0Au++HIaU5gQ IZfl+UKIwzCb4bryYQv5CoKCg1kVwhZDWaDpF8on4F3iYNtsYxo+hdn+US8enCN/3BaIqqmPxo6S smJ+3uu8CVs0OV02dVPhW96dllVaOgMg50FLlk97NvDWiDqDPfcIRf7rNJyHAUBGWCimyMSzYh9g uU8/Vq+qf4GkYymh68ejKa1uyLCoYyV1ecvhtjG8PTKiagTPNlxMTktTk1DvyKNTksrScgfHXIw7 M5Wiss4mEj5rhqulXZsaVBQMzenb/zrWLHB8giDnTG1sJ8eaFReWY7DrDU83DFFf6eMJtbKxW2DV xDu1Gw4l1ar4lke++LWajcVBbO2DVftn0D6mkc0kg1yEK4cpatBpZX9h5bOQGeN7bfCrOTlhs5jK +K2ymHh0URqqxEIyrb1WpTOKH6jaKbRTYZhKF9fFXfFcsrbjxOvVp3NRXhsy5dUk5WQr5BeFiVwl t9u1xPrRCNUQS9yJCSV7o8Q4NeLaMy30UZ4mZ86ThAhiQgYD7K2VyhhLNRGE7G8jUdhMpifFciTj yGpfSeAiXJWLZHHZsEVjTK02NHg8bIDYliWg1CqD3HeNJHrD2D7FA9FBjca00FXSk9If0RWNv5Sz q5WsYZGr2mC4kgFzfsnflyRCuQzPMz0549SWoRmAQEiCgIL4JphzOB2inOgXkLia3QCtaQcU1CMM yEw5CgGRBPfBEAaEREkeAzGBMXCRHIwV7CQ1EQVzSNLSJSMwouYp2/Y5xrgQDYQqArUWxncShO8u OA1iATwjkBMakw/UMREIDIMlLqKIphZRflXIQ00gyriAMiDl0OE9ZnRos1xND0F03FqghgSK7bu0 kTFUkEMYU7PVsjddusKshafuoQ1ToALQIrfUAZePJcY1NcNmqnGEqtsyESXhOaNNJwPIvrKAjIpW OTIi/VrS0hIMHuIiryFGjOSiGX3lTLb6cDLgEhsFoZwaBTFZsqIBC2CQbXLRJlV5GJr1FeKmwXNI PJhlFTs8iC6AMVpmKCv924FhlL6d/tDTWFJEwMjoaDDdwq4O8uy4mC283ck1tHaE4ZlUN6pRMBpL p6yUcZizRRippnX9W9AuB4YGK2onALwDGUmMNAas5jkWaDNhdrZ1sO4IQ4BdNKopkBGRBYtzE7jh g+5iuApkezGlQDXvMSjA3EDmBZwKyLqKUoBmaSdJOP5hcdTlmRLzYY237CPjhXeUKeoro1r59vCr B8wpIOSgU7qCGJ2sgsIG8pinbcu3ENQBPiVQPZzLRlJM7DuKjmWV716yrLuzx3mEmywykLMohUKZ yGWGZVtkZ90enE7a1xpAu9BQX4GJq0aTVI3n6DDmdmVBWWbZvdMIgH19QpRdGkv20h1iuQnNJcOo zgNJSOAw1tCc1GkyGox37eyxsZmWGVU1HHbtnCtIURrFOGJVSMEby5QSMmYWOybqy5pOTIYxKKGx m5pJ3JT6NJKkOSp3uJQBubHjpaUPUM5SFpifbrkRY/SQdUwxgETM4VGBG8bAPR0lUmX6oL9griLj VBWaFIet0LaLfECcAHS5pWIKa38BFMV472ojRqIEHuNHtS2fKueLmKAMfvCtBRTdCZ0Dt+QUgzUm JZ09qaAmi08n8iA/6H6Ew5gMNJiwaTb/lAPX9ZXGwUA4Ax/p+RCBczAUE+0DNhA84BKmeKlHpApI ChRkPUKkA4hmLYyHvAQXnqBFhA1bcoediUgyI9YahmuAID8fqRJNWlwlklpHoROAGg/MJiLAYjGF QGQWw/8BYWn3CMwLJZIxGMwC4NX8yAxoxNaJhMJo3EsIXNA5C4BSAXAyHJDMxQyLDGDPvRN/s5kL UXFAjJWBIwQQa5DV8v8bZsyNvdXoQd6RBJEExEfEUkgkofYg+76YoUgPOgUiKc+6X2zjhE5p+M5z k9B0F71nMdJP6jqRT34UQShzCI848k5Cs93OJD6HpF+U8o0VIy8zbw0cp6UGg29qvF7vnPpdYfN/ CBs5BUXLDMWe2qbLvVJIpV7SJke/TwX8ZqBNpKj61NEmmzFclSF0wg4VGG9aoSQxHTGIFcSX0slS o0tmKlJCcgzGOvMfLGRZlHE5vDPIMBkyBdUdypKJGaM2oHNKLEj6xiNppOEb7dkTznGbpZOkFcVy MFtIcV96uMPTBymJTpmjdOE1dA4mlLQ1UbTzQb7hjvJ9FElrOLBoxMy7yKoX6AzLZVyiJEzn/hgQ RxGtAhR3UkjjURXkdxHCEuuE1KQTGRSjAcIxEMYMYKY00hQIwBM7ARkJlNsrIR7rYEoDGIpM5FUI FwVJJMFFlYMZ2HpWxbMjgafAqN108ZVJaXvEeTF48xslykAPVqelJ204HhyJpFJBawag1w5N3DXJ nQLqSNOyqoOaCB07SVFEQxOYzstrTROaESNFMWUcIl5hoClIgZE0YSoo4InQP7k4+B1O85lOwyLu ttB4M3+OVAsxWGWN7FCGgAuQMKhMxwQGrWUzEt2Woq6HEK7TgnXcQbhdD30DlnCKRNaYzjJC8SCg gNdpqow2+NvXYzkTPYFWYk9PRALOLE6y8DmkCrWwEBwZygT4br0OPzKmaNqgBQyiunnC3OPQlovI dyhLjGkQKvAvZMItVUlyiyPbBFV9uAuQ+00sR8dgoyVom2FnCyEUEYq2SF5ZFaFyQWUgdWNDfMRw Mjw5HidpMKzz8+g6DxKMvQxROB5K/qdQgQS8TE1pd4cjyLgPzFgKAvM4qC0Hp9Aw7+pNExm54rmY Seo9ddrEQgoKiHRZneZlvi0HmG9FhWkHvTOwLQ/2dTLOOJ9pqDGQ4iqCqk6ibqHFjcwluOwESaap ByKBgQB1EJAySTCW5Xw/aTiEKaSMDK4UBfvjQxZ6pqlYiUwpTNRzt1nK85e+7q4+uXLlnmhQEFUc AI104rh9jG2/aO9KaKahXsFrQ0MsVIH0F/ggibzdRcUXaYEG71E+0p2ZefbK1FiDQYLSLDWg+DEJ lbxWQe5HpICBcgC5XinX4sy+01IiiNfhHXizjIUuFJsmhrgcKPEnNNaBZfJGSKWovOsOY41I1Bvo KkE9cwoSREYl9EzWUlA0MVJ8QGsTTaFiJEBREMURR+oaRaSGNSPXRARtq8DcB+A7lpCo6pBqjetw Q0ncUqK6aCANWFOlB65TIE1uLZ9fDVjhRlap5oFCk7AjVtao3HP1FYqJ92JSbQnG4OV0XZge0Ot9 yWIYJT8CWdJ+ImwfrY/hP+PoYFplgZQTWMlFOK4g0g1CiOq5iUlJYkks5CAqkMiHycYHwmzQiIwy yFZWuZTQcioNgb6g8x3FLdoJsCCRBMlOXRpDWh7FBm6dUnCsDfgcLcaVZdzRJgS9uQX8CAoDk7Me gv9A5zuN0mlxQ2esRDagPyq3AItVs9syyoJlGugn3KYQKycwQn3AEILMbGiYA1QqEqTU2zIJ1y5U lIekoNrFZOaVnA+c2hvke429DIEjgtAZ47+w9RuLhrbAW2IuBJkkkoKl7GTciA4LGlA0pKT0WwdC SvICs9i3xMzhGKVRyvtRLAneDEC3YQiEQlJHoCGIFVcDFWFRYWJlvDhviCK3HScFl38yH1z/VvU3 E8CQrkMztD3eCL41Mz862FlDS3YXTxw4362I2Gbqg2sWk1wKIOG+CUaZtHUkTS89wqOKnqjAMUEx bjn4AbFxePXXxjcaSSSSo1g68OJCkoYGUDklezWH2qAHCUIOAxsLDUg2AHdGS7wnhU7NlNu55++N D1owJwhk0zlgs8uMIo7jDruyrKLdByAL0sKDj1LccFcK3zBOtcwaaZiDR8Y1uHFESOEzkktMEJzB 3JxFV1osoUFwEWwh0EKJsGoQ74EGtpDY2jaD2C9wV9r7Ohnqit5RWJluaD27UWKlQgW6DoAGh6BT GKWsiOAHh0DGgt0m6cngXn1B6N6QOZ507jce0+BTUzFKfP5u/PcMR3KFOPj8c1w5cgBF7VqWcOUA bWAayg8lRiNY4YSiBQVvcpVKmQWHXNzcWBsrJjE1qE48wBxlB0ACJw7o8ChgtavMr1aMEo05yw2n B0Tok6f0COIchadjvGzsIC4zQalVedhYcfUCWgxPIvGU2Go4m3GBdL0xPyj8mJLM4zKf8XckU4UJ C3FHgQA= --===============8338058586193825173==--