| Bug #106407 | Computation on an indexed column makes duplicate warnings | ||
|---|---|---|---|
| Submitted: | 8 Feb 2022 14:13 | Modified: | 2 Mar 2022 6:34 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Ubuntu (20.04LTS) |
| Assigned to: | CPU Architecture: | x86 | |
[8 Feb 2022 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 2022 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 2022 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 2022 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 2022 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.
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;