[MCOL-3588] cross engine join with DISTINCT returns null Created: 2019-11-04  Updated: 2020-08-25  Resolved: 2019-11-28

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: None
Fix Version/s: 1.2.6, 1.4.1

Type: Bug Priority: Major
Reporter: Kyle Joiner (Inactive) Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None


 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

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

 Comments   
Comment by Roman [ 2019-11-28 ]

JFYI It is fixed in 1.4.1

MariaDB [test]> create table inno_table (text_column text, id varchar(8));                                                                                                                                                                    
Query OK, 0 rows affected (0.045 sec)                                                                                                                                                                                                         
 
 
MariaDB [test]> insert into inno_table values ('First Farm',1234),('Second Farm',2345);
 
Query OK, 2 rows affected (0.004 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> create table cs_table (text_coumn text, id varchar(8), name varchar(10))engine=columnstore;
 
Query OK, 0 rows affected (1.551 sec)
 
 
 
MariaDB [test]> insert into cs_table values ('First Farm',1234,'test'),('Second Farm',2345,'test');
 
Query OK, 2 rows affected (0.742 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
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 |
 
+-------------+------+------+
 
| First Farm  | 1234 | test |
 
| Second Farm | 2345 | test |
 
+-------------+------+------+
 
2 rows in set (0.073 sec)
 
 
 
MariaDB [test]> insert into cs_table values ('First Farm',1234,'test'),('Second Farm',2345,'test') ;
 
Query OK, 2 rows affected (0.528 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
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 |
 
+-------------+------+------+
 
| First Farm  | 1234 | test |
 
| Second Farm | 2345 | test |
 
+-------------+------+------+
 
2 rows in set (0.032 sec)

Comment by Roman [ 2019-11-28 ]

It also works with unreleased 1.2.6 code:

MariaDB [test]> insert into inno_table values ('First Farm',1234),('Second Farm',2345);
 
Query OK, 2 rows affected (0.155 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> create table cs_table (text_coumn text, id varchar(8), name varchar(10))engine=columnstore;
 
Query OK, 0 rows affected (1.154 sec)
 
 
 
MariaDB [test]>  insert into cs_table values ('First Farm',1234,'test'),('Second Farm',2345,'test');
 
Query OK, 2 rows affected (0.682 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
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 |
 
+-------------+------+------+
 
| First Farm  | 1234 | test |
 
| Second Farm | 2345 | test |
 
+-------------+------+------+
 
2 rows in set (0.137 sec)

Generated at Thu Feb 08 02:43:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.