Description:
Run the following statements, in which the query returns an incorrect query result.
The possible root cause could be that an incorrect data conversion is used when comparing 1 and '1a', since when evaluating SELECT 1='1a', 1 is returned. For SELECT 0='a', 1 is also returned.
Moreover, I got the following warnings when evaluating the query.
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: 'a' for column 'c1' at row 1 |
| Warning | 1366 | Incorrect decimal value: '1a' for column 'c1' at row 2 |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
How to repeat:
CREATE TABLE t1 ( c1 decimal(10,0), UNIQUE KEY i1 (c1));
CREATE TABLE t2 ( c1 text NOT NULL);
INSERT INTO t1(c1) VALUES (0), (1);
INSERT INTO t2(c1) VALUES ('a'), ('1a');
SELECT /*+ JOIN_ORDER(t2,t1) */ t1.c1 FROM t1 USE INDEX (i1) NATURAL JOIN t2; -- actual: {0}, expected: {0,1}
Description: Run the following statements, in which the query returns an incorrect query result. The possible root cause could be that an incorrect data conversion is used when comparing 1 and '1a', since when evaluating SELECT 1='1a', 1 is returned. For SELECT 0='a', 1 is also returned. Moreover, I got the following warnings when evaluating the query. mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1366 | Incorrect decimal value: 'a' for column 'c1' at row 1 | | Warning | 1366 | Incorrect decimal value: '1a' for column 'c1' at row 2 | +---------+------+--------------------------------------------------------+ 2 rows in set (0.00 sec) How to repeat: CREATE TABLE t1 ( c1 decimal(10,0), UNIQUE KEY i1 (c1)); CREATE TABLE t2 ( c1 text NOT NULL); INSERT INTO t1(c1) VALUES (0), (1); INSERT INTO t2(c1) VALUES ('a'), ('1a'); SELECT /*+ JOIN_ORDER(t2,t1) */ t1.c1 FROM t1 USE INDEX (i1) NATURAL JOIN t2; -- actual: {0}, expected: {0,1}