MySQL Bugs: #117534: Create index can lead to incorrect results

Bug #117534	Create index can lead to incorrect results
Submitted:	20 Feb 14:39	Modified:	21 Feb 11:28
Reporter:	jinhui lai	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I create an index, Changing the order of inner join can lead to incorrect results.

CREATE TABLE t0(c0 DECIMAL);
CREATE TABLE t1( c1 TEXT);
CREATE INDEX i0 ON t1(c1(1));

INSERT INTO t0 VALUES(1);
INSERT INTO t1 VALUES('1-0');

SELECT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
Empty set
SELECT * FROM t0 INNER JOIN t1 ON t1.c1 = t0.c0; -- incorrect results
+------+------+
| c0   | c1   |
+------+------+
|    1 | 1-0  |
+------+------+

When I drop the index,i get right results:

DROP INDEX i0 ON t1;

SELECT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
Empty set
SELECT * FROM t0 INNER JOIN t1 ON t1.c1 = t0.c0;
Empty set

How to repeat:
docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.2.0
docker exec -it mysql-9.2.0 mysql -uroot -p

Then execute the above test case.

I am sorry! To reproduce this bug, we should repalace the first statement with "CREATE TABLE t0(c0 DECIMAL UNIQUE); "

Hi Mr. lai,

Thank you for your bug report.

We have managed to repeat the problem that you are reporting:

mysql>
mysql> SELECT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
Empty set, 1 warning (0.00 sec)

mysql>
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '1-0' for column 'c0' at row 1 |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM t0 INNER JOIN t1 ON t1.c1 = t0.c0;
+------+------+
| c0   | c1   |
+------+------+
|    1 | 1-0  |
+------+------+
1 row in set (0.00 sec)

mysql>
mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql>
mysql>
mysql> DROP INDEX i0 ON t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
Empty set, 1 warning (0.00 sec)

mysql>
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '1-0' for column 'c0' at row 1 |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM t0 INNER JOIN t1 ON t1.c1 = t0.c0;
Empty set, 1 warning (0.00 sec)

mysql>
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1366 | Incorrect decimal value: '1-0' for column 'c0' at row 1 |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL Standards strongly prohibits mixing of different data types in the same expression. SQL standard prescribes that the error is returned when mixing, e.g. DECIMAL with INT types.

However, a change of behaviour with / without index is what makes this a bug. This is a VERY low priority bug, but still a bug. Affecting version 8.0 and all higher versions.

Verified.