|
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
|
}
|
]
|
]
|
|