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

Second SELECT from view created from INFORMATION_SCHEMA.OPTIMIZER_TRACE gives NULL

Details

    • Bug
    • Status: In Review (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
    • 10.5, 10.6, 10.11
    • Optimizer
    • None

    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

          relates to

          Technical task - A technical task. MDEV-31933 Make working view-protocol + ps-protocol (running two protocols together)

          • Major - Major loss of function.
          • Stalled

          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.