Bug #102635 federated engine if the plan type is index_merge return wrong result
Submitted: 18 Feb 5:12 Modified: 18 Feb 14:28
Reporter: 송화 SONGHUA Email Updates:
Status: Not a Bug Impact on me:
None 
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 5:12] 송화 SONGHUA
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)
[18 Feb 6:42] 송화 SONGHUA
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 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.