The result of ‘group by having' is incorrect

[wan1y]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(col1 varchar(20));
mysql> insert into t1 values("哈哈哈");
mysql> insert into t1 values("嘿嘿嘿");

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

mysql> select t1.col1, t2.col1 from t1 as t1 inner join t1 as t2 on t1.col1 = t2.col1 where t1.col1 = 0xe59388e59388e59388 group by t1.col1, t2.col1 having t1.col1 = 0xe598bfe598bfe598bf;
Empty set

3. What did you see instead (Required)

mysql> select t1.col1, t2.col1 from t1 as t1 inner join t1 as t2 on t1.col1 = t2.col1 where t1.col1 = 0xe59388e59388e59388 group by t1.col1, t2.col1 having t1.col1 = 0xe598bfe598bfe598bf;
+--------+--------+
| col1   | col1   |
+--------+--------+
| 哈哈哈 | 哈哈哈 |
+--------+--------+
1 row in set (0.05 sec)

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-2390-gfd706ab76
Edition: Community
Git Commit Hash: fd706ab76bd09ac859aa0a4de7fe9e07da3c5508
Git Branch: master
UTC Build Time: 2021-03-17 11:37:12
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[zyguan]

When trying to remove duplicated exprs in LogicalJoin.PredicatePushDown, we calculate the hash code of each expr. However, t1.col1 = 0xe59388e59388e59388 and t1.col1 = 0xe598bfe598bfe598bf actually have same hash code because both binary literals are greater than MaxUint64, then t1.col1 = 0xe598bfe598bfe598bf gets dropped.

[aytrack]

The similar situation was found with in operator.
TiDB

MySQL > drop table if exists t;
                       ->  create table t (col1 varbinary(20));
                       ->  insert into t values (0xf4c7abc4c0140a574bf170ffe31ad3bbac890017), (0xe62746d3b7c0c96314550eb81a7e00adcf0de010), (0x91d8445f6d7b739efce0bc69df454aa16827ae73);
                       ->  select t1.col1, t2.col1 from t as t1 inner join t as t2 on t1.col1 = t2.col1 where t1.col1 in (0xE62746D3B7C0C96314550EB81A7E00ADCF0DE010, 0x91D8445F6D7B739EFCE0BC69DF454AA16827AE73, 0xF4C7ABC4C0140A574BF170FFE31AD3BBAC890017)
                       ->  group by t1.col1, t2.col1 having t1.col1 in (0x8958357F32B1E49AD75B0EFE3F63E1F73794A9D2, 0x53EB8734D4A6E12F01507458EA437D326E29A192, 0xBFC1FABBEDEE461D1546492CEB154BA17DC61CF5);
Query OK, 0 rows affected
Time: 0.013s

Query OK, 0 rows affected
Time: 0.005s

Query OK, 3 rows affected
Time: 0.001s

+--------------------------------------------+--------------------------------------------+
| col1                                       | col1                                       |
+--------------------------------------------+--------------------------------------------+
| 0x91d8445f6d7b739efce0bc69df454aa16827ae73 | 0x91d8445f6d7b739efce0bc69df454aa16827ae73 |
| 0xf4c7abc4c0140a574bf170ffe31ad3bbac890017 | 0xf4c7abc4c0140a574bf170ffe31ad3bbac890017 |
| 0xe62746d3b7c0c96314550eb81a7e00adcf0de010 | 0xe62746d3b7c0c96314550eb81a7e00adcf0de010 |
+--------------------------------------------+--------------------------------------------+

MySQL :

MySQL > drop table if exists t;
                          ->  create table t (col1 varbinary(20));
                          ->  insert into t values (0xf4c7abc4c0140a574bf170ffe31ad3bbac890017), (0xe62746d3b7c0c96314550eb81a7e00adcf0de010), (0x91d8445f6d7b739efce0bc69df454aa16827ae73);
                          ->  select t1.col1, t2.col1 from t as t1 inner join t as t2 on t1.col1 = t2.col1 where t1.col1 in (0xE62746D3B7C0C96314550EB81A7E00ADCF0DE010, 0x91D8445F6D7B739EFCE0BC69DF454AA16827AE73, 0xF4C7ABC4C0140A574BF170FFE31AD3BBAC89001
                          ->  7) group by t1.col1, t2.col1 having t1.col1 in (0x8958357F32B1E49AD75B0EFE3F63E1F73794A9D2, 0x53EB8734D4A6E12F01507458EA437D326E29A192, 0xBFC1FABBEDEE461D1546492CEB154BA17DC61CF5);
                          ->
                          ->
Query OK, 0 rows affected
Time: 0.299s

Query OK, 0 rows affected
Time: 1.727s

Query OK, 3 rows affected
Time: 0.059s

+------+------+
| col1 | col1 |
+------+------+

[xuyifangreeneyes]

/assign

[time-and-fate]

This specific issue has been fixed by #27021.
So closing this issue.

[time-and-fate]

When trying to remove duplicated exprs in LogicalJoin.PredicatePushDown, we calculate the hash code of each expr. However, t1.col1 = 0xe59388e59388e59388 and t1.col1 = 0xe598bfe598bfe598bf actually have same hash code because both binary literals are greater than MaxUint64, then t1.col1 = 0xe598bfe598bfe598bf gets dropped.

Created #30094 to track this issue

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.