MySQL Bugs: #106647: Confusing behaviors of type conversion

Bug #106647	Confusing behaviors of type conversion
Submitted:	6 Mar 2022 6:00	Modified:	8 Mar 2022 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

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

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.

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.

In the first case, strict mode is active and rejects the query due to the invalid conversion, as it should.