Materialize/test/sqllogictest/bytea.slt

# 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.

# Tests for the bytea type.

mode cockroach

statement ok
CREATE TABLE test (ord int, b bytea)

statement ok
INSERT INTO test VALUES (0, 'hello'), (1, '你好'), (2, NULL), (3, ''), (4, 'nonprintablechar:')

query II rowsort
SELECT ord, length(b) FROM test
----
0 5
1 6
2 NULL
3 0
4 18

query II rowsort
SELECT ord, length(b, 'utf-8') FROM test
----
0 5
1 2
2 NULL
3 0
4 18

query I
SELECT length('\xDEADBEEF'::bytea)
----
4

query I
SELECT octet_length('\xDEADBEEF'::bytea)
----
4

query I
SELECT bit_length('\xDEADBEEF'::bytea)
----
32

query I
SELECT length('DEADBEEF'::bytea)
----
8

query I
SELECT octet_length('DEADBEEF'::bytea)
----
8

query I
SELECT octet_length('DEADBEEF'::text);
----
8

query I
SELECT bit_length('DEADBEEF'::bytea)
----
64

query I
SELECT bit_length('DEADBEEF'::text);
----
64

query I
SELECT bit_count('\x1234567890'::bytea);
----
15

query I
SELECT bit_count('\x00'::bytea);
----
0

query I
SELECT bit_count('\x0F'::bytea);
----
4

query I
SELECT bit_count('\xFF'::bytea);
----
8

query I
SELECT bit_count('\xF0FF'::bytea);
----
12

query I
SELECT get_byte('\x1234567890'::bytea, 4);
----
144

query I
SELECT get_bit('\x1234567890'::bytea, 30);
----
1

query II
SELECT n, get_bit('\x1234567890'::bytea, n) FROM generate_series(0, 39) as n ORDER BY n DESC;
----
39  1
38  0
37  0
36  1
35  0
34  0
33  0
32  0
31  0
30  1
29  1
28  1
27  1
26  0
25  0
24  0
23  0
22  1
21  0
20  1
19  0
18  1
17  1
16  0
15  0
14  0
13  1
12  1
11  0
10  1
9  0
8  0
7  0
6  0
5  0
4  1
3  0
2  0
1  1
0  0


query I
SELECT get_bit('\xF00a'::bytea, 13);
----
0

query I
SELECT get_bit('\xF00a'::bytea, 5);
----
1

query II
SELECT n, get_bit('\xF00a'::bytea, n) FROM generate_series(0, 15) as n ORDER BY n DESC;
----
15  0
14  0
13  0
12  0
11  1
10  0
9  1
8  0
7  1
6  1
5  1
4  1
3  0
2  0
1  0
0  0

statement error index 16 out of valid range, 0..15
SELECT get_bit('\xF00a'::bytea, 16);

statement error
SELECT length('deadbeef'::text, 'utf-8')

query IT rowsort
SELECT ord, b::text FROM test
----
0 \x68656c6c6f
1 \xe4bda0e5a5bd
2 NULL
3 \x
4 \x6e6f6e7072696e7461626c65636861723a06

query IT rowsort
SELECT ord, convert_from(b, 'utf-8') FROM test
----
0 hello
1 你好
2 NULL
3 (empty)
4 nonprintablechar:

query error invalid encoding name 'invalid encoding'
SELECT convert_from(b, 'invalid encoding') FROM test

query error invalid utf-8 sequence of 1 bytes
SELECT convert_from('\x00ff', 'utf-8')

# get_byte

statement ok
CREATE TABLE test_value (v bytea);

statement ok
INSERT INTO test_value VALUES ('\x1234567890'::bytea);

query error index -1 out of valid range, 0..4
SELECT get_byte(v, -1) FROM test_value

query error index 5 out of valid range, 0..4
SELECT get_byte(v, 5) FROM test_value

query error index 0 out of valid range, 0..-1
SELECT get_byte('\x'::bytea, 0)

query IIIIII
SELECT
    get_byte(v, 0),
    get_byte(v, 1),
    get_byte(v, 2),
    get_byte(v, 3),
    get_byte(v, 4),
    get_byte(v, NULL)
FROM test_value
----
18  52  86  120  144  NULL

query II
SELECT
    get_byte(NULL, 0),
    get_byte(NULL, 2);
----
NULL NULL

# constant_time_eq for bytea

statement ok
CREATE TABLE test_eq_bytea (ord integer, data1 bytea, data2 bytea);

statement ok
INSERT INTO test_eq_bytea VALUES
    (1, '\x1234567890'::bytea, '\x1234567890'::bytea),
    (2, '\x1234567890'::bytea, '\x9999999999'::bytea),
    (3, 'GOODBYTE'::bytea, 'GOODBYTE'::bytea);

query B
SELECT constant_time_eq(data1, data2) FROM test_eq_bytea ORDER BY ord
----
true
false
true

query BBB
SELECT
    constant_time_eq('x', NULL),
    constant_time_eq(NULL, 'x'),
    constant_time_eq(NULL, NULL)
----
NULL
NULL
NULL