📄 arithmetic.out
字号:
ij> ---- Test the arithmetic operators--create table t (i int, j int);0 rows inserted/updated/deletedij> insert into t values (null, null);1 row inserted/updated/deletedij> insert into t values (0, 100);1 row inserted/updated/deletedij> insert into t values (1, 101);1 row inserted/updated/deletedij> insert into t values (-2, -102);1 row inserted/updated/deletedij> select i + j from t;1 -----------NULL 100 102 -104 ij> select i, i + 10 + 20, j, j + 100 + 200 from t;I |2 |J |4 -----------------------------------------------NULL |NULL |NULL |NULL 0 |30 |100 |400 1 |31 |101 |401 -2 |28 |-102 |198 ij> select i - j, j - i from t;1 |2 -----------------------NULL |NULL -100 |100 -100 |100 100 |-100 ij> select i, i - 10 - 20, 20 - 10 - i, j, j - 100 - 200, 200 - 100 - j from t;I |2 |3 |J |5 |6 -----------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL 0 |-30 |10 |100 |-200 |0 1 |-29 |9 |101 |-199 |-1 -2 |-32 |12 |-102 |-402 |202 ij> select i, j, i * j, j * i from 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 * 10 * -20, j * 100 * -200 from t;I |J |3 |4 -----------------------------------------------NULL |NULL |NULL |NULL 0 |100 |0 |-2000000 1 |101 |-200 |-2020000 -2 |-102 |400 |2040000 ij> -- try unary minus on some expressionsselect -i, -j, -(i * 10 * -20), -(j * 100 * -200) from t;1 |2 |3 |4 -----------------------------------------------NULL |NULL |NULL |NULL 0 |-100 |0 |2000000 -1 |-101 |200 |2020000 2 |102 |-400 |-2040000 ij> -- unary plus doesn't do anythingselect +i, +j, +(+i * +10 * -20), +(+j * +100 * -200) from t;1 |2 |3 |4 -----------------------------------------------NULL |NULL |NULL |NULL 0 |100 |0 |-2000000 1 |101 |-200 |-2020000 -2 |-102 |400 |2040000 ij> -- test null/null, constant/null, null/constantselect i, j, i / j, 10 / j, j / 10 from t;I |J |3 |4 |5 -----------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |100 |0 |0 |10 1 |101 |0 |0 |10 -2 |-102 |0 |0 |-10 ij> -- test for divide by 0select j / i from t;1 -----------NULL ERROR 22012: Attempt to divide by zero.ij> select (j - 1) / (i + 4), 20 / 5 / 4, 20 / 4 / 5 from t;1 |2 |3 -----------------------------------NULL |1 |1 24 |1 |1 20 |1 |1 -51 |1 |1 ij> -- test positive/negative, negative/positive and negative/negativeselect j, j / (0 - j), (0 - j) / j, (0 - j) / (0 - j) from t;J |2 |3 |4 -----------------------------------------------NULL |NULL |NULL |NULL 100 |-1 |-1 |1 101 |-1 |-1 |1 -102 |-1 |-1 |1 ij> -- test some "more complex" expressionsselect i, i + 10, i - (10 - 20), i - 10, i - (20 - 10) from t;I |2 |3 |4 |5 -----------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |10 |10 |-10 |-10 1 |11 |11 |-9 |-9 -2 |8 |8 |-12 |-12 ij> select 'The next 2 columns should agree', 2 + 3 * 4 + 5, 2 + (3 * 4) + 5 from t;1 |2 |3 -------------------------------------------------------The next 2 columns should agree|19 |19 The next 2 columns should agree|19 |19 The next 2 columns should agree|19 |19 The next 2 columns should agree|19 |19 ij> select 'The next column should be 45', (2 + 3) * (4 + 5) from t;1 |2 ----------------------------------------The next column should be 45|45 The next column should be 45|45 The next column should be 45|45 The next column should be 45|45 ij> -- test overflowdelete from t;4 rows inserted/updated/deletedij> insert into t values (null, null);1 row inserted/updated/deletedij> insert into t values (0, 100);1 row inserted/updated/deletedij> insert into t values (1, 101);1 row inserted/updated/deletedij> select i + 2147483647 from t;1 -----------NULL 2147483647 ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> select i - 2147483647 - 1, 'This query should work' from t;1 |2 ----------------------------------NULL |This query should work-2147483648|This query should work-2147483647|This query should workij> select i - 2147483647 - 2, 'This query should fail' from t;1 |2 ----------------------------------NULL |This query should failERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> select j * 2147483647 from t;1 -----------NULL ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> select j * -2147483647 from t;1 -----------NULL ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> insert into t values (-2147483648, 0);1 row inserted/updated/deletedij> select -i from t;1 -----------NULL 0 -1 ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> -- test the arithmetic operators on a type we know they don't work oncreate table s (x char(10), y char(10));0 rows inserted/updated/deletedij> select x + y from s;ERROR 42Y95: The '+' operator with a left operand type of 'CHAR' and a right operand type of 'CHAR' is not supported.ij> select x - y from s;ERROR 42Y95: The '-' operator with a left operand type of 'CHAR' and a right operand type of 'CHAR' is not supported.ij> select x * y from s;ERROR 42Y95: The '*' operator with a left operand type of 'CHAR' and a right operand type of 'CHAR' is not supported.ij> select x / y from s;ERROR 42Y95: The '/' operator with a left operand type of 'CHAR' and a right operand type of 'CHAR' is not supported.ij> select -x from s;ERROR 42X37: The unary '-' operator is not allowed on the 'CHAR' type.ij> -- do the same thing with smallints-- except that integer constants are ints!create table smallint_t (i smallint, j smallint);0 rows inserted/updated/deletedij> create table smallint_s (i smallint, j smallint);0 rows inserted/updated/deletedij> insert into smallint_t values (null, null);1 row inserted/updated/deletedij> insert into smallint_t values (0, 100);1 row inserted/updated/deletedij> insert into smallint_t values (1, 101);1 row inserted/updated/deletedij> insert into smallint_t values (-2, -102);1 row inserted/updated/deletedij> select i + j from smallint_t;1 ------NULL 100 102 -104 ij> select i, j, i + i + j, j + j + i from smallint_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 smallint_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 smallint_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 smallint_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 smallint_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 smallint_t;1 |2 |3 |4 ---------------------------NULL |NULL |NULL |NULL 0 |-100 |0 |0 -1 |-101 |100 |10100 2 |102 |-400 |-20400ij> -- test for divide by 0select j / i from smallint_t;1 ------NULL ERROR 22012: Attempt to divide by zero.ij> -- test for overflowinsert into smallint_s values (1, 32767);1 row inserted/updated/deletedij> select i + j from smallint_s;1 ------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> select i - j - j from smallint_s;1 ------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> select j + j from smallint_s;1 ------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> select j * j from smallint_s;1 ------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> insert into smallint_s values (-32768, 0);1 row inserted/updated/deletedij> select -i from smallint_s;1 -------1 ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> -- test mixed types: int and smallintcreate table smallint_r (y smallint);0 rows inserted/updated/deletedij> insert into smallint_r values (2);1 row inserted/updated/deletedij> select 65535 + y from smallint_r;1 -----------65537 ij> select y + 65535 from smallint_r;1 -----------65537 ij> select 65535 - y from smallint_r;1 -----------65533 ij> select y - 65535 from smallint_r;1 ------------65533 ij> select 65535 * y from smallint_r;1 -----------131070 ij> select y * 65535 from smallint_r;1 -----------131070 ij> select 65535 / y from smallint_r;1 -----------32767 ij> select y / 65535 from smallint_r;1 -----------0 ij> -- do the same thing with bigintscreate table bigint_t (i bigint, j bigint);0 rows inserted/updated/deletedij> create table bigint_s (i bigint, j bigint);0 rows inserted/updated/deletedij> insert into bigint_t values (null, null);1 row inserted/updated/deletedij> insert into bigint_t values (0, 100);1 row inserted/updated/deletedij> insert into bigint_t values (1, 101);1 row inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -