cast string as real has different behaivor between tiflash and tikv/tidb.

[LittleFall]

create table t(a char(5));
alter table t set tiflash replica 1;
insert into t values ('0x01');

tiflash

set tidb_enforce_mpp=1;  -- let calculation push down to tiflash.
select cast(a as real) from t;
+-----------------+
| cast(a as real) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.01 sec)

tidb

set tidb_enforce_mpp=0;
select cast(a as real) from t;
+-----------------+
| cast(a as real) |
+-----------------+
|               0 |
+-----------------+
1 row in set, 1 warning (0.01 sec)
show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '0x01' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

tikv

mysql> select /*+ read_from_storage(tikv[t]) */ * from t where cast(a as real) = 0;
+------+
| a    |
+------+
| 0x01 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: '0x01' |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

version: v5.3.0

[LittleFall]

by the way, cast string as int doesn't have this issue.

set tidb_enforce_mpp=1;
select cast(a as unsigned) from t;
+---------------------+
| cast(a as unsigned) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.01 sec)