Incorrect query reuslt caused by NULLIF keyword

[Syang111]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Consider the following statements.

In theory, query1 and query2 should return the same result. However, query1 return nothing NULL while query2 return nothing.

CREATE TABLE t0(c0 DOUBLE);
REPLACE INTO t0(c0) VALUES (0.40194983109852933);
CREATE VIEW v0(c0) AS SELECT CAST(')' AS TIME) FROM t0 WHERE '0.030417148673465677';

mysql> SELECT f1 FROM (SELECT NULLIF(v0.c0, 1371581446) AS f1 FROM v0, t0) AS t WHERE f1 <=> 1292367147; --query1
+------+
| f1   |
+------+
| NULL |
+------+
1 row in set, 3 warnings (0.01 sec)

mysql> SELECT f1 FROM (SELECT NULLIF(v0.c0, 1371581446) AS f1, (NULLIF(v0.c0, 1371581446) <=> 1292367147 ) IS TRUE AS flag FROM v0, t0) AS t WHERE flag=1; --query2
Empty set, 3 warnings (0.00 sec)

2. What is your TiDB version? (Required)

v7.6.0, v6.4.0

[aytrack]

[17:53:53]TiDB root:test> SELECT NULLIF(v0.c0, 1371581446) <=> 1292367147 FROM v0, t0;
+------------------------------------------+
| NULLIF(v0.c0, 1371581446) <=> 1292367147 |
+------------------------------------------+
| 0                                        |
+------------------------------------------+
1 row in set
Time: 0.005s
[17:54:06]TiDB root:test> explain SELECT f1 FROM (SELECT NULLIF(v0.c0, 1371581446) AS f1 FROM v0, t0) AS t WHERE f1 <=> 1292367147;
+---------------------------+---------+-----------+---------------+----------------------------------------------------+
| id                        | estRows | task      | access object | operator info                                      |
+---------------------------+---------+-----------+---------------+----------------------------------------------------+
| Projection_13             | 1.00    | root      |               | if(eq(Column#3, <nil>), <nil>, Column#3)->Column#6 |
| └─HashJoin_15             | 1.00    | root      |               | CARTESIAN inner join                               |
|   ├─Projection_19(Build)  | 1.00    | root      |               | <nil>->Column#3                                    |
|   │ └─TableReader_21      | 1.00    | root      |               | data:TableFullScan_20                              |
|   │   └─TableFullScan_20  | 1.00    | cop[tikv] | table:t0      | keep order:false                                   |
|   └─TableReader_18(Probe) | 1.00    | root      |               | data:TableFullScan_17                              |
|     └─TableFullScan_17    | 1.00    | cop[tikv] | table:t0      | keep order:false                                   |
+---------------------------+---------+-----------+---------------+----------------------------------------------------+
7 rows in set
Time: 0.009s
[17:54:06]TiDB root:test>

[windtalker]

The root cause is there is a special rule when compare between duration and other types(I think is for some mysql compatibility):

tidb/pkg/expression/builtin_compare.go

Lines 1320 to 1326 in 968f4f2

	/*
	<temporal column> <cmp> <non-temporal constant>
	or
	<non-temporal constant> <cmp> <temporal column>
	Convert the constant to temporal type.
	*/

when compare duration column and other constant, the constant will be converted to duration type
For query

SELECT f1 FROM (SELECT NULLIF(v0.c0, 1371581446) AS f1 FROM v0, t0) AS t WHERE f1 <=> 1292367147;

f1 <=> 1292367147 is a duration column(f1) compare with constant, so 1292367147 will be converted to duration, which will be null, and f1 itself is null, so the filter returns 1
For query

SELECT f1 FROM (SELECT NULLIF(v0.c0, 1371581446) AS f1, (NULLIF(v0.c0, 1371581446) <=> 1292367147 ) IS TRUE AS flag FROM v0, t0) AS t WHERE flag=1

NULLIF(v0.c0, 1371581446) <=> 1292367147 is a comparision between a function with duration type and a constant, so both side will be converted to real type, which can get the correct result.