Materialize/test/mysql-cdc/types-double.td

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

$ set-sql-timeout duration=1s



#
# Test the floating-point types
#

> CREATE SECRET mysqlpass AS '${arg.mysql-root-password}'
> CREATE CONNECTION mysql_conn TO MYSQL (
    HOST mysql,
    USER root,
    PASSWORD SECRET mysqlpass
  )

$ mysql-connect name=mysql url=mysql://root@mysql password=${arg.mysql-root-password}

$ mysql-execute name=mysql
DROP DATABASE IF EXISTS public;
CREATE DATABASE public;
USE public;

# disable strict mode
SET SESSION sql_mode=''

CREATE TABLE t1 (f1 FLOAT, f2 DOUBLE);

INSERT INTO t1 VALUES (NULL, NULL);
INSERT INTO t1 VALUES ('-Inf', '-Inf');
INSERT INTO t1 VALUES ('NaN', 'NaN');
INSERT INTO t1 VALUES ('+Inf', '+Inf');
INSERT INTO t1 VALUES ('0.000000000000000000000000000001', '0.000000000000000000000000000001');
INSERT INTO t1 VALUES ('-11111111', '-11111111');
INSERT INTO t1 VALUES ('-111111111', '-111111111');
INSERT INTO t1 VALUES ('-11111111111', '-11111111111');
INSERT INTO t1 VALUES ('-1111111111111', '-1111111111111');
INSERT INTO t1 VALUES ('-111111111111111', '-111111111111111');
INSERT INTO t1 VALUES ('-11111111111111111', '-11111111111111111');
INSERT INTO t1 VALUES ('-1111111111111111111', '-1111111111111111111');
INSERT INTO t1 VALUES ('-111111111111111111111', '-111111111111111111111');
INSERT INTO t1 VALUES ('-1111111111111111111111111', '-1111111111111111111111111');
INSERT INTO t1 VALUES ('-11111111111111111111111111111', '-11111111111111111111111111111');
INSERT INTO t1 VALUES ('-1111111111111111111111111111111', '-1111111111111111111111111111111');

# REAL in pg maps to DOUBLE in MySQL
CREATE TABLE t2 (f1 REAL);
INSERT INTO t2 VALUES ('-Inf');
INSERT INTO t2 VALUES ('-0.1111111111');

> CREATE SOURCE mz_source FROM MYSQL CONNECTION mysql_conn;

> CREATE TABLE t1 FROM SOURCE mz_source (REFERENCE public.t1);
> CREATE TABLE t2 FROM SOURCE mz_source (REFERENCE public.t2);

> SELECT COUNT(*) > 0 FROM t1;
true

# Insert the same data post-snapshot
$ mysql-execute name=mysql
INSERT INTO t1 SELECT * FROM t1;

> SELECT pg_typeof(f1), pg_typeof(f2) FROM t1 LIMIT 1;
"real" "double precision"

> SELECT pg_typeof(f1) FROM t2 LIMIT 1;
"double precision"


# Note that there is some expected loss of precision on the very large numbers

> SELECT * FROM t1;
-1111111100000000000000000000000 -1111111111111111000000000000000
-1111111100000000000000000000000 -1111111111111111000000000000000
-11111111000000000000000000000 -11111111111111112000000000000
-11111111000000000000000000000 -11111111111111112000000000000
-11111111 -11111111
-11111111 -11111111
-111111110 -111111111
-111111110 -111111111
-11111111000 -11111111111
-11111111000 -11111111111
-1111111100000 -1111111111111
-1111111100000 -1111111111111
-1111111100000000000 -1111111111111111200
-1111111100000000000 -1111111111111111200
-111111110000000000000 -111111111111111110000
-111111110000000000000 -111111111111111110000
-1111111100000000000000000 -1111111111111111100000000
-1111111100000000000000000 -1111111111111111100000000
-111111114000000 -111111111111111
-111111114000000 -111111111111111
-11111112000000000 -11111111111111112
-11111112000000000 -11111111111111112
0 0
0 0
0 0
0 0
0 0
0 0
0.000000000000000000000000000001 0.000000000000000000000000000001
0.000000000000000000000000000001 0.000000000000000000000000000001
<null> <null>
<null> <null>

> SELECT * FROM t2;
-0.1111111111
0

# reset MySQL config
$ mysql-execute name=mysql
SET SESSION sql_mode=default