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

LIMIT ROWS EXAMINED: Assertion `0' failed in net_end_statement(THD*) with subquery in SELECT, constant table, aggregate function

Details

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

    Description

      mysqld: protocol.cc:509: void net_end_statement(THD*): Assertion `0' failed
       
      #8  0xb7479014 in __assert_fail () from /lib/libc.so.6
      #9  0x082c5479 in net_end_statement (thd=0x93f0c18) at protocol.cc:509
      #10 0x082e2464 in dispatch_command (command=COM_QUERY, thd=0x93f0c18, 
          packet=0x944a589 "SELECT    \n( SELECT MAX(c) FROM t1, t2 ) \nFROM t2 \nWHERE c = ( SELECT MAX(b) FROM t2 ) \nLIMIT ROWS_EXAMINED 3", packet_length=109) at sql_parse.cc:1620
      #11 0x082e08bf in do_command (thd=0x93f0c18) at sql_parse.cc:923
      #12 0x082dd845 in handle_one_connection (arg=0x93f0c18) at sql_connect.cc:1193
      #13 0xb7707b25 in start_thread () from /lib/libpthread.so.0
       
      Query (94607a8): SELECT     ( SELECT MAX(c) FROM t1, t2 )  FROM t2  WHERE c = ( SELECT MAX(b) FROM t2 )  LIMIT ROWS_EXAMINED 3
      Connection ID (thread ID): 1
      Status: NOT_KILLED

      bzr version-info
      revision-id: timour@askmonty.org-20120305200233-lyj8vveiuwkm1nq2
      date: 2012-03-05 22:02:33 +0200
      build-date: 2012-03-08 04:21:36 +0400
      revno: 3453

      EXPLAIN:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered Extra
      1       PRIMARY t2      ref     c       c       5       const   0       0.00    Using where; Using index
      3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    6       100.00
      2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
      2       SUBQUERY        t2      index   NULL    c       5       NULL    6       100.00  Using index
      Warnings:
      Note    1003    select (select max(`test`.`t2`.`c`) from `test`.`t1` join `test`.`t2`) AS `( SELECT MAX(c) FROM t1, t2 )` from `test`.`t2` where (`test`.`t2`.`c` = (select max(`test`.`t2`.`b`) from `test`.`t2`))

      Minimal optimizer_switch:

      in_to_exists=on

      Full optimizer_switch:

      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';
       
      CREATE TABLE t1 ( a INT );
      CREATE TABLE t2 ( b INT, c INT, KEY(c) );
      INSERT INTO t2 VALUES 
        (5, 0),(3, 4),(6, 1),
        (5, 8),(4, 9),(8, 1);
       
      SELECT
        ( SELECT MAX(c) FROM t1, t2 )
      FROM t2 
      WHERE c = ( SELECT MAX(b) FROM t2 )
      LIMIT ROWS_EXAMINED 3;

      Attachments

        Issue Links

          Activity

            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            timour Timour Katchaounov (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]

            Problem:
            The failed assert was a result of subquery execution during optimization of the outer query.
            In such cases, query processing can be interrupted before we even get to the execution phase.
            EOF is not sent, and hence the failed assert.

            Solution:
            Make sure that LIMIT ROWS is enabled only when the outer-most query begins execution.

            timour Timour Katchaounov (Inactive) added a comment - Problem: The failed assert was a result of subquery execution during optimization of the outer query. In such cases, query processing can be interrupted before we even get to the execution phase. EOF is not sent, and hence the failed assert. Solution: Make sure that LIMIT ROWS is enabled only when the outer-most query begins execution.
            timour Timour Katchaounov (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 11008 ] MariaDB v2 [ 45108 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 45108 ] MariaDB v3 [ 61995 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 61995 ] MariaDB v4 [ 144723 ]

            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.