Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
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 |
------------------------
Attachments
Activity
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)
|
|
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: 0MariaDB [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: 0MariaDB [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: 0MariaDB [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)