Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0, 10.1, 10.2.6, 10.2.12, 10.2
-
None
Description
The FEDERATEDX engine doesn't seem to respect DISTINCT. I see the following behavior in 10.2.6 and 10.2.12. (Names and values redacted.)
Here's a table FOO in bar.
CREATE TABLE `FOO` (
|
`foo_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`foo_name` varchar(255) DEFAULT NULL,
|
`parent_foo_id` bigint(20) unsigned DEFAULT NULL,
|
PRIMARY KEY (`foo_id`),
|
KEY `foo_name` (`foo_name`),
|
KEY `parent_foo_id` (`parent_foo_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=978304 DEFAULT CHARSET=utf8
|
There's another table bar_FOO in another mariadb instance, defined the same except for using FEDERATEDX:
CREATE TABLE `bar_FOO` (
|
...
|
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pw@bar-host:3306/bar/FOO'
|
The FEDERATEDX plugin is installed. From show plugins:
+-------------------------------+----------+--------------------+------------------+---------+
|
| Name | Status | Type | Library | License |
|
+-------------------------------+----------+--------------------+------------------+---------+
|
| ... | | | | |
|
| FEDERATED | ACTIVE | STORAGE ENGINE | ha_federatedx.so | GPL |
|
+-------------------------------+----------+--------------------+------------------+---------+
|
Here's the query run against the InnoDB table:
MariaDB [bar]> select DISTINCT foo_id,parent_foo_id,foo_name from FOO where parent_foo_id = 822857 or foo_name like 'STRING%' order by foo_id ;
|
+------------+---------------+------------+
|
| foo_id | parent_foo_id | foo_name |
|
+------------+---------------+------------+
|
| 822857 | NULL | STRING |
|
| 968903 | 822857 | STRING - 0 |
|
| 968953 | 822857 | STRING - 1 |
|
| 971603 | 822857 | STRING - 2 |
|
| 971803 | 822857 | STRING - 3 |
|
| 975103 | 822857 | STRING - 4 |
|
+------------+---------------+------------+
|
6 rows in set (0.01 sec)
|
Here's the query run against the FEDERATEDX table. The result is the same with or without DISTINCT.
MariaDB [rab]> select DISTINCT foo_id,parent_foo_id,foo_name from bar_FOO where parent_foo_id = 822857 or foo_name like 'STRING%' order by foo_id ;
|
+--------+---------------+------------+
|
| foo_id | parent_foo_id | foo_name |
|
+--------+---------------+------------+
|
| 822857 | NULL | STRING |
|
| 968903 | 822857 | STRING - 0 |
|
| 968903 | 822857 | STRING - 0 |
|
| 968953 | 822857 | STRING - 1 |
|
| 968953 | 822857 | STRING - 1 |
|
| 971603 | 822857 | STRING - 2 |
|
| 971603 | 822857 | STRING - 2 |
|
| 971803 | 822857 | STRING - 3 |
|
| 971803 | 822857 | STRING - 3 |
|
| 975103 | 822857 | STRING - 4 |
|
| 975103 | 822857 | STRING - 4 |
|
+--------+---------------+------------+
|
11 rows in set (0.00 sec)
|