[#MCOL-4578] CHAR(2) NOT NULL: Empty string or SPACE(N) get converted to DEFAULT

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)

[MCOL-4578] CHAR(2) NOT NULL: Empty string or SPACE(N) get converted to DEFAULT Created: 2021-03-04 Updated: 2023-09-22 Resolved: 2023-04-25
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	PrimProc
Affects Version/s:	5.5.1, 5.6.1
Fix Version/s:	23.10.0

[MCOL-4578] CHAR(2) NOT NULL: Empty string or SPACE(N) get converted to DEFAULT Created: 2021-03-04 Updated: 2023-09-22 Resolved: 2023-04-25