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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set 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# 25proc 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 32do_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 40do_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 44do_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 49do_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 58do_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 64do_test 2.2 { 65 cksort { SELECT * FROM t2 WHERE a = 10 ORDER BY a, b, c } 66} {nosort} 67 68do_test 2.3 { 69 cksort { SELECT * FROM t2 WHERE b = 10 ORDER BY a, b, c } 70} {sort} 71 72do_test 2.4 { 73 cksort { SELECT * FROM t2 WHERE a IS NULL ORDER BY a, b, c } 74} {sort} 75 76finish_test 77