[MDEV-14907] FEDERATEDX doesn't respect DISTINCT - Jira

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)

Transition	Time In Source Status	Execution Times

Elena Stepanova made transition - 2018-01-09 22:46

Open

Confirmed

3h 31m

Michael Widenius made transition - 2021-12-27 17:27

Confirmed

In Progress

1447d 18h 41m

Michael Widenius made transition - 2022-01-07 11:05

In Progress

Closed

10d 17h 37m

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server