[#MDEV-22246] Result rows duplicated by spider engine

I've two mysql server 'G0' and 'G1'. And there is a one row test table on them respectively.

G0:

mysql> select * from test.t1;

+----+--------+

| id | node   |

+----+--------+

|  1 | DB-G0  |

+----+--------+

1 rows in set (0.00 sec)

G1:

mysql> select * from test.t1;

+----+--------+

| id | node   |

+----+--------+

|  2 | DB-G1  |

+----+--------+

1 rows in set (0.00 sec)

I'm then using the spider engine offered by MariaDB 10.4 (mysqld Ver 10.4.12-MariaDB-1:10.4.12+maria~bionic-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)) to combine them like this:

CREATE TABLE t1 (

   id BIGINT PRIMARY KEY,

   node TEXT

) ENGINE=Spider

COMMENT='wrapper "mysql"'

PARTITION BY HASH (id) (

 PARTITION par0 COMMENT = 'srv "G0", tbl "t1"',

 PARTITION par1 COMMENT = 'srv "G1", tbl "t1"'

);

It's working well for a simple query like this:

MariaDB [(none)]> select * from test.t1;

+-----+--------+

| id  | node   |

+-----+--------+

|   1 | DB-G0  |

|   2 | DB-G1  |

+-----+--------+

2 rows in set (0.013 sec)

But it doubled the results when using this condition:

MariaDB [(none)]> select * from test.t1 where id != 0;

+-----+--------+

| id  | node   |

+-----+--------+

|   1 | DB-G0  |

|   2 | DB-G1  |

|   1 | DB-G0  |

|   2 | DB-G1  |

+-----+--------+

4 rows in set (0.017 sec)

Here is my Spider related settings:

# * Spider

spider_bulk_update_mode      = 2

spider_bulk_update_size      = 512K

spider_bulk_size             = 512K

spider_conn_wait_timeout     = 5

spider_connect_timeout       = 3

spider_direct_dup_insert     = 1

spider_direct_order_limit    = 1

spider_multi_split_read      = 1

spider_net_read_timeout      = 5

spider_net_write_timeout     = 5

spider_quick_mode            = 3

spider_quick_page_size       = 1000

spider_remote_trx_isolation  = 1

spider_support_xa            = 0

spider_remote_time_zone        = +00:00

spider_remote_access_charset   = utf8mb4

spider_remote_autocommit       = 1

spider_remote_default_database = 0

spider_general_log          = 1

spider_internal_sql_log_off = 0

spider_log_result_errors    = 4

I commented out all the above settings and tried again, the results are same.

I've also changed the PARTITION BY HASH (id) to PARTITION BY KEY(id). And the result is exactly same, the duplicated rows still there.

[MDEV-22246] Result rows duplicated by spider engine Created: 2020-04-14 Updated: 2020-10-06 Resolved: 2020-08-25
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.4.12
Fix Version/s:	10.4.16, 10.5.7, 10.6.0

[MDEV-22246] Result rows duplicated by spider engine Created: 2020-04-14 Updated: 2020-10-06 Resolved: 2020-08-25