Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.30, 10.6.14, 10.11.4
-
None
Description
When (inner or outer) joining a table with compressed varchar column(s) to another table and sorting on column(s) of the joined table, compressed values are replaced with NULLs in returned result set. We heuristically assume that minimum varchar length to compress is 100, values shorter than that are not compressed and returned correctly.
A minimal reproducible test case (reproduced in 10.11.4 and 10.6.14, probably affects previous releases as well):
Server version: 10.11.4-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
MariaDB [db]> CREATE TABLE t1 ( |
-> id int(10) unsigned not null, |
-> txt varchar(5000) COMPRESSED NOT NULL DEFAULT '', |
-> PRIMARY KEY (id) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; |
|
|
Query OK, 0 rows affected (0.018 sec) |
|
|
MariaDB [db]> CREATE TABLE t2 ( |
-> id int(10) unsigned not null, |
-> n1 bigint(20) NOT NULL, |
-> n2 bigint(20) NOT NULL, |
-> n3 bigint(20) NOT NULL, |
-> PRIMARY KEY (id) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; |
Query OK, 0 rows affected (0.009 sec) |
|
|
MariaDB [db]> INSERT INTO t1 VALUES |
-> (1, 'short string < 100 chars'), |
-> (2, 'long string = 99 chars '), |
-> (3, 'long string = 100 chars !'), |
-> (4, 'long string = 101 chars !'); |
Query OK, 4 rows affected (0.001 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [db]> INSERT INTO t2 VALUES |
-> (1, 24, 1, 1),
|
-> (2, 99, 2, 2),
|
-> (3, 100, 3, 3),
|
-> (4, 101, 4, 4)
|
-> ;
|
Query OK, 4 rows affected (0.001 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id; |
+-------------------------------------------------------------------------------------------------------+------+------+------+------+ |
| txt | id | n1 | n2 | n3 |
|
+-------------------------------------------------------------------------------------------------------+------+------+------+------+ |
| short string < 100 chars | 1 | 24 | 1 | 1 |
|
| long string = 99 chars | 2 | 99 | 2 | 2 |
|
| long string = 100 chars ! | 3 | 100 | 3 | 3 |
|
| long string = 101 chars ! | 4 | 101 | 4 | 4 |
|
+-------------------------------------------------------------------------------------------------------+------+------+------+------+ |
4 rows in set (0.000 sec) |
|
|
MariaDB [db]> SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1; |
+-----------------------------------------------------------------------------------------------------+------+------+------+------+ |
| txt | id | n1 | n2 | n3 |
|
+-----------------------------------------------------------------------------------------------------+------+------+------+------+ |
| short string < 100 chars | 1 | 24 | 1 | 1 |
|
| long string = 99 chars | 2 | 99 | 2 | 2 |
|
| | 3 | 100 | 3 | 3 |
|
| | 4 | 101 | 4 | 4 |
|
+-----------------------------------------------------------------------------------------------------+------+------+------+------+ |
4 rows in set (0.001 sec) |
MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id; |
+-------------------------------------------------------------------------------------------------------+----+-----+----+----+ |
| txt | id | n1 | n2 | n3 |
|
+-------------------------------------------------------------------------------------------------------+----+-----+----+----+ |
| short string < 100 chars | 1 | 24 | 1 | 1 |
|
| long string = 99 chars | 2 | 99 | 2 | 2 |
|
| long string = 100 chars ! | 3 | 100 | 3 | 3 |
|
| long string = 101 chars ! | 4 | 101 | 4 | 4 |
|
+-------------------------------------------------------------------------------------------------------+----+-----+----+----+ |
4 rows in set (0.002 sec) |
|
|
MariaDB [db]> SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1; |
+-----------------------------------------------------------------------------------------------------+----+-----+----+----+ |
| txt | id | n1 | n2 | n3 |
|
+-----------------------------------------------------------------------------------------------------+----+-----+----+----+ |
| short string < 100 chars | 1 | 24 | 1 | 1 |
|
| long string = 99 chars | 2 | 99 | 2 | 2 |
|
| | 3 | 100 | 3 | 3 |
|
| | 4 | 101 | 4 | 4 |
|
+-----------------------------------------------------------------------------------------------------+----+-----+----+----+ |
4 rows in set (0.001 sec) |
DDL and queries to reproduce:
CREATE TABLE t1 ( |
id int(10) unsigned not null, |
txt varchar(5000) COMPRESSED NOT NULL DEFAULT '', |
PRIMARY KEY (id) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; |
|
|
CREATE TABLE t2 ( |
id int(10) unsigned not null, |
n1 bigint(20) NOT NULL, |
n2 bigint(20) NOT NULL, |
n3 bigint(20) NOT NULL, |
PRIMARY KEY (id) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; |
|
|
|
|
INSERT INTO t1 VALUES |
(1, 'short string < 100 chars'), |
(2, 'long string = 99 chars '), |
(3, 'long string = 100 chars !'), |
(4, 'long string = 101 chars !'); |
|
|
INSERT INTO t2 VALUES |
(1, 24, 1, 1),
|
(2, 99, 2, 2),
|
(3, 100, 3, 3),
|
(4, 101, 4, 4)
|
;
|
|
|
SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id; |
SELECT txt, v.* FROM t1 LEFT JOIN t2 v ON t1.id = v.id ORDER BY v.n1; |
SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id; |
SELECT txt, v.* FROM t1 JOIN t2 v ON t1.id = v.id ORDER BY v.n1; |
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-