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

            Unassigned Unassigned
            kjoiner Kyle Joiner (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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