wrong result when using double, cast, union, unaryminus and 0.0

[rebelice]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Please use tiup to deploy TiDB cluster.

drop table if exists test_eq1;
drop table if exists test_eq2;
create table test_eq1(
	change_stock_qty double(16,4)
);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (1);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (6);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (2);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (1);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (3);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (5);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (1);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (4);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (1);
INSERT INTO `test_eq1`(`change_stock_qty`) VALUES (3);


create table test_eq2(
	change_stock_qty double(16,4)
);

INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (3);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (802);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (2);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (1);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (3);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (4);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (2);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (2);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (4);
INSERT INTO `test_eq2`(`change_stock_qty`) VALUES (1);



select t1.change_stock_qty, t1.bill_type
from (
   select
     9 bill_type,
     -tt1.change_stock_qty change_stock_qty
   from test_eq1 tt1
   union
   select 10 bill_type, tt1.change_stock_qty
   from test_eq2 tt1
) t1
where t1.bill_type = 9
  and t1.change_stock_qty <> 0.0;

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

+------------------+-----------+
| change_stock_qty | bill_type |
+------------------+-----------+
|               -1 |         9 |
|               -3 |         9 |
|               -5 |         9 |
|               -6 |         9 |
|               -4 |         9 |
|               -2 |         9 |
+------------------+-----------+

3. What did you see instead (Required)

Empty Set.

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.1.0-alpha-88-gc8c0dd0bb
Edition: Community
Git Commit Hash: c8c0dd0bb94d13f526547dae1448b6ee4f877fb0
Git Branch: master
UTC Build Time: 2021-06-02 03:35:23
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.038 sec)

[lzmhhh123]

It's a bug of the coprocessor.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[jebter]

There is also this problem on release-5.1