Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.1(EOL)
-
None
Description
The Memory engine retained the old behavior.
Instead of:
"access_type": "range" |
"index_condition": "t1.a between '2009-01-01 00:00:00' and '2009-12-31 23:59:59'" |
Query plan has:
"access_type": "ALL" |
"attached_condition": "year(t1.a) = 2009" |
Example test:
create table t1 (pk int primary key, a datetime, key(a)) engine=memory; |
|
|
INSERT INTO t1 (pk,a) VALUES (1,'2009-11-29 13:43:32'); |
INSERT INTO t1 (pk,a) VALUES (2,'2009-11-29 03:23:32'); |
INSERT INTO t1 (pk,a) VALUES (3,'2009-10-16 05:56:32'); |
INSERT INTO t1 (pk,a) VALUES (4,'2010-11-29 13:43:32'); |
INSERT INTO t1 (pk,a) VALUES (5,'2010-10-16 05:56:32'); |
INSERT INTO t1 (pk,a) VALUES (6,'2011-11-29 13:43:32'); |
INSERT INTO t1 (pk,a) VALUES (7,'2012-10-16 05:56:32'); |
|
|
explain format=json select * from t1 where year(a) = 2009; |
|
|
drop table t1; |
Actual result:
explain format=json select * from t1 where year(a) = 2009; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.010296454, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "ALL", |
"loops": 1, |
"rows": 7, |
"cost": 0.010296454, |
"filtered": 100, |
"attached_condition": "year(t1.a) = 2009" |
}
|
}
|
]
|
}
|
}
|
Attachments
Issue Links
- is caused by
-
MDEV-8320 Allow index usage for DATE(datetime_column) = const
-
- Closed
-
Activity
using the check for item_field->field->flags & PART_KEY_FLAG will cause the rewite still occur for key parts of indexes that were disabled with IGNORE INDEX hint:
create table t1 (a datetime, key(a));
|
insert into t1 values (now()),(now());
|
explain format=json
|
select * from t1 ignore index(a) where year(a)=2023;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"cost": 0.01034841,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"loops": 1,
|
"rows": 2,
|
"cost": 0.01034841,
|
"filtered": 100,
|
"attached_condition": "t1.a between <cache>('2023-01-01 00:00:00') and <cache>('2023-12-31 23:59:59')"
|
}
|
}
|
]
|
}
|
}
|
Raising priority to Critical to get into the current release timeline (approved by julien.fritsch).
The reason why the condition rewrite doesn't happen is false result of condition check:
item_field->field->part_of_key.is_overlapping(*used_indexes)which happens here:
opt_rewrite_date.cc lines 201-205
field_type == MYSQL_TYPE_DATETIME ||field_type == MYSQL_TYPE_NEWDATE ||field_type == MYSQL_TYPE_TIMESTAMP) &&item_field->field->part_of_key.is_overlapping(*used_indexes))item_field->field->part_of_key equals to 0 for the MEMORY engine while it equals to 3 for MyISAM. Field::part_of_key is set here:
table.cc TABLE_SHARE::init_from_binary_frm_image
{share->keys_for_keyread.set_bit(key);part_of_key is used to check if we can use the fieldas part of covering key (which implies HA_KEYREAD_ONLY).*/field->part_of_key.set_bit(key);}Condition
is false for the MEMORY engine which prevents setting field->part_of_key bits.