[MCOL-4577] VARCHAR(2) NOT NULL: explicit empty string gets 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

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Sergey Zefirov
Resolution: Fixed Votes: 0
Labels: rm_invalid_data

Issue Links:
Blocks
blocks MCOL-4403 Trailing spaces not behaving as InnoDB Closed
Problem/Incident
is caused by MCOL-3574 EMPTY column for persistant storage Closed
Relates
relates to MCOL-271 Improved support for NULL for Varchar... Closed
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2021-5, 2021-6

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(2) NOT NULL DEFAULT 'aa') ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('');
 
SELECT HEX(a) FROM t1;


+--------+
 
| HEX(a) |
 
+--------+
 
| 6161   |
 
+--------+

Notice, emptry string was conversion to default 'aa'. Looks wrong.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(2) NOT NULL DEFAULT ' ') ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('');
 
SELECT HEX(a) FROM t1;


+--------+
 
| HEX(a) |
 
+--------+
 
| 20     |
 
+--------+

Notice, emptry string was conversion to default ' '. Looks wrong.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(2) NOT NULL DEFAULT '  ') ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES ('  ');
 
SELECT HEX(a) FROM t1;


+--------+
 
| HEX(a) |
 
+--------+
 
| 2020   |
 
+--------+

Notice, emptry string was conversion to default ' '. Looks wrong.

In all above scenarios, INSERTs should:

  • either fail on NOT NULL violation for now
  • or insert an empty string (when we fix the flaw that emptry string is NULL)

But it should not replace to DEFAULT!

 Comments   
Comment by Daniel Lee (Inactive) [ 2023-04-25 ]

Build verified:
engine: 1e56a0b557efb677d07533d05eb02ad723955317
server: 11c83d9ae9eb249d00589cc6ab71e7f4e67ffa27
buildNo: 7534
This scenario has been fixed as part of MCOL-271. ColumnStore and InnoDB results matched.

Generated at Thu Feb 08 02:51:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.