📄 floattypes.out
字号:
------1 1 ij> select cast(d as bigint) from tmp;1 --------------------1 1 ij> select cast(d as float) from tmp;1 ----------------------1.0 1.0 ij> select cast(d as real) from tmp;1 -------------1.0 1.0 ij> select cast(d as double precision) from tmp;1 ----------------------1.0 1.0 ij> select cast(d as dec(10,2)) from tmp;1 -------------1.00 1.00 ij> select cast(d as dec(10,8)) from tmp;1 -------------1.00000000 1.00000000 ij> drop table tmp;0 rows inserted/updated/deletedij> drop table t;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T' because it does not exist.ij> create table t (i int, l bigint, s smallint, d double precision, r real, dc decimal(10,2));0 rows inserted/updated/deletedij> insert into t values (null, null, null, null, null, null);1 row inserted/updated/deletedij> insert into t values (10, -- int 10, -- bigint 10, -- smallint 10, -- double 10, -- real 10 -- decimal(10,2) );1 row inserted/updated/deletedij> insert into t values (-10, -- int -10, -- bigint -10, -- smallint -10, -- double -10, -- real -10 -- decimal(10,2) );1 row inserted/updated/deletedij> insert into t values (0, -- int 0, -- bigint 0, -- smallint 0, -- double 0, -- real 0 -- decimal(10,2) );1 row inserted/updated/deletedij> select dc from t;DC -------------NULL 10.00 -10.00 0.00 ij> select dc + i, dc + s, dc + r, dc + dc from t;1 |2 |3 |4 ------------------------------------------------------------NULL |NULL |NULL |NULL 20.00 |20.00 |20.0 |20.00 -20.00 |-20.00 |-20.0 |-20.00 0.00 |0.00 |0.0 |0.00 ij> select dc - i, dc - s, dc - r, dc - dc from t;1 |2 |3 |4 ------------------------------------------------------------NULL |NULL |NULL |NULL 0.00 |0.00 |0.0 |0.00 0.00 |0.00 |0.0 |0.00 0.00 |0.00 |0.0 |0.00 ij> select dc * i, dc * s, dc * r, dc * dc from t;1 |2 |3 |4 --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 100.00 |100.00 |100.0 |100.0000 100.00 |100.00 |100.0 |100.0000 0.00 |0.00 |0.0 |0.0000 ij> select dc / i, dc / s, dc / r, dc / dc from t;1 |2 |3 |4 ----------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL 1.00000000000000000000000 |1.00000000000000000000000 |1.0 |1.000000000000000000000 1.00000000000000000000000 |1.00000000000000000000000 |1.0 |1.000000000000000000000 ERROR 22012: Attempt to divide by zero.ij> -- try unary minus, plusselect -(dc * 100 / 100e0 ), +(dc * 100e0 / 100 ) from t;1 |2 ---------------------------------------------NULL |NULL -10.0 |10.0 10.0 |-10.0 0.0 |0.0 ij> -- test null/null, constant/null, null/constantselect dc, i / dc, 10 / dc, dc / 10e0 from t;DC |2 |3 |4 ----------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL 10.00 |1.0000000000000000000 |1.0000000000000000000 |1.0 -10.00 |1.0000000000000000000 |-1.0000000000000000000 |-1.0 ERROR 22012: Attempt to divide by zero.ij> -- test for divide by 0select dc / i from t;1 ----------------------------------NULL 1.00000000000000000000000 1.00000000000000000000000 ERROR 22012: Attempt to divide by zero.ij> select 20e0 / 5e0 / 4e0, 20e0 / 4e0 / 5 from t;1 |2 ---------------------------------------------1.0 |1.0 1.0 |1.0 1.0 |1.0 1.0 |1.0 ij> -- test positive/negative, negative/positive and negative/negativeselect dc, dc / -dc, (-dc) / dc, (-dc) / -dc from t;DC |2 |3 |4 ----------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL 10.00 |-1.000000000000000000000 |-1.000000000000000000000 |1.000000000000000000000 -10.00 |-1.000000000000000000000 |-1.000000000000000000000 |1.000000000000000000000 ERROR 22012: Attempt to divide by zero.ij> -- test some "more complex" expressionsselect dc, dc + 10e0, dc - (10 - 20e0), dc - 10, dc - (20 - 10) from t;DC |2 |3 |4 |5 ---------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 10.00 |20.0 |20.0 |0.00 |0.00 -10.00 |0.0 |0.0 |-20.00 |-20.00 0.00 |10.0 |10.0 |-10.00 |-10.00 ij> -- make sure we get the right scale/precision during arithmeticvalues (9.0 + 9.0);1 ------18.0 ij> values (9.9 + 9.9);1 ------19.8 ij> values (-9.0 - 9.0);1 -------18.0 ij> values (-9.9 - 9.9);1 -------19.8 ij> values (9.0 * 9.0);1 -------81.00 ij> values (9.9 * 9.9);1 -------98.01 ij> values (0.9 * 0.9);1 -----0.81 ij> values (0.9999 * 0.9);1 --------0.89991 ij> values (0.9 * 0.9999);1 --------0.89991 ij> values (0.9999 * 0.9999);1 -----------0.99980001 ij> values (1.0 / 3.0);1 ----------------------------------0.33333333333333333333333333333 ij> values (1.0 / 0.3);1 ----------------------------------3.33333333333333333333333333333 ij> values (1.0 / 0.03);1 ----------------------------------33.3333333333333333333333333333 ij> values (1.0 / 0.000003);1 ----------------------------------333333.333333333333333333333333 ij> values (10000.0 / 0.000003);1 ----------------------------------3333333333.33333333333333333333 ij> values (0.0001 / 0.0003);1 ----------------------------------0.333333333333333333333333333 ij> values (0.1 / 3.0);1 ----------------------------------0.033333333333333333333333333333 ij> -- huge numbervalues ( cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)) * cast(1.7e3 as dec(31)));1 --------------------------------69757574410000000000000000 ij> values cast(1.7e30 as dec(31));1 --------------------------------1700000000000000000000000000000 ij> --try a tiny number -- the following seems to be asking a bit-- too much of poor old biginteger, so try-- something smaller--values (cast(1.7e-307 as dec(2147483647,2147483640)) /-- (cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647)) *-- cast(1.7e308 as dec(2147483647))));--values cast(1 as dec(31, 20));1 ----------------------------------1.00000000000000000000 ij> -- test the arithmetic operators on a type we know they don't work oncreate table w (x dec, y long varchar);0 rows inserted/updated/deletedij> select x + y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.ij> select x - y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.ij> select x * y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.ij> select x / y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.ij> -- clean up after ourselvesdrop table w;0 rows inserted/updated/deletedij> ---- comparisons--insert into t values (123, -- int 123, -- bigint 123, -- smallint 1234.56, -- double 1234.56, -- real 1234.56 -- decimal(10,2) );1 row inserted/updated/deletedij> -- test =select dc from t where dc is null;DC -------------NULL ij> select dc from t where dc = 10;DC -------------10.00 ij> select dc from t where dc = -10;DC --------------10.00 ij> select dc from t where dc = 0;DC -------------0.00 ij> select dc from t where dc = 1234.45;DC -------------ij> select dc from t where dc = i;DC -------------10.00 -10.00 0.00 ij> select dc from t where dc = l;DC -------------10.00 -10.00 0.00 ij> select dc from t where dc = s;DC -------------10.00 -10.00 0.00 ij> select dc from t where dc = r;DC -------------10.00 -10.00 0.00 1234.56 ij> select dc from t where dc = d;DC -------------10.00 -10.00 0.00 1234.56 ij> select dc from t where dc = dc;DC -------------10.00 -10.00 0.00 1234.56 ij> -- test >select dc from t where dc > 10;DC -------------1234.56 ij> select dc from t where dc > -10;DC -------------10.00 0.00 1234.56 ij> select dc from t where dc > 0;DC -------------10.00 1234.56 ij> select dc from t where dc > 1234.45;DC -------------1234.56 ij> select dc from t where dc > i;DC -------------1234.56 ij> select dc from t where dc > l;DC -------------1234.56 ij> select dc from t where dc > s;DC -------------1234.56 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -