📄 floattypes.out
字号:
ij> -- these will all fail:delete from source;1 row inserted/updated/deletedij> insert into source values (null, null, null, null, null);1 row inserted/updated/deletedij> -- these fail because the target won't take a null -- of any typeinsert into target values(null);ERROR 23502: Column 'P' cannot accept a NULL value.ij> insert into target select i from source;ERROR 23502: Column 'P' cannot accept a NULL value.ij> insert into target select s from source;ERROR 23502: Column 'P' cannot accept a NULL value.ij> insert into target select d from source;ERROR 23502: Column 'P' cannot accept a NULL value.ij> -- expect 4 rows in target: 1, 2, 5, and 1:select * from target;P ----------------------1.0 2.0 5.0 ij> update target set p = p + 1;3 rows inserted/updated/deletedij> select * from target;P ----------------------2.0 3.0 6.0 ij> update target set p = p - 1;3 rows inserted/updated/deletedij> select * from target;P ----------------------1.0 2.0 5.0 ij> update target set p = p / 10;3 rows inserted/updated/deletedij> select * from target;P ----------------------0.1 0.2 0.5 ij> update target set p = p * 10;3 rows inserted/updated/deletedij> select * from target;P ----------------------1.0 2.0 5.0 ij> -- these should workupdate source set i = 1.4e8;1 row inserted/updated/deletedij> update source set s = 1.4e4;1 row inserted/updated/deletedij> select i, s from source where i=1.4e8 or s=1.4e4;I |S ------------------140000000 |14000 ij> -- these should get overflowupdate source set i = 1.4e12;ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> update source set s = 1.4e12;ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> drop table source;0 rows inserted/updated/deletedij> drop table target;0 rows inserted/updated/deletedij> create table abcfloat (numtest float(20));0 rows inserted/updated/deletedij> insert into abcfloat values (1.23456789);1 row inserted/updated/deletedij> insert into abcfloat values (.123456789);1 row inserted/updated/deletedij> insert into abcfloat values (-.123456789);1 row inserted/updated/deletedij> insert into abcfloat values (0.223456789);1 row inserted/updated/deletedij> insert into abcfloat values (-0.223456789);1 row inserted/updated/deletedij> insert into abcfloat values (12345678.9);1 row inserted/updated/deletedij> select * from abcfloat;NUMTEST -------------1.2345679 0.12345679 -0.12345679 0.22345679 -0.22345679 1.2345679E7 ij> drop table abcfloat;0 rows inserted/updated/deletedij> -- ** insert float.sql---- Test the builtin type 'float'-- Float is a synonym for double or real, depending on-- the precision specified; so all we need to do is-- show the mapping here; the double and real tests-- show how well those types behave.---- this shows several working versions of float, the default-- and all of the boundary values:create table t (d double precision, r real, f float, f1 float(1), f23 float(23), f24 float(24), f53 float(52));0 rows inserted/updated/deletedij> select columnname, columndatatype from sys.syscolumns c, sys.systables twhere c.referenceid = t.tableid and t.tablename='T';COLUMNNAME |COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------D |DOUBLE F |DOUBLE F1 |REAL F23 |REAL F24 |DOUBLE F53 |DOUBLE R |REAL ij> -- invalid float valuesinsert into t(r) values 'NaN';ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'. ij> insert into t(r) values +3.4021E+38;ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into t(r) values -3.4021E+38;ERROR 22003: The resulting value is outside the range for the data type REAL.ij> create table tt(c char(254));0 rows inserted/updated/deletedij> insert into tt values -3.402E+38;ERROR 42821: Columns of type 'CHAR' cannot hold values of type 'DOUBLE'. ij> insert into t(r) select * from tt;ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'. ij> insert into t(r) values '1.0';ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'. ij> update t set r = NaN;ERROR 42X04: Column 'NAN' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NAN' is not a column in the target table.ij> update t set r = +3.4021E+38;0 rows inserted/updated/deletedij> update t set r = -3.4021E+38;0 rows inserted/updated/deletedij> drop table t;0 rows inserted/updated/deletedij> drop table tt;0 rows inserted/updated/deletedij> -- these get errors for invalid precision values:create table t1 (d double precision, r real, f float(-10));ERROR 42X01: Syntax error: Encountered "-" at line 2, column 54.ij> --create table t2 (d double precision, r real, f float(-1));ERROR 42X01: Syntax error: Encountered "-" at line 2, column 54.ij> create table t3 (d double precision, r real, f float(0));ERROR 42X48: Value '0' is not a valid precision for FLOAT.ij> create table t4 (d double precision, r real, f float(100));ERROR 42X48: Value '100' is not a valid precision for FLOAT.ij> create table t5 (d double precision, r real, f float(53));ERROR 42X48: Value '53' is not a valid precision for FLOAT.ij> create table t6 (d double precision, r real, f float(12.3));ERROR 42X49: Value '12.3' is not a valid integer literal.ij> -- ** insert real.sql---- Test the builtin type 'real'-- assumes these builtin types exist:-- int, smallint, char, varchar, double precision---- other things we might test:-- show how reals lose precision on computations---- Test the arithmetic operators--create table t (i int, s smallint, c char(10), v varchar(50), d double precision, r real);0 rows inserted/updated/deletedij> insert into t values (null, null, null, null, null, null);1 row inserted/updated/deletedij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0);1 row inserted/updated/deletedij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0);1 row inserted/updated/deletedij> select r + r, d + r, i + r, s + r, r + i from t;1 |2 |3 |4 |5 ------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 400.0 |400.0 |200.0 |300.0 |200.0 -400.0 |-400.0 |-201.0 |-300.0 |-201.0 ij> select r + r + r, r + 100 + 432e0 from t;1 |2 ------------------------------------NULL |NULL 600.0 |732.0 -600.0 |332.0 ij> select r - r, r - d, d - r, r - i, i - r, r - s, s - r from t;1 |2 |3 |4 |5 |6 |7 -------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL 0.0 |0.0 |0.0 |200.0 |-200.0 |100.0 |-100.0 0.0 |0.0 |0.0 |-199.0 |199.0 |-100.0 |100.0 ij> select r - r - r, r - 100 - 432e0 from t;1 |2 ------------------------------------NULL |NULL -200.0 |-332.0 200.0 |-732.0 ij> select i, d, s, r, i * r, r * i, s * r, d * r, r * r, r * 2, r * 2.0e0 from t;I |D |S |R |5 |6 |7 |8 |9 |10 |11 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 0 |200.0 |100 |200.0 |0.0 |0.0 |20000.0 |40000.0 |40000.0 |400.0 |400.0 -1 |-200.0 |-100 |-200.0 |200.0 |200.0 |20000.0 |40000.0 |40000.0 |-400.0 |-400.0 ij> -- try unary minus, plusselect -(r * 100 / 100e0 ), +(r * 100e0 / 100 ) from t;1 |2 ---------------------------------------------NULL |NULL -200.0 |200.0 200.0 |-200.0 ij> -- test null/null, constant/null, null/constantselect i, d, r, d / r, i / r, 10 / r, r / d, r / 10e0 from t;I |D |R |4 |5 |6 |7 |8 -------------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 0 |200.0 |200.0 |1.0 |0.0 |0.05 |1.0 |20.0 -1 |-200.0 |-200.0 |1.0 |0.0050 |-0.05 |1.0 |-20.0 ij> -- test for divide by 0select r / i from t;1 -------------NULL ERROR 22012: Attempt to divide by zero.ij> -- test positive/negative, negative/positive and negative/negativeselect r, r / -r, (-r) / r, (-r) / -r from t;R |2 |3 |4 -------------------------------------------------------NULL |NULL |NULL |NULL 200.0 |-1.0 |-1.0 |1.0 -200.0 |-1.0 |-1.0 |1.0 ij> -- test some "more complex" expressionsselect r, r + 10e0, r - (10 - 20e0), r - 10, r - (20 - 10) from t;R |2 |3 |4 |5 ---------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 200.0 |210.0 |210.0 |190.0 |190.0 -200.0 |-190.0 |-190.0 |-210.0 |-210.0 ij> drop table t;0 rows inserted/updated/deletedij> -- test overflowcreate table s (d real, p real);0 rows inserted/updated/deletedij> insert into s values (null, null);1 row inserted/updated/deletedij> insert into s values (0, 100);1 row inserted/updated/deletedij> insert into s values (1, 101);1 row inserted/updated/deletedij> select d + 3.4e+38 from s;1 ----------------------NULL 3.4E38 3.4E38 ij> -- these are close enough to the infinities to overflow-- Can't test simple select of literal because literals are doublesinsert into s values(3.403e+38, 3.403e+38);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into s values(- 3.403e+38, - 3.403e+38);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into s values(1.8e+100, 1.8e+100);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> insert into s values(- 1.8e+100, - 1.8e+100);ERROR 22003: The resulting value is outside the range for the data type REAL.ij> select * from s;D |P ---------------------------NULL |NULL 0.0 |100.0 1.0 |101.0 ij> -- these are far enough from the infinities to workinsert into s values(3.402e+38, - 3.402e+38);1 row inserted/updated/deletedij> insert into s values(3.3e+38, - 3.3e+38);1 row inserted/updated/deletedij> -- these show that math is promoted to double because of the double-- literals. If it was real math, it would failselect d - 3.3e+38 - 3.3e+38, p * 3.3e+38, p * -3.3e+38 from s;1 |2 |3 --------------------------------------------------------------------NULL |NULL |NULL -6.6E38 |3.3E40 |-3.3E40 -6.6E38 |3.333E40 |-3.333E40 -3.1979999994446195E38|-1.1226600001832754E77|1.1226600001832754E77 -3.3000000345172877E38|-1.088999988609295E77 |1.088999988609295E77 ij> -- see two more rowsselect -d from s;1 -------------NULL 0.0 -1.0 -3.402E38 -3.3E38 ij> -- to do the math as reals, we have to keep it in the columnsdelete from s;5 rows inserted/updated/deletedij> insert into s values (1,3.3e+38);1 row inserted/updated/deletedij> -- these will fail, because the math is done as realsselect d - p - p from s;1 -------------ERROR 22003: The resulting value is outside the range for the data type REAL.ij> select p * p from s;1 -------------ERROR 22003: The resulting value is outside the range for the data type REAL.ij> select p * -p from s;1 -------------ERROR 22003: The resulting value is outside the range for the data type REAL.ij> delete from s;1 row inserted/updated/deletedij> -- select values between 0 and 1insert into s values (.111, 1e-1);1 row inserted/updated/deletedij> insert into s values (0.222, 0.222);1 row inserted/updated/deletedij> select * from s;D |P ---------------------------0.111 |0.1 0.222 |0.222 ij> delete from s;2 rows inserted/updated/deletedij> insert into s values (10, 1e-10);1 row inserted/updated/deletedij> -- underflow calculation doesn't round off, gives error.update s set d=d*1.4e-55, p=p*1.4e-45;ERROR 22003: The resulting value is outside the range for the data type REAL.ij> select d, p from s;D |P ---------------------------10.0 |1.0E-10 ij> update s set d=d + 1.4e-46;1 row inserted/updated/deletedij> select d from s;D -------------10.0 ij> drop table s;0 rows inserted/updated/deletedij> -- test the arithmetic operators on a type we know they don't work oncreate table w (x real, y char);0 rows inserted/updated/deletedij> select x + y from w;ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> select x - y from w;ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> select x * y from w;ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> select x / y from w;ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> -- clean up after ourselvesdrop table w;0 rows inserted/updated/deletedij> ---- comparisons--create table c (i int, s smallint, d double precision, r real, l real⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -