Bug #117458 Unexpected results when using CHAR function in WHERE clause

• Submitted: 13 Feb 2:50
• Modified: 17 Feb 13:41
• Reporter: wang jack
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.1, 8.0.41
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[13 Feb 2:50] wang jack

Description:
From my understanding, the first query below should return 1 row, but it is actually return empty.

The results are correct on MySQL 9.1.

mysql> SELECT t0.c0, t1.c0 FROM t1 RIGHT JOIN t0 ON  EXISTS (SELECT 1 WHERE FALSE) WHERE (NOT (CHAR(t1.c0)));
Empty set (0.00 sec)

mysql> SELECT SUM(((NOT (CHAR(t1.c0)))) IS TRUE) FROM t1 RIGHT JOIN t0 ON  EXISTS (SELECT 1 WHERE FALSE);
+------------------------------------+
| SUM(((NOT (CHAR(t1.c0)))) IS TRUE) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.1     |
+-----------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 NUMERIC  COLUMN_FORMAT DEFAULT) ;
CREATE TABLE t1(c0 FLOAT NULL UNIQUE);
REPLACE INTO t0(c0) VALUES(1433368138);

SELECT t0.c0, t1.c0 FROM t1 RIGHT JOIN t0 ON  EXISTS (SELECT 1 WHERE FALSE) WHERE (NOT (CHAR(t1.c0)));
SELECT SUM(((NOT (CHAR(t1.c0)))) IS TRUE) FROM t1 RIGHT JOIN t0 ON  EXISTS (SELECT 1 WHERE FALSE);

[13 Feb 7:04] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[17 Feb 13:41] Roy Lyseng

Duplicate of bug#112397.