The SELECT statement fails to return the value

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c1 smallint(5) unsigned not null); 
INSERT INTO t0 VALUES (0);
SELECT c1 FROM t0 WHERE REPLACE(0, (CASE t0.c1 WHEN IFNULL(-('a'), SUBSTRING_INDEX(0, t0.c1, 1)) THEN 0 END ), 1); -- actual: {}, expected: {0}

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

The SELECT statement returns the value.

3. What did you see instead (Required)

The SELECT statement fails to return the value.

4. What is your TiDB version? (Required)

Release Version: v6.6.0
Edition: Community
Git Commit Hash: f4ca082
Git Branch: heads/refs/tags/v6.6.0
UTC Build Time: 2023-02-17 14:49:02
GoVersion: go1.19.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[sayJason]

The above test case can be further reduced into the following case.

CREATE TABLE t0(c1 smallint(5) unsigned not null); 
INSERT INTO t0 VALUES (0);
SELECT c1 FROM t0 WHERE t0.c1 = IFNULL(-(''), ''); -- actual: {}, expected: {0}

[xzhangxian1008]

In TiDB v8.0, commit hash: cac449b, the result is correct and here is the explain result:

mysql> explain SELECT c1 FROM t0 WHERE REPLACE(0, CASE t0.c1 WHEN IFNULL(-('a'), SUBSTRING_INDEX(0, t0.c1, 1)) THEN 0 END, 1);
+-------------------------+---------+-----------+---------------+---------------------------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                                       |
+-------------------------+---------+-----------+---------------+---------------------------------------------------------------------+
| TableReader_7           | 1.60    | root      |               | data:Selection_6                                                    |
| └─Selection_6           | 1.60    | cop[tikv] |               | replace("0", cast(case(eq(test.t0.c1, 0), 0), var_string(20)), "1") |
|   └─TableFullScan_5     | 2.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                      |
+-------------------------+---------+-----------+---------------+---------------------------------------------------------------------+

In TiDB v6.6, commit hash: f4ca082, the result is incorrect and here is the explain result:

mysql> explain SELECT c1 FROM t0 WHERE REPLACE(0, CASE t0.c1 WHEN IFNULL(-('a'), SUBSTRING_INDEX(0, t0.c1, 1)) THEN 0 END, 1);
+-------------------------+---------+-----------+---------------+-----------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                       |
+-------------------------+---------+-----------+---------------+-----------------------------------------------------+
| TableReader_7           | 1.60    | root      |               | data:Selection_6                                    |
| └─Selection_6           | 1.60    | cop[tikv] |               | replace("0", cast(case(0, 0), var_string(20)), "1") |
|   └─TableFullScan_5     | 2.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                      |
+-------------------------+---------+-----------+---------------+-----------------------------------------------------+

We can see that there is wrong planner in tidb v6.6, as case(0, 0) always return null value and it's contrary to the sql semantics.

[xzhangxian1008]

@winoros

[xzhangxian1008]

/sig planner

[xzhangxian1008]

/remove-sig execution

[qw4990]

Can't reproduce, it might have been fixed by some PR:

mysql> SELECT c1 FROM t0 WHERE REPLACE(0, (CASE t0.c1 WHEN IFNULL(-('a'), SUBSTRING_INDEX(0, t0.c1, 1)) THEN 0 END ), 1); -- actual: {}, expected: {0}
+----+
| c1 |
+----+
|  0 |
+----+
1 row in set, 1 warning (0.01 sec)