Materialize/test/sqllogictest/cockroach/bit.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/bit
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

query TTT
SELECT B'1000101'::BIT(4)::STRING,
       B'1000101'::BIT(4),
       B'1000101'
----
1000  1000  1000101


statement ok
CREATE TABLE bits(a BIT, b BIT(4), c VARBIT, d VARBIT(4))

query TT colnames
SHOW CREATE TABLE bits
----
table_name  create_statement
bits        CREATE TABLE bits (
            a BIT NULL,
            b BIT(4) NULL,
            c VARBIT NULL,
            d VARBIT(4) NULL,
            FAMILY "primary" (a, b, c, d, rowid)
)

subtest bit_fixed1

statement ok
INSERT INTO bits(a) VALUES (B'1'), (B'0');

statement error bit string length 0 does not match type BIT
INSERT INTO bits(a) VALUES (B'')

statement error bit string length 4 does not match type BIT
INSERT INTO bits(a) VALUES (B'1110')

subtest bit_fixed4

statement ok
INSERT INTO bits(b) VALUES (B'0000'), (B'1001');

statement error bit string length 0 does not match type BIT\(4\)
INSERT INTO bits(b) VALUES (B'')

statement error bit string length 3 does not match type BIT\(4\)
INSERT INTO bits(b) VALUES (B'111')

statement error bit string length 9 does not match type BIT\(4\)
INSERT INTO bits(b) VALUES (B'111000111')

subtest bit_varying_unlimited

statement ok
INSERT INTO bits(c) VALUES (B'1'), (B'0'), (B''), (B'1110'),
(B'0101010101010101001101010101010101010101010101010101010101010101010010101') -- more than 64 bits

subtest bit_varying_limited

statement ok
INSERT INTO bits(d) VALUES (B'1'), (B'0'), (B''), (B'1110')

statement error bit string length 73 too large for type VARBIT\(4\)
INSERT INTO bits(d) VALUES
(B'0101010101010101001101010101010101010101010101010101010101010101010010101') -- more than 64 bits

subtest results

query TITITITI colnames
SELECT a, length(a::STRING) an,
       b, length(b::STRING) bn,
       c, length(c::STRING) cn,
       d, length(d::STRING) dn
  FROM bits
ORDER BY 1,2,3,4,5,6,7,8
----
a     an    b     bn    c                                                                          cn    d     dn
NULL  NULL  NULL  NULL  NULL                                                                       NULL  ·     0
NULL  NULL  NULL  NULL  NULL                                                                       NULL  0     1
NULL  NULL  NULL  NULL  NULL                                                                       NULL  1     1
NULL  NULL  NULL  NULL  NULL                                                                       NULL  1110  4
NULL  NULL  NULL  NULL  ·                                                                          0     NULL  NULL
NULL  NULL  NULL  NULL  0                                                                          1     NULL  NULL
NULL  NULL  NULL  NULL  0101010101010101001101010101010101010101010101010101010101010101010010101  73    NULL  NULL
NULL  NULL  NULL  NULL  1                                                                          1     NULL  NULL
NULL  NULL  NULL  NULL  1110                                                                       4     NULL  NULL
NULL  NULL  0000  4     NULL                                                                       NULL  NULL  NULL
NULL  NULL  1001  4     NULL                                                                       NULL  NULL  NULL
0     1     NULL  NULL  NULL                                                                       NULL  NULL  NULL
1     1     NULL  NULL  NULL                                                                       NULL  NULL  NULL

subtest bit_arith

statement ok
INSERT INTO bits(b) VALUES (B'0110'), (B'0011')

statement ok
INSERT INTO bits(c) VALUES (B'1010'), (B'11')

# Shifts always truncate/pad to the bit array size.
query TTTTTTT colnames
SELECT x.b,
       x.b << 0 AS l0,
       x.b >> 0 AS r0,
       x.b << -1 AS lm1,
       x.b >> 1 AS r1,
       x.b >> -1 AS rm11,
       x.b << 1 AS l1
  FROM bits x
 WHERE x.b IS NOT NULL
ORDER BY 1,2,3,4,5,6,7
----
b     l0    r0    lm1   r1    rm11  l1
0000  0000  0000  0000  0000  0000  0000
0011  0011  0011  0001  0001  0110  0110
0110  0110  0110  0011  0011  1100  1100
1001  1001  1001  0100  0100  0010  0010

# Concat works on mixed bit arrays.
query TTTT rowsort
SELECT x.b, y.c, x.b || y.c, y.c || x.b FROM bits x, bits y WHERE x.b IS NOT NULL AND length(y.c::string) < 5
----
0000  1     00001     10000
0000  0     00000     00000
0000  ·     0000      0000
0000  1110  00001110  11100000
0000  1010  00001010  10100000
0000  11    000011    110000
1001  1     10011     11001
1001  0     10010     01001
1001  ·     1001      1001
1001  1110  10011110  11101001
1001  1010  10011010  10101001
1001  11    100111    111001
0110  1     01101     10110
0110  0     01100     00110
0110  ·     0110      0110
0110  1110  01101110  11100110
0110  1010  01101010  10100110
0110  11    011011    110110
0011  1     00111     10011
0011  0     00110     00011
0011  ·     0011      0011
0011  1110  00111110  11100011
0011  1010  00111010  10100011
0011  11    001111    110011

query TT rowsort
SELECT x.b, ~x.b AS comp FROM bits x WHERE b IS NOT NULL
----
0000  1111
1001  0110
0110  1001
0011  1100

statement ok
DELETE FROM bits; INSERT INTO bits(c) VALUES (B'0'), (B'1')

query TT rowsort
SELECT x.c, ~x.c AS comp FROM bits x
----
0  1
1  0

query TTTTT rowsort
SELECT x.c AS v1, y.c AS v2,
       x.c & y.c AS "and",
       x.c | y.c AS "or",
       x.c # y.c AS "xor"
FROM bits x, bits y
----
0  0  0  0  0
0  1  0  1  1
1  0  0  1  1
1  1  1  1  0

subtest bit_ordering

statement ok
CREATE TABLE obits(x VARBIT);
 INSERT INTO obits(x) VALUES
 (B'0'),
 (B'1'),
 (B'0000'),
 (B'0001'),
 (B'010'),
 (B'10'),
 (B'11'),
 (B''),
 (B'00100'),
 (B'00110'),
 (B'00001'),
 (B'1001001010101'),
 (B'01001001010101'),
 (B'11001001010101')

# Check unindexed ordering.
query T
SELECT * FROM obits ORDER BY x
----
·
0
0000
00001
0001
00100
00110
010
01001001010101
1
10
1001001010101
11
11001001010101

# Check indexed ordering.
statement ok
CREATE INDEX obits_idx ON obits(x)

query T
SELECT * FROM obits@obits_idx ORDER BY x
----
·
0
0000
00001
0001
00100
00110
010
01001001010101
1
10
1001001010101
11
11001001010101

subtest bit_arrays

query TT colnames
SELECT ARRAY[B'101011'] AS a, '{111001}'::VARBIT[] AS b
----
a         b
{101011}  {111001}

statement ok
CREATE TABLE obitsa(x VARBIT(20)[]);
 INSERT INTO obitsa(x) VALUES
 (ARRAY[B'01', B'']),
 (ARRAY[B'01', B'0']),
 (ARRAY[B'01', B'1']),
 (ARRAY[B'01', B'0000']),
 (ARRAY[B'01', B'0001']),
 (ARRAY[B'01', B'010']),
 (ARRAY[B'01', B'10']),
 (ARRAY[B'01', B'11']),
 (ARRAY[B'01', B'']),
 (ARRAY[B'01', B'00100']),
 (ARRAY[B'01', B'00110']),
 (ARRAY[B'01', B'00001']),
 (ARRAY[B'01', B'1001001010101']),
 (ARRAY[B'01', B'01001001010101']),
 (ARRAY[B'01', B'11001001010101'])

query T
SELECT create_statement FROM [SHOW CREATE obitsa]
----
CREATE TABLE obitsa (
   x VARBIT(20)[] NULL,
   FAMILY "primary" (x, rowid)
)

# Check unindexed ordering.
query T rowsort
SELECT * FROM obitsa
----
{01,""}
{01,0}
{01,1}
{01,0000}
{01,0001}
{01,010}
{01,10}
{01,11}
{01,""}
{01,00100}
{01,00110}
{01,00001}
{01,1001001010101}
{01,01001001010101}
{01,11001001010101}