Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Update a couple of test case files. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-planner |
Files: | files | file ages | folders |
SHA1: |
3b9ae7c6dbbcd88e2b2f7a66b0bcb7c9 |
User & Date: | dan 2013-07-19 20:03:48.077 |
Context
2013-07-20
| ||
16:10 | Another problem with LIKE optimization. check-in: 3eaf921c5b user: dan tags: nextgen-query-planner | |
2013-07-19
| ||
20:03 | Update a couple of test case files. check-in: 3b9ae7c6db user: dan tags: nextgen-query-planner | |
19:20 | Fix a bug to do with ORDER BY and collation sequences. check-in: 57b8ae848c user: dan tags: nextgen-query-planner | |
Changes
Changes to test/between.test.
︙ | ︙ | |||
44 45 46 47 48 49 50 | CREATE INDEX i1zyx ON t1(z,y,x); COMMIT; } } {} # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword depending on whether or not any sorting | | > > > > > > > > > > | | | | | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | CREATE INDEX i1zyx ON t1(z,y,x); COMMIT; } } {} # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword depending on whether or not any sorting # is done. Then it appends the names of the table and index used. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } do_test between-1.1.1 { queryplan { SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 i1w} do_test between-1.1.2 { queryplan { SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 t1} do_test between-1.2.1 { queryplan { SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 i1w} do_test between-1.2.2 { queryplan { SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 t1} do_test between-1.3.1 { queryplan { SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 i1w} do_test between-1.3.2 { queryplan { SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 t1} do_test between-1.4 { queryplan { SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w } } {5 2 36 38 6 2 49 51 sort t1 t1} do_test between-1.5.1 { queryplan { SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w } } {4 2 25 27 sort t1 i1zyx} do_test between-1.5.2 { queryplan { SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w } } {4 2 25 27 sort t1 i1zyx} do_test between-1.5.3 { queryplan { SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w } } {4 2 25 27 sort t1 t1} finish_test |
Changes to test/collate4.test.
︙ | ︙ | |||
85 86 87 88 89 90 91 | cksort {SELECT b FROM collate4t1 ORDER BY b} } {{} A B a b nosort} do_test collate4-1.1.5 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.6 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} | | | 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | cksort {SELECT b FROM collate4t1 ORDER BY b} } {{} A B a b nosort} do_test collate4-1.1.5 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.6 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} } {{} A a B b sort} do_test collate4-1.1.7 { execsql { CREATE TABLE collate4t2( a PRIMARY KEY COLLATE NOCASE, b UNIQUE COLLATE TEXT ); |
︙ | ︙ | |||
162 163 164 165 166 167 168 | INSERT INTO collate4t4 VALUES( 'A', 'A' ); CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT); CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE); } } {} do_test collate4-1.1.22 { cksort {SELECT a FROM collate4t4 ORDER BY a} | | | | 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | INSERT INTO collate4t4 VALUES( 'A', 'A' ); CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT); CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE); } } {} do_test collate4-1.1.22 { cksort {SELECT a FROM collate4t4 ORDER BY a} } {{} A a B b sort} do_test collate4-1.1.23 { cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE} } {{} A a B b sort} do_test collate4-1.1.24 { cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.25 { cksort {SELECT b FROM collate4t4 ORDER BY b} } {{} A B a b sort} do_test collate4-1.1.26 { |
︙ | ︙ | |||
213 214 215 216 217 218 219 | cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} } {{} A B a b sort} do_test collate4-1.2.4 { cksort {SELECT a FROM collate4t1 ORDER BY a, b} } {{} A a B b nosort} do_test collate4-1.2.5 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} | | | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} } {{} A B a b sort} do_test collate4-1.2.4 { cksort {SELECT a FROM collate4t1 ORDER BY a, b} } {{} A a B b nosort} do_test collate4-1.2.5 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} } {{} A a B b sort} do_test collate4-1.2.6 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text} } {{} A a B b nosort} do_test collate4-1.2.7 { execsql { CREATE TABLE collate4t2( |
︙ | ︙ | |||
262 263 264 265 266 267 268 | INSERT INTO collate4t3 VALUES( 'B', 'B' ); INSERT INTO collate4t3 VALUES( 'A', 'A' ); CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE); } } {} do_test collate4-1.2.15 { cksort {SELECT a FROM collate4t3 ORDER BY a} | | | | 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | INSERT INTO collate4t3 VALUES( 'B', 'B' ); INSERT INTO collate4t3 VALUES( 'A', 'A' ); CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE); } } {} do_test collate4-1.2.15 { cksort {SELECT a FROM collate4t3 ORDER BY a} } {{} A a B b sort} do_test collate4-1.2.16 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase} } {{} A a B b sort} do_test collate4-1.2.17 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text} } {{} A B a b nosort} do_test collate4-1.2.18 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b} } {{} A B a b sort} do_test collate4-1.2.19 { |
︙ | ︙ | |||
605 606 607 608 609 610 611 | execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); } count { SELECT min(a) FROM collate4t1; } | | | | 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 | execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); } count { SELECT min(a) FROM collate4t1; } } {10 9} do_test collate4-4.6 { count { SELECT max(a) FROM collate4t1; } } {20 9} do_test collate4-4.7 { execsql { DROP TABLE collate4t1; } } {} # Also test the scalar min() and max() functions. |
︙ | ︙ |