[MCOL-4499] NOT LIKE is not compatible with MariaDB on explicit NULL Created: 2021-01-15  Updated: 2022-02-18

Status: Open
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 5.6.1
Fix Version/s: Icebox

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4498 LIKE is not collation aware Closed
Epic Link: ColumnStore Compatibility Improvements

 Description   

I create a ColumnStore table:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(4)) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES (NULL),('a');

Now run queries with the LIKE predicate in combination with explicit NULL:

SELECT a FROM t1 WHERE a NOT LIKE NULL;


+------+
 
| a    |
 
+------+
 
| a    |
 
+------+


SELECT a FROM t1 WHERE NULL NOT LIKE a;


+------+
 
| a    |
 
+------+
 
| a    |
 
+------+

Looks wrong. The expected result is to return no rows, like other engines do:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(4)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (NULL),('a');


SELECT a FROM t1 WHERE a NOT LIKE NULL;


Empty set (0.000 sec)


SELECT a FROM t1 WHERE NULL NOT LIKE a;


Empty set (0.001 sec)


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