sync-diff-inspector: replace `round` with some other function can be pushed down

[joechenrh]

Feature Request

Is your feature request related to a problem? Please describe:

Currently, double/float type will be wrapped with round, which can't be pushed down to TiKV.

tidb-tools/sync_diff_inspector/utils/utils.go

Lines 152 to 158 in c7d518d

	// When col value is 0, the result is NULL.
	// But we can use ISNULL to distinguish between null and 0.
	if col.FieldType.GetType() == mysql.TypeFloat {
	name = fmt.Sprintf("round(%s, 5-floor(log10(abs(%s)))) as %s", name, name, name)
	} else if col.FieldType.GetType() == mysql.TypeDouble {
	name = fmt.Sprintf("round(%s, 14-floor(log10(abs(%s)))) as %s", name, name, name)
	}

https://github.com/pingcap/tidb/blob/07f4eda04057923b0588eb8d62be45962fa7658b/pkg/expression/infer_pushdown.go#L160

Describe the feature you'd like:

Replace round with other function, for example, cast( as decimal) seems to be feasible:

create table t(c1 double);
insert into t values (11111130.888600154), (11111130.888600156);

mysql> select c1, round(c1, 14 - floor(log10(abs(c1)))), cast(c1 as decimal(30, 15)) from t;
+--------------------+---------------------------------------+-----------------------------+
| c1                 | round(c1, 14 - floor(log10(abs(c1)))) | cast(c1 as decimal(30, 15)) |
+--------------------+---------------------------------------+-----------------------------+
| 11111130.888600154 |                      11111130.8886002 |    11111130.888600154000000 |
| 11111130.888600156 |                      11111130.8886002 |    11111130.888600156000000 |
+--------------------+---------------------------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> explain select bit_xor(CONCAT(cast(c1 as decimal(30, 15)))) from t;                                                                                                                                   
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------+                    
| id                         | estRows | task      | access object | operator info                                                                                                      |                    
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------+                    
| StreamAgg_16               | 1.00    | root      |               | funcs:bit_xor(Column#5)->Column#3                                                                                  |                    
| └─TableReader_17           | 1.00    | root      |               | data:StreamAgg_8                                                                                                   |                    
|   └─StreamAgg_8            | 1.00    | cop[tikv] |               | funcs:bit_xor(cast(concat(cast(cast(test.t.c1, decimal(30,15) BINARY), var_string(33))), bigint BINARY))->Column#5 |                    
|     └─TableFullScan_15     | 2.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                     |                    
+----------------------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------+                    
4 rows in set (0.01 sec) 

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[joechenrh]

/component sync-diff-inspector

[joechenrh]

@kennytm Do you have some suggestions? Thanks

[kennytm]

@joechenrh The round was used to ensure the same floating-point number produces the same result across multiple implementations (MySQL, MariaDB, TiDB). Does the CAST solution behave the same everywhere? If yes LGTM.