gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt-2a5629202f.test (about) 1 # 2012 April 19 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # The tests in this file were used while developing the SQLite 4 code. 12 # 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix tkt-2a5629202f 17 18 # This procedure executes the SQL. Then it checks to see if the OP_Sort 19 # opcode was executed. If an OP_Sort did occur, then "sort" is appended 20 # to the result. If no OP_Sort happened, then "nosort" is appended. 21 # 22 # This procedure is used to check to make sure sorting is or is not 23 # occurring as expected. 24 # 25 proc cksort {sql} { 26 set data [execsql $sql] 27 if {[db status sort]} {set x sort} {set x nosort} 28 lappend data $x 29 return $data 30 } 31 32 do_execsql_test 1.1 { 33 CREATE TABLE t8(b TEXT, c TEXT); 34 INSERT INTO t8 VALUES('a', 'one'); 35 INSERT INTO t8 VALUES('b', 'two'); 36 INSERT INTO t8 VALUES(NULL, 'three'); 37 INSERT INTO t8 VALUES(NULL, 'four'); 38 } 39 40 do_execsql_test 1.2 { 41 SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c 42 } {null/four null/three a/one b/two} 43 44 do_execsql_test 1.3 { 45 CREATE UNIQUE INDEX i1 ON t8(b); 46 SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c 47 } {null/four null/three a/one b/two} 48 49 do_execsql_test 1.4 { 50 DROP INDEX i1; 51 CREATE UNIQUE INDEX i1 ON t8(b, c); 52 SELECT coalesce(b, 'null') || '/' || c FROM t8 x ORDER BY x.b, x.c 53 } {null/four null/three a/one b/two} 54 55 #------------------------------------------------------------------------- 56 # 57 58 do_execsql_test 2.1 { 59 CREATE TABLE t2(a, b NOT NULL, c); 60 CREATE UNIQUE INDEX t2ab ON t2(a, b); 61 CREATE UNIQUE INDEX t2ba ON t2(b, a); 62 } 63 64 do_test 2.2 { 65 cksort { SELECT * FROM t2 WHERE a = 10 ORDER BY a, b, c } 66 } {nosort} 67 68 do_test 2.3 { 69 cksort { SELECT * FROM t2 WHERE b = 10 ORDER BY a, b, c } 70 } {sort} 71 72 do_test 2.4 { 73 cksort { SELECT * FROM t2 WHERE a IS NULL ORDER BY a, b, c } 74 } {sort} 75 76 finish_test