gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/orderby5.test (about) 1 # 2013-06-14 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing that the optimizations that disable 13 # ORDER BY clauses work correctly 14 # 15 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set ::testprefix orderby5 20 21 # Generate test data for a join. Verify that the join gets the 22 # correct answer. 23 # 24 do_execsql_test 1.1 { 25 CREATE TABLE t1(a,b,c); 26 CREATE INDEX t1bc ON t1(b,c); 27 28 EXPLAIN QUERY PLAN 29 SELECT DISTINCT a, b, c FROM t1 WHERE a=0; 30 } {~/B-TREE/} 31 do_execsql_test 1.2.1 { 32 EXPLAIN QUERY PLAN 33 SELECT DISTINCT a, c, b FROM t1 WHERE a=0; 34 } {~/B-TREE/} 35 do_execsql_test 1.2.2 { 36 EXPLAIN QUERY PLAN 37 SELECT DISTINCT a, c, b FROM t1 WHERE a='xyz' COLLATE nocase; 38 } {/B-TREE/} 39 do_execsql_test 1.2.3 { 40 EXPLAIN QUERY PLAN 41 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz'; 42 } {/B-TREE/} 43 do_execsql_test 1.2.4 { 44 EXPLAIN QUERY PLAN 45 SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz' COLLATE nocase; 46 } {~/B-TREE/} 47 do_execsql_test 1.3 { 48 EXPLAIN QUERY PLAN 49 SELECT DISTINCT b, a, c FROM t1 WHERE a=0; 50 } {~/B-TREE/} 51 do_execsql_test 1.4 { 52 EXPLAIN QUERY PLAN 53 SELECT DISTINCT b, c, a FROM t1 WHERE a=0; 54 } {~/B-TREE/} 55 do_execsql_test 1.5 { 56 EXPLAIN QUERY PLAN 57 SELECT DISTINCT c, a, b FROM t1 WHERE a=0; 58 } {~/B-TREE/} 59 do_execsql_test 1.6 { 60 EXPLAIN QUERY PLAN 61 SELECT DISTINCT c, b, a FROM t1 WHERE a=0; 62 } {~/B-TREE/} 63 do_execsql_test 1.7 { 64 EXPLAIN QUERY PLAN 65 SELECT DISTINCT c, b, a FROM t1 WHERE +a=0; 66 } {/B-TREE/} 67 68 # In some cases, it is faster to do repeated index lookups than it is to 69 # sort. But in other cases, it is faster to sort than to do repeated index 70 # lookups. 71 # 72 do_execsql_test 2.1a { 73 CREATE TABLE t2(a,b,c); 74 CREATE INDEX t2bc ON t2(b,c); 75 ANALYZE; 76 INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9'); 77 INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5'); 78 ANALYZE sqlite_master; 79 80 EXPLAIN QUERY PLAN 81 SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c; 82 } {~/B-TREE/} 83 84 do_execsql_test 2.1b { 85 EXPLAIN QUERY PLAN 86 SELECT * FROM t1 WHERE likelihood(a=0, 0.03) ORDER BY a, b, c; 87 } {/B-TREE/} 88 89 do_execsql_test 2.2 { 90 EXPLAIN QUERY PLAN 91 SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c; 92 } {/B-TREE/} 93 do_execsql_test 2.3 { 94 EXPLAIN QUERY PLAN 95 SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c; 96 } {~/B-TREE/} 97 do_execsql_test 2.4 { 98 EXPLAIN QUERY PLAN 99 SELECT * FROM t1 WHERE a=0 ORDER BY b, c, a; 100 } {~/B-TREE/} 101 do_execsql_test 2.5 { 102 EXPLAIN QUERY PLAN 103 SELECT * FROM t1 WHERE a=0 ORDER BY a, c, b; 104 } {/B-TREE/} 105 do_execsql_test 2.6 { 106 EXPLAIN QUERY PLAN 107 SELECT * FROM t1 WHERE a=0 ORDER BY c, a, b; 108 } {/B-TREE/} 109 do_execsql_test 2.7 { 110 EXPLAIN QUERY PLAN 111 SELECT * FROM t1 WHERE a=0 ORDER BY c, b, a; 112 } {/B-TREE/} 113 114 115 do_execsql_test 3.0 { 116 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f); 117 CREATE INDEX t3bcde ON t3(b, c, d, e); 118 EXPLAIN QUERY PLAN 119 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC; 120 } {~/B-TREE/} 121 do_execsql_test 3.1 { 122 DROP TABLE t3; 123 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid; 124 CREATE INDEX t3bcde ON t3(b, c, d, e); 125 EXPLAIN QUERY PLAN 126 SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC; 127 } {~/B-TREE/} 128 129 #------------------------------------------------------------------------- 130 do_execsql_test 4.1.0 { 131 CREATE TABLE t4(b COLLATE nocase); 132 INSERT INTO t4 VALUES('abc'); 133 INSERT INTO t4 VALUES('ABC'); 134 INSERT INTO t4 VALUES('aBC'); 135 } 136 do_execsql_test 4.1.1 { 137 SELECT * FROM t4 ORDER BY b COLLATE binary 138 } {ABC aBC abc} 139 do_execsql_test 4.1.2 { 140 SELECT * FROM t4 WHERE b='abc' ORDER BY b COLLATE binary 141 } {ABC aBC abc} 142 143 do_execsql_test 4.2.1 { 144 CREATE TABLE Records(typeID INTEGER, key TEXT COLLATE nocase, value TEXT); 145 CREATE INDEX RecordsIndex ON Records(typeID, key, value); 146 } 147 do_execsql_test 4.2.2 { 148 explain query plan 149 SELECT typeID, key, value FROM Records 150 WHERE typeID = 2 AND key = 'x' 151 ORDER BY key, value; 152 } {~/TEMP B-TREE/} 153 do_execsql_test 4.2.3 { 154 explain query plan 155 SELECT typeID, key, value FROM Records 156 WHERE typeID = 2 AND (key = 'x' COLLATE binary) 157 ORDER BY key, value; 158 } {~/TEMP B-TREE/} 159 do_execsql_test 4.2.4 { 160 explain query plan 161 SELECT typeID, key, value FROM Records 162 WHERE typeID = 2 163 ORDER BY key, value; 164 } {~/TEMP B-TREE/} 165 166 db collate hello [list string match] 167 do_execsql_test 4.3.1 { 168 CREATE TABLE t5(a INTEGER PRIMARY KEY, b COLLATE hello, c, d); 169 } 170 db close 171 sqlite3 db test.db 172 do_catchsql_test 4.3.2 { 173 SELECT a FROM t5 WHERE b='def' ORDER BY b; 174 } {1 {no such collation sequence: hello}} 175 176 # 2020-02-13 ticket 41c1456a6e61c0e7 177 do_execsql_test 4.4.0 { 178 DROP TABLE t1; 179 CREATE TABLE t1(a); 180 DROP TABLE t2; 181 CREATE TABLE t2(b INTEGER PRIMARY KEY, c INT); 182 SELECT DISTINCT * 183 FROM t1 LEFT JOIN t2 ON b=c AND b=(SELECT a FROM t1) 184 WHERE c>10; 185 } {} 186 187 finish_test