Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.1, 5.6.1
-
2021-5, 2021-6
Description
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT 'aa') ENGINE=ColumnStore; |
INSERT INTO t1 VALUES (''); |
INSERT INTO t1 VALUES (' '); |
INSERT INTO t1 VALUES (' '); |
SELECT HEX(a) FROM t1; |
+--------+
|
| HEX(a) |
|
+--------+
|
| 6161 |
|
| 6161 |
|
| 6161 |
|
+--------+
|
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT ' ') ENGINE=ColumnStore; |
INSERT INTO t1 VALUES (''); |
INSERT INTO t1 VALUES (' '); |
INSERT INTO t1 VALUES (' '); |
SELECT HEX(a) FROM t1; |
+--------+
|
| HEX(a) |
|
+--------+
|
| 20 |
|
| 20 |
|
| 20 |
|
+--------+
|
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT ' ') ENGINE=ColumnStore; |
INSERT INTO t1 VALUES (''); |
INSERT INTO t1 VALUES (' '); |
INSERT INTO t1 VALUES (' '); |
SELECT HEX(a) FROM t1; |
 |
In all above cases, empty strings as well as strings consisting only of space characters were replaced to the DEFAULT value.
Looks wrong. Strings consisting of only spaces should never be replaced to DEFAULT.
CHAR values should be padded with spaces to their full length.
For MariaDB compatibility, the SELECT behaviour should depend on sql_mode=PAD_CHAR_TO_FULL_LENGTH:
- trailing spaces should be preserved on SELECT if PAD_CHAR_TO_FULL_LENGTH is set
- trailing spaces should be trimmed on SELECT, if PAD_CHAR_TO_FULL_LENGTH is not set
Also, inserting an empty string should:
- either fail on NOT NULL violation for now
- or store the true empty string (when we fix the flaw that an empty string is NULL)
