count() is incompatible with mysql and tidb

[wan1y]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t1`  (
  `COL1` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
insert into t1 values("tidb");

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

mysql:
mysql> select col1 from t1 group by col1 having count(col1) = "1e64";
Empty set

3. What did you see instead (Required)

tidb:
mysql> select col1 from t1 group by col1 having count(col1) = "1e20";
Empty set

mysql> select col1 from t1 group by col1 having count(col1) = "1e64";
+------+
| col1 |
+------+
| tidb |
+------+
1 row in set (0.59 sec)

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-2444-g5715eefd2-dirty
Edition: Community
Git Commit Hash: 5715eefd2b2febdd6e3b64413bb066f4d425a3dc
Git Branch: master
UTC Build Time: 2021-03-24 14:28:28
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[morgo]

Verified as described. My client returned a warning for this statement though:

mysql> select col1 from t1 group by col1 having count(col1) = "1e64";
+------+
| col1 |
+------+
| tidb |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1690 | BIGINT value is out of range in '1e64' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

[XuHuaiyu]

count(col1) = "1e64" is rewritten to eq(Column#3, 1)

 desc select col1 from t1 group by col1 having count(col1) = "1e64";
+------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                          |
+------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------+
| Projection_6                 | 0.80    | root      |               | test.t1.col1                                                                                           |
| └─Selection_7                | 0.80    | root      |               | eq(Column#3, 1)                                                                                        |
|   └─HashAgg_10               | 1.00    | root      |               | group by:test.t1.col1, funcs:count(test.t1.col1)->Column#3, funcs:firstrow(test.t1.col1)->test.t1.col1 |
|     └─TableReader_15         | 1.00    | root      |               | data:TableFullScan_14                                                                                  |
|       └─TableFullScan_14     | 1.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                         |
+------------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

[time-and-fate]

This issue is introduced by #23281. Obviously, it's not directly related to this issue. The root cause and how to fix it need further investigation.

[time-and-fate]

As XuHuaiyu said above, the result is wrong because the 1e64 becomes 1 in the query plan.

The direct reason for the 1e64 becoming 1 is this line of code:

tidb/types/convert.go

Line 496 in 9339955

return validFloat[:eIdx], nil

Changing it to return validFloat, nil seems a reasonable fix, but it's not easy to estimate the influence of this change, and I'm not sure if this change is correct.
I tried this fix in #34453, but one unit test case failed. I believe the test case itself is questionable but I'm not sure.

[qw4990]

Can't reproduce this, this issue might have been fixed by some other PR:

mysql> select col1 from t1 group by col1 having count(col1) = "1e64";
Empty set (0.00 sec)

mysql> select col1 from t1 group by col1 having count(col1) = "1e20";
Empty set (0.00 sec)

[qw4990]

Fixed by #47803