github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/collatedstring (about) 1 # LogicTest: !3node-tenant 2 statement error pq: invalid locale bad_locale: language: subtag "locale" is well-formed but unknown 3 SELECT 'a' COLLATE bad_locale 4 5 statement error pq: unsupported comparison operator: <collatedstring{en}> = <string> 6 SELECT 'A' COLLATE en = 'a' 7 8 statement error pq: unsupported comparison operator: <collatedstring{en}> = <collatedstring{de}> 9 SELECT 'A' COLLATE en = 'a' COLLATE de 10 11 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} 12 SELECT ('a' COLLATE en_u_ks_level1) IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en) 13 14 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} 15 SELECT ('a' COLLATE en_u_ks_level1, 'a' COLLATE en) < ('A' COLLATE en, 'B' COLLATE en) 16 17 18 query T 19 SELECT 'A' COLLATE en 20 ---- 21 A 22 23 query T 24 SELECT ('A' COLLATE de) COLLATE en 25 ---- 26 A 27 28 query T 29 SELECT NAME 'A' COLLATE en 30 ---- 31 A 32 33 query T 34 SELECT (NAME 'A' COLLATE de) COLLATE en 35 ---- 36 A 37 38 query T 39 SELECT NULL COLLATE en 40 ---- 41 NULL 42 43 query B 44 SELECT 'a' COLLATE en < ('B' COLLATE de) COLLATE en 45 ---- 46 true 47 48 49 query B 50 SELECT (1, 'a' COLLATE en) < (1, 'B' COLLATE en) 51 ---- 52 true 53 54 query B 55 SELECT ('a' COLLATE en_u_ks_level1, 'a' COLLATE en) < ('A' COLLATE en_u_ks_level1, 'B' COLLATE en) 56 ---- 57 true 58 59 60 query B 61 SELECT 'A' COLLATE en_u_ks_level1 = 'a' COLLATE en_u_ks_level1 62 ---- 63 true 64 65 query B 66 SELECT 'A' COLLATE en_u_ks_level1 <> 'a' COLLATE en_u_ks_level1 67 ---- 68 false 69 70 query B 71 SELECT 'A' COLLATE en_u_ks_level1 < 'a' COLLATE en_u_ks_level1 72 ---- 73 false 74 75 query B 76 SELECT 'A' COLLATE en_u_ks_level1 >= 'a' COLLATE en_u_ks_level1 77 ---- 78 true 79 80 query B 81 SELECT 'A' COLLATE en_u_ks_level1 <= 'a' COLLATE en_u_ks_level1 82 ---- 83 true 84 85 query B 86 SELECT 'A' COLLATE en_u_ks_level1 > 'a' COLLATE en_u_ks_level1 87 ---- 88 false 89 90 91 query B 92 SELECT 'a' COLLATE en_u_ks_level1 = 'B' COLLATE en_u_ks_level1 93 ---- 94 false 95 96 query B 97 SELECT 'a' COLLATE en_u_ks_level1 <> 'B' COLLATE en_u_ks_level1 98 ---- 99 true 100 101 query B 102 SELECT 'a' COLLATE en_u_ks_level1 < 'B' COLLATE en_u_ks_level1 103 ---- 104 true 105 106 query B 107 SELECT 'a' COLLATE en_u_ks_level1 >= 'B' COLLATE en_u_ks_level1 108 ---- 109 false 110 111 query B 112 SELECT 'a' COLLATE en_u_ks_level1 <= 'B' COLLATE en_u_ks_level1 113 ---- 114 true 115 116 query B 117 SELECT 'a' COLLATE en_u_ks_level1 > 'B' COLLATE en_u_ks_level1 118 ---- 119 false 120 121 122 query B 123 SELECT 'B' COLLATE en_u_ks_level1 = 'A' COLLATE en_u_ks_level1 124 ---- 125 false 126 127 query B 128 SELECT 'B' COLLATE en_u_ks_level1 <> 'A' COLLATE en_u_ks_level1 129 ---- 130 true 131 132 query B 133 SELECT 'B' COLLATE en_u_ks_level1 < 'A' COLLATE en_u_ks_level1 134 ---- 135 false 136 137 query B 138 SELECT 'B' COLLATE en_u_ks_level1 >= 'A' COLLATE en_u_ks_level1 139 ---- 140 true 141 142 query B 143 SELECT 'B' COLLATE en_u_ks_level1 <= 'A' COLLATE en_u_ks_level1 144 ---- 145 false 146 147 query B 148 SELECT 'B' COLLATE en_u_ks_level1 > 'A' COLLATE en_u_ks_level1 149 ---- 150 true 151 152 153 query B 154 SELECT ('a' COLLATE en_u_ks_level1) IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en_u_ks_level1) 155 ---- 156 true 157 158 query B 159 SELECT ('a' COLLATE en_u_ks_level1) NOT IN ('A' COLLATE en_u_ks_level1, 'b' COLLATE en_u_ks_level1) 160 ---- 161 false 162 163 query B 164 SELECT ('a' COLLATE en) IN ('A' COLLATE en, 'b' COLLATE en) 165 ---- 166 false 167 168 query B 169 SELECT ('a' COLLATE en) NOT IN ('A' COLLATE en, 'b' COLLATE en) 170 ---- 171 true 172 173 174 query B 175 SELECT 'Fussball' COLLATE de = 'Fußball' COLLATE de 176 ---- 177 false 178 179 query B 180 SELECT 'Fussball' COLLATE de_u_ks_level1 = 'Fußball' COLLATE de_u_ks_level1 181 ---- 182 true 183 184 185 query B 186 SELECT 'ü' COLLATE da < 'x' COLLATE da 187 ---- 188 false 189 190 query B 191 SELECT 'ü' COLLATE de < 'x' COLLATE de 192 ---- 193 true 194 195 196 statement error syntax error: invalid locale e: language: tag is not well-formed 197 CREATE TABLE e1 ( 198 a STRING COLLATE e 199 ) 200 201 statement error multiple COLLATE declarations for column "a" 202 CREATE TABLE e2 ( 203 a STRING COLLATE en COLLATE de 204 ) 205 206 statement error COLLATE declaration for non-string-typed column "a" 207 CREATE TABLE e3 ( 208 a INT COLLATE en 209 ) 210 211 statement ok 212 CREATE TABLE t ( 213 a STRING COLLATE en 214 ) 215 216 query TT 217 SHOW CREATE TABLE t 218 ---- 219 t CREATE TABLE t ( 220 a STRING COLLATE en NULL, 221 FAMILY "primary" (a, rowid) 222 ) 223 224 statement ok 225 INSERT INTO t VALUES 226 ('A' COLLATE en), 227 ('B' COLLATE en), 228 ('a' COLLATE en), 229 ('b' COLLATE en), 230 ('x' COLLATE en), 231 ('ü' COLLATE en) 232 233 statement error value type collatedstring{de} doesn't match type collatedstring{en} of column "a" 234 INSERT INTO t VALUES ('X' COLLATE de) 235 236 query T 237 SELECT a FROM t ORDER BY t.a 238 ---- 239 a 240 A 241 b 242 B 243 ü 244 x 245 246 query T 247 SELECT a FROM t ORDER BY t.a COLLATE da 248 ---- 249 a 250 A 251 b 252 B 253 x 254 ü 255 256 query T 257 SELECT a FROM t WHERE a = 'A' COLLATE en; 258 ---- 259 A 260 261 query T 262 SELECT 'a' COLLATE en::STRING || 'b' 263 ---- 264 ab 265 266 query B 267 SELECT 't' COLLATE en::BOOLEAN 268 ---- 269 true 270 271 query I 272 SELECT '42' COLLATE en::INTEGER 273 ---- 274 42 275 276 query R 277 SELECT '42.0' COLLATE en::FLOAT 278 ---- 279 42 280 281 query R 282 SELECT '42.0' COLLATE en::DECIMAL 283 ---- 284 42.0 285 286 query T 287 SELECT 'a' COLLATE en::BYTES 288 ---- 289 a 290 291 query T 292 SELECT '2017-01-10 16:05:50.734049+00:00' COLLATE en::TIMESTAMP 293 ---- 294 2017-01-10 16:05:50.734049 +0000 +0000 295 296 query T 297 SELECT '2017-01-10 16:05:50.734049+00:00' COLLATE en::TIMESTAMPTZ 298 ---- 299 2017-01-10 16:05:50.734049 +0000 UTC 300 301 query T 302 SELECT '40 days' COLLATE en::INTERVAL 303 ---- 304 40 days 305 306 statement ok 307 CREATE TABLE foo(a STRING COLLATE en_u_ks_level2) 308 309 statement ok 310 PREPARE x AS INSERT INTO foo VALUES ($1 COLLATE en_u_ks_level2) RETURNING a 311 312 query T 313 EXECUTE x(NULL) 314 ---- 315 NULL 316 317 query T 318 SELECT a FROM foo 319 ---- 320 NULL 321 322 # Regression test for #24449 323 324 statement ok 325 INSERT INTO foo VALUES ('aBcD' COLLATE en_u_ks_level2) 326 327 query T 328 SELECT * FROM foo WHERE a = 'aBcD' COLLATE en_u_ks_level2 329 ---- 330 aBcD 331 332 query T 333 SELECT * FROM foo WHERE a = 'abcd' COLLATE en_u_ks_level2 334 ---- 335 aBcD 336 337 # Test quoted collations. 338 339 statement ok 340 CREATE TABLE quoted_coll ( 341 a STRING COLLATE "en", 342 b STRING COLLATE "en_US", 343 c STRING COLLATE "en-Us" DEFAULT ('c' COLLATE "en-Us"), 344 d STRING COLLATE "en-u-ks-level1" DEFAULT ('d'::STRING COLLATE "en-u-ks-level1"), 345 e STRING COLLATE "en-us" AS (a COLLATE "en-us") STORED, 346 FAMILY "primary" (a, b, c, d, e, rowid) 347 ) 348 349 query TT 350 SHOW CREATE TABLE quoted_coll 351 ---- 352 quoted_coll CREATE TABLE quoted_coll ( 353 a STRING COLLATE en NULL, 354 b STRING COLLATE en_US NULL, 355 c STRING COLLATE en_Us NULL DEFAULT 'c':::STRING COLLATE en_us, 356 d STRING COLLATE en_u_ks_level1 NULL DEFAULT 'd':::STRING::STRING COLLATE en_u_ks_level1, 357 e STRING COLLATE en_us NULL AS (a COLLATE en_us) STORED, 358 FAMILY "primary" (a, b, c, d, e, rowid) 359 ) 360 361 # Regression for #46570. 362 statement ok 363 CREATE TABLE t46570(c0 BOOL, c1 STRING COLLATE en); 364 CREATE INDEX ON t46570(rowid, c1 DESC); 365 INSERT INTO t46570(c1, rowid) VALUES('' COLLATE en, 0); 366 UPSERT INTO t46570(rowid) VALUES (0), (1)