# Copyright 2023 PingCAP, Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

mysql> drop table if exists test.int8
mysql> create table test.int8 (id int, a tinyint)
mysql> alter table test.int8 set tiflash replica 1
mysql> insert into test.int8 values (1, 0), (2, 1), (3, -1), (4, 127), (5, -128), (6, null)

mysql> drop table if exists test.uint8
mysql> create table test.uint8 (id int, a tinyint unsigned)
mysql> alter table test.uint8 set tiflash replica 1
mysql> insert into test.uint8 values (1, 0), (2, 1), (3, 255), (4, null)

mysql> drop table if exists test.int64
mysql> create table test.int64 (id int, a bigint)
mysql> alter table test.int64 set tiflash replica 1
mysql> insert into test.int64 values (1, 0), (2, 1), (3, -1), (4, 9223372036854775807), (5, -9223372036854775808), (6, null)

mysql> drop table if exists test.uint64
mysql> create table test.uint64 (id int, a bigint unsigned)
mysql> alter table test.uint64 set tiflash replica 1
mysql> insert into test.uint64 values (1, 0), (2, 1), (3, 18446744073709551615), (4, null)

mysql> drop table if exists test.float32
mysql> create table test.float32 (id int, a float)
mysql> alter table test.float32 set tiflash replica 1
mysql> insert into test.float32 values (1, -5.5), (2, -4.5), (3, -3.5), (4, -2.5), (5, -1.5), (6, -0.6), (7, -0.5), (8, -0.4), (9, 0), (10, 0.4), (11, 0.5), (12, 0.6), (13, 1.5), (14, 2.5), (15, 3.5), (16, 4.5), (17, 5.5), (18, 1e30), (19, -1e30), (20, null)

mysql> drop table if exists test.float64
mysql> create table test.float64 (id int, a double)
mysql> alter table test.float64 set tiflash replica 1
mysql> insert into test.float64 values (1, -5.5), (2, -4.5), (3, -3.5), (4, -2.5), (5, -1.5), (6, -0.6), (7, -0.5), (8, -0.4), (9, 0), (10, 0.4), (11, 0.5), (12, 0.6), (13, 1.5), (14, 2.5), (15, 3.5), (16, 4.5), (17, 5.5), (18, 1e100), (19, -1e100), (20, null)

mysql> drop table if exists test.decimal32_1
mysql> create table test.decimal32_1 (id int, a decimal(9, 0))
mysql> alter table test.decimal32_1 set tiflash replica 1
mysql> insert into test.decimal32_1 values (1, 0), (2, 1), (3, -1), (4, 999999999), (5, -999999999), (6, null)

mysql> drop table if exists test.decimal32_2
mysql> create table test.decimal32_2 (id int, a decimal(9, 1))
mysql> alter table test.decimal32_2 set tiflash replica 1
mysql> insert into test.decimal32_2 values (1, -5.5), (2, -4.5), (3, -3.5), (4, -2.5), (5, -1.5), (6, -0.6), (7, -0.5), (8, -0.4), (9, 0), (10, 0.4), (11, 0.5), (12, 0.6), (13, 1.5), (14, 2.5), (15, 3.5), (16, 4.5), (17, 5.5), (18, 99999999.9), (19, -99999999.9), (20, null)

mysql> drop table if exists test.decimal32_3
mysql> create table test.decimal32_3 (id int, a decimal(9, 9))
mysql> alter table test.decimal32_3 set tiflash replica 1
mysql> insert into test.decimal32_3 values (1, 0), (2, -0.000000001), (3, 0.000000001), (4, 0.5), (5, -0.5), (6, 0.999999999), (7, -0.999999999), (8, null)

mysql> drop table if exists test.decimal256_1
mysql> create table test.decimal256_1 (id int, a decimal(65, 0))
mysql> alter table test.decimal256_1 set tiflash replica 1
mysql> insert into test.decimal256_1 values (1, 0), (2, 1), (3, -1), (4, 99999999999999999999999999999999999999999999999999999999999999999), (5, -99999999999999999999999999999999999999999999999999999999999999999), (6, null)

mysql> drop table if exists test.decimal256_2
mysql> create table test.decimal256_2 (id int, a decimal(65, 1))
mysql> alter table test.decimal256_2 set tiflash replica 1
mysql> insert into test.decimal256_2 values (1, -5.5), (2, -4.5), (3, -3.5), (4, -2.5), (5, -1.5), (6, -0.6), (7, -0.5), (8, -0.4), (9, 0), (10, 0.4), (11, 0.5), (12, 0.6), (13, 1.5), (14, 2.5), (15, 3.5), (16, 4.5), (17, 5.5), (18, 9999999999999999999999999999999999999999999999999999999999999999.9), (19, -9999999999999999999999999999999999999999999999999999999999999999.9), (20, null)

mysql> drop table if exists test.decimal256_3
mysql> create table test.decimal256_3 (id int, a decimal(40, 10))
mysql> alter table test.decimal256_3 set tiflash replica 1
mysql> insert into test.decimal256_3 values (1, -5.5), (2, -4.5), (3, -3.5), (4, -2.5), (5, -1.5), (6, -0.6), (7, -0.5), (8, -0.4), (9, 0), (10, 0.4), (11, 0.5), (12, 0.6), (13, 1.5), (14, 2.5), (15, 3.5), (16, 4.5), (17, 5.5), (18, 9999999999.999999999999999999999999999999), (19, -9999999999.999999999999999999999999999999), (20, 1.000000000000000000000000000001), (21, -1.000000000000000000000000000001), (22, null)

mysql> drop table if exists test.decimal128
mysql> create table test.decimal128 (id int, a decimal(30, 30))
mysql> alter table test.decimal128 set tiflash replica 1
mysql> insert into test.decimal128 values (1, 0), (2, -0.000000000000000000000000000001), (3, 0.000000000000000000000000000001), (4, 0.5), (5, -0.5), (6, 0.999999999999999999999999999999), (7, -0.999999999999999999999999999999), (8, null)

func> wait_table test int8 uint8 int64 uint64 float32 float64 decimal32_1 decimal32_2 decimal32_3 decimal256_1 decimal256_2 decimal256_3 decimal128

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.int8 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |             0 |
|    2 |             1 |
|    3 |            -1 |
|    4 |           127 |
|    5 |          -128 |
|    6 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.uint8 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |             0 |
|    2 |             1 |
|    3 |           255 |
|    4 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.int64 group by id order by id
+------+----------------------+
| id   | sum(round(a))        |
+------+----------------------+
|    1 |                    0 |
|    2 |                    1 |
|    3 |                   -1 |
|    4 |  9223372036854775807 |
|    5 | -9223372036854775808 |
|    6 |                 NULL |
+------+----------------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.uint64 group by id order by id
+------+----------------------+
| id   | sum(round(a))        |
+------+----------------------+
|    1 |                    0 |
|    2 |                    1 |
|    3 | 18446744073709551615 |
|    4 |                 NULL |
+------+----------------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.float32 group by id order by id
+------+------------------------+
| id   | sum(round(a))          |
+------+------------------------+
|    1 |                     -6 |
|    2 |                     -4 |
|    3 |                     -4 |
|    4 |                     -2 |
|    5 |                     -2 |
|    6 |                     -1 |
|    7 |                      0 |
|    8 |                      0 |
|    9 |                      0 |
|   10 |                      0 |
|   11 |                      0 |
|   12 |                      1 |
|   13 |                      2 |
|   14 |                      2 |
|   15 |                      4 |
|   16 |                      4 |
|   17 |                      6 |
|   18 |  1.0000000150474662e30 |
|   19 | -1.0000000150474662e30 |
|   20 |                   NULL |
+------+------------------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.float64 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |            -6 |
|    2 |            -4 |
|    3 |            -4 |
|    4 |            -2 |
|    5 |            -2 |
|    6 |            -1 |
|    7 |             0 |
|    8 |             0 |
|    9 |             0 |
|   10 |             0 |
|   11 |             0 |
|   12 |             1 |
|   13 |             2 |
|   14 |             2 |
|   15 |             4 |
|   16 |             4 |
|   17 |             6 |
|   18 |         1e100 |
|   19 |        -1e100 |
|   20 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal32_1 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |             0 |
|    2 |             1 |
|    3 |            -1 |
|    4 |     999999999 |
|    5 |    -999999999 |
|    6 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal32_2 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |            -6 |
|    2 |            -5 |
|    3 |            -4 |
|    4 |            -3 |
|    5 |            -2 |
|    6 |            -1 |
|    7 |            -1 |
|    8 |             0 |
|    9 |             0 |
|   10 |             0 |
|   11 |             1 |
|   12 |             1 |
|   13 |             2 |
|   14 |             3 |
|   15 |             4 |
|   16 |             5 |
|   17 |             6 |
|   18 |     100000000 |
|   19 |    -100000000 |
|   20 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal32_3 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |             0 |
|    2 |             0 |
|    3 |             0 |
|    4 |             1 |
|    5 |            -1 |
|    6 |             1 |
|    7 |            -1 |
|    8 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal256_1 group by id order by id
+------+--------------------------------------------------------------------+
| id   | sum(round(a))                                                      |
+------+--------------------------------------------------------------------+
|    1 |                                                                  0 |
|    2 |                                                                  1 |
|    3 |                                                                 -1 |
|    4 |  99999999999999999999999999999999999999999999999999999999999999999 |
|    5 | -99999999999999999999999999999999999999999999999999999999999999999 |
|    6 |                                                               NULL |
+------+--------------------------------------------------------------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal256_2 group by id order by id
+------+--------------------------------------------------------------------+
| id   | sum(round(a))                                                      |
+------+--------------------------------------------------------------------+
|    1 |                                                                 -6 |
|    2 |                                                                 -5 |
|    3 |                                                                 -4 |
|    4 |                                                                 -3 |
|    5 |                                                                 -2 |
|    6 |                                                                 -1 |
|    7 |                                                                 -1 |
|    8 |                                                                  0 |
|    9 |                                                                  0 |
|   10 |                                                                  0 |
|   11 |                                                                  1 |
|   12 |                                                                  1 |
|   13 |                                                                  2 |
|   14 |                                                                  3 |
|   15 |                                                                  4 |
|   16 |                                                                  5 |
|   17 |                                                                  6 |
|   18 |  10000000000000000000000000000000000000000000000000000000000000000 |
|   19 | -10000000000000000000000000000000000000000000000000000000000000000 |
|   20 |                                                               NULL |
+------+--------------------------------------------------------------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal256_3 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |            -6 |
|    2 |            -5 |
|    3 |            -4 |
|    4 |            -3 |
|    5 |            -2 |
|    6 |            -1 |
|    7 |            -1 |
|    8 |             0 |
|    9 |             0 |
|   10 |             0 |
|   11 |             1 |
|   12 |             1 |
|   13 |             2 |
|   14 |             3 |
|   15 |             4 |
|   16 |             5 |
|   17 |             6 |
|   18 |   10000000000 |
|   19 |  -10000000000 |
|   20 |             1 |
|   21 |            -1 |
|   22 |          NULL |
+------+---------------+

mysql> set tidb_enforce_mpp=1; select id, sum(round(a)) from test.decimal128 group by id order by id
+------+---------------+
| id   | sum(round(a)) |
+------+---------------+
|    1 |             0 |
|    2 |             0 |
|    3 |             0 |
|    4 |             1 |
|    5 |            -1 |
|    6 |             1 |
|    7 |            -1 |
|    8 |          NULL |
+------+---------------+