Unexpected Results in TiFlash

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 CHAR);
INSERT INTO t0 VALUES ('1');
ALTER TABLE t0 SET TIFLASH REPLICA 1;

SELECT SLEEP(5);
EXPLAIN SELECT t0.c0 FROM t0 WHERE CAST((t0.c0 NOT LIKE t0.c0) AS DATE)<FLOOR(t0.c0); -- {}
EXPLAIN SELECT /*+ READ_FROM_STORAGE(TIKV[t0])*/t0.c0 FROM t0 WHERE CAST((t0.c0 NOT LIKE t0.c0) AS DATE)<FLOOR(t0.c0); -- {1}

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

3. What did you see instead (Required)

Both queries return different results.

4. What is your TiDB version? (Required)

| Release Version: v7.3.0
Edition: Community
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471
Git Branch: heads/refs/tags/v7.3.0
UTC Build Time: 2023-08-08 10:08:14
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv |

Startup command:

tiup playground 7.3.0

[bajinsheng]

/label fuzz/sqlancer

[jebter]

In 6.5.4, both queries returned 1

[windtalker]

root cause is TiFlash return null for cast(0 as date), this is actually expected in a sql mode that has NO_ZERO_IN_DATE

[jebter]

/remove- severity critical
/severity major

[jebter]

/remove-severity critical

[bajinsheng]

I removed the NO_ZERO_IN_DATE in sql_mode, but I still observed the same results.

CREATE TABLE t0(c0 CHAR);
INSERT INTO t0 VALUES ('1');
ALTER TABLE t0 SET TIFLASH REPLICA 1;
SET sql_mode='';

SELECT SLEEP(5);
SELECT t0.c0 FROM t0 WHERE CAST((t0.c0 NOT LIKE t0.c0) AS DATE)<FLOOR(t0.c0); -- {}
SELECT /*+ READ_FROM_STORAGE(TIKV[t0])*/t0.c0 FROM t0 WHERE CAST((t0.c0 NOT LIKE t0.c0) AS DATE)<FLOOR(t0.c0); -- {1}

[windtalker]

Yes, SQL mode is not supported well in TiDB/TiKV/TiFlash :(

[yibin87]

/assign @solotzg