Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.6, 10.2.12, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Fix Version/s | 10.0 [ 16000 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Workflow | MariaDB v3 [ 84808 ] | MariaDB v4 [ 144001 ] |
| Assignee | Sergei Golubchik [ serg ] | Michael Widenius [ monty ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2022-01-07 11:05:28.0 | 2022-01-07 11:05:28.978 |
| Fix Version/s | 10.4.23 [ 26807 ] | |
| Fix Version/s | 10.5.14 [ 26809 ] | |
| Fix Version/s | 10.6.6 [ 26811 ] | |
| Fix Version/s | 10.7.2 [ 26813 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |