MySQL Bugs: #113403: query result is inconsistence when operands of differing types

Bug #113403	query result is inconsistence when operands of differing types
Submitted:	13 Dec 2023 5:34	Modified:	14 Dec 2023 12:06
Reporter:	Shawn Yan (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when querying data from two tables, where conditions including differing data types, the results are different, and the results differ with other databases.

How to repeat:
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t1` (`id`, `name`) VALUES (1, '123456789012345611');
INSERT INTO `t1` (`id`, `name`) VALUES (2, '123456789012345612');
INSERT INTO `t1` (`id`, `name`) VALUES (3, '123123');

 CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
 
INSERT INTO `t2` (`id`, `name`) VALUES (1, 123456789012345611);
INSERT INTO `t2` (`id`, `name`) VALUES (2, 123456789012345611);
INSERT INTO `t2` (`id`, `name`) VALUES (3, 123123);

select t1.*,t2.* from t1,t2 where t1.name=t2.name;
select t1.*,t2.* from t1,t2 where cast(t1.name as decimal(20))=t2.name;
select t1.*,t2.* from t1,t2 where cast(t1.name as double)=t2.name;

-- expect result:
+----+--------------------+----+--------------------+
| id | name               | id | name               |
+----+--------------------+----+--------------------+
|  1 | 123456789012345611 |  1 | 123456789012345611 |
|  1 | 123456789012345611 |  2 | 123456789012345611 |
|  3 | 123123             |  3 |             123123 |
+----+--------------------+----+--------------------+
3 rows in set (0.00 sec)

-- but we get this now:
+----+--------------------+----+--------------------+
| id | name               | id | name               |
+----+--------------------+----+--------------------+
|  2 | 123456789012345612 |  1 | 123456789012345611 |
|  1 | 123456789012345611 |  1 | 123456789012345611 |
|  2 | 123456789012345612 |  2 | 123456789012345611 |
|  1 | 123456789012345611 |  2 | 123456789012345611 |
|  3 | 123123             |  3 |             123123 |
+----+--------------------+----+--------------------+
5 rows in set (0.00 sec)

Suggested fix:
when "varchar" = "bigint", 
better cast "varchar" to "decimal", 
or cast "bigint" to "string".

Hello Shawn Yan,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
The proposed solution could be reasonable, however this is not a bug.
The documented behavior for MySQL when comparing a character string to a number
is to convert both operands to double precision floating point values.
In that process, some lack of precision is expected, and thus results may be
imprecise compared to expected behavior.
The user is advised to add explicit CAST operations when implicit CAST operations
cause loss of precision.

@Roy Lyseng

thank you for your quick reply,

> In that process, some lack of precision is expected, and thus results may be imprecise compared to expected behavior.

we don't think thus results are expected behavior. A lossless comparison is easier to accept.
if mysql is one of the best rdbms, should be able to do lossless imprecise conversion, not let end user to cover it.

meanwhile, we run this test case in other DB (like MariaDB, Oracle), and get expected results (3 rows in set).

more evidence, in Oracle doc, https://docs.oracle.com/cd/E73729_01/DR/Implicit_Conversion.html
INTEGER will convert to STRING

I can just repeat that the results are correct according to the type rules
implemented by MySQL. Notice also that comparing a string with a numeric value
is outside the SQL standard, so any database is free to define its own semantics
for such operations.

I will also strongly encourage you to add explicit CAST operations when handling
comparisons on data types that are outside the SQL standard.

MySQL may also change this semantics at some later time, but this would have
to be done in a major release and it would come with extensive documentation and
warnings to users depending on the current functionality.

well, thank you for your support.