Bug #116250 Update statement reports error 'Truncated incorrect DOUBLE value'

• Submitted: 27 Sep 2024 6:59
• Modified: 27 Sep 2024 11:40
• Reporter: Jingqi Tian (OCA)
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[27 Sep 2024 6:59] Jingqi Tian

Description:
When an EXISTS subquery exists in an UPDATE statement, if the subquery uses 
'duplicateweedout' as the semijoin strategy and there is type conversion in an equality condition, the UPDATE statement fails under a specific join order.

How to repeat:
> create table t1 (id int primary key, col1 varchar(20));

> create table t2 (id int primary key, col1 varchar(20));

> insert into t2 values (1, 'abc');

> set optimizer_switch='firstmatch=off,materialization=off';

This UPDATE statement will not modify any records and will success:

> update /*+ JOIN_ORDER(t1, t2) */ t1 set id = 1 where col1 = 1 and exists (select * from t2 where t1.col1 = t2.col1);

However, if the change order is modified, this UPDATE statement will report an error：

> update /*+ JOIN_ORDER(t2, t1) */ t1 set id = 1 where col1 = 1 and exists (select * from t2 where t1.col1 = t2.col1);

We get error:
 ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'abc'

Obviously, the join order should not affect the execution results of the UPDATE statement, so this is a bug.

[27 Sep 2024 8:08] MySQL Verification Team

Hello Jingqi Tian,

Thank you for the report and test case.

regards,
Umesh

[27 Sep 2024 11:40] Roy Lyseng

Posted by developer:
 
When join order varies, expressions will be evaluated in different order.
With join order (t1, t2), the optimizer evaluates the predicate "col1 = 1" first,
which aborts execution and reports the truncation error.
With other join order, the problematic predicate is never evaluated because it is already
determined that the overall condition is false.

Hence, this is not an error.

To avoid such problems, try to make sure that comparisons are with compatible data types.