📄 comparisons.out
字号:
ij> ---- this test shows the current supported comparison operators---- first, do comparisons on the int type-- create a table with couple of int columnscreate table inttab (c1 int, c2 int);0 rows inserted/updated/deletedij> -- insert some valuesinsert into inttab values (0, 0);1 row inserted/updated/deletedij> insert into inttab values (null, 5);1 row inserted/updated/deletedij> insert into inttab values (1, 1);1 row inserted/updated/deletedij> insert into inttab values (2147483647, 2147483647);1 row inserted/updated/deletedij> -- select each one in turnselect c1 from inttab where c1 = 0;C1 -----------0 ij> select c1 from inttab where c1 = 1;C1 -----------1 ij> select c1 from inttab where c1 = 2147483647;C1 -----------2147483647 ij> -- now look for a value that isn't in the tableselect c1 from inttab where c1 = 2;C1 -----------ij> -- now test null = null semanticsselect c1 from inttab where c1 = c1;C1 -----------0 1 2147483647 ij> -- test is null semanticsselect c1 from inttab where c1 is null;C1 -----------NULL ij> select c1 from inttab where c1 is not null;C1 -----------0 1 2147483647 ij> select c1 from inttab where not c1 is null;C1 -----------0 1 2147483647 ij> -- now test <>select c1 from inttab where c1 <> 0;C1 -----------1 2147483647 ij> select c1 from inttab where c1 <> 1;C1 -----------0 2147483647 ij> select c1 from inttab where c1 <> 2147483647;C1 -----------0 1 ij> select c1 from inttab where c1 <> 2;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 <> c1;C1 -----------ij> select c1 from inttab where c1 <> c2;C1 -----------ij> -- now test !=select c1 from inttab where c1 != 0;C1 -----------1 2147483647 ij> select c1 from inttab where c1 != 1;C1 -----------0 2147483647 ij> select c1 from inttab where c1 != 2147483647;C1 -----------0 1 ij> select c1 from inttab where c1 != 2;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 != c1;C1 -----------ij> select c1 from inttab where c1 != c2;C1 -----------ij> -- now test <select c1 from inttab where c1 < 0;C1 -----------ij> select c1 from inttab where c1 < 1;C1 -----------0 ij> select c1 from inttab where c1 < 2;C1 -----------0 1 ij> select c1 from inttab where c1 < 2147483647;C1 -----------0 1 ij> select c1 from inttab where c1 < c1;C1 -----------ij> select c1 from inttab where c1 < c2;C1 -----------ij> -- now test >select c1 from inttab where c1 > 0;C1 -----------1 2147483647 ij> select c1 from inttab where c1 > 1;C1 -----------2147483647 ij> select c1 from inttab where c1 > 2;C1 -----------2147483647 ij> select c1 from inttab where c1 > 2147483647;C1 -----------ij> select c1 from inttab where c1 > c1;C1 -----------ij> select c1 from inttab where c1 > c2;C1 -----------ij> -- now test <=select c1 from inttab where c1 <= 0;C1 -----------0 ij> select c1 from inttab where c1 <= 1;C1 -----------0 1 ij> select c1 from inttab where c1 <= 2;C1 -----------0 1 ij> select c1 from inttab where c1 <= 2147483647;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 <= c1;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 <= c2;C1 -----------0 1 2147483647 ij> -- now test >=select c1 from inttab where c1 >= 0;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 >= 1;C1 -----------1 2147483647 ij> select c1 from inttab where c1 >= 2;C1 -----------2147483647 ij> select c1 from inttab where c1 >= 2147483647;C1 -----------2147483647 ij> select c1 from inttab where c1 >= c1;C1 -----------0 1 2147483647 ij> select c1 from inttab where c1 >= c2;C1 -----------0 1 2147483647 ij> -- now test notselect c1 from inttab where not (c1 = 0);C1 -----------1 2147483647 ij> select c1 from inttab where not (c1 <> 0);C1 -----------0 ij> select c1 from inttab where not (c1 != 0);C1 -----------0 ij> select c1 from inttab where not (c1 < 0);C1 -----------0 1 2147483647 ij> select c1 from inttab where not (c1 <= 0);C1 -----------1 2147483647 ij> select c1 from inttab where not (c1 > 0);C1 -----------0 ij> select c1 from inttab where not (c1 >= 0);C1 -----------ij> -- create a table with a couple of smallint columns. All smallint vs. smallint-- comparisons must be done between columns, because there are no smallint-- constants in the languagecreate table smallinttab (c1 smallint, c2 smallint);0 rows inserted/updated/deletedij> -- insert some valuesinsert into smallinttab values (0, 0);1 row inserted/updated/deletedij> insert into smallinttab values (null, null);1 row inserted/updated/deletedij> insert into smallinttab values (1, 1);1 row inserted/updated/deletedij> insert into smallinttab values (32767, 32767);1 row inserted/updated/deletedij> insert into smallinttab values (0, 9);1 row inserted/updated/deletedij> insert into smallinttab values (null, 8);1 row inserted/updated/deletedij> insert into smallinttab values (1, 7);1 row inserted/updated/deletedij> insert into smallinttab values (32767, 6);1 row inserted/updated/deletedij> -- select the ones where the columns are equalselect c1, c2 from smallinttab where c1 = c2;C1 |C2 -------------0 |0 1 |1 32767 |32767 ij> -- test smallint = int semanticsselect c1 from smallinttab where c1 = 0;C1 ------0 0 ij> select c1 from smallinttab where c1 = 1;C1 ------1 1 ij> select c1 from smallinttab where c1 = 32767;C1 ------32767 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 = 65537;C1 ------ij> -- test int = smallint semanticsselect c1 from smallinttab where 0 = c1;C1 ------0 0 ij> select c1 from smallinttab where 1 = c1;C1 ------1 1 ij> select c1 from smallinttab where 32767 = c1;C1 ------32767 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 = c1;C1 ------ij> -- Now test <>select c1, c2 from smallinttab where c1 <> c2;C1 |C2 -------------0 |9 1 |7 32767 |6 ij> select c1, c2 from smallinttab where c1 != c2;C1 |C2 -------------0 |9 1 |7 32767 |6 ij> -- test smallint <> int semanticsselect c1 from smallinttab where c1 <> 0;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where c1 <> 1;C1 ------0 32767 0 32767 ij> select c1 from smallinttab where c1 <> 32767;C1 ------0 1 0 1 ij> select c1 from smallinttab where c1 != 0;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where c1 != 1;C1 ------0 32767 0 32767 ij> select c1 from smallinttab where c1 != 32767;C1 ------0 1 0 1 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 <> 65537;C1 ------0 1 32767 0 1 32767 ij> select c1 from smallinttab where c1 != 65537;C1 ------0 1 32767 0 1 32767 ij> -- test int = smallint semanticsselect c1 from smallinttab where 0 <> c1;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where 1 <> c1;C1 ------0 32767 0 32767 ij> select c1 from smallinttab where 32767 <> c1;C1 ------0 1 0 1 ij> select c1 from smallinttab where 0 != c1;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where 1 != c1;C1 ------0 32767 0 32767 ij> select c1 from smallinttab where 32767 != c1;C1 ------0 1 0 1 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 <> c1;C1 ------0 1 32767 0 1 32767 ij> select c1 from smallinttab where 65537 != c1;C1 ------0 1 32767 0 1 32767 ij> -- Now test <select c1, c2 from smallinttab where c1 < c2;C1 |C2 -------------0 |9 1 |7 ij> -- test smallint < int semanticsselect c1 from smallinttab where c1 < 0;C1 ------ij> select c1 from smallinttab where c1 < 1;C1 ------0 0 ij> select c1 from smallinttab where c1 < 32767;C1 ------0 1 0 1 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 < 65537;C1 ------0 1 32767 0 1 32767 ij> -- test int < smallint semanticsselect c1 from smallinttab where 0 < c1;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where 1 < c1;C1 ------32767 32767 ij> select c1 from smallinttab where 32767 < c1;C1 ------ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 < c1;C1 ------ij> -- Now test >select c1, c2 from smallinttab where c1 > c2;C1 |C2 -------------32767 |6 ij> -- test smallint > int semanticsselect c1 from smallinttab where c1 > 0;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where c1 > 1;C1 ------32767 32767 ij> select c1 from smallinttab where c1 > 32767;C1 ------ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 > 65537;C1 ------ij> -- test int > smallint semanticsselect c1 from smallinttab where 0 > c1;C1 ------ij> select c1 from smallinttab where 1 > c1;C1 ------0 0 ij> select c1 from smallinttab where 32767 > c1;C1 ------0 1 0 1 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 > c1;C1 ------0 1 32767 0 1 32767 ij> -- Now test <=select c1, c2 from smallinttab where c1 <= c2;C1 |C2 -------------0 |0 1 |1 32767 |32767 0 |9 1 |7 ij> -- test smallint <= int semanticsselect c1 from smallinttab where c1 <= 0;C1 ------0 0 ij> select c1 from smallinttab where c1 <= 1;C1 ------0 1 0 1 ij> select c1 from smallinttab where c1 <= 32767;C1 ------0 1 32767 0 1 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 <= 65537;C1 ------0 1 32767 0 1 32767 ij> -- test int <= smallint semanticsselect c1 from smallinttab where 0 <= c1;C1 ------0 1 32767 0 1 32767 ij> select c1 from smallinttab where 1 <= c1;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where 32767 <= c1;C1 ------32767 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 <= c1;C1 ------ij> -- Now test >=select c1, c2 from smallinttab where c1 >= c2;C1 |C2 -------------0 |0 1 |1 32767 |32767 32767 |6 ij> -- test smallint >= int semanticsselect c1 from smallinttab where c1 >= 0;C1 ------0 1 32767 0 1 32767 ij> select c1 from smallinttab where c1 >= 1;C1 ------1 32767 1 32767 ij> select c1 from smallinttab where c1 >= 32767;C1 ------32767 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where c1 >= 65537;C1 ------ij> -- test int >= smallint semanticsselect c1 from smallinttab where 0 >= c1;C1 ------0 0 ij> select c1 from smallinttab where 1 >= c1;C1 ------0 1 0 1 ij> select c1 from smallinttab where 32767 >= c1;C1 ------0 1 32767 0 1 32767 ij> -- test is null semanticsselect c1 from smallinttab where c1 is null;C1 ------NULL NULL ij> select c1 from smallinttab where c1 is not null;C1 ------0 1 32767 0 1 32767 ij> select c1 from smallinttab where not c1 is null;C1 ------0 1 32767 0 1 32767 ij> -- test that the smallint gets promoted to int, and not vice versa. 65537-- when converted to short becomes 1select c1 from smallinttab where 65537 >= c1;C1 ------0 1 32767 0 1 32767 ij> -- create a table with a couple of bigint columns. create table biginttab (c1 bigint, c2 bigint, c3 int, c4 smallint);0 rows inserted/updated/deletedij> -- insert some valuesinsert into biginttab values (0, 0, 0, 0 );1 row inserted/updated/deletedij> insert into biginttab values (null, null, null, null);1 row inserted/updated/deletedij> insert into biginttab values (9223372036854775807, 9223372036854775807, 2147483647, 32767);1 row inserted/updated/deletedij> insert into biginttab values (-9223372036854775808, -9223372036854775808, -2147483648, -32768);1 row inserted/updated/deletedij> -- select the ones where the columns are equalselect c1, c2 from biginttab where c1 = c2;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -