MySQL Bugs: #95997: Index makes DELETE fail with "Truncated incorrect DOUBLE value"

Bug #95997	Index makes DELETE fail with "Truncated incorrect DOUBLE value"
Submitted:	26 Jun 2019 10:13	Modified:	2 Jul 2019 10:03
Reporter:	Manuel Rigger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
A specific index allows insertion of records with INSERT IGNORE, but prevents a record from being deleted with "ERROR 1292 (22007): Truncated incorrect DOUBLE value".

How to repeat:
CREATE TABLE t0(c0 VARCHAR(5));
CREATE INDEX i0 ON t0((t0.c0 > 0));
INSERT IGNORE INTO t0(c0) VALUES("a");
DELETE FROM t0; -- unexpected: ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'

Note that the INSERT would already unexpectedly fail if the IGNORE was removed:

INSERT INTO t0(c0) VALUES("a"); -- ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'

The bug seems to be specific to VARCHAR, so if the VARCHAR type is replaced by, for example, TEXT, the bug is no longer triggered.

Suggested fix:
Until the bug is fixed in MySQL, DELETE IGNORE will remove the record.

Hello Manuel Rigger,

Thank you for the report.
Imho in strict mode, MySQL inserts adjusted values for invalid or missing values and produces warnings when using INSERT IGNORE or UPDATE IGNORE. Here https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

-
mysql> CREATE TABLE t0(c0 VARCHAR(5));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE INDEX i0 ON t0((t0.c0 > 0));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT IGNORE INTO t0(c0) VALUES("a");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t0;
+------+
| c0   |
+------+
| a    |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t0; -- unexpected: ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'

regards,
Umesh

Hi Umesh,

Thanks for the feedback! Yes, I agree. I think though that in this example, no adjusted value is inserted, but the original one. When using the IGNORE in other circumstances, the table is in a "working" state, while here, the value is recognized as invalid, but still inserted into the table, thus propagating the error.

Best,
Manuel

Posted by developer:
 
Bug #96010/#29962076 "NON-UNIQUE FUNCTIONAL INDEX PREVENTS PRIMARY KEY FROM BEING DROPPED" was marked as a duplicate of this bug.

Posted by developer:
 
Hello!

Even though the above behavior is unobvious, the server still behaves as
intended and documented in this case.

In strict SQL mode, which is default nowadays (see section 5.1.11 Server SQL Modes/
Strict SQL Mode in MySQL 8.0 manual --
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict)
warning about invalid value for the row being inserted is promoted to error and
causes statement failure.

However, usage of IGNORE clause in INSERT (or turning strict mode off temporarily)
allows to avoid this promotion and thus the row with invalid value gets inserted.

This is documented effect of IGNORE clause (see 13.2.6 INSERT Syntax in 8.0 manual
https://dev.mysql.com/doc/refman/8.0/en/insert.html):

"... Data conversions that would trigger errors abort the statement if IGNORE is not specified.
With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are
produced but the statement does not abort. ..."

So what happens in your case is :

CREATE TABLE t0(c0 VARCHAR(5));

# Then CREATE INDEX creates hidden indexed field of INT type, since
# this is the evaluated type of the whole index expression.
# In order to do comparison with 0, t0.c0 is converted to DOUBLE in
# in the process of expression evaluation.
CREATE INDEX i0 ON t0((t0.c0 > 0));

# The IGNORE below overrides default strict mode, the "a" is converted
# to 0.0, the expression for index is evaluated to false.
INSERT IGNORE INTO t0(c0) VALUES("a");
# Warning is emitted about this conversion:
# Warning        3751    Data truncated for functional index 'i0' at row 1

So we end up with table containing row for which index expression produces invalid
value and for which modification statements that require evaluation of this value
DELETE in this case) produce error by default/in strict mode.

SELECT * FROM t0;
# c0
# a

DELETE FROM t0;
# Fails with ERROR 3751 (01000): Data truncated for functional index 'i0' at row 1

The workaround is to either use IGNORE clause in these statements (where supported)
or to temporarily turn off strict mode temporarily for them:

DELETE IGNORE FROM t0;
# Succeeds with some warnings:
# Warning        3751    Data truncated for functional index 'i0' at row 1
# Warning        3751    Data truncated for functional index 'i0' at row 1

SELECT * FROM t0;
# Returns empty set.

The same behavior could be observed with explicit generated columns as well.

So to sum up -- overriding strict mode using IGNORE clause or by turning it off
temporarily allows to insert data into table that would have been not allowed
normally. Later this causes problem for statements not using IGNORE clause/which
are run in strict mode.

Since this is intended and documented behavior of IGNORE/turning off strict
mode what observe is Not a bug, thus I am marking this bug report as such.

However, I agree that it would be nice to have a way to insert data into
table ignoring duplicate key and foreign key errors but not ignoring
data conversion/truncation errors. So I have opened the following
feature request Bug #96049 "IGNORE clause which doesn't override strict mode".

Posted by developer:
 
Bug #96295 / #30088103 "DISABLE KEYS, DELAY_KEY_WRITE RESULTS IN "DATA TRUNCATED
FOR FUNCTIONAL INDEX"" has been marked as a duplicate of this bug.