Bug #117452 Unexpected results with IS_IPV4_MAPPED in WHERE clause.

• Submitted: 13 Feb 1:38
• Modified: 13 Feb 4:38
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.1 9.1.0 , 8.0.41
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[13 Feb 1:38] wang jack

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

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

mysql> SELECT ALL SUM(((NOT (IS_IPV4_MAPPED(t0.c0)))) IS TRUE) FROM t0 RIGHT JOIN t1 ON t1.c0;
+----------------------------------------------+
| SUM(((NOT (IS_IPV4_MAPPED(t0.c0)))) IS TRUE) |
+----------------------------------------------+
|                                            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 SMALLINT) ;
CREATE TABLE IF NOT EXISTS t1(c0 SMALLINT(15)) ;
INSERT INTO t1(c0) VALUES(NULL);

SELECT t1.c0, t0.c0 FROM t0 RIGHT JOIN t1 ON t1.c0 WHERE (NOT (IS_IPV4_MAPPED(t0.c0)));
SELECT ALL SUM(((NOT (IS_IPV4_MAPPED(t0.c0)))) IS TRUE) FROM t0 RIGHT JOIN t1 ON t1.c0;

[13 Feb 4:38] MySQL Verification Team

Hello wang jack,

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

regards,
Umesh