📄 ejbql.out
字号:
1.0E8 -3.402E38 3.402E38 -1.175E-37 1.175E-37 ij> select abs(a) from myreal;1 -------------NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select abs(-a) from myreal;1 -------------NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select -abs(a) from myreal;1 -------------NULL 0.0 0.0 -1.0 -1.0 NULL -1.0E8 -1.0E8 -3.402E38 -3.402E38 -1.175E-37 -1.175E-37 ij> select abs(abs(-abs(-abs(a)))) from myreal;1 -------------NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYREAL;1 -------------NULL 0.0 0.0 1.0 1.0 NULL 1.0E8 1.0E8 3.402E38 3.402E38 1.175E-37 1.175E-37 ij> select distinct abs(a) from myreal;1 -------------0.0 1.175E-37 1.0 1.0E8 3.402E38 NULL ij> ---- There is nothing wrong with returning 1.0. The float overflows and this is just the way it behaves.-- this used to work on CS, not any more when adopted to DB2 style floats-- since contant numbers are (parsed as) doublesselect abs(-abs(a)) + 1 from myreal where a=1.175E-37;1 -------------ij> -- when casted to a real, it is foundselect abs(-abs(a)) + 1 from myreal where a=cast(1.175E-37 as real);1 -------------1.0 ij> -- Errorinsert into myreal values ( 3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into myreal values (-3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> drop table myreal;0 rows inserted/updated/deletedij> -- End of Real test-- Double Precision has a range of +/-2.225E-307 to +/-1.79769E+308 -- Basiccreate table mydoubleprecision( a double precision );0 rows inserted/updated/deletedij> select abs(a) from mydoubleprecision;1 ----------------------ij> insert into mydoubleprecision values (null), (+0), (-0), (+1), (-1), (100000000), (-100000000), (null),(1.79769E+308), (-1.79769E+308),(2.225E-307), (-2.225E-307);12 rows inserted/updated/deletedij> select a from mydoubleprecision;A ----------------------NULL 0.0 0.0 1.0 -1.0 1.0E8 -1.0E8 NULL 1.79769E308 -1.79769E308 2.225E-307 -2.225E-307 ij> select -a from mydoubleprecision;1 ----------------------NULL 0.0 0.0 -1.0 1.0 -1.0E8 1.0E8 NULL -1.79769E308 1.79769E308 -2.225E-307 2.225E-307 ij> select abs(a) from mydoubleprecision;1 ----------------------NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select abs(-a) from mydoubleprecision;1 ----------------------NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select -abs(a) from mydoubleprecision;1 ----------------------NULL 0.0 0.0 -1.0 -1.0 -1.0E8 -1.0E8 NULL -1.79769E308 -1.79769E308 -2.225E-307 -2.225E-307 ij> select abs(abs(-abs(-abs(a)))) from mydoubleprecision;1 ----------------------NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))) FROM MYDOUBLEPRECISION;1 ----------------------NULL 0.0 0.0 1.0 1.0 1.0E8 1.0E8 NULL 1.79769E308 1.79769E308 2.225E-307 2.225E-307 ij> select distinct abs(a) from mydoubleprecision;1 ----------------------0.0 2.225E-307 1.0 1.0E8 1.79769E308 NULL ij> -- There is nothing wrong with returning 1.0. The double overflows and this is just the way it behaves.select abs(-abs(a)) + 1 from mydoubleprecision where a=2.225E-307;1 ----------------------1.0 ij> -- Errorinsert into mydoubleprecision values ( 1.79769E+308 *2);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> insert into mydoubleprecision values (-1.79769E+308 *2);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> drop table mydoubleprecision;0 rows inserted/updated/deletedij> -- End of Double Precision test-- Float has a the range or a REAL or DOUBLE depending on-- the precision you specify. Below a is a double, b is a floatcreate table myfloat( a float, b float(23) );0 rows inserted/updated/deletedij> select abs(a), abs(b) from myfloat;1 |2 ------------------------------------ij> select columnname, columndatatypefrom sys.syscolumns c, sys.systables t where c.referenceid = t.tableid and t.tablename='MYFLOAT';COLUMNNAME |COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------A |DOUBLE B |REAL ij> insert into myfloat values (null, null), (+0, +0), (-0, -0), (+1, +1), (-1, -1),(100000000, 100000000), (-100000000, -100000000), (null, null),(1.79769E+308, 3.402E+38),(-1.79769E+308, -3.402E+38),(2.225E-307, 1.175E-37),(-2.225E-307, -1.175E-37);12 rows inserted/updated/deletedij> select a, b from myfloat;A |B ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 -1.0 |-1.0 1.0E8 |1.0E8 -1.0E8 |-1.0E8 NULL |NULL 1.79769E308 |3.402E38 -1.79769E308 |-3.402E38 2.225E-307 |1.175E-37 -2.225E-307 |-1.175E-37 ij> select -a, -b from myfloat;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 -1.0 |-1.0 1.0 |1.0 -1.0E8 |-1.0E8 1.0E8 |1.0E8 NULL |NULL -1.79769E308 |-3.402E38 1.79769E308 |3.402E38 -2.225E-307 |-1.175E-37 2.225E-307 |1.175E-37 ij> select abs(a), abs(b) from myfloat;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select abs(-a), abs(-b) from myfloat;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select -abs(a), -abs(b) from myfloat;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 -1.0 |-1.0 -1.0 |-1.0 -1.0E8 |-1.0E8 -1.0E8 |-1.0E8 NULL |NULL -1.79769E308 |-3.402E38 -1.79769E308 |-3.402E38 -2.225E-307 |-1.175E-37 -2.225E-307 |-1.175E-37 ij> select abs(abs(-abs(-abs(a)))), abs(abs(-abs(-abs(b)))) from myfloat;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> SELECT ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(A)))), ABSVAL(ABSVAL(-ABSVAL(-ABSVAL(B)))) FROM MYFLOAT;1 |2 ------------------------------------NULL |NULL 0.0 |0.0 0.0 |0.0 1.0 |1.0 1.0 |1.0 1.0E8 |1.0E8 1.0E8 |1.0E8 NULL |NULL 1.79769E308 |3.402E38 1.79769E308 |3.402E38 2.225E-307 |1.175E-37 2.225E-307 |1.175E-37 ij> select distinct abs(a) from myfloat;1 ----------------------0.0 2.225E-307 1.0 1.0E8 1.79769E308 NULL ij> -- -- There is nothing wrong with returning 1.0. The float overflows and this is just the way it behaves.-- this used to work in CS, but no more, = on floating point values isn't really usefulselect abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307 AND b=1.175E-37;1 |2 ------------------------------------ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where b=3.402E+38;1 |2 ------------------------------------ij> -- 'real =' works on DB2 and DB2 Cloudscapeselect abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where b=cast(3.402E+38 as real);1 |2 ------------------------------------1.79769E308 |3.402E38 ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307 AND b=cast(1.175E-37 as real);1 |2 ------------------------------------1.0 |1.0 ij> select abs(-abs(a)) + 1, abs(-abs(b)) + 1 from myfloat where a=2.225E-307;1 |2 ------------------------------------1.0 |1.0 ij> -- Errorinsert into myfloat values ( 1.79769E+308 *2, 3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> insert into myfloat values (-1.79769E+308 *2, -3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> insert into myfloat values ( 2.225E-307, 3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into myfloat values (-2.225E-307, -3.402E+38 *2);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> drop table myfloat;0 rows inserted/updated/deletedij> -- End of Float test-- Decimal is java.math.BigDecimal-- Basiccreate table myDecimal( a decimal(31, 0), b decimal(31,31));0 rows inserted/updated/deletedij> select abs(a) from myDecimal;1 --------------------------------ij> insert into myDecimal values (null,0), (+0,0), (-0,0), (+1,0), (-1,0), (100000000,.10000000), (-100000000,-.10000000), (null,null), (1.0e30, 1.0e-30), (-1.0e30, -1.0e-30);10 rows inserted/updated/deletedij> select a from myDecimal;A --------------------------------NULL 0 0 1 -1 100000000 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -