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

EXPLAIN FORMAT=JSON crashes for loose scan query

Details

    Description

      create table ten(a int);
      		 
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
      create table t1 (a int, b int, c int, d int, key(a,b,c));
      		 
      insert into t1 select  A.a, B.a, C.a, D.a from ten A, ten B, ten C, ten D;
      		 

      MariaDB [j2]> explain select count(distinct b) from t1 group by a;
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra                               |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------------------------------+
      		 
      |    1 | SIMPLE      | t1    | range | NULL          | a    | 10      | NULL | 10250 | Using index for group-by (scanning) |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------------------------------+
      		 

      MariaDB [j2]> explain format=json select count(distinct b) from t1 group by a;
      		 

        mysqld: /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:1585: const char* Explain_quick_select::get_name_by_type(): Assertion `0' failed.
      		 
        
        Program received signal SIGABRT, Aborted.
      		 
        [Switching to Thread 0x7ffff0339700 (LWP 22858)]
      		 
        0x00007ffff5f90425 in __GI_raise (sig=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
      		 
        64	../nptl/sysdeps/unix/sysv/linux/raise.c: No such file or directory.
      		 
      (gdb)  wher
      		 
        #0  0x00007ffff5f90425 in __GI_raise (sig=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
      		 
        #1  0x00007ffff5f93b8b in __GI_abort () at abort.c:91
      		 
        #2  0x00007ffff5f890ee in __assert_fail_base (fmt=<optimized out>, assertion=0x555556377658 "0", file=0x5555563777a8 "/home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc", line=<optimized out>, function=<optimized out>) at assert.c:94
      		 
        #3  0x00007ffff5f89192 in __GI___assert_fail (assertion=0x555556377658 "0", file=0x5555563777a8 "/home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc", line=1585, function=0x555556377fc0 "const char* Explain_quick_select::get_name_by_type()") at assert.c:103
      		 
        #4  0x0000555555b7b456 in Explain_quick_select::get_name_by_type (this=0x7fff9000a640) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:1585
      		 
        #5  0x0000555555b7b340 in Explain_quick_select::print_extra_recursive (this=0x7fff9000a640, str=0x7ffff0336d00) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:1554
      		 
        #6  0x0000555555b79019 in Explain_table_access::fill_key_str (this=0x7fff9000a340, key_str=0x7ffff0336e20, is_json=true) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:822
      		 
        #7  0x0000555555b7a405 in Explain_table_access::print_explain_json (this=0x7fff9000a340, query=0x7fff90009b90, writer=0x7ffff0337010, is_analyze=false) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:1230
      		 
        #8  0x0000555555b78df2 in Explain_basic_join::print_explain_json_interns (this=0x7fff90009d00, query=0x7fff90009b90, writer=0x7ffff0337010, is_analyze=false) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:777
      		 
        #9  0x0000555555b78cae in Explain_select::print_explain_json (this=0x7fff90009d00, query=0x7fff90009b90, writer=0x7ffff0337010, is_analyze=false) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:747
      		 
        #10 0x0000555555b770b0 in Explain_query::print_explain_json (this=0x7fff90009b90, output=0x7fff90008130, is_analyze=false) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_explain.cc:208
      		 
        #11 0x0000555555a1fece in execute_sqlcom_select (thd=0x5555582cac60, all_tables=0x7fff90007918) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_parse.cc:5733
      		 
        #12 0x0000555555a16556 in mysql_execute_command (thd=0x5555582cac60) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_parse.cc:2926
      		 
        #13 0x0000555555a237cb in mysql_parse (thd=0x5555582cac60, rawbuf=0x7fff90007558 "explain format=json select count(distinct b) from t1 group by a", length=63, parser_state=0x7ffff03380c0) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_parse.cc:7165
      		 
        #14 0x0000555555a127f5 in dispatch_command (command=COM_QUERY, thd=0x5555582cac60, packet=0x5555582d1ee1 "", packet_length=63) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_parse.cc:1462
      		 
        #15 0x0000555555a115bf in do_command (thd=0x5555582cac60) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_parse.cc:1090
      		 
        #16 0x0000555555b3e6fc in do_handle_one_connection (thd_arg=0x5555582cac60) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_connect.cc:1347
      		 
        #17 0x0000555555b3e441 in handle_one_connection (arg=0x5555582cac60) at /home/psergey/dev-git/10.1-explain-analyze/sql/sql_connect.cc:1258
      		 
        #18 0x0000555555edae78 in pfs_spawn_thread (arg=0x5555582d71c0) at /home/psergey/dev-git/10.1-explain-analyze/storage/perfschema/pfs.cc:1860
      		 
        #19 0x00007ffff691de9a in start_thread (arg=0x7ffff0339700) at pthread_create.c:308
      		 
        #20 0x00007ffff604e3fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          AFAIU, EXPLAIN FORMAT=JSON code is simply missing the code that handles QUICK_GROUP_MIN_MAX_SELECT.

          psergei Sergei Petrunia added a comment - AFAIU, EXPLAIN FORMAT=JSON code is simply missing the code that handles QUICK_GROUP_MIN_MAX_SELECT.
          sanja Oleksandr Byelkin added a comment -

          revision-id: 0f3132bae62f98aa78836e3341975aad78ab5627
          parent(s): 0df8c0aa5ed1a6d3869783a30cbe71521cffa4e4
          committer: Oleksandr Byelkin
          branch nick: server
          timestamp: 2015-04-07 17:05:45 +0200
          message:

          MDEV-7860: EXPLAIN FORMAT=JSON crashes for loose scan query

          Forgotten pattern for using index added.

          —

          sanja Oleksandr Byelkin added a comment - revision-id: 0f3132bae62f98aa78836e3341975aad78ab5627 parent(s): 0df8c0aa5ed1a6d3869783a30cbe71521cffa4e4 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-04-07 17:05:45 +0200 message: MDEV-7860 : EXPLAIN FORMAT=JSON crashes for loose scan query Forgotten pattern for using index added. —
          psergei Sergei Petrunia added a comment -

          The patch fixes the crash but does not fully resolve the issue of printing the LooseScan query plans. I am looking at these two queries I have found in group_min_max.test:

          explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a');
          		 
          explain format=json select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121');

          Tabular EXPLAINs are different:

          +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+
          		 
          | id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                                 |
          		 
          +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+
          		 
          |    1 | SIMPLE      | t1    | range | NULL          | idx_t1_1 | 147     | NULL |   24 | Using where; Using index for group-by |
          		 
          +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+
          		 
           
          		 
          +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+
          		 
          | id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                                            |
          		 
          +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+
          		 
          |    1 | SIMPLE      | t1    | range | NULL          | idx_t1_1 | 163     | NULL |  119 | Using where; Using index for group-by (scanning) |
          		 
          +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+
          		 

          EXPLAIN FORMAT=JSON is the same:
          		 
          | {
          		 
            "query_block": {
          		 
              "select_id": 1,
          		 
              "table": {
          		 
                "table_name": "t1",
          		 
                "access_type": "range",
          		 
                "key": "idx_t1_1",
          		 
                "key_length": "147",
          		 
                "used_key_parts": ["a1", "a2", "b"],
          		 
                "rows": 24,
          		 
                "filtered": 100,
          		 
                "attached_condition": "((t1.b = 'a') and (t1.a2 >= 'b'))",
          		 
                "using_index": "GROUP_BY"
          		 
              }
          		 
            }
          		 
          }
          		 
           
          		 
           
          		 
          {
          		 
            "query_block": {
          		 
              "select_id": 1,
          		 
              "table": {
          		 
                "table_name": "t1",
          		 
                "access_type": "range",
          		 
                "key": "idx_t1_1",
          		 
                "key_length": "163",
          		 
                "used_key_parts": ["a1", "a2", "b", "c"],
          		 
                "rows": 119,
          		 
                "filtered": 99.16,
          		 
                "attached_condition": "((t1.b = 'a') and (t1.c = 'i121') and (t1.a2 >= 'b'))",
          		 
                "using_index": "GROUP_BY"
          		 
              }
          		 
            }
          		 
          } |

          psergei Sergei Petrunia added a comment - The patch fixes the crash but does not fully resolve the issue of printing the LooseScan query plans. I am looking at these two queries I have found in group_min_max.test: explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); explain format=json select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); Tabular EXPLAINs are different: +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t1 | range | NULL | idx_t1_1 | 147 | NULL | 24 | Using where; Using index for group-by | +------+-------------+-------+-------+---------------+----------+---------+------+------+---------------------------------------+   +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | t1 | range | NULL | idx_t1_1 | 163 | NULL | 119 | Using where; Using index for group-by (scanning) | +------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------------------------------+ EXPLAIN FORMAT=JSON is the same: | { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "range", "key": "idx_t1_1", "key_length": "147", "used_key_parts": ["a1", "a2", "b"], "rows": 24, "filtered": 100, "attached_condition": "((t1.b = 'a') and (t1.a2 >= 'b'))", "using_index": "GROUP_BY" } } }     { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "range", "key": "idx_t1_1", "key_length": "163", "used_key_parts": ["a1", "a2", "b", "c"], "rows": 119, "filtered": 99.16, "attached_condition": "((t1.b = 'a') and (t1.c = 'i121') and (t1.a2 >= 'b'))", "using_index": "GROUP_BY" } } } |
          psergei Sergei Petrunia added a comment -

          Another issue is WHY WRITE "GROUP_BY" IN CAPS WHEN ALL OTHER OUTPUT IS IN SMALL LETTERS?

          psergei Sergei Petrunia added a comment - Another issue is WHY WRITE "GROUP_BY" IN CAPS WHEN ALL OTHER OUTPUT IS IN SMALL LETTERS?
          psergei Sergei Petrunia added a comment -

          MySQL 5.6 uses these

                "using_index_for_group_by": true,
          		 
                "using_index_for_group_by": "scanning",

          full outputs:

          | {
          		 
            "query_block": {
          		 
              "select_id": 1,
          		 
              "table": {
          		 
                "table_name": "t1",
          		 
                "access_type": "range",
          		 
                "possible_keys": [
          		 
                  "idx_t1_1",
          		 
                  "idx_t1_2"
          		 
                ],
          		 
                "key": "idx_t1_1",
          		 
                "used_key_parts": [
          		 
                  "a1",
          		 
                  "a2",
          		 
                  "b"
          		 
                ],
          		 
                "key_length": "147",
          		 
                "rows": 33,
          		 
                "filtered": 100,
          		 
                "using_index_for_group_by": true,
          		 
                "attached_condition": "((`j4`.`t1`.`b` = 'a') and (`j4`.`t1`.`a2` >= 'b'))"
          		 
              }
          		 
            }
          		 
          } |
          		 
           
          		 
          | {
          		 
            "query_block": {
          		 
              "select_id": 1,
          		 
              "table": {
          		 
                "table_name": "t1",
          		 
                "access_type": "range",
          		 
                "possible_keys": [
          		 
                  "idx_t1_1"
          		 
                ],
          		 
                "key": "idx_t1_1",
          		 
                "used_key_parts": [
          		 
                  "a1",
          		 
                  "a2",
          		 
                  "b",
          		 
                  "c"
          		 
                ],
          		 
                "key_length": "163",
          		 
                "rows": 129,
          		 
                "filtered": 99.225,
          		 
                "using_index_for_group_by": "scanning",
          		 
                "attached_condition": "((`j4`.`t1`.`c` = 'i121') and (`j4`.`t1`.`b` = 'a') and (`j4`.`t1`.`a2` >= 'b'))"
          		 
              }
          		 
            }
          		 
          } |

          psergei Sergei Petrunia added a comment - MySQL 5.6 uses these "using_index_for_group_by": true, "using_index_for_group_by": "scanning", full outputs: | { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "range", "possible_keys": [ "idx_t1_1", "idx_t1_2" ], "key": "idx_t1_1", "used_key_parts": [ "a1", "a2", "b" ], "key_length": "147", "rows": 33, "filtered": 100, "using_index_for_group_by": true, "attached_condition": "((`j4`.`t1`.`b` = 'a') and (`j4`.`t1`.`a2` >= 'b'))" } } } |   | { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "range", "possible_keys": [ "idx_t1_1" ], "key": "idx_t1_1", "used_key_parts": [ "a1", "a2", "b", "c" ], "key_length": "163", "rows": 129, "filtered": 99.225, "using_index_for_group_by": "scanning", "attached_condition": "((`j4`.`t1`.`c` = 'i121') and (`j4`.`t1`.`b` = 'a') and (`j4`.`t1`.`a2` >= 'b'))" } } } |
          psergei Sergei Petrunia added a comment -

          sanja, thanks for the patch. I will fix it and push.

          psergei Sergei Petrunia added a comment - sanja , thanks for the patch. I will fix it and push.

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.