sync-diff-inspector treats 0 of double columns as null in fix.sql

[kennytm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

1. Prepare data

drop schema if exists up;
drop schema if exists down;
create schema up;
create schema down;

drop user if exists up;
drop user if exists down;
create user up;
create user down;
grant select on up.* to up;
grant select on down.* to down;

create table up.test (id bigint primary key, d double not null, a int not null);
create table down.test like up.test;
insert into up.test values (1, 0, 4);
insert into down.test values (1, 2, 3);

2. Set diff config

export-fix-sql = true

[data-sources.up]
host = "127.0.0.1"
port = 4000
user = "up"
password = ""
route-rules = ["rename"]

[data-sources.down]
host = "127.0.0.1"
port = 4000
user = "down"
password = ""

[routes]
[routes.rename]
schema-pattern = "up"
table-pattern = "test"
target-schema = "down"
target-table = "test"

[task]
output-dir = "./output"
source-instances = ["up"]
target-instance = "down"
target-check-tables = ["down.test"]

3. Run sync-diff. It should say "The data of `down`.`test` is not equal".
4. Check the fix-sql content cat output/fix-on-down/down:test:0:0-0:0.sql

2. What did you expect to see?

The column d in the fix-sql is 0.

3. What did you see instead?

The column d in the fix-sql is NULL:

-- table: down.test
-- range in sequence: Full
/*
  DIFF COLUMNS ╏ `D`  ╏ `A`  
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╋╍╍╍╍╍╍
  source data  ╏ NULL ╏ 4    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╋╍╍╍╍╍╍
  target data  ╏ 2    ╏ 3    
╍╍╍╍╍╍╍╍╍╍╍╍╍╍╍╋╍╍╍╍╍╍╋╍╍╍╍╍╍
*/
REPLACE INTO `down`.`test`(`id`,`d`,`a`) VALUES (1,NULL,4);

[kennytm]

This is caused by the select query being used:

SELECT /*!40001 SQL_NO_CACHE */ 
  `id`, 
  round(`d`, 14-floor(log10(abs(`d`)))) as `d`,    -- <-----
  `a` 
FROM `up`.`test` 
WHERE ((TRUE) AND (TRUE)) 
ORDER BY `id`;

and, well, log10(0) is NULL.

---

I think changing the expression to

round(`d`, 14-floor(log10(abs(`d`) + 5e-324)))

is enough to avoid the NULL (use 1e-45 for single precision).