MySQL Bugs: #106386: Missing warning information in UPDATE statement

Bug #106386	Missing warning information in UPDATE statement
Submitted:	6 Feb 2022 14:46	Modified:	23 Apr 2022 15: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:
Warning message is missing in the UPDATE statement. In the 'How to repeat' part, the result of SHOW WARNINGS is as following:

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Error | 1292 | Truncated incorrect INTEGER value: '' |
+-------+------+---------------------------------------+
2 rows in set (0.00 sec)

I think the second warning message should not appear, and the message content miss the value of INTEGER, which shows as ''.

How to repeat:
Run the following statements:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0 VALUES('a');
UPDATE t0 SET c0 = 'b' WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

Hi Mr. where,

Thank you for your bug report.

However, this is not a bug, but the expected behaviour. First warning comes from the IF () condition and second warning comes from the CAST().

Hence, this is not a bug.

Thanks for your reply. But I think there should be no warning from the CAST() as the return value from the IF() condition is 1. Moreover, CAST(1 AS SIGNED) is a valid operation. 
I wonder whether the IF condition affects the warning thrown by the CAST().

Actually, no .....

Do note that you have used CAST() as a filtering condition in your query !!!!!!

It's still not clear to me. You can try the following statements(just same as above):

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 TEXT);
INSERT INTO t0 VALUES('a');
UPDATE t0 SET c0 = 'b' WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

I think there should be a problem because the second row displayed in SHOW WARNINGS did not show a correct value. There is no such INTEGER value '', not in the table content, not in the UPDATE statement.

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Error | 1292 | Truncated incorrect INTEGER value: '' |
+-------+------+---------------------------------------+
2 rows in set (0.00 sec)

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

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('a');
SELECT c0 FROM t0 WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

There is only one warning message returned.
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

Does the CAST() not cause warnings in SELECT statement?

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

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('a');
SELECT c0 FROM t0 WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

There is only one warning message returned.
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

Does the CAST() not cause warnings in SELECT statement?

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

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('a');
SELECT c0 FROM t0 WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

There is only one warning message returned.
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

Does the CAST() not cause warnings in SELECT statement?

No, not necessarily. 

CAST () can issue warnings, even errors, when it can not find a common denominator or can not use a  default value at all .....

I try the follow statements. When the UPDATE statement's WHERE clause the IF function takes 1 as the second argument, there is no extra error happening.

DROP TABLE t0;
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('a');
UPDATE t0 SET c0 = 'b' WHERE CAST(IF('a', 1, 1) AS SIGNED);
SHOW WARNINGS;

+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

When the second argument in IF function is '1', an extra error is generated.

UPDATE t0 SET c0 = 'b' WHERE CAST(IF('a', '1', 1) AS SIGNED);
SHOW WARNINGS;

+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Error | 1292 | Truncated incorrect INTEGER value: '' |
+-------+------+---------------------------------------+
2 rows in set (0.00 sec)

I think the second error should not happen, because the first argument in IF function is 'a', which is expected to be FALSE.

Hi Mr. Jove,

This is all expected behaviour.

You can not convert a TEXT domain into the signed integer.