Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.6.5, 23.02.3
Description
Consider the following simple test case:
MariaDB [test]> create table A (id bigint, vc varchar(100), t text, fk_b bigint) engine = Columnstore;
|
Query OK, 0 rows affected (10.585 sec)
|
|
MariaDB [test]> create table B (id bigint, vc varchar(100)) engine = Columnstore;
|
Query OK, 0 rows affected (9.859 sec)
|
|
MariaDB [test]> insert into A values(11, 'abc', 'XYZ', 2);
|
Query OK, 1 row affected (2.188 sec)
|
|
MariaDB [test]> insert into B values (1, 'bla'), (2, 'doh');
|
Query OK, 2 rows affected (1.672 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
This queries produce correct and expected results:
MariaDB [test]> select A.t from B left outer join A on A.fk_b = B.id;
|
+------+
|
| t |
|
+------+
|
| NULL |
|
| XYZ |
|
+------+
|
2 rows in set (0.321 sec)
|
|
MariaDB [test]> select left(A.vc,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
|
+---------------+
|
| left(A.vc,20) |
|
+---------------+
|
| NULL |
|
+---------------+
|
1 row in set (0.370 sec)
|
But if we SELECT the TEXT column with WHERE clause, we get some weird 'pNuLl_' string:
MariaDB [test]> select left(A.t,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
|
+----------------------+
|
| left(A.t,20) |
|
+----------------------+
|
| pNuLl_ |
|
+----------------------+
|
1 row in set (0.037 sec)
|
This is NOT the case with InnoDB:
MariaDB [test]> alter table A engine = InnoDB;
|
Query OK, 1 row affected (6.289 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> alter table B engine = InnoDB;
|
Query OK, 2 rows affected (6.121 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select left(A.t,20) from B left outer join A on A.fk_b = B.id where B.id = 1;
|
+--------------+
|
| left(A.t,20) |
|
+--------------+
|
| NULL |
|
+--------------+
|
1 row in set (0.104 sec)
|