[MCOL-4499] NOT LIKE is not compatible with MariaDB on explicit NULL - Jira

XML

Word

Printable

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)

relates to

MCOL-4498 LIKE is not collation aware

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.