Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3588

cross engine join with DISTINCT returns null

    XMLWordPrintable

    Details

      Description

      When doing a distinct on a TEXT column while joining a Columnstore and InnoDB table returns the wrong result. The correct amount seems to be returned, but the TEXT column shows as NULL. If the tables are created of the same engine, the distinct works as expected. An example test case for the issue:

      MariaDB [test]> create table inno_table (text_column text, id varchar(8));
      MariaDB [test]> insert into inno_table values ('First Farm',1234),('Second Farm',2345);

      MariaDB [test]> create table cs_table (text_coumn text, id varchar(8), name varchar(10))engine=columnstore;
      MariaDB [test]> insert into cs_table values ('First Farm',1234,'test'),('Second Farm',2345,'test');

      MariaDB [test]> select distinct a.text_column, b.id, b.name from inno_table a join cs_table b on a.id=b.id;
      ------------------------

      text_column id name

      ------------------------

      NULL 1234 test
      NULL 2345 test

      ------------------------

      As you can see, the distinct TEXT column show as null. If the tables are created in the same engine, either one, it works.

      MariaDB [test]> select distinct a.text_column, b.id, b.name from cs_table a join cs_inno_table b on a.id=b.id;
      ------------------------

      text_column id name

      ------------------------

      First Farm 1234 test
      Second Farm 2345 test

      ------------------------

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            kjoiner Kyle Joiner
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: