select count(distinct decimal(40, 20)) get wrong result

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
source distinct.txt

SELECT count(distinct `col_decimal(40, 20)_undef_signed`) FROM table_200000_utf8_4;
+----------------------------------------------------+
| count(distinct `col_decimal(40, 20)_undef_signed`) |
+----------------------------------------------------+
|                                              78217 |
+----------------------------------------------------+
1 row in set (4.50 sec)

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

in mysql 8.0.17

mysql> SELECT count(distinct `col_decimal(40, 20)_undef_signed`) FROM table_200000_utf8_4;
+----------------------------------------------------+
| count(distinct `col_decimal(40, 20)_undef_signed`) |
+----------------------------------------------------+
|                                              79389 |
+----------------------------------------------------+
1 row in set (9.23 sec)

3. Affected version (Required)

Release Version: v4.0.0-rc.1-79-g1934f34c9
Edition: Community
Git Commit Hash: 1934f34c94a173a492506489ccfb2fc931cd2200
Git Branch: release-4.0
UTC Build Time: 2020-05-14 07:10:09
GoVersion: go1.14.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

4. Root Cause Analysis

[ghost]

Here is a minimal testcase against master:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  `pk` int(11) NOT NULL,
  col1 decimal(40,20) DEFAULT NULL
);

INSERT INTO `t1` VALUES (2084,0.02040000000000000000),(35324,0.02190000000000000000),(43760,0.00510000000000000000),(46084,0.01400000000000000000),(46312,0.00560000000000000000),(61632,0.02730000000000000000),(94676,0.00660000000000000000),(102244,0.01810000000000000000),(113144,0.02140000000000000000),(157024,0.02750000000000000000),(157144,0.01750000000000000000),(182076,0.02370000000000000000),(188696,0.02330000000000000000),(833,0.00390000000000000000),(6701,0.00230000000000000000),(8533,0.01690000000000000000),(13801,0.01360000000000000000),(20797,0.00680000000000000000),(36677,0.00550000000000000000),(46305,0.01290000000000000000),(76113,0.00430000000000000000),(76753,0.02400000000000000000),(92393,0.01720000000000000000),(111733,0.02690000000000000000),(152757,0.00250000000000000000),(162393,0.02760000000000000000),(167169,0.00440000000000000000),(168097,0.01360000000000000000),(180309,0.01720000000000000000),(19918,0.02620000000000000000),(58674,0.01820000000000000000),(67454,0.01510000000000000000),(70870,0.02880000000000000000),(89614,0.02530000000000000000),(106742,0.00180000000000000000),(107886,0.01580000000000000000),(147506,0.02230000000000000000),(148366,0.01340000000000000000),(167258,0.01860000000000000000),(194438,0.00500000000000000000),(10307,0.02850000000000000000),(14539,0.02210000000000000000),(27703,0.00050000000000000000),(32495,0.00680000000000000000),(39235,0.01450000000000000000),(52379,0.01640000000000000000),(54551,0.01910000000000000000),(85659,0.02330000000000000000),(104483,0.02670000000000000000),(109911,0.02040000000000000000),(114523,0.02110000000000000000),(119495,0.02120000000000000000),(137603,0.01910000000000000000),(154031,0.02580000000000000000);

SELECT count(distinct col1) FROM t1;

..

mysql> SELECT count(distinct col1) FROM t1;
+----------------------+
| count(distinct col1) |
+----------------------+
|                   47 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-932-g8978773f5
Edition: Community
Git Commit Hash: 8978773f5e3d43a100550e6babea9904a99e5938
Git Branch: master
UTC Build Time: 2020-08-10 12:35:53
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

Vs MySQL:

mysql [localhost:8021] {msandbox} (test) > SELECT count(distinct col1) FROM t1;
+----------------------+
| count(distinct col1) |
+----------------------+
|                   48 |
+----------------------+
1 row in set (0.00 sec)

[ichn-hu]

#19131 this PR would solve this issue, we could close it once this PR get merged.