cast real as string is not compatible with tidb/mysql

[SeaRise]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table test.t(a float, b double);
insert into test.t values (3.4028234e38, 1.7976931348623157e308);
insert into test.t values (1.1754944e-38, 2.2250738585072014e-308);

// tiflash
set tidb_enforce_mpp=1; set tidb_isolation_read_engines='tiflash';
select cast(a as char), cast(b as char) from t;

// tidb
set tidb_enforce_mpp=0; set tidb_isolation_read_engines='tikv';
select cast(a as char), cast(b as char) from t;

// mysql
// execute in mysql
select cast(a as char), cast(b as char) from t;

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

// tidb
+-----------------+-------------------------+
| cast(a as char) | cast(b as char)         |
+-----------------+-------------------------+
| 3.40282e38      | 1.7976931348623157e308  |
| 1.17549e-38     | 2.2250738585072014e-308 |
+-----------------+-------------------------+

// tiflash
+-----------------+-------------------------+
| cast(a as char) | cast(b as char)         |
+-----------------+-------------------------+
| 3.40282e38      | 1.7976931348623157e308  |
| 1.17549e-38     | 2.2250738585072014e-308 |
+-----------------+-------------------------+

// mysql
+-----------------+-------------------------+
| cast(a as char) | cast(b as char)         |
+-----------------+-------------------------+
| 3.40282e38      | 1.7976931348623157e308  |
| 1.17549e-38     | 2.2250738585072014e-308 |
+-----------------+-------------------------+

3. What did you see instead (Required)

// tidb
+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cast(a as char)                                 | cast(b as char)                                                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 340282350000000000000000000000000000000         | 179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                  |
| 0.000000000000000000000000000000000000011754944 | 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000022250738585072014 |
+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

// tiflash
+-----------------+-------------------------+
| cast(a as char) | cast(b as char)         |
+-----------------+-------------------------+
| 3.4028235e38    | 1.7976931348623157e308  |
| 1.1754944e-38   | 2.2250738585072014e-308 |
+-----------------+-------------------------+

// mysql
+-----------------+-------------------------+
| cast(a as char) | cast(b as char)         |
+-----------------+-------------------------+
| 3.40282e38      | 1.7976931348623157e308  |
| 1.17549e-38     | 2.2250738585072014e-308 |
+-----------------+-------------------------+

4. What is your TiFlash version? (Required)

master

[fuzhe1989]

seems it's TiDB that is not compatible with TiFlash/MySQL, right?

[SeaRise]

seems it's TiDB that is not compatible with TiFlash/MySQL, right?

Not really. For Float32, all three are different

[fuzhe1989]

@SeaRise sorry for my mistake. The float precision in TiDB is 8 while in MySQL it is 6, so for float32 I think it's as expected.