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
- relates to
-
MDEV-31933
Make working view-protocol + ps-protocol (running two protocols together)
-
- Stalled
-