Unexpected results of ANY operator

[DerZc]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Consider the following program:

CREATE TABLE t0(c2 BIGINT );
INSERT INTO t0(c2) VALUES (1);

SELECT MIN(t0.c2) FROM t0 WHERE false; -- NULL

SELECT t0.c2 FROM t0 WHERE ((-1)<=(~ ('n') = ANY (SELECT (NULL)))); -- empty result

SELECT t0.c2 FROM t0 WHERE ((-1)<=(~ ('n') = ANY (SELECT MIN(t0.c2) FROM t0 WHERE false))); -- 1

The second one is equivalent to the third one, because both the inner query of them return NULL. I believe the third one returns an incorrect result because (~ ('n') = ANY (SELECT MIN(t0.c2) FROM t0 WHERE false)) should return NULL as described in the document of MySQL https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html

The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

And -1 <= NULL also equals to NULL, then this query should have an empty result.

I also check this program in MySQL, and get empty result on this query. https://www.db-fiddle.com/f/5JLDSSaK6mWoXZyHEr64a5/0

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

The third query returns an empty result.

3. What did you see instead (Required)

The third query returns a non-empty result.

4. What is your TiDB version? (Required)

Release Version: v7.2.0-alpha-434-g3ca5d3d618\nEdition: Community\nGit Commit Hash: 3ca5d3d6187a7659bec532132757711c256d6ade\nGit Branch: master\nUTC Build Time: 2023-06-15 10:17:03\nGoVersion: go1.20.3\nRace Enabled: false\nCheck Table Before Drop: false\nStore: unistore