Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
DATASET
CREATE TABLE t1 (a int) ; |
INSERT INTO t1 VALUES (1), (1); |
CREATE TABLE t2 (b int) ; |
INSERT INTO t2 VALUES (9), (NULL), (7); |
set @@use_stat_tables= PREFERABLY |
set @@optimizer_use_condition_selectivity=3; |
analyze format=json
|
SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL; |
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4197,
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.0274,
|
"filtered": 0,
|
"r_filtered": 33.333,
|
"attached_condition": "t2.b is null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_total_time_ms": 0.0181,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"r_filtered": 100
|
}
|
}
|
}
|
Filtered for table t2 shows 0 which is incorrect as we have 1 row that will satisfy the WHERE clause
Attachments
Issue Links
- is part of
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
Activity
So in our current approach of EITS ,we try to use the statistics of columns of a table even if we have not calculated them.
This does not look correct , because this can impact query plans.
So we have 2 case which are a bit problematic when we change the defaults to use EITS
- When we have no statistics for a table.
- When we have outdated statistics for a table.
When I run ANALYZE on table t2 on the above query and run the same query, filtered is set correctly
ANALYZE TABLE t2;Table Op Msg_type Msg_texttest.t2 analyze status Engine-independent statistics collectedtest.t2 analyze status OKset @@optimizer_use_condition_selectivity=3;analyze format=jsonSELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;ANALYZE{"query_block": {"select_id": 1,"r_loops": 1,"r_total_time_ms": 0.2351,"table": {"table_name": "t2","access_type": "ALL","r_loops": 1,"rows": 3,"r_rows": 3,"r_total_time_ms": 0.022,"filtered": 33.33,"r_filtered": 33.333,"attached_condition": "t2.b is null"},"block-nl-join": {"table": {"table_name": "t1","access_type": "ALL","r_loops": 1,"rows": 2,"r_rows": 2,"r_total_time_ms": 0.0139,"filtered": 100,"r_filtered": 100},"buffer_type": "flat","buffer_size": "256Kb","join_type": "BNL","r_filtered": 100}}}