Materialize/test/sqllogictest/cockroach/uuid.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/uuid
#
# 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

statement ok
CREATE TABLE u (token uuid PRIMARY KEY,
                token2 uuid,
                token3 uuid,
                UNIQUE INDEX i_token2 (token2))

statement ok
INSERT INTO u VALUES
  ('63616665-6630-3064-6465-616462656562', '{63616665-6630-3064-6465-616462656563}', b'kafef00ddeadbeed'),
  ('urn:uuid:63616665-6630-3064-6465-616462656564', '63616665-6630-3064-6465-616462656565'::uuid, b'kafef00ddeadbeee'),
  (b'cafef00ddeadbeef', '63616665-6630-3064-6465-616462656567', b'kafef00ddeadbeef')

query TTT
SELECT * FROM u ORDER BY token
----
63616665-6630-3064-6465-616462656562 63616665-6630-3064-6465-616462656563 6b616665-6630-3064-6465-616462656564
63616665-6630-3064-6465-616462656564 63616665-6630-3064-6465-616462656565 6b616665-6630-3064-6465-616462656565
63616665-6630-3064-6465-616462656566 63616665-6630-3064-6465-616462656567 6b616665-6630-3064-6465-616462656566

query TTT
SELECT * FROM u WHERE token < '63616665-6630-3064-6465-616462656564'::uuid
----
63616665-6630-3064-6465-616462656562 63616665-6630-3064-6465-616462656563 6b616665-6630-3064-6465-616462656564

query TTT
SELECT * FROM u WHERE token <= '63616665-6630-3064-6465-616462656564'::uuid ORDER BY token
----
63616665-6630-3064-6465-616462656562 63616665-6630-3064-6465-616462656563 6b616665-6630-3064-6465-616462656564
63616665-6630-3064-6465-616462656564 63616665-6630-3064-6465-616462656565 6b616665-6630-3064-6465-616462656565

statement error duplicate key value
INSERT INTO u VALUES ('63616665-6630-3064-6465-616462656566')

statement error duplicate key value
INSERT INTO u VALUES ('63616665-6630-3064-6465-616462656569', '63616665-6630-3064-6465-616462656565')

statement error UUID must be exactly 16 bytes long, got 15 bytes
INSERT INTO u VALUES (b'cafef00ddeadbee')

statement error UUID must be exactly 16 bytes long, got 17 bytes
INSERT INTO u VALUES (b'cafef00ddeadbeefs')

statement error uuid: incorrect UUID length
INSERT INTO u VALUES ('63616665-6630-3064-6465-61646265656')

statement error uuid: incorrect UUID length
INSERT INTO u VALUES ('63616665-6630-3064-6465-6164626565620')

statement error unsupported comparison operator: <uuid> = <bytes>
SELECT token FROM u WHERE token=b'cafef00ddeadbeef'::bytes

statement error unsupported comparison operator: <uuid> = <string>
SELECT token FROM u WHERE token='63616665-6630-3064-6465-616462656562'::string

statement ok
SELECT token FROM u WHERE token='63616665-6630-3064-6465-616462656562'::uuid

query T
SELECT token FROM u WHERE token='urn:uuid:63616665-6630-3064-6465-616462656562'
----
63616665-6630-3064-6465-616462656562

query T
SELECT token FROM u WHERE token=b'cafef00ddeadbeef'
----
63616665-6630-3064-6465-616462656566

query T
SELECT token2 FROM u WHERE token2='63616665-6630-3064-6465-616462656563'
----
63616665-6630-3064-6465-616462656563

query T
SELECT token FROM u WHERE token IN ('63616665-6630-3064-6465-616462656562', '63616665-6630-3064-6465-616462656564') ORDER BY token
----
63616665-6630-3064-6465-616462656562
63616665-6630-3064-6465-616462656564

statement ok
INSERT INTO u VALUES ('63616665-6630-3064-6465-616462656567'::uuid)

statement ok
INSERT INTO u VALUES ('urn:uuid:63616665-6630-3064-6465-616462656568'::uuid)

statement ok
INSERT INTO u VALUES (uuid_v4()::uuid)

statement error value type bytes doesn't match type uuid
INSERT INTO u VALUES ('cafef00ddeadbeef'::bytes)

statement error value type text doesn't match type uuid
INSERT INTO u VALUES ('63616665-6630-3064-6465-616462656562'::string)

statement error value type bytes doesn't match type uuid
INSERT INTO u VALUES (uuid_v4())

query T
SELECT token::uuid FROM u WHERE token=b'cafef00ddeadbeef'
----
63616665-6630-3064-6465-616462656566

query T
SELECT token::string FROM u WHERE token=b'cafef00ddeadbeef'
----
63616665-6630-3064-6465-616462656566

query T
SELECT token::bytes FROM u WHERE token=b'cafef00ddeadbeef'
----
cafef00ddeadbeef

statement error invalid cast: uuid -> int
SELECT token::int FROM u

query T
SELECT ('63616665-6630-3064-6465-616462656562' COLLATE en)::uuid
----
63616665-6630-3064-6465-616462656562