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

    XMLWordPrintable

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

            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.