📄 floattypes.out
字号:
-- int, smallint, char, varchar---- other things we might test:-- show how doubles lose precision on computations---- Test the arithmetic operators--create table t (i int, s smallint, c char(10), v varchar(50), d double precision);0 rows inserted/updated/deletedij> insert into t values (null, null, null, null, null);1 row inserted/updated/deletedij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0);1 row inserted/updated/deletedij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0);1 row inserted/updated/deletedij> select d + d, i + d, s + d from t;1 |2 |3 --------------------------------------------------------------------NULL |NULL |NULL 400.0 |200.0 |300.0 -400.0 |-201.0 |-300.0 ij> select d + d + d, d + 100 + 432e0 from t;1 |2 ---------------------------------------------NULL |NULL 600.0 |732.0 -600.0 |332.0 ij> select d - i, i - d, d - s, s - d from t;1 |2 |3 |4 -------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL 200.0 |-200.0 |100.0 |-100.0 -199.0 |199.0 |-100.0 |100.0 ij> select d - d - d, d - 100 - 432e0 from t;1 |2 ---------------------------------------------NULL |NULL -200.0 |-332.0 200.0 |-732.0 ij> select i, d, i * d, d * i, d * d, d * 2, d * 2.0e0 from t;I |D |3 |4 |5 |6 |7 -----------------------------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |NULL 0 |200.0 |0.0 |0.0 |40000.0 |400.0 |400.0 -1 |-200.0 |200.0 |200.0 |40000.0 |-400.0 |-400.0 ij> -- try unary minus, plusselect -(d * 100 / 100e0 ), +(d * 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, i / d, 10 / d, d / 10e0 from t;I |D |3 |4 |5 -------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |200.0 |0.0 |0.05 |20.0 -1 |-200.0 |0.0050 |-0.05 |-20.0 ij> -- test for divide by 0select d / i from t;1 ----------------------NULL 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 ij> -- test positive/negative, negative/positive and negative/negativeselect d, d / -d, (-d) / d, (-d) / -d from t;D |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 d, d + 10e0, d - (10 - 20e0), d - 10, d - (20 - 10) from t;D |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> -- show that decimals will go into doubles:select d+1.1 from t;1 ----------------------NULL 201.1 -198.9 ij> insert into t (d) values(1.1);1 row inserted/updated/deletedij> select d from t where d=1.1;D ----------------------1.1 ij> drop table t;0 rows inserted/updated/deletedij> -- test overflowcreate table s (d double precision, p double);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 + 1.7e+308 from s;1 ----------------------NULL 1.7E308 1.7E308 ij> -- these are close enough to the infinities to overflow-- the null row will still get returnedselect 1.798e+308, - 1.798e+308, 'This query should not work' from s;ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> select 1.8e+1000, - 1.8e+1000, 'This query should not work' from s;ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> -- these are far enough from the infinities to workselect 1.797e+308, - 1.797e+308, 'This query should work' from s;1 |2 |3 --------------------------------------------------------------------1.797E308 |-1.797E308 |This query should work1.797E308 |-1.797E308 |This query should work1.797E308 |-1.797E308 |This query should workij> select 1.6e+308, - 1.6e+308, 'This query should work' from s;1 |2 |3 --------------------------------------------------------------------1.6E308 |-1.6E308 |This query should work1.6E308 |-1.6E308 |This query should work1.6E308 |-1.6E308 |This query should workij> -- the null row will still get returnedselect d - 1.6e+308 - 0, 'This query should work' from s;1 |2 ---------------------------------------------NULL |This query should work-1.6E308 |This query should work-1.6E308 |This query should workij> select d - 1.6e+308 - 1.6e+308, 'This query should fail' from s;1 |2 ---------------------------------------------NULL |This query should failERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> -- these should failselect p * 1.6e+308 from s;1 ----------------------NULL ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> select p * -1.6e+308 from s;1 ----------------------NULL ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> -- these workinsert into s values (-1.6e+308, 0);1 row inserted/updated/deletedij> insert into s values (-1.797e+308, 0);1 row inserted/updated/deletedij> -- these don't workinsert into s values (-1.798e+308, 0);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> insert into s values (-1.8e+308, 0);ERROR 22003: The resulting value is outside the range for the data type DOUBLE.ij> -- see two more rowsselect -d from s;1 ----------------------NULL 0.0 -1.0 1.6E308 1.797E308 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 double precision, y long varchar);0 rows inserted/updated/deletedij> select x + y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.ij> select x - y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.ij> select x * y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.ij> select x / y from w;ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.ij> -- clean up after ourselvesdrop table w;0 rows inserted/updated/deletedij> ---- comparisons--create table c (i int, s smallint, d double precision, p double precision);0 rows inserted/updated/deletedij> -- insert some valuesinsert into c values (0, 0, 0e0, 0e0);1 row inserted/updated/deletedij> insert into c values (null, null, 5e0, null);1 row inserted/updated/deletedij> insert into c values (1, 1, 1e0, 2e0);1 row inserted/updated/deletedij> insert into c values (1956475, 1956, 1956475e0, 1956475e0);1 row inserted/updated/deletedij> -- select each one in turnselect d from c where d = 0e0;D ----------------------0.0 ij> select d from c where d = 1e0;D ----------------------1.0 ij> select d from c where d = 1956475e0;D ----------------------1956475.0 ij> -- now look for a value that isn't in the tableselect d from c where p = 2e0;D ----------------------1.0 ij> -- now test null = null semanticsselect d from c where d = d;D ----------------------0.0 5.0 1.0 1956475.0 ij> -- now test <>, <, >select d from c where d <> 0e0;D ----------------------5.0 1.0 1956475.0 ij> select d from c where d <> 1e0;D ----------------------0.0 5.0 1956475.0 ij> select d from c where d < 1956475e0;D ----------------------0.0 5.0 1.0 ij> select d from c where d < 2e0;D ----------------------0.0 1.0 ij> select d from c where d > d;D ----------------------ij> select d from c where d > p;D ----------------------ij> -- now test <=, >=select d from c where d <= 0e0;D ----------------------0.0 ij> select d from c where d <= 1e0;D ----------------------0.0 1.0 ij> select d from c where d <= 2e0;D ----------------------0.0 1.0 ij> select d from c where d >= 1956475e0;D ----------------------1956475.0 ij> select d from c where d >= d;D ----------------------0.0 5.0 1.0 1956475.0 ij> select d from c where d >= p;D ----------------------0.0 1956475.0 ij> -- test comparisons with int and smallintselect d from c where d <= i;D ----------------------0.0 1.0 1956475.0 ij> select d from c where d < s;D ----------------------ij> select d from c where d > i;D ----------------------ij> select d from c where d >= s;D ----------------------0.0 1.0 1956475.0 ij> select d from c where d <> i;D ----------------------ij> select d from c where d = s;D ----------------------0.0 1.0 ij> -- test that the smallint gets promoted to double, and not vice versa. 65537-- when converted to short becomes 1select d from c where s = 65537e0;D ----------------------ij> -- test =SQ-- this gets cardinality errorselect d from c where d = (select d from c);ERROR 21000: Scalar subquery is only allowed to return a single row.ij> -- this worksselect d from c where d = (select d from c where d=5);D ----------------------5.0 ij> -- show that double is comparable to realcreate table o (c char(10), v varchar(30), dc decimal);0 rows inserted/updated/deletedij> select d from c,o where d <> dc;D ----------------------ij> -- clean updrop table c;0 rows inserted/updated/deletedij> drop table o;0 rows inserted/updated/deletedij> ---- test alternate syntax: just double will work for DB2 compatibility--create table db2version (d double);0 rows inserted/updated/deletedij> drop table db2version;0 rows inserted/updated/deletedij> ---- test a variety of inserts and updates--create table source (i int, s smallint, c char(10), v varchar(50), d double precision);0 rows inserted/updated/deletedij> create table target (p double precision not null);0 rows inserted/updated/deletedij> -- we have already tested inserting integer and double literals.insert into source values (1, 2, '3', '4', 5);1 row inserted/updated/deletedij> -- these will all work:insert into target select i from source;1 row inserted/updated/deletedij> insert into target select s from source;1 row inserted/updated/deletedij> insert into target select d from source;1 row inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -