MariaDB [test]> create table t1 (a int, b varchar(100))engine=columnstore;
|
Query OK, 0 rows affected (0.213 sec)
|
|
MariaDB [test]> create table i1 (a int, b varchar(100));
|
Query OK, 0 rows affected (0.007 sec)
|
|
MariaDB [test]> insert into t1 values (1, 'val'), (2, 'val'), (null, 'nullval'), (4, 'val');
|
Query OK, 4 rows affected (0.507 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into i1 values (1, 'val'), (3, 'val'), (null, 'nullval'), (4, 'val');
|
Query OK, 4 rows affected (0.004 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
-- Works as expected
|
MariaDB [test]> select * from i1 where a not in (select a from t1);
|
+------+------+
|
| a | b |
|
+------+------+
|
| 3 | val |
|
+------+------+
|
1 row in set (0.128 sec)
|
|
-- Doesn't work as expected
|
MariaDB [test]> select * from t1 where a not in (select a from i1);
|
Empty set (0.038 sec)
|
|
-- Doesn't work as expected
|
MariaDB [test]> update i1 set b='Update' where a not in (select a from t1);
|
Query OK, 0 rows affected (0.063 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
MariaDB [test]> select * from i1;
|
+------+---------+
|
| a | b |
|
+------+---------+
|
| 1 | val |
|
| 3 | val |
|
| NULL | nullval |
|
| 4 | val |
|
+------+---------+
|
4 rows in set (0.001 sec)
|
|
-- Doesn't work as expected, updates more records than required
|
MariaDB [test]> update t1 set b='Update' where a not in (select a from i1);
|
Query OK, 3 rows affected (0.447 sec)
|
Rows matched: 3 Changed: 3 Warnings: 0
|
|
MariaDB [test]> select * from t1;
|
+------+---------+
|
| a | b |
|
+------+---------+
|
| 1 | Update |
|
| 2 | Update |
|
| NULL | nullval |
|
| 4 | Update |
|
+------+---------+
|
4 rows in set (0.028 sec)
|
|
-- works as expected
|
MariaDB [test]> update t1 set b='Update2' where a not in (select a from t1);
|
Query OK, 0 rows affected (0.247 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
MariaDB [test]> select * from t1;
|
+------+---------+
|
| a | b |
|
+------+---------+
|
| 1 | Update |
|
| 2 | Update |
|
| NULL | nullval |
|
| 4 | Update |
|
+------+---------+
|
4 rows in set (0.016 sec)
|