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)
Build verified:
engine: 1e56a0b557efb677d07533d05eb02ad723955317
server: 11c83d9ae9eb249d00589cc6ab71e7f4e67ffa27
buildNo: 7534
This scenario has been fixed as part of
MCOL-271. ColumnStore and InnoDB results matched.