An incorrect query result.

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Case 1:

CREATE TABLE t1(c0 tinyint(1));
INSERT INTO t1 VALUES (0);
SELECT c0 FROM t1 WHERE (NOT (CAST(IFNULL(t1.c0, HEX(((t1.c0)>>(t1.c0)))) AS DATE))); -- {}

Case 2:

CREATE TABLE t1(c0 tinyint(1) not null );
INSERT INTO t1 VALUES (0);
SELECT c0 FROM t1 WHERE (NOT (CAST(IFNULL(t1.c0, HEX(((t1.c0)>>(t1.c0)))) AS DATE))); -- {0}

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

Case 1 and case 2 should return the same result.

3. What did you see instead (Required)

Case 1 and case 2 return different results.

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

[LittleFall]

Analysis

Simple Reproduce

ifnull(nullable tinyint, string) produces a string return type although the value is not null. And then cast(string as date) produces null.

In the meanwhile, ifnull(notnullable tinyint, string) produces an int return type. And cast(0 as date) produces 0000-00-00.

CREATE TABLE t1(cnotnull tinyint not null, cnull tinyint null);
INSERT INTO t1 VALUES (0, 0);

mysql> select IFNULL(cnull, '1'), IFNULL(cnotnull, '1') from t1;
Field   1:  `IFNULL(cnull, '1')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_bin (46)
Length:     16
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY

Field   2:  `IFNULL(cnotnull, '1')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     4
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE NUM


+--------------------+-----------------------+
| IFNULL(cnull, '1') | IFNULL(cnotnull, '1') |
+--------------------+-----------------------+
| 0                  |                     0 |
+--------------------+-----------------------+
1 row in set (0.01 sec)

select cast('0' as date), cast(0 as date);
+-------------------+-----------------+
| cast('0' as date) | cast(0 as date) |
+-------------------+-----------------+
| NULL              | 0000-00-00      |
+-------------------+-----------------+

Root Cause

1. ifnull's result type should be the merge type of the two arguments.
2. When tidb finds the first argument can't be null, it will directly eliminate the ifnull. Which makes the return type the first argument's.
3. for mysql, it still keep the return type. (see below picture)

explain select IFNULL(cnull, now()), IFNULL(cnotnull, now()) from t1;
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                                                                |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
| Projection_3            | 2.00    | root      |               | ifnull(cast(test.t1.cnull, var_string(20)), 2023-03-01 15:26:50)->Column#4, test.t1.cnotnull |
| └─TableReader_5         | 2.00    | root      |               | data:TableFullScan_4                                                                         |
|   └─TableFullScan_4     | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                               |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Fix

When we find the first argument is not null, we should optimize it to a cast instead of eliminate it.