Description:
A query involving a string constant compared to a DECIMAL column in an OUTER JOIN condition may produce inconsistent results.
In the following example, the string "1T" is implicitly converted to 1, so the join condition t0.c0 = "1T" should behave as t0.c0 = 1.
The UNION ALL query splits the result into IS NULL and IS NOT NULL partitions, which together should be equivalent to the original query.
However, inconsistent results may be observed.
How to repeat:
CREATE TABLE t0(c0 DECIMAL KEY);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0) VALUES(1), (2);
INSERT INTO t1(c0) VALUES(3);
(SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" WHERE t0.c0 IS NULL)
UNION ALL
(SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" WHERE t0.c0 IS NOT NULL);
/*
+------+------+
| ref0 | ref1 |
+------+------+
| NULL | 3 |
| 1 | 3 |
+------+------+
2 rows in set, 2 warnings (0.01 sec)
*/
SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" ;
/*
+------+------+
| ref0 | ref1 |
+------+------+
| NULL | 3 |
+------+------+
1 row in set, 1 warning (0.00 sec)
*/
~
Description: A query involving a string constant compared to a DECIMAL column in an OUTER JOIN condition may produce inconsistent results. In the following example, the string "1T" is implicitly converted to 1, so the join condition t0.c0 = "1T" should behave as t0.c0 = 1. The UNION ALL query splits the result into IS NULL and IS NOT NULL partitions, which together should be equivalent to the original query. However, inconsistent results may be observed. How to repeat: CREATE TABLE t0(c0 DECIMAL KEY); CREATE TABLE t1(c0 INT); INSERT INTO t0(c0) VALUES(1), (2); INSERT INTO t1(c0) VALUES(3); (SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" WHERE t0.c0 IS NULL) UNION ALL (SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" WHERE t0.c0 IS NOT NULL); /* +------+------+ | ref0 | ref1 | +------+------+ | NULL | 3 | | 1 | 3 | +------+------+ 2 rows in set, 2 warnings (0.01 sec) */ SELECT t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON t0.c0 = "1T" ; /* +------+------+ | ref0 | ref1 | +------+------+ | NULL | 3 | +------+------+ 1 row in set, 1 warning (0.00 sec) */ ~