DROP DATABASE IF EXISTS `double_float`;
CREATE DATABASE `double_float`;
USE `double_float`;
SET default_storage_engine=Columnstore;
SELECT @@default_storage_engine;
@@default_storage_engine
Columnstore
set autocommit=0;
CREATE TABLE test1 (dkey int);
INSERT INTO test1 VALUES (1), (2), (3);
SELECT test1.dkey FROM test1 ORDER BY test1.dkey;
dkey
1
2
3
CREATE TABLE qatabledouble (col DOUBLE) ;
CREATE TABLE qatablefloat (col float) ;
INSERT INTO qatabledouble VALUES (-2.225073858507201E-307);
INSERT INTO qatabledouble VALUES (-1.807302187774382E-127);
INSERT INTO qatabledouble VALUES (0);
INSERT INTO qatabledouble VALUES (1.993777023789432E+21);
INSERT INTO qatabledouble VALUES (1.797693134862315E+38);
INSERT INTO qatabledouble VALUES (-19937770237894323221);
INSERT INTO qatabledouble VALUES (17976931348623158);
SELECT * FROM qatabledouble;
col
-2.225073858507201e-307
-1.807302187774382e-127
0
1.993777023789432e21
1.797693134862315e38
-1.9937770237894324e19
1.7976931348623158e16
INSERT INTO qatabledouble VALUES (null);
INSERT INTO qatabledouble VALUES (null);
INSERT INTO qatabledouble VALUES (null);
SELECT * FROM qatabledouble;
col
-2.225073858507201e-307
-1.807302187774382e-127
0
1.993777023789432e21
1.797693134862315e38
-1.9937770237894324e19
1.7976931348623158e16
NULL
NULL
NULL
INSERT INTO qatablefloat VALUES (null);
INSERT INTO qatablefloat VALUES (null);
INSERT INTO qatablefloat VALUES (null);
SELECT * FROM qatablefloat;
col
NULL
NULL
NULL
DELETE FROM qatabledouble WHERE col IS NULL;
SELECT * FROM qatabledouble;
col
-2.225073858507201e-307
-1.807302187774382e-127
0
1.993777023789432e21
1.797693134862315e38
-1.9937770237894324e19
1.7976931348623158e16
delete FROM qatablefloat WHERE col IS NULL;
SELECT * FROM qatablefloat;
col
CREATE TABLE qatabledouble_v2 (col1 DOUBLE, col2 DOUBLE, col3 DOUBLE) ;
INSERT INTO qatabledouble_v2 VALUES (-0.50, -0.50, -0.50);
INSERT INTO qatabledouble_v2 VALUES (-0.49, -0.49, -0.49);
INSERT INTO qatabledouble_v2 VALUES (0.49, 0.49, 0.49);
INSERT INTO qatabledouble_v2 VALUES (0.50, 0.50, 0.50);
INSERT INTO qatabledouble_v2 VALUES (+8,+8,+8);
INSERT INTO qatabledouble_v2 VALUES (+0.50,+0.50,+0.50);
INSERT INTO qatabledouble_v2 VALUES (+0.49,+0.49,+0.49);
INSERT INTO qatabledouble_v2 VALUES (+0.0,+0.0,+0.0);
INSERT INTO qatabledouble_v2 VALUES (+.50,+.50,+.50);
INSERT INTO qatabledouble_v2 VALUES (+.49,+.49,+.49);
INSERT INTO qatabledouble_v2 VALUES (+.0,+.0,+.0);
INSERT INTO qatabledouble_v2 VALUES (-.0,-.0,-.0);
INSERT INTO qatabledouble_v2 VALUES (-.49,-.49,-.49);
INSERT INTO qatabledouble_v2 VALUES (-.50,-.50,-.50);
INSERT INTO qatabledouble_v2 VALUES (-0.0,-0.0,-0.0);
INSERT INTO qatabledouble_v2 VALUES (-0.49,-0.49,-0.49);
INSERT INTO qatabledouble_v2 VALUES (-0.50,-0.50,-0.50);
INSERT INTO qatabledouble_v2 VALUES (-8,-8,-8);
INSERT INTO qatabledouble_v2 VALUES (8,8,8);
INSERT INTO qatabledouble_v2 VALUES (0.50,0.50,0.50);
INSERT INTO qatabledouble_v2 VALUES (0.49,0.49,0.49);
INSERT INTO qatabledouble_v2 VALUES (0.0,0.0,0.0);
INSERT INTO qatabledouble_v2 VALUES (.50,.50,.50);
INSERT INTO qatabledouble_v2 VALUES (.49,.49,.49);
INSERT INTO qatabledouble_v2 VALUES (.0,.0,.0);
SELECT * FROM qatabledouble_v2;
col1	col2	col3
-0.5	-0.5	-0.5
-0.49	-0.49	-0.49
0.49	0.49	0.49
0.5	0.5	0.5
8	8	8
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0	0	0
-0.49	-0.49	-0.49
-0.5	-0.5	-0.5
0	0	0
-0.49	-0.49	-0.49
-0.5	-0.5	-0.5
-8	-8	-8
8	8	8
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
CREATE TABLE qatablefloat_v3 (col1 float, col2 float, col3 float) ;
INSERT INTO qatablefloat_v3 VALUES (-0.50, -0.50, -0.50);
INSERT INTO qatablefloat_v3 VALUES (-0.49, -0.49, -0.49);
INSERT INTO qatablefloat_v3 VALUES (0.49, 0.49, 0.49);
INSERT INTO qatablefloat_v3 VALUES (0.50, 0.50, 0.50);
INSERT INTO qatablefloat_v3 VALUES (+8,+8,+8);
INSERT INTO qatablefloat_v3 VALUES (+0.50,+0.50,+0.50);
INSERT INTO qatablefloat_v3 VALUES (+0.49,+0.49,+0.49);
INSERT INTO qatablefloat_v3 VALUES (+0.0,+0.0,+0.0);
INSERT INTO qatablefloat_v3 VALUES (+.50,+.50,+.50);
INSERT INTO qatablefloat_v3 VALUES (+.49,+.49,+.49);
INSERT INTO qatablefloat_v3 VALUES (+.0,+.0,+.0);
INSERT INTO qatablefloat_v3 VALUES (-.0,-.0,-.0);
INSERT INTO qatablefloat_v3 VALUES (-.49,-.49,-.49);
INSERT INTO qatablefloat_v3 VALUES (-.50,-.50,-.50);
INSERT INTO qatablefloat_v3 VALUES (-0.0,-0.0,-0.0);
INSERT INTO qatablefloat_v3 VALUES (-0.49,-0.49,-0.49);
INSERT INTO qatablefloat_v3 VALUES (-0.50,-0.50,-0.50);
INSERT INTO qatablefloat_v3 VALUES (-8,-8,-8);
INSERT INTO qatablefloat_v3 VALUES (8,8,8);
INSERT INTO qatablefloat_v3 VALUES (0.50,0.50,0.50);
INSERT INTO qatablefloat_v3 VALUES (0.49,0.49,0.49);
INSERT INTO qatablefloat_v3 VALUES (0.0,0.0,0.0);
INSERT INTO qatablefloat_v3 VALUES (.50,.50,.50);
INSERT INTO qatablefloat_v3 VALUES (.49,.49,.49);
INSERT INTO qatablefloat_v3 VALUES (.0,.0,.0);
SELECT * FROM qatablefloat_v3;
col1	col2	col3
-0.5	-0.5	-0.5
-0.49	-0.49	-0.49
0.49	0.49	0.49
0.5	0.5	0.5
8	8	8
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0	0	0
-0.49	-0.49	-0.49
-0.5	-0.5	-0.5
0	0	0
-0.49	-0.49	-0.49
-0.5	-0.5	-0.5
-8	-8	-8
8	8	8
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
0.5	0.5	0.5
0.49	0.49	0.49
0	0	0
DROP DATABASE `double_float`;