sql-tests/inputs/operators.sql

0001
0002 -- unary minus and plus
0003 select -100;
0004 select +230;
0005 select -5.2;
0006 select +6.8e0;
0007 select -key, +key from testdata where key = 2;
0008 select -(key + 1), - key + 1, +(key + 5) from testdata where key = 1;
0009 select -max(key), +max(key) from testdata;
0010 select - (-10);
0011 select + (-key) from testdata where key = 32;
0012 select - (+max(key)) from testdata;
0013 select - - 3;
0014 select - + 20;
0015 select + + 100;
0016 select - - max(key) from testdata;
0017 select + - key from testdata where key = 33;
0018
0019 -- division
0020 select 5 / 2;
0021 select 5 / 0;
0022 select 5 / null;
0023 select null / 5;
0024
0025 -- integral div
0026 select 5 div 2;
0027 select 5 div 0;
0028 select 5 div null;
0029 select null div 5;
0030 select cast(51 as decimal(10, 0)) div cast(2 as decimal(2, 0));
0031 select cast(5 as decimal(1, 0)) div cast(0 as decimal(2, 0));
0032 select cast(5 as decimal(1, 0)) div cast(null as decimal(2, 0));
0033 select cast(null as decimal(1, 0)) div cast(5 as decimal(2, 0));
0034
0035 -- other arithmetics
0036 select 1 + 2;
0037 select 1 - 2;
0038 select 2 * 5;
0039 select 5 % 3;
0040 select pmod(-7, 3);
0041
0042 -- math functions
0043 select cot(1);
0044 select cot(null);
0045 select cot(0);
0046 select cot(-1);
0047
0048 -- ceil and ceiling
0049 select ceiling(0);
0050 select ceiling(1);
0051 select ceil(1234567890123456);
0052 select ceiling(1234567890123456);
0053 select ceil(0.01);
0054 select ceiling(-0.10);
0055
0056 -- floor
0057 select floor(0);
0058 select floor(1);
0059 select floor(1234567890123456);
0060 select floor(0.01);
0061 select floor(-0.10);
0062
0063 -- comparison operator
0064 select 1 > 0.00001;
0065
0066 -- mod
0067 select mod(7, 2), mod(7, 0), mod(0, 2), mod(7, null), mod(null, 2), mod(null, null);
0068
0069 -- length
0070 select BIT_LENGTH('abc');
0071 select CHAR_LENGTH('abc');
0072 select CHARACTER_LENGTH('abc');
0073 select OCTET_LENGTH('abc');
0074
0075 -- abs
0076 select abs(-3.13), abs('-2.19');
0077
0078 -- positive/negative
0079 select positive('-1.11'), positive(-1.11), negative('-1.11'), negative(-1.11);
0080
0081 -- pmod
0082 select pmod(-7, 2), pmod(0, 2), pmod(7, 0), pmod(7, null), pmod(null, 2), pmod(null, null);
0083 select pmod(cast(3.13 as decimal), cast(0 as decimal)), pmod(cast(2 as smallint), cast(0 as smallint));