sql-tests/inputs/bitwise.sql

0001 -- test cases for bitwise functions
0002
0003 -- null
0004 select bit_count(null);
0005
0006 -- boolean
0007 select bit_count(true);
0008 select bit_count(false);
0009
0010 -- byte/tinyint
0011 select bit_count(cast(1 as tinyint));
0012 select bit_count(cast(2 as tinyint));
0013 select bit_count(cast(3 as tinyint));
0014
0015 -- short/smallint
0016 select bit_count(1S);
0017 select bit_count(2S);
0018 select bit_count(3S);
0019
0020 -- int
0021 select bit_count(1);
0022 select bit_count(2);
0023 select bit_count(3);
0024
0025 -- long/bigint
0026 select bit_count(1L);
0027 select bit_count(2L);
0028 select bit_count(3L);
0029
0030 -- negative num
0031 select bit_count(-1L);
0032
0033 -- edge value
0034 select bit_count(9223372036854775807L);
0035 select bit_count(-9223372036854775808L);
0036
0037 -- other illegal arguments
0038 select bit_count("bit count");
0039 select bit_count('a');
0040
0041 -- test for bit_xor
0042 --
0043 CREATE OR REPLACE TEMPORARY VIEW bitwise_test AS SELECT * FROM VALUES
0044   (1, 1, 1, 1L),
0045   (2, 3, 4, null),
0046   (7, 7, 7, 3L) AS bitwise_test(b1, b2, b3, b4);
0047
0048 -- empty case
0049 SELECT BIT_XOR(b3) AS n1 FROM bitwise_test where 1 = 0;
0050
0051 -- null case
0052 SELECT BIT_XOR(b4) AS n1 FROM bitwise_test where b4 is null;
0053
0054 -- the suffix numbers show the expected answer
0055 SELECT
0056  BIT_XOR(cast(b1 as tinyint))  AS a4,
0057  BIT_XOR(cast(b2 as smallint))  AS b5,
0058  BIT_XOR(b3)  AS c2,
0059  BIT_XOR(b4)  AS d2,
0060  BIT_XOR(distinct b4) AS e2
0061 FROM bitwise_test;
0062
0063 -- group by
0064 SELECT bit_xor(b3) FROM bitwise_test GROUP BY b1 & 1;
0065
0066 --having
0067 SELECT b1, bit_xor(b2) FROM bitwise_test GROUP BY b1 HAVING bit_and(b2) < 7;
0068
0069 -- window
0070 SELECT b1, b2, bit_xor(b2) OVER (PARTITION BY b1 ORDER BY b2) FROM bitwise_test;