📄 arithmetic.out
字号:
ij> insert into bigint_t values (-2, -102);1 row inserted/updated/deletedij> select i + j from bigint_t;1 --------------------NULL 100 102 -104 ij> select i, j, i + i + j, j + j + i from bigint_t;I |J |3 |4 -----------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |100 |100 |200 1 |101 |103 |203 -2 |-102 |-106 |-206 ij> select i - j, j - i from bigint_t;1 |2 -----------------------------------------NULL |NULL -100 |100 -100 |100 100 |-100 ij> select i, i - j - j, j - j - i, j, j - i - i, i - i - j from bigint_t;I |2 |3 |J |5 |6 -----------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL 0 |-200 |0 |100 |100 |-100 1 |-201 |-1 |101 |99 |-101 -2 |202 |2 |-102 |-98 |102 ij> select i, j, i * j, j * i from bigint_t;I |J |3 |4 -----------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |100 |0 |0 1 |101 |101 |101 -2 |-102 |204 |204 ij> select i, j, i * i * (i - j), j * i * (i - j) from bigint_t;I |J |3 |4 -----------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |100 |0 |0 1 |101 |-100 |-10100 -2 |-102 |400 |20400 ij> select -i, -j, -(i * i * (i - j)), -(j * i * (i - j)) from bigint_t;1 |2 |3 |4 -----------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |-100 |0 |0 -1 |-101 |100 |10100 2 |102 |-400 |-20400 ij> -- test for divide by 0select j / i from bigint_t;1 --------------------NULL ERROR 22012: Attempt to divide by zero.ij> -- test for overflowinsert into bigint_s values (1, 9223372036854775807);1 row inserted/updated/deletedij> select i + j from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select i - j - j from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select j + j from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select j * j from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select 2 * (9223372036854775807 / 2 + 1) from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select -2 * (9223372036854775807 / 2 + 2) from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select 2 * (-9223372036854775808 / 2 - 1) from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select -2 * (-9223372036854775808 / 2 - 1) from bigint_s;1 --------------------ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> insert into bigint_s values (-9223372036854775808, 0);1 row inserted/updated/deletedij> select -i from bigint_s;1 ---------------------1 ERROR 22003: The resulting value is outside the range for the data type BIGINT.ij> select -j from bigint_s;1 ---------------------92233720368547758070 ij> select i / 2 * 2 + 1 from bigint_s;1 --------------------1 -9223372036854775807ij> select j / 2 * 2 from bigint_s;1 --------------------9223372036854775806 0 ij> -- test mixed types: int and bigintcreate table bigint_r (y bigint);0 rows inserted/updated/deletedij> insert into bigint_r values (2);1 row inserted/updated/deletedij> select 2147483647 + y from bigint_r;1 --------------------2147483649 ij> select y + 2147483647 from bigint_r;1 --------------------2147483649 ij> select 2147483647 - y from bigint_r;1 --------------------2147483645 ij> select y - 2147483647 from bigint_r;1 ---------------------2147483645 ij> select 2147483647 * y from bigint_r;1 --------------------4294967294 ij> select y * 2147483647 from bigint_r;1 --------------------4294967294 ij> select 2147483647 / y from bigint_r;1 --------------------1073741823 ij> select y / 2147483647 from bigint_r;1 --------------------0 ij> -- test precedence and associativitycreate table r (x int);0 rows inserted/updated/deletedij> insert into r values (1);1 row inserted/updated/deletedij> select 2 + 3 * 4 from r;1 -----------14 ij> select (2 + 3) * 4 from r;1 -----------20 ij> select 3 * 4 + 2 from r;1 -----------14 ij> select 3 * (4 + 2) from r;1 -----------18 ij> select 2 - 3 * 4 from r;1 ------------10 ij> select (2 - 3) * 4 from r;1 ------------4 ij> select 3 * 4 - 2 from r;1 -----------10 ij> select 3 * (4 - 2) from r;1 -----------6 ij> select 4 + 3 / 2 from r;1 -----------5 ij> select (4 + 3) / 2 from r;1 -----------3 ij> select 3 / 2 + 4 from r;1 -----------5 ij> select 3 / (2 + 4) from r;1 -----------0 ij> select 4 - 3 / 2 from r;1 -----------3 ij> select (4 - 3) / 2 from r;1 -----------0 ij> -- + and - are of equal precedence, so they should be evaluated left to right-- The result is the same regardless of order of evaluation, so test it-- by causing an overflow. The first test should get an overflow, and the-- second one shouldn't.select 1 + 2147483647 - 2 from r;1 -----------ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> select 1 + (2147483647 - 2) from r;1 -----------2147483646 ij> select 4 * 3 / 2 from r;1 -----------6 ij> select 4 * (3 / 2) from r;1 -----------4 ij> -- Test associativity of unary - versus the binary operatorsselect -1 + 2 from r;1 -----------1 ij> select -(1 + 2) from r;1 ------------3 ij> select -1 - 2 from r;1 ------------3 ij> select -(1 - 2) from r;1 -----------1 ij> -- The test the associativity of unary - with respect to binary *, we must-- use a trick. The value -1073741824 is the minimum integer divided by 2.-- So, 1073741824 * 2 will overflow, but (-1073741824) * 2 will not (because-- of two's complement arithmetic.select -1073741824 * 2 from r;1 ------------2147483648ij> select -(1073741824 * 2) from r;1 -----------ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> -- This should not get an overflowselect -2147483648 / 2 from r;1 ------------1073741824ij> -- arithmetic on a numeric data typecreate table u (c1 int, c2 char(10));0 rows inserted/updated/deletedij> insert into u (c2) values 'asdf';1 row inserted/updated/deletedij> insert into u (c1) values null;1 row inserted/updated/deletedij> insert into u (c1) values 1;1 row inserted/updated/deletedij> insert into u (c1) values null;1 row inserted/updated/deletedij> insert into u (c1) values 2;1 row inserted/updated/deletedij> select c1 + c1 from u;1 -----------NULL NULL 2 NULL 4 ij> select c1 / c1 from u;1 -----------NULL NULL 1 NULL 1 ij> -- arithmetic between a numeric and a string data type failsselect c1 + c2 from u;1 -----------ERROR 22018: Invalid character string format for type INTEGER.ij> -- clean up after ourselvesdrop table t;0 rows inserted/updated/deletedij> drop table s;0 rows inserted/updated/deletedij> drop table r;0 rows inserted/updated/deletedij> drop table u;0 rows inserted/updated/deletedij> drop table smallint_t;0 rows inserted/updated/deletedij> drop table smallint_s;0 rows inserted/updated/deletedij> drop table smallint_r;0 rows inserted/updated/deletedij> drop table bigint_t;0 rows inserted/updated/deletedij> drop table bigint_s;0 rows inserted/updated/deletedij> drop table bigint_r;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -