[MDEV-32034] Second SELECT from view created on JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE gives NULL - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3
Component/s: Optimizer
Labels:
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

MDEV-31933 Make working view-protocol + ps-protocol (running two protocols together)

Stalled

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-08-29 05:11

Updated:: Yesterday 10:30

Git Integration

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