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

LP:827416 - Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries

    XMLWordPrintable

Details

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

    Description

      This query:

      EXPLAIN SELECT *
      FROM t1
      WHERE t1.a = (
      SELECT SUM( c )
      FROM t2
      WHERE (
      SELECT DISTINCT b
      FROM t3
      ) > 0
      );

      crashes as follows:

      #4 <signal handler called>
      #5 0x08347fd1 in select_describe (join=0x9b130cc8, need_tmp_table=true, need_order=false, distinct=false, message=0x0) at sql_select.cc:20567
      #6 0x0831d997 in JOIN::exec (this=0x9b130cc8) at sql_select.cc:2181
      #7 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xa3314b04, tables=0xa3314f58, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
      group=0x0, having=0x0, proc_param=0x0, select_options=2147764741, result=0xa3315660, unit=0xa3314ba0, select_lex=0xa33149c8) at sql_select.cc:2891
      #8 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xa3314ba0, result=0xa3315660) at sql_select.cc:21040
      #9 0x08349b7a in select_describe (join=0xa332daa0, need_tmp_table=false, need_order=false, distinct=false,
      message=0x88e931c "Impossible WHERE noticed after reading const tables") at sql_select.cc:20997
      #10 0x08330ddf in return_zero_rows (join=0xa332daa0, result=0xa3315660, tables=..., fields=..., send_row=true, select_options=2147764740,
      info=0x88e931c "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:10170
      #11 0x0831d583 in JOIN::exec (this=0xa332daa0) at sql_select.cc:2122
      #12 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xa33141dc, tables=0xa3314778, wild_num=0, fields=..., conds=0xa3315308, og_num=0,
      order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xa3315660, unit=0xa3314278, select_lex=0xa33140a0)
      at sql_select.cc:2891
      #13 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xa3314278, result=0xa3315660) at sql_select.cc:21040
      #14 0x08349b7a in select_describe (join=0xa3326830, need_tmp_table=false, need_order=false, distinct=false,
      message=0x88e931c "Impossible WHERE noticed after reading const tables") at sql_select.cc:20997
      #15 0x08330ddf in return_zero_rows (join=0xa3326830, result=0xa3315660, tables=..., fields=..., send_row=false, select_options=2147764740,
      info=0x88e931c "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:10170
      #16 0x0831d583 in JOIN::exec (this=0xa3326830) at sql_select.cc:2122
      #17 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xaeced64, tables=0xa3313da0, wild_num=1, fields=..., conds=0xa33154f0, og_num=0,
      order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xa3315660, unit=0xaece94c, select_lex=0xaecec28)
      at sql_select.cc:2891
      #18 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xaece94c, result=0xa3315660) at sql_select.cc:21040
      #19 0x082b2e07 in execute_sqlcom_select (thd=0xaecd1e8, all_tables=0xa3313da0) at sql_parse.cc:5069
      #20 0x082a9d89 in mysql_execute_command (thd=0xaecd1e8) at sql_parse.cc:2234
      #21 0x082b55c1 in mysql_parse (thd=0xaecd1e8,
      rawbuf=0xa3313bb0 "EXPLAIN SELECT *\nFROM t1\nWHERE t1.a = (\nSELECT SUM( c )\nFROM t2\nWHERE (\nSELECT DISTINCT b\nFROM t3\n) > 0\n)", length=105,
      found_semicolon=0x9f6f9228) at sql_parse.cc:6091
      #22 0x082a7a06 in dispatch_command (command=COM_QUERY, thd=0xaecd1e8, packet=0xaf258b1 "", packet_length=105) at sql_parse.cc:1211
      #23 0x082a6e61 in do_command (thd=0xaecd1e8) at sql_parse.cc:906
      #24 0x082a3ec9 in handle_one_connection (arg=0xaecd1e8) at sql_connect.cc:1186
      #25 0x00821919 in start_thread () from /lib/libpthread.so.0
      #26 0x00453cce in clone () from /lib/libc.so.6

      explain in other versions:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
      2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
      3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary

      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=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      bzr version-info
      revision-id: <email address hidden>
      date: 2011-08-16 15:51:40 +0300
      build-date: 2011-08-16 17:45:06 +0300
      revno: 3157
      branch-nick: maria-5.3

      DISTINCT appears to be required which makes the query partially unrealistic. Maybe it is best to ignore/filter out DISTINCT inside subqueries early in the optimization.

      Repeatable on maria-5.3. Not repeatable in maria-5.2, mysql-5.5. Not influenced by any particular switches.

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            philipstoev Philip Stoev (Inactive)
            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.