wrong results in associated subquery statement

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists PK_S_MULTI_31;
CREATE TABLE `PK_S_MULTI_31` (
  `COL1` tinyint(45) NOT NULL,
  `COL2` tinyint(45) NOT NULL,
  PRIMARY KEY (`COL1`,`COL2`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `PK_S_MULTI_31` VALUES (-128,-83),(-128,96),(-124,-122),(-124,-12),(-124,-2),(-122,32),(-121,-100),(-112,39),(-112,65),(-112,97),(-111,-20),(-105,-128),(-101,87),(-96,-87),(-95,-127),(-91,-18),(-83,21),(-73,112),(-71,105),(-61,-84),(-59,-117),(-59,-53),(-56,6),(-55,19),(-54,-119),(-54,-2),(-53,0),(-46,-106),(-42,7),(-42,40),(-41,-96),(-41,-43),(-39,3),(-39,48),(-39,50),(-38,-90),(-38,-19),(-30,54),(-30,66),(-30,93),(-29,-14),(-26,126),(-24,88),(-21,66),(-21,72),(-21,117),(-18,78),(-16,122),(-10,111),(-2,101),(-2,104),(2,88),(4,-1),(11,-9),(14,78),(18,-27),(18,120),(19,95),(24,-44),(30,-119),(38,98),(39,-62),(39,72),(43,-86),(43,38),(44,-72),(44,99),(45,43),(49,81),(50,116),(56,-80),(56,-2),(58,-93),(59,-17),(62,-45),(67,-91),(67,79),(69,106),(70,-32),(70,106),(71,-57),(72,50),(74,67),(75,-92),(75,-76),(75,-47),(87,75),(90,77),(91,-74),(98,-69),(103,-73),(105,51),(105,108),(112,-15),(114,92),(115,2),(115,29),(120,87),(122,100),(124,-22),(124,34),(127,103);
SELECT col2 FROM PK_S_MULTI_31 AS T1 WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;
SELECT col2 FROM PK_S_MULTI_31 AS T1 ignore index(primary)  WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;

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

release-4.0 & release-5.0 & release-5.1

mysql> SELECT col2 FROM PK_S_MULTI_31 AS T1 WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;
+------+
| col2 |
+------+
|  -83 |
...
|   87 |
+------+
99 rows in set (0.00 sec)

mysql> SELECT col2 FROM PK_S_MULTI_31 AS T1 ignore index(primary)  WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;
| col2 |
+------+
|  -83 |
...  
|  100 |
+------+
99 rows in set (0.02 sec)

3. What did you see instead (Required)

release-5.2 & master

mysql> SELECT col2 FROM PK_S_MULTI_31 AS T1 WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;
+------+
| col2 |
+------+
|  -83 |
...
|   87 |
+------+
98 rows in set (0.00 sec)

mysql> SELECT col2 FROM PK_S_MULTI_31 AS T1 ignore index(primary)  WHERE (SELECT count(DISTINCT COL1, COL2) FROM PK_S_MULTI_31 AS T2 WHERE T2.COL1>T1.COL1)>2 ;
| col2 |
+------+
|  -83 |
...  
|  100 |
+------+
99 rows in set (0.02 sec)

4. What is your TiDB version? (Required)

Release Version: v5.2.0-alpha-551-g88f60fe11
Edition: Community
Git Commit Hash: 88f60fe1140de999c2e64ca56edffb7db7393f1e
Git Branch: release-5.2
UTC Build Time: 2021-08-16 02:41:59
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions