Bug #106407 Computation on an indexed column makes duplicate warnings
Submitted: 8 Feb 14:13 Modified: 2 Mar 6:34
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

[8 Feb 14:13] John Jove
Description:
When computing on an indexed column like c0 in 'How to repeat' part, duplicate warnings are generated as follows:

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

After removing the index (i.e., removing the labelled 'remove it' statement), things get normal and only one warning message is generated.

How to repeat:
You can repeat this problem by the following statements:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 FLOAT);
INSERT INTO t0 VALUES(1.0);
CREATE UNIQUE INDEX i1 ON t0(c0 DESC); -- remove it
SELECT c0 FROM t0 WHERE ('a' | 1) BETWEEN 0 AND t0.c0;
SHOW WARNINGS;
[8 Feb 14:52] MySQL Verification Team
Hi Mr. where,

Thank you for your bug report.

However, this is not a bug.

First of all, there should be no difference between table having index or not. A table with a single row can not use index anyway.

The warnings that you get are quite correct, because, please believe us, character 'a' is not an integer. SQL is not C programming language where (practically) everything is a number.

You get three warnings, which is expected behaviour. First warning comes from optimiser that tries to resolve a bit expression between a character and an integer. Second warning comes from comparing ('a' | 0) with 0 and third comes from comparing the same expression with a value from the first tuple for the column t0.c0.

Not a bug.
[14 Feb 12:38] John Jove
As you said, there should be no difference between a table having index or not. Moreover, a table with a single row can not use index anyway. I try the following statements, where the create index statement is removed from the above case.

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 FLOAT);
INSERT INTO t0 VALUES(1.0);
SELECT c0 FROM t0 WHERE ('a' | 1) BETWEEN 0 AND t0.c0;
SHOW WARNINGS;

The results of SHOW WARNINGS changed. Here, only one exception message displayed, which is expected to be the same as above.

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
[14 Feb 14:18] MySQL Verification Team
Hi Mr. where,

Yes, you are quite correct.

However, duplicate warnings, which are all identical, do not constitute a bug.

Not a bug.
[2 Mar 6:34] John Jove
I try the same where clause in UPDATE statement as follows:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 FLOAT);
INSERT INTO t0 VALUES (1);
CREATE UNIQUE INDEX i1 ON t0(c0 DESC);
UPDATE t0 SET c0 = 'b' WHERE ('a' | 1) BETWEEN 0 AND t0.c0;
SHOW WARNINGS;

There are two warnings instead of three warnings. Does SELECT and UPDATE evaluate the same where clause differently?
[2 Mar 14:30] MySQL Verification Team
Hi Mr. Jove,

Yes, that is true. 

SELECT and UPDATE evaluate the same where clause differently. SELECT has to return the result set, while UPDATE does not have to.

Not a bug.