Description:
Different results are returned when the optimizer chooses different plans on the federated engine.
How to repeat:
# On the remote server
CREATE TABLE federated.test_table (
id INT NOT NULL,
non_id SMALLINT UNSIGNED,
name VARCHAR(32) NOT NULL DEFAULT '',
non_name VARCHAR(20),
KEY (id)
);
INSERT INTO federated.test_table VALUES (14, 65535, 'exhope', 'bmtalk'), (14, 32767, 'eared', 'biblue');
# On the local server
CREATE TABLE federated.t1 (
id INT NOT NULL,
non_id SMALLINT,
name VARCHAR(32) NOT NULL DEFAULT '',
non_name VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_german1_ci,
KEY (non_id)
) ENGINE=FEDERATED
CONNECTION='mysql://root@127.0.0.1:3306/federated/test_table';
EXPLAIN SELECT * FROM federated.t1 WHERE non_id = 32767;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | non_id | non_id | 3 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
SELECT * FROM federated.t1 WHERE non_id = 32767;
+----+--------+-------+----------+
| id | non_id | name | non_name |
+----+--------+-------+----------+
| 14 | 32767 | eared | biblue |
+----+--------+-------+----------+
1 row in set (0.00 sec)
EXPLAIN SELECT /*+ NO_INDEX(t1 non_id) */ * FROM federated.t1 WHERE non_id = 32767;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
SELECT /*+ NO_INDEX(t1 non_id) */ * FROM federated.t1 WHERE non_id = 32767;
+----+--------+--------+----------+
| id | non_id | name | non_name |
+----+--------+--------+----------+
| 14 | 32767 | exhope | bmtalk |
| 14 | 32767 | eared | biblue |
+----+--------+--------+----------+
2 rows in set (0.02 sec)
Suggested fix:
The root cause of the above issue is that when using index ref scan, the optimizer pushes down the ref condition to the remote server without checking whether the data type definition in the remote table is consistent with the local table.
I think only when the data type in the local table has a larger range than the corresponding field data type in the remote table, we can safely push down the ref condition to the remote server. Otherwise, we should evaluate conditions in the local server.
Description: Different results are returned when the optimizer chooses different plans on the federated engine. How to repeat: # On the remote server CREATE TABLE federated.test_table ( id INT NOT NULL, non_id SMALLINT UNSIGNED, name VARCHAR(32) NOT NULL DEFAULT '', non_name VARCHAR(20), KEY (id) ); INSERT INTO federated.test_table VALUES (14, 65535, 'exhope', 'bmtalk'), (14, 32767, 'eared', 'biblue'); # On the local server CREATE TABLE federated.t1 ( id INT NOT NULL, non_id SMALLINT, name VARCHAR(32) NOT NULL DEFAULT '', non_name VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_german1_ci, KEY (non_id) ) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:3306/federated/test_table'; EXPLAIN SELECT * FROM federated.t1 WHERE non_id = 32767; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | non_id | non_id | 3 | const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) SELECT * FROM federated.t1 WHERE non_id = 32767; +----+--------+-------+----------+ | id | non_id | name | non_name | +----+--------+-------+----------+ | 14 | 32767 | eared | biblue | +----+--------+-------+----------+ 1 row in set (0.00 sec) EXPLAIN SELECT /*+ NO_INDEX(t1 non_id) */ * FROM federated.t1 WHERE non_id = 32767; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) SELECT /*+ NO_INDEX(t1 non_id) */ * FROM federated.t1 WHERE non_id = 32767; +----+--------+--------+----------+ | id | non_id | name | non_name | +----+--------+--------+----------+ | 14 | 32767 | exhope | bmtalk | | 14 | 32767 | eared | biblue | +----+--------+--------+----------+ 2 rows in set (0.02 sec) Suggested fix: The root cause of the above issue is that when using index ref scan, the optimizer pushes down the ref condition to the remote server without checking whether the data type definition in the remote table is consistent with the local table. I think only when the data type in the local table has a larger range than the corresponding field data type in the remote table, we can safely push down the ref condition to the remote server. Otherwise, we should evaluate conditions in the local server.