avg/sum of integer types reports overflow

[birdstorm]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

mysql> select avg(tp_bigint) from full_data_type_table;

2. What did you expect to see?

mysql> select avg(tp_bigint) from full_data_type_table;
+------------------------+
| avg(tp_bigint)         |
+------------------------+
| 74281968802033755.5509 |
+------------------------+
1 row in set (0.01 sec)

3. What did you see instead?

mysql> select avg(tp_bigint) from full_data_type_table;
ERROR 1105 (HY000): other error: unknown error Codec(Other(StringError("I64(5497181421909890696) + I64(4376283986062299391) overflow")))

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v1.0.6
Git Commit Hash: c17edb149375e320e62725453a26bcd5236a0f90
Git Branch: HEAD
UTC Build Time: 2018-01-15 05:47:42 |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

TiKV
Release Version:   1.1.0-dev
Git Commit Hash:   6a2a459f965c11404edae6c6a7d52985e268d0ee
Git Commit Branch: master
UTC Build Time:    2018-01-15 05:55:10
Rust Version:      1.22.0-nightly (fd4bef54a 2017-09-15)

Can be reproduced since TiKV v1.0.5 tag, haven't tested on other more versions except current master branch.

I believe this is related to TiKV's implement on avg and sum.

sum() should not report overflow since its result is converted into NEWDECIMAL.

Maybe I should create an issue there?

[winoros]

@XuHuaiyu @zz-jason PTAL

[zz-jason]

@birdstorm from the error message, it's possibly produced by tikv, can you provide us the following infos:

• result of explain select avg(tp_bigint) from full_data_type_table;
• tidb log
• tikv log

[birdstorm]

mysql> explain select avg(tp_bigint) from full_data_type_table;
+---------------+-----------+---------------+------+-----------------------------------------------------------------------+-------+
| id            | parents   | children      | task | operator info                                                         | count |
+---------------+-----------+---------------+------+-----------------------------------------------------------------------+-------+
| TableScan_5   | HashAgg_4 |               | cop  | table:full_data_type_table, range:(-inf,+inf), keep order:false       |  2005 |
| HashAgg_4     |           | TableScan_5   | cop  | type:complete, funcs:avg(tispark_test.full_data_type_table.tp_bigint) |     1 |
| TableReader_7 | HashAgg_6 |               | root | data:HashAgg_4                                                        |     1 |
| HashAgg_6     |           | TableReader_7 | root | type:final, funcs:avg(col_0, col_0)                                   |     1 |
+---------------+-----------+---------------+------+-----------------------------------------------------------------------+-------+
4 rows in set (0.00 sec)

TiDB log:

2018/01/15 15:41:52.009 metrics.go:355: [warning] [EXPENSIVE_QUERY] select avg(tp_bigint) from full_data_type_table
2018/01/15 15:41:52.010 coprocessor.go:506: [warning] coprocessor err: other error: unknown error Codec(Other(StringError("I64(5497181421909890696) + I64(4376283986062299391) overflow")))
2018/01/15 15:41:52.010 conn.go:428: [warning] [1] dispatch error:
id:1, addr:127.0.0.1:54684 status:2, collation:utf8_general_ci, user:root
"select avg(tp_bigint) from full_data_type_table"
/Users/birdstorm/go/src/github.com/pingcap/tidb/store/tikv/coprocessor.go:505: other error: unknown error Codec(Other(StringError("I64(5497181421909890696) + I64(4376283986062299391) overflow")))
/Users/birdstorm/go/src/github.com/pingcap/tidb/store/tikv/coprocessor.go:507:
/Users/birdstorm/go/src/github.com/pingcap/tidb/store/tikv/coprocessor.go:441:
/Users/birdstorm/go/src/github.com/pingcap/tidb/distsql/new_distsql.go:84:
/Users/birdstorm/go/src/github.com/pingcap/tidb/distsql/new_distsql.go:106:
/Users/birdstorm/go/src/github.com/pingcap/tidb/executor/new_distsql.go:97:
/Users/birdstorm/go/src/github.com/pingcap/tidb/executor/aggregate.go:128:
/Users/birdstorm/go/src/github.com/pingcap/tidb/executor/aggregate.go:70:
/Users/birdstorm/go/src/github.com/pingcap/tidb/executor/adapter.go:76:
/Users/birdstorm/go/src/github.com/pingcap/tidb/server/driver_tidb.go:297:
/Users/birdstorm/go/src/github.com/pingcap/tidb/server/conn.go:792:
/Users/birdstorm/go/src/github.com/pingcap/tidb/server/conn.go:757:

TiKV didn't output log in console.

Updated. @zz-jason

[birdstorm]

A simple SQL snippet to reproduce this issue

mysql> create table tp_bigint(tp_bigint bigint);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into tp_bigint VALUES (4355836469450447576),(122222),(122222),(20447516611607371),(5497181421909890696),(6387179353066726476),(-7260847692614697409),(-3477351678416769936),(5437962876475347781),(-1628190533622764116),(-2330452168930527122),(8259799199455697424),(-2902580959275580308),(971283248198403122),(-8807075840248874481),(8248914302266631926),(8399993308832803593),(4550604595377357530),(1929022876966447933),(-6729217870649426929),(-1833603574228514635),(4775295891604299783),(-3610941412239005474),(7709048667283253829),(-8132728854144268899),(6375004787186210691),(1087444924170509644),(-5286964075838479461),(9003378287025114833),(7252938492752085426),(8445122284020178753),(4061440243387565963),(-5955936700838274864),(3249762782338184216),(8025375129710453506),(-6462182048084492641),(-906234377751498157),(-4475711602926303475),(5567338844075493839),(-1655914522202431155),(-4511898209778166952),(-8915186185280840515),(-7946757876132865144),(9015679606705022486),(1825138943709402224),(2992842387394282852),(-6847227920888236382),(-7796637819654532384),(-4962698495629018549),(7693118460369214665),(4304969163348574795),(-1901079264528929390),(2550372539345453443),(-9095193610104963846),(-1726229544955957541),(-1565887615709157393),(-7144546948748831064),(-8995010522481221047),(3333113699420082301),(-1878006244639636809),(-2375914373688318325),(-8229374699652031355);
Query OK, 62 rows affected (0.02 sec)

mysql> select avg(tp_bigint) from tp_bigint;
ERROR 1105 (HY000): other error: unknown error Codec(Other(StringError("I64(5497181421909890696) + I64(4376283986062299391) overflow")))