Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
-
None
Description
--source include/have_sequence.inc
|
|
|
CREATE TABLE t (a INT, b INT, KEY(a)) ENGINE=MyISAM; |
INSERT INTO t SELECT 5, 5 FROM seq_1_to_1000; |
|
|
ANALYZE TABLE t PERSISTENT FOR ALL; |
|
|
ANALYZE FORMAT=JSON
|
SELECT * FROM t WHERE 100 = b AND a = 100 OR a IS NULL AND a > 100; |
|
|
# Cleanup
|
DROP TABLE t; |
|
428b057e |
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 0.021893114, |
"table": { |
"table_name": "t", |
"access_type": "ref_or_null", |
"possible_keys": ["a"], |
"key": "a", |
"key_length": "5", |
"used_key_parts": ["a"], |
"ref": ["const"], |
"r_loops": 1, |
"rows": 2000, |
"r_rows": 0, |
"r_table_time_ms": 0.014110678, |
"r_other_time_ms": 0.005044542, |
"filtered": 99.21875, |
"r_filtered": 100, |
"index_condition": "t.a = 100", |
"attached_condition": "t.b = 100" |
}
|
}
|
}
|
So, neither the WHERE condition as a whole nor any part of it can keep any rows, and yet r_filtered is 100%.
Also, it's unclear how the plan comes up with rows==2000 while the table has 1000 rows.
Initially the issue popped up in the course of MDEV-26519 testing, when it turned out that filtered rows with JSON_HB (close to 0) was very far from both r_filtered (100) and from filtered with DOUBLE_PREC_HB (close to 100). However, I'm not sure it's even a JSON_HB problem, given that r_filtered=100% is hard to understand here. Please feel free to adjust the issue as needed: close if not a bug (but please with an explanation how to interpret r_filtered value in this case), or handle as a maintenance problem, or make it MDEV-26519-related if it is.
|
preview-10.8-MDEV-26519-json-histograms 9a86900b |
SET histogram_type=JSON_HB; |
ANALYZE TABLE t PERSISTENT FOR ALL; |
Table Op Msg_type Msg_text |
test.t analyze status Engine-independent statistics collected |
test.t analyze status Table is already up to date |
ANALYZE FORMAT=JSON
|
SELECT * FROM t WHERE 100 = b AND a = 100 OR a IS NULL AND a > 100; |
ANALYZE
|
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 0.109424208, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t", |
"access_type": "ref_or_null", |
"possible_keys": ["a"], |
"key": "a", |
"key_length": "5", |
"used_key_parts": ["a"], |
"ref": ["const"], |
"r_loops": 1, |
"rows": 2000, |
"r_rows": 0, |
"r_table_time_ms": 0.072815926, |
"r_other_time_ms": 0.02403827, |
"filtered": 0.100000001, |
"r_filtered": 100, |
"index_condition": "t.a = 100", |
"attached_condition": "t.b = 100" |
}
|
}
|
]
|
}
|
}
|
Attachments
Issue Links
- relates to
-
MDEV-26519 JSON Histograms: improve histogram collection
-
- Closed
-