[MCOL-4403] Trailing spaces not behaving as InnoDB Created: 2020-11-19  Updated: 2023-09-22  Resolved: 2023-04-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr, PrimProc
Affects Version/s: 5.4.3
Fix Version/s: 23.10.0

Type: Bug Priority: Critical
Reporter: David Hall (Inactive) Assignee: Sergey Zefirov
Resolution: Fixed Votes: 0
Labels: rm_invalid_data

Issue Links:
Blocks
is blocked by MCOL-4577 VARCHAR(2) NOT NULL: explicit empty s... Closed
is blocked by MCOL-4578 CHAR(2) NOT NULL: Empty string or SPA... Closed
is blocked by MCOL-4579 CHAR(2) NULL: empty strings or SPACE(... Closed
Duplicate
duplicates MCOL-271 Improved support for NULL for Varchar... Closed
is duplicated by MCOL-4909 Columnstore handles nulls and zero-le... Open
Problem/Incident
is caused by MCOL-3574 EMPTY column for persistant storage Closed
Relates
relates to MCOL-495 Make string comparison not case sensi... Closed
relates to MCOL-271 Improved support for NULL for Varchar... Closed
relates to MCOL-1673 Different trailing behaviour between ... Closed
relates to MCOL-4568 Change short CHAR to pad values with ... Open
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2021-1, 2021-2, 2021-3, 2021-4, 2021-5, 2021-6

 Description   

Trailing spaces are not comparing as InnoDB. The rules have changed again.

 Comments   
Comment by Roman [ 2020-12-08 ]

As we found out the issue has been partially solved in the upcoming ES release so that all queries returns an expected number of records. However there is a difference in behavior b/w MCS and InnoDB. InnoDB returns empty sring in testval4 column for the 3d record whilst MCS returns NULL. This looks like a different issue not connected with wrong filtering.

MariaDB [test]> select *  FROM testblank where hex(substring(testval1,1,1)) = 20; -- OK
 
+------+----------+----------+----------+----------+
 
| id   | testval1 | testval2 | testval3 | testval4 |
 
+------+----------+----------+----------+----------+
 
|    1 |          |          | NULL     | NULL     |
 
|    2 |          |          | NULL     | NULL     |
 
|    3 |          |          | NULL     | NULL     |
 
+------+----------+----------+----------+----------+
 
3 rows in set (0.014 sec)

Comment by Alexander Barkov [ 2021-02-19 ]

A test script:

DELIMITER $$
 
CREATE OR REPLACE PROCEDURE p1(t VARCHAR(32))
 
BEGIN
 
  DECLARE sql_create VARCHAR(256) DEFAULT 'CREATE TABLE t1 (msg VARCHAR(32), c TYPE) ENGINE=ColumnStore DEFAULT CHARACTER SET latin1';
 
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 
  BEGIN
 
    SHOW ERRORS;
 
  END;
 
  DROP TABLE IF EXISTS t1;
 
  EXECUTE IMMEDIATE REPLACE(sql_create, 'TYPE', t);
 
  INSERT INTO t1 VALUES ('<NULL>', NULL);
 
  INSERT INTO t1 VALUES ('<DEFAULT>', DEFAULT);
 
  INSERT INTO t1 VALUES ('<empty>'  , '');
 
  INSERT INTO t1 VALUES ('space(1)' , ' ');
 
  INSERT INTO t1 VALUES ('space(2)' , '  ');
 
  SELECT msg, HEX(c) FROM t1;
 
END;
 
$$
 
DELIMITER ;
 
 
 
CALL p1('varchar(2) NULL DEFAULT ''''');
 
CALL p1('varchar(2) NULL DEFAULT '' ''');
 
CALL p1('varchar(2) NULL DEFAULT ''  ''');
 
 
 
CALL p1('varchar(2) NOT NULL DEFAULT ''''');
 
CALL p1('varchar(2) NOT NULL DEFAULT '' ''');
 
CALL p1('varchar(2) NOT NULL DEFAULT ''  ''');
 
 
 
 
 
CALL p1('char(2) NULL DEFAULT ''''');
 
CALL p1('char(2) NULL DEFAULT '' ''');
 
CALL p1('char(2) NULL DEFAULT ''  ''');
 
 
 
CALL p1('char(2) NOT NULL DEFAULT ''''');
 
CALL p1('char(2) NOT NULL DEFAULT '' ''');
 
CALL p1('char(2) NOT NULL DEFAULT ''  ''');

Comment by Alexander Barkov [ 2021-02-20 ]

Test results for VARCHAR(2)

VARCHAR(2) NULL

CALL p1('varchar(2) NULL DEFAULT ''''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | NULL   |  Bad: DEFAULT empty string was converted to NULL
 
| <empty>   | NULL   |  Bad: Explicit empty string was converted to NULL
 
| space(1)  | 20     |
 
| space(2)  | 2020   |
 
+-----------+--------+


CALL p1('varchar(2) NULL DEFAULT '' ''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | 20     |
 
| <empty>   | NULL   | Bad: Explicit empty string was converted to NULL
 
| space(1)  | 20     |
 
| space(2)  | 2020   |
 
+-----------+--------+


CALL p1('varchar(2) NULL DEFAULT ''  ''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | 2020   |
 
| <empty>   | NULL   | Explicit empty string was converted to NULL
 
| space(1)  | 20     |
 
| space(2)  | 2020   |
 
+-----------+--------+

Observations:

  • DEFAULT empty string was converted to NULL
  • Explicit empty string was converted to NULL

VARCHAR(2) NOT NULL

CALL p1('varchar(2) NOT NULL DEFAULT ''''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(The above error was correctly produced by MariaDB)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(The above error is wrong: DEFAULT empty string was treated as NULL)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(The above error is wrong: Explicit empty string was treated as NULL)


+----------+--------+
 
| msg      | HEX(c) |
 
+----------+--------+
 
| space(1) | 20     |
 
| space(2) | 2020   |
 
+----------+--------+

Observations:

  • DEFAULT empty string was treated as NULL
  • Explicit empty string is treated as NULL

CALL p1('varchar(2) NOT NULL DEFAULT '' ''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(the above error was correctly produced by MariaDB)


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <DEFAULT> | 20     |
 
| <empty>   | 20     |  Bad: Explicit empty string was converted to DEFAULT SPACE(1)
 
| space(1)  | 20     |
 
| space(2)  | 2020   |
 
+-----------+--------+

Observations:

  • Explicit empty string was converted to DEFAULT SPACE(1)

CALL p1('varchar(2) NOT NULL DEFAULT ''  ''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(the above error was correctly produced by MariaDB)


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <DEFAULT> | 2020   |
 
| <empty>   | 2020   |  Bad: Explicit empty string was converted to DEFAULT SPACE(2)
 
| space(1)  | 20     |
 
| space(2)  | 2020   |
 
+-----------+--------+

Obvervations:

  • Explicit empty string was converted to DEFAULT SPACE(2)
Comment by Alexander Barkov [ 2021-02-20 ]

Test results for CHAR(2)

CHAR(2) NULL

CALL p1('char(2) NULL DEFAULT ''''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | NULL   | Bad: empty DEFAULT was converted to NULL
 
| <empty>   | NULL   | Bad: empty string was converted to NULL
 
| space(1)  | NULL   | Bad: SPACE(1) was converted to NULL
 
| space(2)  | NULL   | Bad: SPACE(2) was converted to NULL
 
+-----------+--------+

Observations:

  • Empty DEFAULT was converted to NULL
  • Empty string was converted to NULL
  • SPACE(1,2) was converted to NULL

CALL p1('char(2) NULL DEFAULT '' ''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | NULL   | Bad: DEFAULT SPACE(1) was converted to NULL
 
| <empty>   | NULL   | Bad: empty string was converted to NULL
 
| space(1)  | NULL   | Bad: SPACE(1) was converted to NULL
 
| space(2)  | NULL   | Bad: SPACE(2) was converted to NULL
 
+-----------+--------+

Observations:

  • DEFAULT SPACE(1) was converted to NULL
  • Empty string was converted to NULL
  • SPACE(1,2) was converted to NULLs

CALL p1('char(2) NULL DEFAULT ''  ''');


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <NULL>    | NULL   |
 
| <DEFAULT> | NULL   | Bad: DEFAULT '  ' was converted to NULL
 
| <empty>   | NULL   | Bad: empty string was converted to NULL
 
| space(1)  | NULL   | Bad: SPACE(1) was converted to NULL
 
| space(2)  | NULL   | Bad: SPACE(2) was converted to NULL
 
+-----------+--------+

Observations:

  • Default SPACE(2) was converted to NULL
  • Explicit empty string was converted to NULL
  • Explicit SPACE(1,2) was converted to NULL

CHAR(2) NOT NULL

CALL p1('char(2) NOT NULL DEFAULT ''''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(the above error was correctly returned by MariaDB)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(the above error is wrong: DEFAULT empty string was treated as NULL)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(the above error is wrong: explicit empty string was treated as NULL)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(the above error is wrong: SPACE(1) was treated as NULL)


+-------+------+---------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                         |
 
+-------+------+---------------------------------------------------------------------------------+
 
| Error | 1815 | Internal error: CAL0001: Insert Failed:  IDB-4015: Column 'c' cannot be null.   |
 
| Error | 1030 | Got error 1 "Operation not permitted" from storage engine Columnstore           |
 
+-------+------+---------------------------------------------------------------------------------+
 
(the above error is wrong: SPACE(2) was treated as NULL)

Observations:

  • DEFAULT empty string was treated as NULL
  • Explicit empty string was treated as NULL
  • Explicit SPACE(1) was treated as NULL
  • Explicit SPACE(2) was treated as NULL

CALL p1('char(2) NOT NULL DEFAULT '' ''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(the above error was correctly generated by MariaDB)


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <DEFAULT> | 20     |
 
| <empty>   | 20     | Explicit empty string was converted to DEFAULT SPACE(1)
 
| space(1)  | 20     |
 
| space(2)  | 20     | Explicit SPACE(2) was converted to DEFAULT SPACE(1)
 
+-----------+--------+

Obvervations:

  • Explicit empty string was converted to DEFAULT SPACE(1)
  • Explicit SPACE(2) converted to DEFAULT SPACE(1)

CALL p1('char(2) NOT NULL DEFAULT ''  ''');


+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Error | 1048 | Column 'c' cannot be null |
 
+-------+------+---------------------------+
 
(the above error was correctly returned by MariaDB)


+-----------+--------+
 
| msg       | HEX(c) |
 
+-----------+--------+
 
| <DEFAULT> | 2020   |
 
| <empty>   | 2020   | Explicit empty string was converted to DEFAULT SPACE(2)
 
| space(1)  | 2020   | Explicit SPACE(1) was converted to DEFAULT SPACE(2)
 
| space(2)  | 2020   |
 
+-----------+--------+

Observations:

  • Explicit empty string was converted to DEFAULT SPACE(2)
  • Explicit SPACE(1) was converted to DEFAULT SPACE(2)
Comment by Alexander Barkov [ 2021-02-20 ]

Summary of the problems found

VARCHAR(2) NULL

  • DEFAULT empty string gets converted to NULL
  • Explicit empty string + empty DEFAULT gets converted to NULL
  • Explicit empty string + DEFAULT SPACE(1,2) gets converted to NULL

VARCHAR(2) NOT NULL

  • DEFAULT empty string causes NOT NULL constraint violation
  • Explicit empty string + DEFAULT empty string causes NOT NULL constraint violation
  • Explicit empty string + DEFAULT SPACE(1,2) gets converted to DEFAULT SPACE(1,2) - MCOL-4577

CHAR(2) NULL

  • DEFAULT empty string gets converted to NULL
  • DEFAULT SPACE(1,2) gets converted to NULL
  • Explicit empty string + empty DEFAULT gets converted to NULL - MCOL-4579
  • Explicit empty string + DEFAULT SPACE(1,2) gets converted to NULL - MCOL-4579
  • Explicit SPACE(1,2) + empty DEFAULT gets converted to NULL - MCOL-4579
  • Explicit SPACE(1,2) + DEFAULT SPACE(1,2) gets converted to NULL - MCOL-4579

CHAR(2) NOT NULL

  • DEFAULT empty string causes NOT NULL constraint violation
  • Explicit empty string + DEFAULT empty string causes NOT NULL constraint violation
  • Explicit SPACE(1,2) + DEFAULT empty string causes NOT NULL constraint violation
  • Explicit empty string + DEFAULT SPACE(1) gets converted to SPACE(1) - MCOL-4578
  • Explicit SPACE(2) + DEFAULT SPACE(1) gets converted to SPACE(1) - MCOL-4578
  • Explicit empty string + DEFAULT SPACE(2) gets converted to SPACE(2) - MCOL-4578
  • Explicit SPACE(1) + DEFAULT SPACE(2) gets converted to SPACE(2) - MCOL-4578
Comment by alexey vorovich (Inactive) [ 2022-11-30 ]

toddstoffel far from trivial... lets delay ..

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:50:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.