Different results between plan on tiflash and plan on tikv

[xuyifangreeneyes]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tiup playground v5.1.1

CREATE TABLE t (
id bigint(20) NOT NULL AUTO_INCREMENT,
a varchar(20) DEFAULT NULL,
b varchar(50) DEFAULT NULL,
c varchar(50) DEFAULT NULL,
d varchar(50) DEFAULT NULL,
e varchar(200) DEFAULT NULL,
f varchar(200) DEFAULT NULL,
g varchar(100) DEFAULT NULL,
h varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_b (b)
) ENGINE=InnoDB AUTO_INCREMENT=51670272 DEFAULT CHARSET=utf8;

alter table t set tiflash replica 1;

insert into t (a, b, c, g) values ('1', '9ead706a6ee03bd6c548480abc351b8d', '-100,000,000', 'xxxyy'), ('2', '9ead706a6ee03bd6c548480abc351b8d', '-200,000,000', 'zzz');

SELECT b,
       Sum(CASE
             WHEN g LIKE 'xxx%yy%'
                   OR g LIKE 'zzz%' THEN Cast(
             Replace(c, ',', '') AS DECIMAL)
             ELSE 0
           END) AS equity
FROM   t
GROUP  BY b;

SELECT /*+ READ_FROM_STORAGE(TIKV[t]) */ b,
       Sum(CASE
             WHEN g LIKE 'xxx%yy%'
                   OR g LIKE 'zzz%' THEN Cast(
             Replace(c, ',', '') AS DECIMAL)
             ELSE 0
           END) AS equity
FROM   t
GROUP  BY b;

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

The two queries have the same results.

3. What did you see instead (Required)

mysql> SELECT b,
    ->        Sum(CASE
    ->              WHEN g LIKE 'xxx%yy%'
    ->                    OR g LIKE 'zzz%' THEN Cast(
    ->              Replace(c, ',', '') AS DECIMAL)
    ->              ELSE 0
    ->            END) AS equity
    -> FROM   t
    -> GROUP  BY b;
+----------------------------------+-----------+
| b                                | equity    |
+----------------------------------+-----------+
| 9ead706a6ee03bd6c548480abc351b8d | 300000000 |
+----------------------------------+-----------+
1 row in set (0.07 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(TIKV[t]) */ b,
    ->        Sum(CASE
    ->              WHEN g LIKE 'xxx%yy%'
    ->                    OR g LIKE 'zzz%' THEN Cast(
    ->              Replace(c, ',', '') AS DECIMAL)
    ->              ELSE 0
    ->            END) AS equity
    -> FROM   t
    -> GROUP  BY b;
+----------------------------------+------------+
| b                                | equity     |
+----------------------------------+------------+
| 9ead706a6ee03bd6c548480abc351b8d | -300000000 |
+----------------------------------+------------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.1.1
Edition: Community
Git Commit Hash: 797bddd25310ed42f0791c8eccb78be8cce2f502
Git Branch: heads/refs/tags/v5.1.1
UTC Build Time: 2021-07-28 11:07:25
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[xuyifangreeneyes]

mysql> explain SELECT b,
    ->        Sum(CASE
    ->              WHEN g LIKE 'xxx%yy%'
    ->                    OR g LIKE 'zzz%' THEN Cast(
    ->              Replace(c, ',', '') AS DECIMAL)
    ->              ELSE 0
    ->            END) AS equity
    -> FROM   t
    -> GROUP  BY b;
+----------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task              | access object | operator info                                                                                                                               |
+----------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4                           | 1.60    | root              |               | test.t.b, Column#10                                                                                                                         |
| └─TableReader_33                       | 1.60    | root              |               | data:ExchangeSender_32                                                                                                                      |
|   └─ExchangeSender_32                  | 1.60    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                                                                                   |
|     └─Projection_28                    | 1.60    | batchCop[tiflash] |               | Column#10, test.t.b                                                                                                                         |
|       └─HashAgg_29                     | 1.60    | batchCop[tiflash] |               | group by:test.t.b, funcs:sum(Column#13)->Column#10, funcs:firstrow(test.t.b)->test.t.b                                                      |
|         └─ExchangeReceiver_31          | 1.60    | batchCop[tiflash] |               |                                                                                                                                             |
|           └─ExchangeSender_30          | 1.60    | batchCop[tiflash] |               | ExchangeType: HashPartition, Hash Cols: test.t.b                                                                                            |
|             └─HashAgg_9                | 1.60    | batchCop[tiflash] |               | group by:Column#20, funcs:sum(Column#19)->Column#13                                                                                         |
|               └─Projection_41          | 2.00    | batchCop[tiflash] |               | case(or(like(test.t.g, xxx%yy%, 92), like(test.t.g, zzz%, 92)), cast(replace(test.t.c, ,, ), decimal(10,0) BINARY), 0)->Column#19, test.t.b |
|                 └─TableFullScan_27     | 2.00    | batchCop[tiflash] | table:t       | keep order:false, stats:pseudo                                                                                                              |
+----------------------------------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

mysql> explain SELECT /*+ READ_FROM_STORAGE(TIKV[t]) */ b,
    ->        Sum(CASE
    ->              WHEN g LIKE 'xxx%yy%'
    ->                    OR g LIKE 'zzz%' THEN Cast(
    ->              Replace(c, ',', '') AS DECIMAL)
    ->              ELSE 0
    ->            END) AS equity
    -> FROM   t
    -> GROUP  BY b;
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                                                                           |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4                 | 1.60    | root      |               | test.t.b, Column#10                                                                                                                                                     |
| └─HashAgg_14                 | 1.60    | root      |               | group by:test.t.b, funcs:sum(Column#11)->Column#10, funcs:firstrow(test.t.b)->test.t.b                                                                                  |
|   └─TableReader_15           | 1.60    | root      |               | data:HashAgg_5                                                                                                                                                          |
|     └─HashAgg_5              | 1.60    | cop[tikv] |               | group by:test.t.b, funcs:sum(case(or(like(test.t.g, "xxx%yy%", 92), like(test.t.g, "zzz%", 92)), cast(replace(test.t.c, ",", ""), decimal(10,0) BINARY), 0))->Column#11 |
|       └─TableFullScan_13     | 2.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                                                                          |
+------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

[LittleFall]

minimal reproduce:

drop table t;
create table t(a char(5));
alter table t set tiflash replica 1;
insert into t values ('-1'), ('-1');
SELECT /*+ READ_FROM_STORAGE(tikv[t]) */ sum(Cast(a AS DECIMAL)) FROM t;
+-------------------------+
| sum(Cast(a AS DECIMAL)) |
+-------------------------+
|                      -2 |
+-------------------------+
SELECT /*+ READ_FROM_STORAGE(tiflash[t]) */ sum(Cast(a AS DECIMAL)) FROM t;
+-------------------------+
| sum(Cast(a AS DECIMAL)) |
+-------------------------+
|                       2 |
+-------------------------+
explain SELECT /*+ READ_FROM_STORAGE(tiflash[t]) */ sum(Cast(a AS DECIMAL)) FROM t;
+------------------------------+---------+-------------------+---------------+------------------------------------------------+
| id                           | estRows | task              | access object | operator info                                  |
+------------------------------+---------+-------------------+---------------+------------------------------------------------+
| StreamAgg_21                 | 1.00    | root              |               | funcs:sum(Column#6)->Column#3                  |
| └─TableReader_22             | 1.00    | root              |               | data:StreamAgg_9                               |
|   └─StreamAgg_9              | 1.00    | batchCop[tiflash] |               | funcs:sum(Column#7)->Column#6                  |
|     └─Projection_25          | 2.00    | batchCop[tiflash] |               | cast(test.t.a, decimal(32,0) BINARY)->Column#7 |
|       └─TableFullScan_20     | 2.00    | batchCop[tiflash] | table:t       | keep order:false, stats:pseudo                 |
+------------------------------+---------+-------------------+---------------+------------------------------------------------+
5 rows in set (0.00 sec)

[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.

[LittleFall]

Hi here, this issue will be closed because it will be transferred to tiflash's repo.

update: we have found the root cause and solution of this bug, and will fix it in the next release. Thanks for the feedback!

[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.