Can't UPDATE 1 to NULL if there are multiple rows to change

[federico-razzoli]

mysql> CREATE TABLE t (a INT) ENGINE WARP;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE t SET a = 1 WHERE a IS NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> UPDATE t SET a = NULL WHERE a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

If you repeat the test with only 1 line it works as expected.

[greenlion]

This is really weird.

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.00 sec)

mysql> update t set a = 1 where a is null;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
ERROR 1054 (42S22): Unknown column '*' in 'field list'
mysql> set global warp_rewriter_parallel_query=off;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> update t set a = null where a=1;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> select * from t where a = 1;
Empty set (0.01 sec)

mysql> select * from t where a is null;
Empty set (0.01 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> truncate table t;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> update t set a = null;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
+------+
| a    |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.01 sec)

mysql> update t set a = 1;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.02 sec)

[federico-razzoli]

What is special with having 2 identical values? Why does everything work with only 1, stops working properly with 2, and then if you insert more you keep seeing 2 values?

[greenlion]

this bug is fixed in the latest 8.0 branch

[greenlion]

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select * from t1; +------+ | c1 | +------+ | NULL | | NULL | +------+ 2 rows in set (0.01 sec)

mysql> update t1 set c1 = 1; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from t1; +------+ | c1 | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec)

mysql> update t1 set c1 = null; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from t1; +------+ | c1 | +------+ | NULL | | NULL | +------+ 2 rows in set (0.00 sec)

mysql> update t1 set c1 = 1 limit 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1; +------+ | c1 | +------+ | NULL | | 1 | +------+ 2 rows in set (0.00 sec)

mysql> insert into t1 values (2); Query OK, 1 row affected (0.01 sec)

mysql> select * from t1; +------+ | c1 | +------+ | NULL | | 1 | | 2 | +------+ 3 rows in set (0.00 sec)

mysql> update t1 set t1 = null; ERROR 1054 (42S22): Unknown column 't1' in 'field list' mysql> update t1 set c11 = null; ERROR 1054 (42S22): Unknown column 'c11' in 'field list' mysql> update t1 set c1 = null; Query OK, 2 rows affected (0.01 sec) Rows matched: 3 Changed: 2 Warnings: 0

mysql> select * from t1; +------+ | c1 | +------+ | NULL | | NULL | | NULL | +------+ 3 rows in set (0.01 sec)

mysql> update t1 set c1 =1 limit 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> update t1 set c1 =2 limit 2,1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',1' at line 1 mysql> select * from t1;; +------+ | c1 | +------+ | NULL | | NULL | | 1 | +------+ 3 rows in set (0.00 sec)

ERROR: No query specified

mysql> select * from t1; +------+ | c1 | +------+ | NULL | | NULL | | 1 | +------+ 3 rows in set (0.00 sec)

mysql> update t1 set c1 = 2 where c1 is null limit 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> update t1 set c1 = 3 where c1 is null limit 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1; +------+ | c1 | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)