bitwise.sql

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.

-- Setup
statement
CREATE TABLE test(col1 int, col2 int) USING parquet

statement
INSERT INTO test VALUES(1111, 2)

statement
INSERT INTO test VALUES(1111, 2)

statement
INSERT INTO test VALUES(3333, 4)

statement
INSERT INTO test VALUES(5555, 6)

-- Queries
query
SELECT col1 & col2, col1 | col2, col1 ^ col2 FROM test

query
SELECT col1 & 1234, col1 | 1234, col1 ^ 1234 FROM test

query
SELECT shiftright(col1, 2), shiftright(col1, col2) FROM test

query
SELECT shiftleft(col1, 2), shiftleft(col1, col2) FROM test

-- ShiftRightUnsigned: first arg is Int or Long, second is Int. Returns the
-- same integer type as the first argument. Shift amount is normalized to the
-- bit width (Java semantics) for negative and large shifts.
statement
CREATE TABLE test_shiftrightunsigned_int(v int, s int) USING parquet

statement
INSERT INTO test_shiftrightunsigned_int VALUES
  (1, 1),
  (-1, 1),
  (8, 2),
  (2147483647, 1),
  (-2147483648, 1),
  (0, 0),
  (1, 0),
  (1, 31),
  (1, 32),
  (1, 33),
  (1, -1),
  (NULL, 1),
  (1, NULL)

query
SELECT shiftrightunsigned(v, s) FROM test_shiftrightunsigned_int

statement
CREATE TABLE test_shiftrightunsigned_long(v bigint, s int) USING parquet

statement
INSERT INTO test_shiftrightunsigned_long VALUES
  (1, 1),
  (-1, 1),
  (9223372036854775807, 1),
  (-9223372036854775808, 1),
  (0, 0),
  (1, 63),
  (1, 64),
  (1, -1),
  (NULL, 1),
  (1, NULL)

query
SELECT shiftrightunsigned(v, s) FROM test_shiftrightunsigned_long

query
SELECT ~(11), ~col1, ~col2 FROM test

-- BitwiseCount
statement
CREATE TABLE test_bit_count(i int, l long) USING parquet

statement
INSERT INTO test_bit_count VALUES (0, 0), (1, 1), (7, 7), (-1, -1), (2147483647, 9223372036854775807), (NULL, NULL)

query
SELECT bit_count(i), bit_count(l) FROM test_bit_count

-- BitwiseGet
statement
CREATE TABLE test_bit_get(i int, pos int) USING parquet

statement
INSERT INTO test_bit_get VALUES (11, 0), (11, 1), (11, 2), (11, 3), (0, 0), (NULL, 0), (11, NULL)

query spark_answer_only
SELECT bit_get(i, pos) FROM test_bit_get

-- literal arguments
query
SELECT 1111 & 2, 1111 | 2, 1111 ^ 2

query
SELECT bit_count(0), bit_count(7), bit_count(-1)

query spark_answer_only
SELECT bit_get(11, 0), bit_get(11, 1), bit_get(11, 2), bit_get(11, 3)

query
SELECT shiftright(1111, 2), shiftleft(1111, 2)

query
SELECT shiftrightunsigned(1, 1), shiftrightunsigned(-1, 1), shiftrightunsigned(2147483647, 1), shiftrightunsigned(cast(-1 as bigint), 1)