Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14907

FEDERATEDX doesn't respect DISTINCT

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            bgrossman Brian Grossman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: