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:
None 
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:16] John Embretsen
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;
[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.