[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Wrong ATAN/ATAN2 Function Result in TiKV #57542

Open
cindy-zhoutingting opened this issue Nov 20, 2024 · 4 comments
Open

Wrong ATAN/ATAN2 Function Result in TiKV #57542

cindy-zhoutingting opened this issue Nov 20, 2024 · 4 comments
Labels
affects-8.4 impact/wrong-result may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@cindy-zhoutingting
Copy link

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 cindy-zhoutingting added the type/bug The issue is confirmed as a bug. label Nov 20, 2024
@cindy-zhoutingting
Copy link
Author

/label affects-8.4

@yibin87
Copy link
Contributor
yibin87 commented Nov 25, 2024 •
edited
Loading

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
Copy link
Contributor
yibin87 commented Nov 25, 2024

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
Copy link
Contributor
yibin87 commented Nov 25, 2024

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affects-8.4 impact/wrong-result may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@jebter @yibin87 @cindy-zhoutingting