| Bug #102635 | federated engine if the plan type is index_merge return wrong result | ||
|---|---|---|---|
| Submitted: | 18 Feb 2021 5:12 | Modified: | 18 Feb 2021 14:28 |
| Reporter: | SONGHUA ZHENG | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Federated storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql8/mysql5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | federated | ||
[18 Feb 2021 6:42]
SONGHUA ZHENG
in this case there are two instance ,a,b
instance b :
create database IF NOT EXISTS test ;
create user test@'%' identified by 'test';
grant all privileges on test.* to test@'%';
use test ;
create table test(
id bigint not null auto_increment primary key ,
a1 varchar(10),
a2 varchar(10),
key key1 (a1),
key key2 (a2)
)
;
insert into test (a1,a2) values('a1','a2') ;
set global long_query_time =0 ;
the instance a :
create database IF NOT EXISTS test2 ;
create server t2 foreign data wrapper mysql
options (host '127.0.0.1', database 'test' ,user 'test' , password 'test', port 3357) ;
use tset2 ;
create table t2_test(
id bigint not null auto_increment primary key ,
a1 varchar(10),
a2 varchar(10),
key key1 (a1),
key key2 (a2)
) engine=federated connection='t2/test'
;
root@mysql3306.sock>[test2]>select /*+ INDEX_MERGE(t2_test key1, key2 )*/ * from t2_test where a1='a1' and a2='a2';
slow query file of the instance b ：
# Time: 2021-02-18T04:57:51.496498Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 83
# Query_time: 0.000313 Lock_time: 0.000111 Rows_sent: 1 Rows_examined: 1
SET timestamp=1613624271;
SHOW TABLE STATUS LIKE 'test';
# Time: 2021-02-18T04:57:51.503898Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 88
# Query_time: 0.000162 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 0
SET timestamp=1613624271;
SELECT `id`, `a1`, `a2` FROM `test` WHERE 1=0;
# Time: 2021-02-18T04:57:51.504653Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 88
# Query_time: 0.000188 Lock_time: 0.000049 Rows_sent: 1 Rows_examined: 1
SET timestamp=1613624271;
SELECT `id`, `a1`, `a2` FROM `test` WHERE ( (`a1` = 'a1') ) AND ( (1=1) );
# Time: 2021-02-18T04:57:51.511607Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 89
# Query_time: 0.000122 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 0
SET timestamp=1613624271;
SELECT `id`, `a1`, `a2` FROM `test` WHERE 1=0;
# Time: 2021-02-18T04:57:51.511861Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 89
# Query_time: 0.000134 Lock_time: 0.000041 Rows_sent: 1 Rows_examined: 1
SET timestamp=1613624271;
SELECT `id`, `a1`, `a2` FROM `test` WHERE ( (`a2` = 'a2') ) AND ( (1=1) );
# Time: 2021-02-18T04:57:51.512098Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 88
# Query_time: 0.000003 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1613624271;
# administrator command: Quit;
# Time: 2021-02-18T04:57:51.512206Z
# User@Host: test[test] @ localhost [127.0.0.1] Id: 89
# Query_time: 0.000003 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1613624271;
# administrator command: Quit;
I think the problem is validation in the process of merging result sets.
[18 Feb 2021 14:28]
MySQL Verification Team
Hi Mr. SONGHUA, Thank you very much for your bug report. However, this is not a bug. The behaviour is expected one, as described in chapter 16.8. Namely, you can not expect correct results from another server, when you are stipulating indices. Usage of indices can not be stipulated for the federated table on another table.
Description: The federated engine if the sql plan type is index_merge return wrong result How to repeat: root@mysql3306.sock>[test2]>show variables like '%version%'; +--------------------------+-------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------+ | admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 | | immediate_server_version | 999999 | | innodb_version | 8.0.22 | | original_server_version | 999999 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 | | version | 8.0.22 | | version_comment | MySQL Community Server - GPL | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | +--------------------------+-------------------------------+ create user test@'%' identified by 'test'; create database IF NOT EXISTS test ; create database IF NOT EXISTS test2 ; use test; create server t1 foreign data wrapper mysql options (host '127.0.0.1', database 'test' ,user 'test' , password 'test', port 3306) ; create table test( id bigint not null auto_increment primary key , a1 varchar(10), a2 varchar(10), key key1 (a1), key key2 (a2) ) ; insert into test (a1,a2) values('a1','a2') ; use test2; create table t1_test( id bigint not null auto_increment primary key , a1 varchar(10), a2 varchar(10), key key1 (a1), key key2 (a2) ) engine=federated connection='t1/test' ; root@mysql3306.sock>[test2]>desc select * from t1_test where a1='a1' and a2='a2'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1_test partitions: NULL type: ref possible_keys: key1,key2 key: key1 key_len: 43 ref: const rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) root@mysql3306.sock>[test2]>select * from t1_test where a1='a1' and a2='a2'; +----+------+------+ | id | a1 | a2 | +----+------+------+ | 1 | a1 | a2 | +----+------+------+ 1 row in set (0.00 sec) root@mysql3306.sock>[test2]>desc select /*+ INDEX_MERGE(t1_test key1, key2 )*/ * from t1_test where a1='a1' and a2='a2'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1_test partitions: NULL type: index_merge possible_keys: key1,key2 key: key1,key2 key_len: 43,43 ref: NULL rows: 9223372036854775808 filtered: 100.00 Extra: Using intersect(key1,key2); Using where 1 row in set, 1 warning (0.00 sec) root@mysql3306.sock>[test2]>select /*+ INDEX_MERGE(t1_test key1, key2 )*/ * from t1_test where a1='a1' and a2='a2' ; Empty set (0.01 sec)