having clause does not work correctly

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists UK_NSPRE_19416;
CREATE TABLE `UK_NSPRE_19416` (  `COL1` binary(20) DEFAULT NULL,  `COL2` varchar(20) DEFAULT NULL,  `COL4` datetime DEFAULT NULL,  `COL3` bigint(20) DEFAULT NULL,  `COL5` float DEFAULT NULL,  UNIQUE KEY `UK_COL1` (`COL1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into `UK_NSPRE_19416`(col1) values (0xc5b428e2ebc1b78f0b183899a8df55c88a333f86), (0x004dad637b37cc4a9742484ab93f97ede2ab8bd5), (0x550c4a4390ba14fd6d382dd29063e10210c99381);
select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);

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

mysql> select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);
Empty set (0.06 sec)

3. What did you see instead (Required)

4.0.14

mysql> select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);
+--------------------------------------------+----------------+
| col1                                       | count(t2.col1) |
+--------------------------------------------+----------------+
| 0x550C4A4390BA14FD6D382DD29063E10210C99381 |              1 |
| 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86 |              1 |
+--------------------------------------------+----------------+

5.0.3 & 5.1.0 & master(4116dd2)

MySQL > select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);
+--------------------------------------------+----------------+
| col1                                       | count(t2.col1) |
+--------------------------------------------+----------------+
| 0xc5b428e2ebc1b78f0b183899a8df55c88a333f86 | 1              |
| 0x004dad637b37cc4a9742484ab93f97ede2ab8bd5 | 1              |
| 0x550c4a4390ba14fd6d382dd29063e10210c99381 | 1              |
+--------------------------------------------+----------------+

4. What is your TiDB version? (Required)

[lzmhhh123]

tidb:

tidb(127.0.0.1:4000) > explain select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);
+-------------------------------------+---------+------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows | task | access object                             | operator info                                                                                                                                                                     |
+-------------------------------------+---------+------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_8                        | 2.40    | root |                                           | test.uk_nspre_19416.col1, Column#13                                                                                                                                               |
| └─HashAgg_9                         | 2.40    | root |                                           | group by:test.uk_nspre_19416.col1, test.uk_nspre_19416.col1, funcs:count(test.uk_nspre_19416.col1)->Column#13, funcs:firstrow(test.uk_nspre_19416.col1)->test.uk_nspre_19416.col1 |
|   └─MergeJoin_10                    | 3.00    | root |                                           | left outer join, left key:test.uk_nspre_19416.col1, right key:test.uk_nspre_19416.col1                                                                                            |
|     ├─Selection_26(Build)           | 2.40    | root |                                           | not(isnull(test.uk_nspre_19416.col1))                                                                                                                                             |
|     │ └─Batch_Point_Get_25          | 3.00    | root | table:UK_NSPRE_19416, index:UK_COL1(COL1) | keep order:true, desc:false                                                                                                                                                       |
|     └─Batch_Point_Get_24(Probe)     | 3.00    | root | table:UK_NSPRE_19416, index:UK_COL1(COL1) | keep order:true, desc:false                                                                                                                                                       |
+-------------------------------------+---------+------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set, 6 warnings (0.00 sec)

mysql:

mysql> explain analyze select t1.col1, count(t2.col1) from UK_NSPRE_19416 as t1 left join UK_NSPRE_19416 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x550C4A4390BA14FD6D382DD29063E10210C99381, 0x004DAD637B37CC4A9742484AB93F97EDE2AB8BD5, 0xC5B428E2EBC1B78F0B183899A8DF55C88A333F86) group by t1.col1, t2.col1 having t1.col1 in (0x9B4B48FEBA9225BACF8F9ADEAEE810AEC26DC7A2, 0x25A6C4FAD832F8E0267AAA504CFAE767565C8B84, 0xE26E5B0080EC5A8156DACE67D13B239500E540E6);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN


                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.COL1 in (0x9b4b48feba9225bacf8f9adeaee810aec26dc7a2,0x25a6c4fad832f8e0267aaa504cfae767565c8b84,0xe26e5b0080ec5a8156dace67d13b239500e540e6))  (actual time=0.069..0.069 rows=0 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.001 rows=3 loops=1)
        -> Aggregate using temporary table  (actual time=0.066..0.067 rows=3 loops=1)
            -> Nested loop left join  (cost=1.60 rows=3) (actual time=0.034..0.046 rows=3 loops=1)
                -> Filter: (t1.COL1 in (0x550c4a4390ba14fd6d382dd29063e10210c99381,0x004dad637b37cc4a9742484ab93f97ede2ab8bd5,0xc5b428e2ebc1b78f0b183899a8df55c88a333f86))  (cost=0.55 rows=3) (actual time=0.022..0.029 rows=3 loops=1)
                    -> Index scan on t1 using UK_COL1  (cost=0.55 rows=3) (actual time=0.020..0.026 rows=3 loops=1)
                -> Single-row index lookup on t2 using UK_COL1 (COL1=t1.COL1)  (cost=0.28 rows=1) (actual time=0.005..0.005 rows=1 loops=3)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Having statement is ignored by TiDB.

[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