Wrong ATAN/ATAN2 Function Result in TiKV

[cindy-zhoutingting]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;
create table t0(`COL1` bigint(64) NOT NULL);
INSERT INTO t0 (COL1) VALUES 
    (314159265),     
    (-271828182),    
    (223372036),     
    (-223372036),    
    (720720720),
    (2718281828),
    (123456789), 
    (987654321), 
    (9876543210),    
    (3141592653),    
    (2718281828);    
    
ALTER TABLE t0 SET TIFLASH REPLICA 1;

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

I find that the tikv return empty result but tiflash can return correctly in ATAN/ATAN2.

3. What did you see instead (Required)

mysql> SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
    ->     COL1,
    ->     COT(COL1) AS cot_val,
    ->     ABS(COL1) AS abs_val,
    ->     CEIL(LOG10(ABS(COL1) + 1)) AS ceil_log10_val,
    ->     EXP(MOD(COL1, 5)) AS exp_mod_val,
    ->     SIGN(COL1) AS sign_val,
    ->     SIN(RADIANS(COL1)) AS sin_radians_val,
    ->     COS(RADIANS(COL1)) AS cos_radians_val,
    ->     TAN(RADIANS(COL1)) AS tan_radians_val,
    ->     DEGREES(ATAN(COL1)) AS degrees_atan_val,
    ->     POW(COL1, 3) AS pow_val,
    ->     SQRT(ABS(COL1)) AS sqrt_abs_val,
    ->     TRUNCATE(LOG(ABS(COL1) + 1), 3) AS truncated_log_val,
    ->     ROUND(PI() * COL1, -2) AS pi_col1_rounded,
    ->     CONV(ABS(COL1), 10, 16) AS hex_val
    -> FROM
    ->     t0
    -> WHERE
    ->      ATAN(COL1) = ATAN(-223372036) or ATAN2(col1, 1) = ATAN2(2718281828, 1);
+------------+----------------------+------------+----------------+---------------------+----------+---------------------+--------------------+---------------------+--------------------+------------------------+--------------------+-------------------+-----------------+----------+
| COL1       | cot_val              | abs_val    | ceil_log10_val | exp_mod_val         | sign_val | sin_radians_val     | cos_radians_val    | tan_radians_val     | degrees_atan_val   | pow_val                | sqrt_abs_val       | truncated_log_val | pi_col1_rounded | hex_val  |
+------------+----------------------+------------+----------------+---------------------+----------+---------------------+--------------------+---------------------+--------------------+------------------------+--------------------+-------------------+-----------------+----------+
| -223372036 | -0.48677612988309826 |  223372036 |              9 | 0.36787944117144233 |       -1 |  0.6946583706279824 | 0.7193398001754642 |  0.9656887752610639 | -89.99999974349618 | -1.1145162583041158e25 | 14945.636018584153 |            19.224 |      -701743900 | D506304  |
| 2718281828 |    0.795254664583062 | 2718281828 |             10 |  20.085536923187668 |        1 | -0.7880107526837651 | 0.6156614765069892 | -1.2799416282379972 |  89.99999997892206 |  2.0085536913011936e28 |   52137.1444173921 |            21.723 |      8539734200 | A205B064 |
| 2718281828 |    0.795254664583062 | 2718281828 |             10 |  20.085536923187668 |        1 | -0.7880107526837651 | 0.6156614765069892 | -1.2799416282379972 |  89.99999997892206 |  2.0085536913011936e28 |   52137.1444173921 |            21.723 |      8539734200 | A205B064 |
+------------+----------------------+------------+----------------+---------------------+----------+---------------------+--------------------+---------------------+--------------------+------------------------+--------------------+-------------------+-----------------+----------+
3 rows in set (0.04 sec)

mysql>
mysql> SELECT /*+ READ_FROM_STORAGE(tikv[t0]) */
    ->     COL1,
    ->     COT(COL1) AS cot_val,
    ->     ABS(COL1) AS abs_val,
    ->     CEIL(LOG10(ABS(COL1) + 1)) AS ceil_log10_val,
    ->     EXP(MOD(COL1, 5)) AS exp_mod_val,
    ->     SIGN(COL1) AS sign_val,
    ->     SIN(RADIANS(COL1)) AS sin_radians_val,
    ->     COS(RADIANS(COL1)) AS cos_radians_val,
    ->     TAN(RADIANS(COL1)) AS tan_radians_val,
    ->     DEGREES(ATAN(COL1)) AS degrees_atan_val,
    ->     POW(COL1, 3) AS pow_val,
    ->     SQRT(ABS(COL1)) AS sqrt_abs_val,
    ->     TRUNCATE(LOG(ABS(COL1) + 1), 3) AS truncated_log_val,
    ->     ROUND(PI() * COL1, -2) AS pi_col1_rounded,
    ->     CONV(ABS(COL1), 10, 16) AS hex_val
    -> FROM
    ->     t0
    -> WHERE
    ->      ATAN(COL1) = ATAN(-223372036) or ATAN2(col1, 1) = ATAN2(2718281828, 1);
Empty set (0.04 sec)

4. What is your TiDB version? (Required)

TiDB v8.4.0

[cindy-zhoutingting]

/label affects-8.4

[yibin87]

Simplified the case:

mysql> set session tidb_isolation_read_engines='tiflash';
Query OK, 0 rows affected (0.00 sec)

mysql> select col1, atan(col1), atan2(col1, 1) from t0 where ATAN(COL1) = ATAN(-223372036);
+------------+---------------------+---------------------+
| col1       | atan(col1)          | atan2(col1, 1)      |
+------------+---------------------+---------------------+
| -223372036 | -1.5707963223180603 | -1.5707963223180603 |
+------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> set session tidb_isolation_read_engines='tikv';
Query OK, 0 rows affected (0.00 sec)

mysql> select col1, atan(col1), atan2(col1, 1) from t0 where ATAN(COL1) = ATAN(-223372036);
Empty set (0.01 sec)

[yibin87]

Further, tidb does push down atan functions to tiflash side:

mysql> explain select col1, atan(col1), atan2(col1, 1) from t0 where ATAN(COL1) = ATAN(-223372036);
+-----------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows | task         | access object | operator info                                                                                                          |
+-----------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------+
| Projection_4                | 8.80    | root         |               | test.t0.col1, atan(cast(test.t0.col1, double BINARY))->Column#3, atan2(cast(test.t0.col1, double BINARY), 1)->Column#4 |
| └─Selection_8               | 8.80    | root         |               | eq(atan(cast(test.t0.col1, double BINARY)), -1.5707963223180603)                                                       |
|   └─TableReader_7           | 11.00   | root         |               | MppVersion: 2, data:ExchangeSender_6                                                                                   |
|     └─ExchangeSender_6      | 11.00   | mpp[tiflash] |               | ExchangeType: PassThrough                                                                                              |
|       └─TableFullScan_5     | 11.00   | mpp[tiflash] | table:t0      | keep order:false, stats:pseudo                                                                                         |
+-----------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------+
5 rows in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function 'atan'(signature: Atan1Arg, return type: double) is not supported to push down to tiflash noww.   |
| Warning | 1105 | Scalar function 'atan2'(signature: Atan2Args, return type: double) is not supported to push down to tiflash nnow. |
| Warning | 1105 | Scalar function 'atan'(signature: Atan1Arg, return type: double) is not supported to push down to tiflash noww.   |
+---------+------+-------------------------------------------------------------------------------------------------------------------+

[yibin87]

While for tikv, it does push down:

mysql> explain select col1, atan(col1), atan2(col1, 1) from t0 where ATAN(COL1) = ATAN(-223372036);
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| id                        | estRows | task      | access object | operator info                                                                                                          |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| Projection_4              | 8.80    | root      |               | test.t0.col1, atan(cast(test.t0.col1, double BINARY))->Column#3, atan2(cast(test.t0.col1, double BINARY), 1)->Column#4 |
| └─TableReader_7           | 8.80    | root      |               | data:Selection_6                                                                                                       |
|   └─Selection_6           | 8.80    | cop[tikv] |               | eq(atan(cast(test.t0.col1, double BINARY)), -1.5707963223180603)                                                       |
|     └─TableFullScan_5     | 11.00   | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                                                                         |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)