During push-down insert, the precision of numeric type fields is lost.

[IZ-ONE]

First ，I create table like this in mysql

CREATE TABLEvalid_numeric( primary_idvarchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci , tag_nonumeric(32,0), PRIMARY KEY (primary_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Next, I created a corresponding wrapper table

CREATE FOREIGN TABLE "public"."z_mysql_fdw" ( "primary_id" varchar(64), "tag_no" numeric(32, 0) ) SERVER "server_mysql_shared" OPTIONS ("dbname" 'csshare', "table_name" 'valid_numeric');

Push down a piece of data insert into z_mysql_fdw VALUES ('xyz', 1694671451644637184);

But when I query the data, the value of the tag_no field is not the value when saved.

The correct value should be 1694671451644637184 instead of 1694671451644637200 The last three significant digits changed from 184 to 200 （I execute the insert statement directly in mysql and there is no problem）

Postgre Version : PostgreSQL 14.8 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit Mysql Version: 8.2.0

[surajkharage19]

Hi @IZ-ONE,

Thanks for reporting the issue.

As I can see from the code, when we have a numeric value, we convert that to float8 and insert the same into MySQL side.

For e.g: If we convert your given value to float8 then it returns :

edb@115587=#select '1694671451644637184'::float8;
         float8         
------------------------
 1.6946714516446372e+18
(1 row)

and when this value gets inserted into MySQL, it gets rounded off as you mentioned. for e.g:

edb@115587=#select '1.6946714516446372e+18'::numeric;
       numeric       
---------------------
 1694671451644637200
(1 row)

We will check whether we can avoid converting numeric to float8 which should insert the original value, but Im not sure whether that is feasible or not.