This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Should I be able to use NOT and null safe compare?

I can work around with colum1 is not null and column2 is null... but should it work with NOT (column1 <=> column2)? Here's an example.

MariaDB> CREATE TABLE cs1 (
    ->     id              INT(10) UNSIGNED NOT NULL,
    ->     `timestamp_`    DATETIME,
    ->     numeric_val     DOUBLE DEFAULT NULL
    -> ) ENGINE=columnstore;
Query OK, 0 rows affected (0.44 sec)

MariaDB> CREATE TABLE cs2 (
    ->     id              INT(10) UNSIGNED NOT NULL,
    ->     `timestamp_`    DATETIME,
    ->     numeric_val     DOUBLE DEFAULT NULL
    -> ) ENGINE=columnstore;
Query OK, 0 rows affected (0.25 sec)

MariaDB> SELECT @@version,@@version_comment;
+---------------------+---------------------+
| @@version           | @@version_comment   |
+---------------------+---------------------+
| 10.2.10-MariaDB-log | Columnstore 1.1.2-1 |
+---------------------+---------------------+
1 row in set (0.00 sec)

MariaDB]> INSERT INTO cs1 VALUES (1, '2018-01-09 21:59:02', 11.11);
Query OK, 1 row affected (0.11 sec)

MariaDB> INSERT INTO cs2 VALUES (1, '2018-01-09 21:59:02', 22.22);
Query OK, 1 row affected (0.10 sec)

MariaDB> SELECT * FROM cs1
    ->     JOIN cs2 USING(id,timestamp_)
    -> ;
+----+---------------------+-------------+-------------+
| id | timestamp_          | numeric_val | numeric_val |
+----+---------------------+-------------+-------------+
|  1 | 2018-01-09 21:59:02 |       11.11 |       22.22 |
+----+---------------------+-------------+-------------+
1 row in set (0.04 sec)

MariaDB> SELECT * FROM cs1
    ->     JOIN cs2 USING(id,timestamp_)
    ->     WHERE cs1.numeric_val <=> cs2.numeric_val
    -> ;
Empty set (0.02 sec)

MariaDB> SELECT * FROM cs1
    ->     JOIN cs2 USING(id,timestamp_)
    ->     WHERE NOT (cs1.numeric_val = cs2.numeric_val)
    -> ;
+----+---------------------+-------------+-------------+
| id | timestamp_          | numeric_val | numeric_val |
+----+---------------------+-------------+-------------+
|  1 | 2018-01-09 21:59:02 |       11.11 |       22.22 |
+----+---------------------+-------------+-------------+
1 row in set (0.02 sec)

MariaDB> SELECT * FROM cs1
    ->     JOIN cs2 USING(id,timestamp_)
    ->     WHERE NOT (cs1.numeric_val <=> cs2.numeric_val)
    -> ;
ERROR 1815 (HY000): Internal error: IDB-2030: Predicate and Logic operators can not be used where an expression is expected.

Answer Answered by David Thompson in this comment.

We did a special query rewrite to handle the query rewrite case so i suspect this is a bug in that logic. Best to track this in jira so i've filed: https://jira.mariadb.org/browse/MCOL-1155

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.