MySQL Bugs: #106420: IS [NOT] UNKNOWN impacts the execution of statements

Bug #106420	IS [NOT] UNKNOWN impacts the execution of statements
Submitted:	9 Feb 2022 15:36	Modified:	2 Mar 2022 9:44
Reporter:	John Jove	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Ubuntu (20.04LTS)
Assigned to:		CPU Architecture:	x86

Description:
'IS [NOT] UNKNOWN' changes the evaluation results in the UPDATE statment.

How to repeat:
Repeat the following statements, you can see the UPDATE statement failed to change the value of c1 and generated an error.

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR(5), c1 FLOAT);
INSERT INTO t0 VALUES ('b', 0);
UPDATE t0 SET c1 = 1 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0));

But when you append IS NOT UNKNOWN to the UPDATE statement and repeat it again, the UPDATE statement succeeded and caused no errors.

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR(5), c1 FLOAT);
INSERT INTO t0 VALUES ('b', 0);
UPDATE t0 SET c1 = 1 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0)) IS NOT UNKNOWN;

Moreover, I evaluated the expression '(GREATEST('a', t0.c1) IN (0 < t0.c0))' on the table t0, and the result is true. 
I think the op 'IS NOT UNKNOWN' should not change the evaluation and their behaviors should be the same in such a case.

This report is lacking some detail so I added it.

The first statement generates an error due to comparison of double with a string (truncated value).  One would think the second query would also generate the error but the rewrite in the EXPLAIN is different.

Here are the explain plans for the two queries.
mysql> explain UPDATE t0 SET c1 = 1 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | UPDATE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update `test`.`t0` set `test`.`t0`.`c1` = 1 where (greatest('a',`test`.`t0`.`c1`) = (0 < `test`.`t0`.`c0`)) |
+-------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain UPDATE t0 SET c1 = 1 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0)) is unknown;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | UPDATE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                               |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update `test`.`t0` set `test`.`t0`.`c1` = 1 where ((greatest('a',`test`.`t0`.`c1`) = (0 < `test`.`t0`.`c0`)) is null) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

If it isn't clear, in the second case the expression is STRING = INT which is coercible without truncation so the query executes without errors.  I don't think this is a bug.

Till now, I am confused about the data truncation, such as when to truncate the data and how to truncate the data. Intuitively, it's better to process the data truncation in a unified way, which helps to locate data truncation problems. And I tried the above two cases in other databases like TiDB, where the two UPDATE statements both failed and generated the same error. If the op 'IS [NOT] UNKNOWN' can tolerate some issues, it may change the database state in an unexpected, even bad way.

Hi Mr. where,

Thank you for your bug report.

However, this is not a bug.

Your first UPDATE fails due to two facts. First, you compare a VARCHAR domain to the FLOAT domain. This must result in an error of the following type:

ERROR 1292 (22007) Truncated incorrect DOUBLE value: 'b'

However, when you encapsulate all (basically very wrong) condition and add IS NOT UNKNOWN, the result is TRUE or 1, since the result is NOT NULL.

That is how SQL is supposed to function.

Not a bug.

More context:
With the first condition, we must evaluate the equality between a string and an integer, which causes a truncation.
With the second condition, IS NOT UNKNOWN is added, thus we only need to check whether the two arguments to the equality are NULL or not, without having to actually evaluate the equality.

While I try the same where clause in SELECT statement as follows:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR(5), c1 FLOAT);
INSERT INTO t0 VALUES ('b', 0);
SELECT c0 FROM t0 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0));
SHOW WARNINGS;

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR(5), c1 FLOAT);
INSERT INTO t0 VALUES ('b', 0);
SELECT c0 FROM t0 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0)) IS NOT UNKNOWN;
SHOW WARNINGS;

They both throw the same warning. According to Roy's answer, the second case should not throw the warning, as the DBMS only needs to check whether the two arguments to the equality are NULL or not.

Hi Mr. Jove,

Warnings are totally unrelated to errors. Warnings are there to help you write your SQL better.

In this case, warnings have been thrown during optimising stage and not during execution stage. The explanation that our esteemed colleague Roy Lyseng has provided you is related to what part of the SQL statement is executed or not. However, still, all the DML statements have to pass through the optimising stage.

Not a bug.

I try the same where clause in the following  DELETE statement:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 VARCHAR(5), c1 FLOAT);
INSERT INTO t0 VALUES ('b', 0);

DELETE FROM t0 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0)); -- failed
DELETE FROM t0 WHERE (GREATEST('a', t0.c1) IN (0 < t0.c0)) IS NOT UNKNOWN; -- succeeded

The situations of DELETE are the same as UPDATE. I wonder whether DELETE and UPDATE pass through the same optimizing process. Thanks!

Hi Mr. Jove,

No, UPDATE and DELETE do not pass through the same optimising process.