Bug case in semi join When having complicated data transformation

[curry-oss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0 (
    c0 BLOB(338),
    c1 NUMERIC NOT NULL,
    c2 NUMERIC UNSIGNED ZEROFILL UNIQUE
);

CREATE TABLE t1 LIKE t0;

INSERT IGNORE INTO t0 VALUES (NULL, 2025, NULL);
INSERT INTO t1 (c2, c0, c1) VALUES (2025, '', -2023);

SELECT 
    CRC32(LOG10(t1.c2)) & (0.2 OR IS_IPV4_MAPPED('J'))
FROM 
    t1 WHERE 
    EXISTS ( SELECT 1 FROM t0);

2. What did you expect to see? (Required)

see the following case:

3. What did you see instead (Required)

// MySQL 8.4.1
MySQL [test]> SELECT
    ->     CRC32(LOG10(t1.c2)) & (0.2 OR IS_IPV4_MAPPED('J'))
    -> FROM
    ->     t1
    -> WHERE
    ->     EXISTS (
    ->         SELECT 1
    ->         FROM t0
    ->     );
+----------------------------------------------------+
| CRC32(LOG10(t1.c2)) & (0.2 OR IS_IPV4_MAPPED('J')) |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.01 sec)

// TiDB
mysql> SELECT
    ->     CRC32(LOG10(t1.c2)) & (0.2 OR IS_IPV4_MAPPED('J'))
    -> FROM
    ->     t1
    -> WHERE
    ->     EXISTS (
    ->         SELECT 1
    ->         FROM t0
    ->     );
+----------------------------------------------------+
| CRC32(LOG10(t1.c2)) & (0.2 OR IS_IPV4_MAPPED('J')) |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.04 sec)

4. What is your TiDB version? (Required)

TiDB v8.5.0

[curry-oss]

/label affects-8.5