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

ANALYZE FORMAT=JSON doesn't show expression cache for IN subqueries

    XMLWordPrintable

Details

    Description

      Stumbled on this when reviewing MDEV-34041.

      Consider this testcase (not necessarily minimal):

      create table t1 (a int, b int, c int);
      insert into t1 select seq,seq,seq from seq_1_to_10;
      create table t2 (a int, b int, c int);
      insert into t2 select seq,seq,seq from seq_1_to_10;
      insert into t2 select seq,seq,seq from seq_1_to_10;
      insert into t2 select seq,seq,seq from seq_1_to_10;
      insert into t2 select seq,seq,seq from seq_1_to_10;
      insert into t2 select seq,seq,seq from seq_1_to_10;
      insert into t1 values (),();
      insert into t1 values (),();
      

      analyze format=json select a in (select a from t2) from t1;
      

      shows

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 6515166.843,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 14,
            "r_rows": 14,
            "r_table_time_ms": 0.421506134,
            "r_other_time_ms": 6515162.901,
            "filtered": 100,
            "r_filtered": 100
          },
          "subqueries": [
            {
              "materialization": {
                "r_strategy": "partial_match_scan",
                "r_loops": 11,
                "r_index_lookup_loops": 10,
                "r_partial_match_loops": 1,
                "query_block": {
                  "select_id": 2,
                  "r_loops": 1,
                  "r_total_time_ms": 3.349656134,
                  "table": {
                    "table_name": "t2",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 50,
                    "r_rows": 50,
                    "r_table_time_ms": 2.853555948,
                    "r_other_time_ms": 0.474497955,
                    "filtered": 100,
                    "r_filtered": 100
                  }
                }
              }
            }
          ]
        }
      }
      

      This doesn't show expression_cache, but if I put a breakpoint into
      Expression_cache_tmptable::check_value I see that it is hit.

      (gdb) wher
        #0  Expression_cache_tmptable::check_value (this=0x7fff14078448, value=0x7ffff4074c78) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/              sql_expression_cache.cc:219
        #1  0x000055555626bcdc in Item_cache_wrapper::check_cache (this=0x7fff140782c0) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/item.cc:8970
        #2  0x000055555626bf37 in Item_cache_wrapper::val_int (this=0x7fff140782c0) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/item.cc:9033
        #3  0x000055555611f6e3 in Type_handler::Item_send_long (this=0x555557aaf120 <type_handler_bool>, item=0x7fff140782c0, protocol=0x55555e53f600,            buf=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_type.cc:7598
        #4  0x000055555612cb18 in Type_handler_long::Item_send (this=0x555557aaf120 <type_handler_bool>, item=0x7fff140782c0, protocol=0x55555e53f600,            buf=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_type.h:5735
        #5  0x0000555555d72e86 in Item::send (this=0x7fff140782c0, protocol=0x55555e53f600, buffer=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-review-34041-   orig/sql/item.h:1082
        #6  0x000055555626b9a4 in Item_cache_wrapper::send (this=0x7fff140782c0, protocol=0x55555e53f600, buffer=0x7ffff4074e60) at /home/psergey/dev-git2/10.5-  review-34041-orig/sql/item.cc:8891
        #7  0x0000555555d6c5b3 in Protocol::send_result_set_row (this=0x55555e53f600, row_items=0x7fff14016698) at /home/psergey/dev-git2/10.5-review-34041-orig/ sql/protocol.cc:1086
        #8  0x0000555555e2b033 in select_send::send_data (this=0x7fff14019468, items=@0x7fff14016698: {<base_list> = {<Sql_alloc> = {<No data fields>}, first =   0x7fff14018390, last = 0x7fff14018390, elements = 1}, <No data fields>}) at /home/psergey/dev-git2/10.5-review-34041-orig/sql/sql_class.cc:3160
        #9  0x0000555555f4cde5 in select_result_sink::send_data_with_check (this=0x7fff14019468, items=@0x7fff14016698: {<base_list> = {<Sql_alloc> = {<No data   fields>}, first = 0x7fff14018390, last = 0x7fff14018390, elements = 1}, <No data fields>}, u=0x7fff14004f28, sent=0) at /home/psergey/dev-git2/10.5-review- 34041-orig/sql/sql_class.h:5564
      

      For comparison, a non-IN subquery does show expression_cache:

      analyze format=json select a > (select max(a) from t2 where t2.c<=t1.c) from t1;
      

      ...
          "subqueries": [
            {
              "expression_cache": {
                "r_loops": 10,
                "r_hit_ratio": 0,
                "query_block": {
                  "select_id": 2,
                  "r_loops": 10,
      

      Attachments

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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