Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.1(EOL)
-
None
Description
The Connect 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 (see the part of the test for connection 'master'):
"access_type": "ALL" |
"attached_condition": "year(t1.a) = 2009" |
Example test:
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); |
connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,); |
|
|
connection master; |
CREATE DATABASE connect; |
|
|
connection slave; |
CREATE DATABASE connect; |
|
|
create table t1 (pk int primary key, a datetime, key(a)) ; |
|
|
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; |
|
|
connection master; |
eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MYSQL |
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT'; |
explain format=json select * from t1 where year(a) = 2009; |
DROP TABLE t1; |
|
|
connection slave; |
DROP TABLE t1; |
DROP DATABASE connect; |
|
|
connection master; |
DROP DATABASE connect; |
Actual result:
explain format=json select * from t1 where year(a) = 2009; |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"cost": 0.11378782, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"access_type": "ALL", |
"loops": 1, |
"rows": 10, |
"cost": 0.11378782, |
"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
-
As I can see from https://mariadb.com/kb/en/using-connect-indexing/#remote-indexing and https://mariadb.com/kb/en/connect-mysql-table-type-accessing-mysqlmariadb-tables/#indexing-of-mysql-tables it doesn't make sense (or even not allowed) to create indexes on tables referencing remote ones. And the SQL rewrite happens only when the column is a part of an index. That's why there's no rewrite at the master side.
Same applies for FederatedX tables.
If you can somehow retrieve an explain plan for the master query from the slave side (i.e. how the slave executes the query from the master server) we can check whether the rewrite happens there.