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

Revision: 64679
User: Mike Thibodeau
Date: 2018-01-16 19:03

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.
<<sql>>
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]> 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.
<</sql>>

Answer

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.

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

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB