Select stmt with union that returns the result with DOUBLE is inconsistent with MySQL

[zimulala]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table tbl_16, tbl_25;
create table tbl_16 ( col_124 decimal(1,1) not null , col_125 decimal(38,22) default 73175 , col_126 double , col_127 double not null , col_128 double , col_129 decimal(54,20) not null , col_130 decimal(25,15) default 52.193 not null , col_131 decimal);
replace into tbl_16 (col_124,col_126,col_127,col_128,col_129,col_131,col_130) values ( 0.3,4126.864354635885,1158.2294613912238,3182.465483903795,92546.7632,0,93947.512 ) , ( 0,5035.146514532988,772.8675559577869,3978.5818883527813,94494.13,652,0.8 );

create table tbl_25 ( col_173 double , col_174 decimal(40,6) default 1658.116 , col_179 double not null , col_180 decimal);

( select   col_174,col_180,col_173 from tbl_25 where tbl_25.col_173 in ( 6916.701790663465 , 8087.845924790919 , 2717.35656335315 , 868.1683958776397 , 1147.1378193357375 ) )
    union ( select   col_131,col_129,col_124 from tbl_16 where tbl_16.col_130 in ( 9 , 146075 ) or tbl_16.col_126 <= 5152.4735222659265 );

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

+------------+----------------------------+---------+
| col_174    | col_180                    | col_173 |
+------------+----------------------------+---------+
|   0.000000 | 92546.76320000000000000000 |     0.3 |
| 652.000000 | 94494.13000000000000000000 |       0 |
+------------+----------------------------+---------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

+------------+----------------------------+---------+
| col_174    | col_180                    | col_173 |
+------------+----------------------------+---------+
|   0.000000 | 92546.76320000000000000000 |     0.3 |
| 652.000000 | 94494.13000000000000000000 |     0.0 |
+------------+----------------------------+---------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-2957-g4000975ad
Edition: Community
Git Commit Hash: 4000975ad1785fbc4c0e5175407e8486c0cef763
Git Branch: master

[zimulala]

drop table tbl_13, tbl_1;
create table tbl_13 ( col_76 decimal(54,6) , col_77 double , col_78 decimal(45,26) default 0 not null , col_79 double not null , col_80 double default 6425.228703024704 not null , primary key idx_22 ( col_76 ) ) collate utf8_general_ci;
insert  into tbl_13 set col_76 = 3.7, col_77 = 1340.9682183300451, col_79 = 7350.051371421769;

create table tbl_1 ( col_5 double not null , col_6 double , col_7 decimal(38,21) default 93 not null , col_8 decimal(4,3) default 0.7 , col_9 decimal(54,26) , primary key idx_1 ( col_5,col_6,col_7,col_8,col_9 ) , key idx_2 ( col_5,col_6,col_7,col_8 ) ) collate utf8mb4_unicode_ci;
alter table tbl_1 add column col_49 float default 5110.434865754793;
alter table tbl_1 change column col_49 col_151 int first;


( select   col_79,col_80,col_76 from tbl_13 where tbl_13.col_76 >= 0.8  for update ) union all
    ( select   col_8,col_6,col_151 from tbl_1 where IsNull( tbl_1.col_5 )
    or not( tbl_1.col_8 in ( 0 , 0 , 6.92 , 0 ) ) or not( IsNull( tbl_1.col_7 ) ) or tbl_1.col_151 not in ( -1446745701 , -407150137 , 8.124 , 0.514 ) or not( tbl_1.col_8 = 4.849 )  for update );

MySQL

+-------------------+-------------------+----------+
| col_79            | col_80            | col_76   |
+-------------------+-------------------+----------+
| 7350.051371421769 | 6425.228703024704 | 3.700000 |
+-------------------+-------------------+----------+
1 row in set (0.01 sec)

TiDB

+----------+-------------------+----------+
| col_79   | col_80            | col_76   |
+----------+-------------------+----------+
| 7350.051 | 6425.228703024704 | 3.700000 |
+----------+-------------------+----------+
1 row in set (0.00 sec)