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

Second SELECT from view created from INFORMATION_SCHEMA.OPTIMIZER_TRACE gives NULL

    XMLWordPrintable

Details

    Description

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

      In case below if the query "select ... from INFORMATION_SCHEMA.OPTIMIZER_TRACE;" repeat twice - both SELECTs give right result, but if create view on this select - second query "select * from v1" gives NULL.
      Testcase:

      --source include/have_sequence.inc
      CREATE TABLE t1(a INT, b INT);
      INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
      SET optimizer_trace=1;
      ANALYZE TABLE t1 PERSISTENT FOR ALL;
      EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      create view v1 as select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      select * from v1;
      select * from v1;
      drop view v1;
      drop table t1;
      

      Actual result:

      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      create view v1 as select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      select * from v1;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      select * from v1;
      JS
      NULL
      

      Expected result:

      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      create view v1 as select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      select * from v1;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      select * from v1;
      JS
      [
          [
              {
                  "column_name": "a",
                  "ranges": 
                  ["1 <= a <= 5"],
                  "selectivity_from_histogram": 0.0469
              },
              {
                  "column_name": "b",
                  "ranges": 
                  ["NULL < b <= 5"],
                  "selectivity_from_histogram": 0.0469
              }
          ]
      ]
      

      Attachments

        Issue Links

          Activity

            People

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