collatedstring.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/collatedstring
#
# 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 error pq: invalid locale bad_locale: language: subtag "locale" is well-formed but unknown
SELECT 'a' COLLATE bad_locale

statement error pq: unsupported comparison operator: <collatedstring{en}> = <string>
SELECT 'A' COLLATE en = 'a'

statement error pq: unsupported comparison operator: <collatedstring{en}> = <collatedstring{de}>
SELECT 'A' COLLATE en = 'a' COLLATE de

statement error pq: unsupported comparison operator: \('a' COLLATE en_u_ks_level1\) IN \('A' COLLATE en_u_ks_level1, 'b' COLLATE en\): expected 'b' COLLATE en to be of type collatedstring{en_u_ks_level1}, found type collatedstring{en}
SELECT ('a' COLLATE en_u_ks_level1) IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en)

statement error pq: tuples \('a' COLLATE en_u_ks_level1, 'a' COLLATE en\), \('A' COLLATE en, 'B' COLLATE en\) are not comparable at index 1: unsupported comparison operator: <collatedstring{en_u_ks_level1}> < <collatedstring{en}
SELECT ('a' COLLATE en_u_ks_level1, 'a' COLLATE en) < ('A' COLLATE en, 'B' COLLATE en)


query T
SELECT 'A' COLLATE en
----
A

query T
SELECT NULL COLLATE en
----
NULL

query B
SELECT 'a' COLLATE en < ('B' COLLATE de) COLLATE en
----
true


query B
SELECT (1, 'a' COLLATE en) < (1, 'B' COLLATE en)
----
true

query B
SELECT ('a' COLLATE en_u_ks_level1, 'a' COLLATE en) < ('A' COLLATE en_u_ks_level1, 'B' COLLATE en)
----
true


query B
SELECT 'A' COLLATE en_u_ks_level1 = 'a' COLLATE en_u_ks_level1
----
true

query B
SELECT 'A' COLLATE en_u_ks_level1 <> 'a' COLLATE en_u_ks_level1
----
false

query B
SELECT 'A' COLLATE en_u_ks_level1 < 'a' COLLATE en_u_ks_level1
----
false

query B
SELECT 'A' COLLATE en_u_ks_level1 >= 'a' COLLATE en_u_ks_level1
----
true

query B
SELECT 'A' COLLATE en_u_ks_level1 <= 'a' COLLATE en_u_ks_level1
----
true

query B
SELECT 'A' COLLATE en_u_ks_level1 > 'a' COLLATE en_u_ks_level1
----
false


query B
SELECT 'a' COLLATE en_u_ks_level1 = 'B' COLLATE en_u_ks_level1
----
false

query B
SELECT 'a' COLLATE en_u_ks_level1 <> 'B' COLLATE en_u_ks_level1
----
true

query B
SELECT 'a' COLLATE en_u_ks_level1 < 'B' COLLATE en_u_ks_level1
----
true

query B
SELECT 'a' COLLATE en_u_ks_level1 >= 'B' COLLATE en_u_ks_level1
----
false

query B
SELECT 'a' COLLATE en_u_ks_level1 <= 'B' COLLATE en_u_ks_level1
----
true

query B
SELECT 'a' COLLATE en_u_ks_level1 > 'B' COLLATE en_u_ks_level1
----
false


query B
SELECT 'B' COLLATE en_u_ks_level1 = 'A' COLLATE en_u_ks_level1
----
false

query B
SELECT 'B' COLLATE en_u_ks_level1 <> 'A' COLLATE en_u_ks_level1
----
true

query B
SELECT 'B' COLLATE en_u_ks_level1 < 'A' COLLATE en_u_ks_level1
----
false

query B
SELECT 'B' COLLATE en_u_ks_level1 >= 'A' COLLATE en_u_ks_level1
----
true

query B
SELECT 'B' COLLATE en_u_ks_level1 <= 'A' COLLATE en_u_ks_level1
----
false

query B
SELECT 'B' COLLATE en_u_ks_level1 > 'A' COLLATE en_u_ks_level1
----
true


query B
SELECT ('a' COLLATE en_u_ks_level1) IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en_u_ks_level1)
----
true

query B
SELECT ('a' COLLATE en_u_ks_level1) NOT IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en_u_ks_level1)
----
false

query B
SELECT ('a' COLLATE en) IN ('A' COLLATE en, 'b' COLLATE en)
----
false

query B
SELECT ('a' COLLATE en) NOT IN ('A' COLLATE en, 'b' COLLATE en)
----
true


query B
SELECT 'Fussball' COLLATE de = 'Fußball' COLLATE de
----
false

query B
SELECT 'Fussball' COLLATE de_u_ks_level1 = 'Fußball' COLLATE de_u_ks_level1
----
true


query B
SELECT 'ü' COLLATE da < 'x' COLLATE da
----
false

query B
SELECT 'ü' COLLATE de < 'x' COLLATE de
----
true


statement error invalid locale e: language: tag is not well-formed at or near "\)"
CREATE TABLE e1 (
  a STRING COLLATE e
)

statement error multiple COLLATE declarations for column "a" at or near "\)"
CREATE TABLE e2 (
  a STRING COLLATE en COLLATE de
)

statement error COLLATE declaration for non-string-typed column "a" at or near "\)"
CREATE TABLE e3 (
  a INT COLLATE en
)

statement ok
CREATE TABLE t (
  a STRING COLLATE en
)

query TT
SHOW CREATE TABLE t
----
t  CREATE TABLE t (
     a STRING COLLATE en NULL,
     FAMILY "primary" (a, rowid)
   )

statement ok
INSERT INTO t VALUES
  ('A' COLLATE en),
  ('B' COLLATE en),
  ('a' COLLATE en),
  ('b' COLLATE en),
  ('x' COLLATE en),
  ('ü' COLLATE en)

statement error value type collatedstring{de} doesn't match type collatedstring{en} of column "a"
INSERT INTO t VALUES ('X' COLLATE de)

query T
SELECT a FROM t ORDER BY t.a
----
a
A
b
B
ü
x

query T
SELECT a FROM t ORDER BY t.a COLLATE da
----
a
A
b
B
x
ü

query T
SELECT a FROM t WHERE a = 'A' COLLATE en;
----
A

query T
SELECT 'a' COLLATE en::STRING || 'b'
----
ab

query B
SELECT 't' COLLATE en::BOOLEAN
----
true

query I
SELECT '42' COLLATE en::INTEGER
----
42

query R
SELECT '42.0' COLLATE en::FLOAT
----
42

query R
SELECT '42.0' COLLATE en::DECIMAL
----
42.0

query T
SELECT 'a' COLLATE en::BYTES
----
a

query T
SELECT '2017-01-10 16:05:50.734049+00:00' COLLATE en::TIMESTAMP
----
2017-01-10 16:05:50.734049 +0000 +0000

query T
SELECT '2017-01-10 16:05:50.734049+00:00' COLLATE en::TIMESTAMPTZ
----
2017-01-10 16:05:50.734049 +0000 UTC

query T
SELECT '40 days' COLLATE en::INTERVAL
----
40 days

statement ok
CREATE TABLE foo(a STRING COLLATE en_u_ks_level2)

statement ok
PREPARE x AS INSERT INTO foo VALUES ($1 COLLATE en_u_ks_level2) RETURNING a

query T
EXECUTE x(NULL)
----
NULL

query T
SELECT a FROM foo
----
NULL

# Regression test for #24449

statement ok
INSERT INTO foo VALUES ('aBcD' COLLATE en_u_ks_level2)

query T
SELECT * FROM foo WHERE a = 'aBcD' COLLATE en_u_ks_level2
----
aBcD

query T
SELECT * FROM foo WHERE a = 'abcd' COLLATE en_u_ks_level2
----
aBcD

# Test quoted collations.

statement ok
CREATE TABLE quoted_coll (
  a STRING COLLATE "en",
  b STRING COLLATE "en_US",
  c STRING COLLATE "en-Us" DEFAULT ('c' COLLATE "en-Us"),
  d STRING COLLATE "en-u-ks-level1" DEFAULT ('d'::STRING COLLATE "en-u-ks-level1"),
  e STRING COLLATE "en-us" AS (a COLLATE "en-us") STORED
)

query TT
SHOW CREATE TABLE quoted_coll
----
quoted_coll  CREATE TABLE quoted_coll (
  a STRING COLLATE en NULL,
  b STRING COLLATE en_US NULL,
  c STRING COLLATE en_Us NULL DEFAULT 'c':::STRING COLLATE en_Us,
  d STRING COLLATE en_u_ks_level1 NULL DEFAULT 'd':::STRING::STRING COLLATE en_u_ks_level1,
  e STRING COLLATE en_us NULL AS (a COLLATE en_us) STORED,
  FAMILY "primary" (a, b, c, d, e, rowid)
)