NOT NULL should not change the query result

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Case 1:

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);
SELECT c1 FROM t1 WHERE (NULLIF(t1.c1, (t1.c1 IS NOT NULL) REGEXP('\\>{U'))); -- [HY000][1105] other error: Invalid regexp pattern
mysql> EXPLAIN SELECT c1 FROM t1 WHERE (NULLIF(t1.c1, (t1.c1 IS NOT NULL) REGEXP('\\>{U')));
+-------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
| id                      | estRows  | task      | access object | operator info                                                                                       |
+-------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
| TableReader_7           | 8000.00  | root      |               | data:Selection_6                                                                                    |
| └─Selection_6           | 8000.00  | cop[tikv] |               | if(eq(test.t1.c1, regexp(cast(not(isnull(test.t1.c1)), var_string(20)), "\>{U")), NULL, test.t1.c1) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                      |
+-------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Case 2:

CREATE TABLE t1 (c1 INT NOT NULL);
INSERT INTO t1 VALUES (1);
SELECT c1 FROM t1 WHERE (NULLIF(t1.c1, (t1.c1 IS NOT NULL) REGEXP('\\>{U'))); -- 1
mysql> EXPLAIN SELECT c1 FROM t1 WHERE (NULLIF(t1.c1, (t1.c1 IS NOT NULL) REGEXP('\\>{U')));
+-------------------------+----------+-----------+---------------+-----------------------------------------+
| id                      | estRows  | task      | access object | operator info                           |
+-------------------------+----------+-----------+---------------+-----------------------------------------+
| TableReader_7           | 8000.00  | root      |               | data:Selection_6                        |
| └─Selection_6           | 8000.00  | cop[tikv] |               | if(eq(test.t1.c1, 0), NULL, test.t1.c1) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo          |
+-------------------------+----------+-----------+---------------+-----------------------------------------+
3 rows in set (0.00 sec)

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

The same SELECT statement should both return value 1 or return an error, since the only difference between the two cases is whether the column c1 is NOT NULL or not. NOT NULL should not change the query result.

3. What did you see instead (Required)

Case 1 returns an error, case 2 return a value 1.

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]

I tried the case 2 in MySQL, which reports an error, instead of value 1 in TiDB.

CREATE TABLE t1 (c1 INT NOT NULL);
INSERT INTO t1 VALUES (1);
SELECT c1 FROM t1 WHERE (NULLIF(t1.c1, (t1.c1 IS NOT NULL) REGEXP('\\>{U'))); -- ERROR 3692 (HY000): Incorrect description of a {min,max} interval.