| Bug #56254 | Assertion tab->ref.use_count fails in join_read_key_unlock_row() on 4-way JOIN | ||
|---|---|---|---|
| Submitted: | 25 Aug 2010 14:16 | Modified: | 22 Nov 2010 1:21 |
| Reporter: | John Embretsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | bzr_mysql-next-mr-opt-team | OS: | Any |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[25 Aug 2010 14:19]
John Embretsen
Verified with debug builds on OpenSolaris 2009.06 snv_111b X86_64 and Ubuntu 9.04 x86_64.
[26 Aug 2010 13:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/116887 3232 Guilhem Bichot 2010-08-26 Fix for BUG#56254 "Assertion tab->ref.use_count fails in join_read_key_unlock_row() on 4-way JOIN" now that evaluate_null_complemented_join_record() cascades into evaluate_join_record() (after fix for BUG 54437), this started unlocking a NULL-complemented (thus non-locked) row... @ mysql-test/t/join_outer.test test for bug, used to assert in join_read_key_unlock_row() @ sql/sql_select.cc don't unlock NULL-complemented rows, they are not in table, they are not locked. For the test to be reliable, not_null_compl needs to be properly set: we set it to true at start of sub_select(), before we read rows from the table; if we find no matching row, sub_select() calls evaluate_null_complemented_join_record() which sets it to false. Next time we read the table in the nested loop join, sub_select() sets it to true again.
[1 Sep 2010 7:49]
Guilhem Bichot
queued to next-mr-opt-backporting
[2 Oct 2010 18:14]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:22]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:21]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.
Description: With a debug build of MySQL Server, when executing a query joining 4 tables, such as: SELECT t1.col_int FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.col_int = t4.pk ON t2.col_varchar_10 = t3.col_varchar_10 ON t2.col_int = t1.pk WHERE t1.col_int_key IS NULL OR t4.pk < t3.col_int; the server fails an assertion "tab->ref.use_count" with the following stacktrace: [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff1fdc95 [13] join_read_key_unlock_row(tab = 0x48b0e40), line 17677 in "sql_select.cc" [14] evaluate_join_record(join = 0x48dc210, join_tab = 0x48b0e40, error = 0), line 17318 in "sql_select.cc" [15] evaluate_null_complemented_join_record(join = 0x48dc210, join_tab = 0x48b0e40), line 17389 in "sql_select.cc" [16] sub_select(join = 0x48dc210, join_tab = 0x48b0b90, end_of_records = false), line 16998 in "sql_select.cc" [17] evaluate_join_record(join = 0x48dc210, join_tab = 0x48b08e0, error = 0), line 17284 in "sql_select.cc" [18] sub_select(join = 0x48dc210, join_tab = 0x48b08e0, end_of_records = false), line 16980 in "sql_select.cc" [19] do_select(join = 0x48dc210, fields = 0x485af90, table = (nil), procedure = (nil)), line 16533 in "sql_select.cc" [20] JOIN::exec(this = 0x48dc210), line 3158 in "sql_select.cc" [21] mysql_select(thd = 0x4858fc0, rref_pointer_array = 0x485b070, tables = 0x48a4468, wild_num = 0, fields = CLASS, conds = 0x48d9c68, og_num = 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2147748608ULL, result = 0x48d9e00, unit = 0x485a870, select_lex = 0x485ae88), line 3359 in "sql_select.cc" [22] handle_select(thd = 0x4858fc0, lex = 0x485a7c0, result = 0x48d9e00, setup_tables_done_option = 0), line 310 in "sql_select.cc" [23] execute_sqlcom_select(thd = 0x4858fc0, all_tables = 0x48a4468), line 4562 in "sql_parse.cc" [24] mysql_execute_command(thd = 0x4858fc0), line 2166 in "sql_parse.cc" [25] mysql_parse(thd = 0x4858fc0, rawbuf = 0x48a4120 "SELECT t1.col_int\nFROM t1\nLEFT JOIN t2\nLEFT JOIN t3\nJOIN t4 \nON t3.col_int = t4.pk\nON t2.col_varchar_10 = t3.col_varchar_10\nON t2.col_int = t1.pk\nWHERE t1.col_int_key IS NULL OR t4.pk < t3.col_int", length = 200U, parser_state = 0xfffffd7fff07da50), line 5591 in "sql_parse.cc" [26] dispatch_command(command = COM_QUERY, thd = 0x4858fc0, packet = 0x489c0d1 "", packet_length = 200U), line 1130 in "sql_parse.cc" [27] do_command(thd = 0x4858fc0), line 802 in "sql_parse.cc" [28] do_handle_one_connection(thd_arg = 0x4858fc0), line 1191 in "sql_connect.cc" [29] handle_one_connection(arg = 0x4858fc0), line 1130 in "sql_connect.cc" [30] pfs_spawn_thread(arg = 0x488b9b0), line 1061 in "pfs.cc" [31] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27acf5 [32] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27afb0 Code in question (sql/sql_select.cc around line 17655): /** Since join_read_key may buffer a record, do not unlock it if it was not used in this invocation of join_read_key(). Only count locks, thus remembering if the record was left unused, and unlock already when pruning the current value of TABLE_REF buffer. @sa join_read_key() */ static void join_read_key_unlock_row(st_join_table *tab) { DBUG_ASSERT(tab->ref.use_count); if (tab->ref.use_count) tab->ref.use_count--; } This issue seems to have emerged first in mysql-next-mr-opt-backporting with revision guilhem.bichot@oracle.com-20100812090211-gjfm8ip4k21efrfr (bugfix for Bug#54437). Issue is not present in mysql-next-mr-bugfixing, nor mysql-trunk-bugfixing, nor mysql-5.1-bugteam, as of 2010-08-25. There does not seem to be an issue with optimized (non-debug) builds. How to repeat: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t4; CREATE TABLE t1 ( pk INT NOT NULL, col_int_key INT, col_int INT, PRIMARY KEY (pk), KEY col_int_key (col_int_key) ); INSERT INTO t1 VALUES (6, -448724992, NULL); CREATE TABLE t2 ( col_int INT, col_varchar_10 VARCHAR(10) ); INSERT INTO t2 VALUES (6,'afasdkiyum'); CREATE TABLE t3 ( col_varchar_10 VARCHAR(10), col_int INT ); CREATE TABLE t4 ( pk INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t4 VALUES (1); INSERT INTO t4 VALUES (2); SELECT t1.col_int FROM t1 LEFT JOIN t2 LEFT JOIN t3 JOIN t4 ON t3.col_int = t4.pk ON t2.col_varchar_10 = t3.col_varchar_10 ON t2.col_int = t1.pk WHERE t1.col_int_key IS NULL OR t4.pk < t3.col_int;