Details
Major Description
CREATE TABLE t (a int, b int, KEY b (b,a)) ENGINE=MyISAM; |
INSERT INTO t VALUES (0,100),(2,100),(2,101),(3,102); |
|
|
SELECT DISTINCT b FROM t WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); |
|
|
# Cleanup
|
DROP TABLE t; |
|
Actual result on 10.5 4c3ad244 |
SELECT DISTINCT b FROM t WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); |
b
|
101
|
102
|
This is naturally wrong, 100 should also be there.
The failure appeared in 10.5 branch after this commit in 10.5.3:
commit eb483c5181ab430877c135c16224284cfc517b3d
|
Author: Monty
|
Date: Fri Feb 28 12:59:30 2020 +0200
|
|
|
Updated optimizer costs in multi_range_read_info_const() and sql_select.cc
|
|
Plan on the current branch (wrong result) |
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t",
|
"access_type": "range",
|
"key": "b",
|
"key_length": "5",
|
"used_key_parts": ["b"],
|
"rows": 5,
|
"filtered": 100,
|
"attached_condition": "<in_optimizer>(1,exists(subquery#2))",
|
"using_index_for_group_by": true
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"state": "uninitialized",
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"message": "No tables used"
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
|
Plan before the change (correct result) |
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t",
|
"access_type": "index",
|
"key": "b",
|
"key_length": "10",
|
"used_key_parts": ["b", "a"],
|
"rows": 4,
|
"filtered": 100,
|
"attached_condition": "<in_optimizer>(1,exists(subquery#2))",
|
"using_index": true
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"state": "uninitialized",
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"message": "No tables used"
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|