Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-155

Assertion `0' failed in net_end_statement(THD*) with LIMIT_ROWS_EXAMINED, outer_join_with_cache, DISTINCT, HAVING

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • None
    • None
    • None

    Description

      mysqld: protocol.cc:509: void net_end_statement(THD*): Assertion `0' failed.
      120216  1:46:27 [ERROR] mysqld got signal 6 ;
       
      #8  0xb74a3014 in __assert_fail () from /lib/libc.so.6
      #9  0x082c50ed in net_end_statement (thd=0x93efc00) at protocol.cc:509
      #10 0x082e25cc in dispatch_command (command=COM_QUERY, thd=0x93efc00, 
          packet=0x9449561 "SELECT DISTINCT a AS field1 FROM t1, t2 \nWHERE EXISTS ( \nSELECT c FROM t3 RIGHT JOIN t2 ON b = d \n) \nHAVING field1 > 8 \nLIMIT_ROWS_EXAMINED 20", packet_length=142)
          at sql_parse.cc:1620
      #11 0x082e0a27 in do_command (thd=0x93efc00) at sql_parse.cc:923
      #12 0x082dd9ad in handle_one_connection (arg=0x93efc00) at sql_connect.cc:1193
       
      Query (0x945f7c8): SELECT DISTINCT a AS field1 FROM t1, t2  WHERE EXISTS (  SELECT c FROM t3 LEFT JOIN t2 ON b = d  )  HAVING field1 > 8  LIMIT_ROWS_EXAMINED 20
      Connection ID (thread ID): 1
      Status: NOT_KILLED

      Could not reproduce on the same query without LIMIT_ROWS_EXAMINED.

      bzr version-info
      revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r
      date: 2012-02-13 16:52:48 +0200
      build-date: 2012-02-16 01:48:07 +0400
      revno: 3417

      EXPLAIN:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered    Extra
      1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       100.00  Using temporary
      1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Distinct
      2       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    3       100.00
      2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note    1003    select distinct 'USA' AS `field1` from `test`.`t1` join `test`.`t2` where exists(select `test`.`t3`.`c` from `test`.`t3` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t3`.`d`)) where 1) having (`field1` > 8)

      Minimal optimizer_switch: in_to_exists=on,outer_join_with_cache=on
      Full optimizer_switch (default): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      Test case:

      SET optimizer_switch='in_to_exists=on,outer_join_with_cache=on';
       
      CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('USA');
       
      CREATE TABLE t2 ( b INT );
      INSERT INTO t2 VALUES 
      (3899),(3914),(3888);
       
      CREATE TABLE t3 ( c VARCHAR(33), d INT );
      INSERT INTO t3 VALUES 
      ('USASpanish',8),('USATagalog',0),('USAVietnamese',0);
       
      SELECT DISTINCT a AS field1 FROM t1, t2 
      WHERE EXISTS ( 
        SELECT c FROM t3 LEFT JOIN t2 ON b = d 
      ) 
      HAVING field1 > 8 
      LIMIT_ROWS_EXAMINED 20;
       
      # End of test case

      Attachments

        Issue Links

          Activity

            A simpler test case:

            SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT_ROWS_EXAMINED 2;

            timour Timour Katchaounov (Inactive) added a comment - A simpler test case: SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT_ROWS_EXAMINED 2;

            The cause for the assert is that the query plan for the query above calls JOIN::exec->remove_duplicates.
            When remove_duplicates calls handler methods it may exceed LIMIT_ROWS_EXAMINED, however in
            the case of THD::killed, this function returns an error. This results in returning from JOIN::exec immediately
            without setting the status of THD::main_da to Diagnostics_area::DA_EOF. This causes the assert.

            The solution is to disable LIMIT_ROWS_EXAMINED within remove_duplicates. The reasoning is that
            this function is reasonably cheap, and it is called after most of the query execution was done. Therefore
            it makes sense to complete this final step, and produce a possibly incomplete query result.

            timour Timour Katchaounov (Inactive) added a comment - The cause for the assert is that the query plan for the query above calls JOIN::exec->remove_duplicates. When remove_duplicates calls handler methods it may exceed LIMIT_ROWS_EXAMINED, however in the case of THD::killed, this function returns an error. This results in returning from JOIN::exec immediately without setting the status of THD::main_da to Diagnostics_area::DA_EOF. This causes the assert. The solution is to disable LIMIT_ROWS_EXAMINED within remove_duplicates. The reasoning is that this function is reasonably cheap, and it is called after most of the query execution was done. Therefore it makes sense to complete this final step, and produce a possibly incomplete query result.

            The test case is added to MDEV-28.

            timour Timour Katchaounov (Inactive) added a comment - The test case is added to MDEV-28 .

            People

              timour Timour Katchaounov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.