AryaWu/sqlite
0
1#2# 2001 September 153#4# The author disclaims copyright to this source code. In place of5# a legal notice, here is a blessing:6#7# May you do good and not evil.8# May you find forgiveness for yourself and forgive others.9# May you share freely, never taking more than you give.10#11#***********************************************************************12# This file implements regression tests for SQLite library. The13# focus of this script is testing collation sequences.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix collate119 20#21# Tests are roughly organised as follows:22#23# collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.24# collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.25# collate1-3.* - ORDER BY using a default collation type. Also that an 26# explicit collate type overrides a default collate type.27# collate1-4.* - ORDER BY using a data type.28#29 30#31# Collation type 'HEX'. If an argument can be interpreted as a hexadecimal32# number, then it is converted to one before the comparison is performed. 33# Numbers are less than other strings. If neither argument is a number, 34# [string compare] is used.35#36db collate HEX hex_collate37proc hex_collate {lhs rhs} {38 set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]39 set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]40 if {$lhs_ishex && $rhs_ishex} { 41 set lhsx [scan $lhs %x]42 set rhsx [scan $rhs %x]43 if {$lhs < $rhs} {return -1}44 if {$lhs == $rhs} {return 0}45 if {$lhs > $rhs} {return 1}46 }47 if {$lhs_ishex} {48 return -1;49 }50 if {$rhs_ishex} {51 return 1;52 }53 return [string compare $lhs $rhs]54}55db function hex {format 0x%X}56 57# Mimic the SQLite 2 collation type NUMERIC.58db collate numeric numeric_collate59proc numeric_collate {lhs rhs} {60 if {$lhs == $rhs} {return 0} 61 return [expr ($lhs>$rhs)?1:-1]62}63 64do_test collate1-1.0 {65 execsql {66 CREATE TABLE collate1t1(c1, c2);67 INSERT INTO collate1t1 VALUES(45, hex(45));68 INSERT INTO collate1t1 VALUES(NULL, NULL);69 INSERT INTO collate1t1 VALUES(281, hex(281));70 }71} {}72do_test collate1-1.1 {73 execsql {74 SELECT c2 FROM collate1t1 ORDER BY 1;75 }76} {{} 0x119 0x2D}77do_test collate1-1.2 {78 execsql {79 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;80 }81} {{} 0x2D 0x119}82do_test collate1-1.3 {83 execsql {84 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;85 }86} {0x119 0x2D {}}87do_test collate1-1.4 {88 execsql {89 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;90 }91} {{} 0x2D 0x119}92do_test collate1-1.5 {93 execsql {94 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 195 }96} {{} 0x2D 0x119}97do_test collate1-1.6 {98 execsql {99 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC100 }101} {{} 0x2D 0x119}102do_test collate1-1.7 {103 execsql {104 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC105 }106} {0x119 0x2D {}}107do_test collate1-1.99 {108 execsql {109 DROP TABLE collate1t1;110 }111} {}112 113do_test collate1-2.0 {114 execsql {115 CREATE TABLE collate1t1(c1, c2);116 INSERT INTO collate1t1 VALUES('5', '0x11');117 INSERT INTO collate1t1 VALUES('5', '0xA');118 INSERT INTO collate1t1 VALUES(NULL, NULL);119 INSERT INTO collate1t1 VALUES('7', '0xA');120 INSERT INTO collate1t1 VALUES('11', '0x11');121 INSERT INTO collate1t1 VALUES('11', '0x101');122 }123} {}124do_test collate1-2.2 {125 execsql {126 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;127 }128} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}129do_test collate1-2.3 {130 execsql {131 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;132 }133} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}134do_test collate1-2.4 {135 execsql {136 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;137 }138} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}139do_test collate1-2.5 {140 execsql {141 SELECT c1, c2 FROM collate1t1 142 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;143 }144} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}145do_test collate1-2.6 {146 execsql {147 SELECT c1, c2 FROM collate1t1 148 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;149 }150} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}151do_test collate1-2.12.1 {152 execsql {153 SELECT c1 COLLATE numeric, c2 FROM collate1t1 154 ORDER BY 1, 2 COLLATE hex;155 }156} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}157do_test collate1-2.12.2 {158 execsql {159 SELECT c1 COLLATE hex, c2 FROM collate1t1 160 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;161 }162} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}163do_test collate1-2.12.3 {164 execsql {165 SELECT c1, c2 COLLATE hex FROM collate1t1 166 ORDER BY 1 COLLATE numeric, 2;167 }168} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}169do_test collate1-2.12.4 {170 execsql {171 SELECT c1 COLLATE numeric, c2 COLLATE hex172 FROM collate1t1 173 ORDER BY 1, 2;174 }175} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}176do_test collate1-2.13 {177 execsql {178 SELECT c1 COLLATE binary, c2 COLLATE hex179 FROM collate1t1180 ORDER BY 1, 2;181 }182} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}183do_test collate1-2.14 {184 execsql {185 SELECT c1, c2186 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;187 }188} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}189do_test collate1-2.15 {190 execsql {191 SELECT c1 COLLATE binary, c2 COLLATE hex192 FROM collate1t1 193 ORDER BY 1 DESC, 2 DESC;194 }195} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}196do_test collate1-2.16 {197 execsql {198 SELECT c1 COLLATE hex, c2 COLLATE binary199 FROM collate1t1 200 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;201 }202} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}203do_test collate1-2.99 {204 execsql {205 DROP TABLE collate1t1;206 }207} {}208 209#210# These tests ensure that the default collation type for a column is used 211# by an ORDER BY clause correctly. The focus is all the different ways212# the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.213#214do_test collate1-3.0 {215 execsql {216 CREATE TABLE collate1t1(a COLLATE hex, b);217 INSERT INTO collate1t1 VALUES( '0x5', 5 );218 INSERT INTO collate1t1 VALUES( '1', 1 );219 INSERT INTO collate1t1 VALUES( '0x45', 69 );220 INSERT INTO collate1t1 VALUES( NULL, NULL );221 SELECT * FROM collate1t1 ORDER BY a;222 }223} {{} {} 1 1 0x5 5 0x45 69}224 225do_test collate1-3.1 {226 execsql {227 SELECT * FROM collate1t1 ORDER BY 1;228 }229} {{} {} 1 1 0x5 5 0x45 69}230do_test collate1-3.2 {231 execsql {232 SELECT * FROM collate1t1 ORDER BY collate1t1.a;233 }234} {{} {} 1 1 0x5 5 0x45 69}235do_test collate1-3.3 {236 execsql {237 SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;238 }239} {{} {} 1 1 0x5 5 0x45 69}240do_test collate1-3.4 {241 execsql {242 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;243 }244} {{} {} 1 1 0x5 5 0x45 69}245do_test collate1-3.5 {246 execsql {247 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;248 }249} {{} {} 0x45 69 0x5 5 1 1}250do_test collate1-3.5.1 {251 execsql {252 SELECT a COLLATE binary as c1, b as c2253 FROM collate1t1 ORDER BY c1;254 }255} {{} {} 0x45 69 0x5 5 1 1}256do_test collate1-3.6 {257 execsql {258 DROP TABLE collate1t1;259 }260} {}261 262# Update for SQLite version 3. The collate1-4.* test cases were written263# before manifest types were introduced. The following test cases still264# work, due to the 'affinity' mechanism, but they don't prove anything265# about collation sequences.266#267do_test collate1-4.0 {268 execsql {269 CREATE TABLE collate1t1(c1 numeric, c2 text);270 INSERT INTO collate1t1 VALUES(1, 1);271 INSERT INTO collate1t1 VALUES(12, 12);272 INSERT INTO collate1t1 VALUES(NULL, NULL);273 INSERT INTO collate1t1 VALUES(101, 101);274 }275} {}276do_test collate1-4.1 {277 execsql {278 SELECT c1 FROM collate1t1 ORDER BY 1;279 }280} {{} 1 12 101}281do_test collate1-4.2 {282 execsql {283 SELECT c2 FROM collate1t1 ORDER BY 1;284 }285} {{} 1 101 12}286do_test collate1-4.3 {287 execsql {288 SELECT c2+0 FROM collate1t1 ORDER BY 1;289 }290} {{} 1 12 101}291do_test collate1-4.4 {292 execsql {293 SELECT c1||'' FROM collate1t1 ORDER BY 1;294 }295} {{} 1 101 12}296do_test collate1-4.4.1 {297 execsql {298 SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;299 }300} {{} 1 12 101}301do_test collate1-4.5 {302 execsql {303 DROP TABLE collate1t1;304 }305} {}306 307# A problem reported on the mailing list: A CREATE TABLE statement308# is allowed to have two or more COLLATE clauses on the same column.309# That probably ought to be an error, but we allow it for backwards310# compatibility. Just make sure it works and doesn't leak memory.311#312do_test collate1-5.1 {313 execsql {314 CREATE TABLE c5(315 id INTEGER PRIMARY KEY,316 a TEXT COLLATE binary COLLATE nocase COLLATE rtrim,317 b TEXT COLLATE nocase COLLATE binary,318 c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase319 );320 INSERT INTO c5 VALUES(1, 'abc','abc','abc');321 INSERT INTO c5 VALUES(2, 'abc ','ABC','ABC');322 SELECT id FROM c5 WHERE a='abc' ORDER BY id;323 }324} {1 2}325do_test collate1-5.2 {326 execsql {327 SELECT id FROM c5 WHERE b='abc' ORDER BY id;328 }329} {1}330do_test collate1-5.3 {331 execsql {332 SELECT id FROM c5 WHERE c='abc' ORDER BY id;333 }334} {1 2}335 336 337 338#-------------------------------------------------------------------------339# Fix problems with handling collation sequences named '"""'.340#341sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1342do_execsql_test 6.1 {343 SELECT """""""";344} {\"\"\"}345 346do_catchsql_test 6.2 {347 CREATE TABLE x1(a);348 SELECT a FROM x1 ORDER BY a COLLATE """""""";349} {1 {no such collation sequence: """}}350 351do_catchsql_test 6.3 {352 SELECT a FROM x1 ORDER BY 1 COLLATE """""""";353} {1 {no such collation sequence: """}}354 355do_catchsql_test 6.4 {356 SELECT 0 UNION SELECT 0 ORDER BY 1 COLLATE """""""";357} {1 {no such collation sequence: """}}358 359db collate {"""} [list string compare -nocase]360 361do_execsql_test 6.5 {362 PRAGMA foreign_keys = ON;363 CREATE TABLE p1(a PRIMARY KEY COLLATE '"""');364 CREATE TABLE c1(x, y REFERENCES p1);365} {}366 367do_execsql_test 6.6 { 368 INSERT INTO p1 VALUES('abc'); 369 INSERT INTO c1 VALUES(1, 'ABC'); 370}371 372ifcapable foreignkey {373 do_catchsql_test 6.7 { 374 DELETE FROM p1 WHERE rowid = 1 375 } {1 {FOREIGN KEY constraint failed}}376}377 378do_execsql_test 6.8 { 379 INSERT INTO p1 VALUES('abb');380 INSERT INTO p1 VALUES('wxz');381 INSERT INTO p1 VALUES('wxy');382 383 INSERT INTO c1 VALUES(2, 'abb');384 INSERT INTO c1 VALUES(3, 'wxz');385 INSERT INTO c1 VALUES(4, 'WXY');386 SELECT x, y FROM c1 ORDER BY y COLLATE """""""";387} {2 abb 1 ABC 4 WXY 3 wxz}388 389# 2015-04-15: Nested COLLATE operators390#391do_execsql_test 7.0 {392 SELECT 'abc' UNION ALL SELECT 'DEF'393 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE nocase;394} {abc DEF}395do_execsql_test 7.1 {396 SELECT 'abc' UNION ALL SELECT 'DEF'397 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE binary;398} {DEF abc}399do_execsql_test 7.2 {400 SELECT 'abc' UNION ALL SELECT 'DEF'401 ORDER BY 1 COLLATE binary COLLATE binary COLLATE binary COLLATE nocase;402} {abc DEF}403 404# 2019-06-14405# https://sqlite.org/src/info/f1580ba1b574e9e9406#407do_execsql_test 8.0 {408 SELECT ' ' > char(20) COLLATE rtrim;409} 0410do_execsql_test 8.1 {411 SELECT '' < char(20) COLLATE rtrim;412} 1413do_execsql_test 8.2 {414 DROP TABLE IF EXISTS t0;415 CREATE TABLE t0(c0 COLLATE RTRIM, c1 BLOB UNIQUE,416 PRIMARY KEY (c0, c1)) WITHOUT ROWID;417 INSERT INTO t0 VALUES (123, 3), (' ', 1), (' ', 2), ('', 4);418 SELECT * FROM t0 WHERE c1 = 1;419} {{ } 1}420 421# 2019-10-09422# ALWAYS() macro fails following OOM423# Problem detected by dbsqlfuzz.424#425do_execsql_test 9.0 {426 CREATE TABLE t1(a, b);427 CREATE TABLE t2(c, d);428}429 430do_faultsim_test 9.1 -faults oom* -body {431 execsql {432 SELECT * FROM (433 SELECT b COLLATE nocase IN (SELECT c FROM t2) FROM t1434 );435 }436} -test {437 faultsim_test_result {0 {}}438}439 440# 2020-01-03 dbsqlfuzz find441#442reset_db443do_catchsql_test 10.0 {444 CREATE TABLE t1(a INTEGER PRIMARY KEY,b);445 INSERT INTO t1 VALUES(0,NULL);446 CREATE TABLE t2(x UNIQUE);447 CREATE VIEW v1a(z,y) AS SELECT x COLLATE x FROM t2;448 SELECT a,b,z,y,'' FROM t1 JOIN v1a ON b IS NOT FALSE;449} {1 {no such collation sequence: x}}450 451 452finish_test453