Bug #106647 Confusing behaviors of type conversion
Submitted: 6 Mar 6:00 Modified: 8 Mar 16:09
Reporter: John Jove Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[6 Mar 6:00] John Jove
Description:
I am confused about the type conversion that happens in the following three cases.
In case 1, the UPDATE failed and threw a data truncate error.
In case 2, the UPDATE succeeded.
In case 3, the UPDATE succeeded.
It seems that these three cases do not pass through the same type conversion process, but they all involve type conversion between DOUBLE and TEXT.
Since MySQL is not a strongly typed system, are there some rules that define exactly which type conversions are allowed or not?

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 DOUBLE);
INSERT INTO t0 VALUES (1);
UPDATE t0 SET c0 = 2 WHERE t0.c0 > 'a'; -- case 1

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0 VALUES ('a');
UPDATE t0 SET c0 = 2 WHERE 1> t0.c0; -- case 2

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 DOUBLE, c1 TEXT);
INSERT INTO t0 VALUES (1, 'a');
UPDATE t0 SET c0 = 2 WHERE t0.c0 > t0.c1; -- case 3
[8 Mar 13:53] MySQL Verification Team
Hi Mr. Jove,

Thank you for your bug report.

However, it is not a bug.

Conversion between a character and floating point has a common denominator that truncates the floating point. This is less so with integer values.

This is all described in our Reference Manual. Please, for all future questions, consult our Reference Manual and do not ask questions on this forum.

Not a bug.
[8 Mar 16:09] John Jove
Sorry for the inconvenience. I think case 1 should also succeed as case 3.
I have read the reference manual for type conversion part. In case 1 and case 3, the arguments should be both compared as floating-point (double-precision) numbers following the reference module. But, case 1 failed, case 3 succeeded.
May I have some misunderstanding? I am appreciated for your reply.
[8 Mar 20:55] Roy Lyseng
In the first case, strict mode is active and rejects the query due to the invalid conversion, as it should.