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

Second SELECT from VIEW based on information_schema.optimizer_trace gives NULL result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.4(EOL)
    • 10.4.33
    • Optimizer
    • None

    Description

      In case below it is expected that result from both SELECT from VIEW will be the same:

      --source include/have_sequence.inc
       
      create table t1(a int, b int);
      insert into t1 
      select seq,seq from seq_1_to_5;
       
      create table t2(a int, b int, key(a));
      insert into t2
      select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
       
      create table t3(a int, b int, key(a));
      insert into t3
      select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
       
      create table t10 (grp_id int, col1 int, key(grp_id));
      insert into t10
      select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B;
       
      create table t11 (col1 int, col2 int);
      insert into t11
      select A.seq, A.seq from seq_1_to_10 A;
       
      analyze table t1,t2,t3,t10,t11 persistent for all;
       
      set optimizer_trace=1;
      explain 
      select * from
        (
          (t1 left join t2 on t2.a=t1.b)
          left join t3 on t3.a=t1.b
        ) left join (select grp_id, count(*)
                     from t10 left join t11 on t11.col1=t10.col1
                     group by grp_id) T on T.grp_id=t1.b;
       
       
      create view v1 as select 
        json_detailed(
          json_remove(
            json_extract(trace, '$**.choose_best_splitting')
            , '$[0].split_plan_search[0]'
          )
        ) as JS
      from information_schema.optimizer_trace;
       
      select * from v1;
      select * from v1;
       
      drop view v1;
      drop table t1,t2,t3,t10,t11;
      set optimizer_trace=DEFAULT;
      

      Actual result:

      JS
      [
          {
              "considered_keys": 
              [
                  {
                      "table_name": "t10",
                      "index": "grp_id",
                      "rec_per_key": 100,
                      "param_tables": 1
                  }
              ],
              "refills": 5,
              "spl_pd_boundary": 2,
              "split_plan_search": 
              [],
              "lead_table": "t10",
              "index": "grp_id",
              "parts": 1,
              "split_sel": 0.001,
              "cost": 2536,
              "records": 100,
              "refills": 5,
              "chosen": true
          }
      ]
      select * from v1;
      JS
      NULL
      

      Expected result:

      JS
      [
          {
              "considered_keys": 
              [
                  {
                      "table_name": "t10",
                      "index": "grp_id",
                      "rec_per_key": 100,
                      "param_tables": 1
                  }
              ],
              "refills": 5,
              "spl_pd_boundary": 2,
              "split_plan_search": 
              [],
              "lead_table": "t10",
              "index": "grp_id",
              "parts": 1,
              "split_sel": 0.001,
              "cost": 2536,
              "records": 100,
              "refills": 5,
              "chosen": true
          }
      ]
      select * from v1;
      JS
      [
          {
              "considered_keys": 
              [
                  {
                      "table_name": "t10",
                      "index": "grp_id",
                      "rec_per_key": 100,
                      "param_tables": 1
                  }
              ],
              "refills": 5,
              "spl_pd_boundary": 2,
              "split_plan_search": 
              [],
              "lead_table": "t10",
              "index": "grp_id",
              "parts": 1,
              "split_sel": 0.001,
              "cost": 2536,
              "records": 100,
              "refills": 5,
              "chosen": true
          }
      ]
      

      After fix enable "view-protocol" for cases marked with MDEV-31408

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.