[MySQL] Unsigned datatypes, Double precision cannot be migrated

[chetank-yb]

No description provided.

[chetank-yb]

data which could not be migrated successfully

1. Double precision - COPY "floating_point_types" FROM file "/home/ubuntu/export-dir/metainfo/data/floating_point_types.0.5.5.439.P": ERROR: "-1.79769313486232e+308" is out of range for type double precision (SQLSTATE 22003)

MySQL queries
create table if not exists floating_point_types(float_type float,double_type double,double_precision_type DOUBLE PRECISION,real_type REAL);
insert into floating_point_types values (-3.402823466E+38,-1.7976931348623157E+308,-1.7976931348623157E+308,-1.7976931348623157E+308);

---

2. Small Int - COPY "int_types" FROM file "/home/ubuntu/export-dir/metainfo/data/int_types.0.4.4.656.P": ERROR: value "65535" is out of range for type smallint (SQLSTATE 22003)

MySQL queries
create table if not exists int_types (tint_s tinyint signed, tint_u tinyint unsigned zerofill,
sint_s smallint signed, sint_u smallint unsigned zerofill,
mint_s mediumint signed, mint_u mediumint unsigned zerofill,
iINT_s int signed, iINT_u int unsigned zerofill,
iINTEGER_s INTEGER signed, iINTEGER_u INTEGER unsigned zerofill,
bint_s bigint signed,bint_u bigint unsigned zerofill);
insert into int_types values (127,255,32767,65535,8388607,16777215,2147483647,4294967295,2147483647,4294967295,9223372036854775807,18446744073709551615);

NOTE : Migration of data for any field which is of type unsigned is not possible as of now

---

[rahulb-yb]

1. Float-based data types are always approximations of the values they represent- any values exported near MAX or MIN values may be out of range due to this nature of the datatype.
2. Unsigned datatypes are not native to YB, they are usually enforced by check constraints if the user so wishes to include them.

Any other suggested solutions may be explored as per the manual review doc: https://docs.google.com/document/d/1ZzPGdn2tSHdbqzQ4b-WdXFRedLaiQqun5osV1vAG-CY