Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | New test case for block-sorting. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | orderby-planning |
Files: | files | file ages | folders |
SHA1: |
e70cfa28aa393661ccc742ecd5e672d8 |
User & Date: | drh 2014-03-21 15:24:07.126 |
Context
2014-03-21
| ||
18:45 | Merge the OFFSET-on-query-without-FROM fix from trunk. (check-in: 71e9ae72c2 user: drh tags: orderby-planning) | |
15:24 | New test case for block-sorting. (check-in: e70cfa28aa user: drh tags: orderby-planning) | |
2014-03-20
| ||
20:56 | Merge trunk fixes for "x IN (?)" handling. (check-in: eca35871c3 user: drh tags: orderby-planning) | |
Changes
Added test/orderby6.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 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 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | # 2014-03-21 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing that the block-sort optimization. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix orderby6 # Run all tests twice. Once with a normal table and a second time # with a WITHOUT ROWID table # foreach {tn rowidclause} {1 {} 2 {WITHOUT ROWID}} { # Construct a table with 1000 rows and a split primary key # reset_db do_test $tn.1 { db eval "CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c)) $rowidclause;" db eval { WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000) INSERT INTO t1 SELECT x, x%40, x/40 FROM cnt; } } {} # Run various ORDER BY queries that can benefit from block-sort. # Compare the output to the same output using a full-sort enforced # by adding + to each term of the ORDER BY clause. # do_execsql_test $tn.2 { SELECT b,a,c FROM t1 ORDER BY b,a,c; } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+a,+c}] do_execsql_test $tn.3 { SELECT b,a,c FROM t1 ORDER BY b,c DESC,a; } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+c DESC,+a}] do_execsql_test $tn.4 { SELECT b,a,c FROM t1 ORDER BY b DESC,c,a; } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+c,+a}] do_execsql_test $tn.5 { SELECT b,a,c FROM t1 ORDER BY b DESC,a,c; } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+a,+c}] # LIMIT and OFFSET clauses on block-sort queries. # do_execsql_test $tn.11 { SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20; } {840 880 920 960 1000 1 41 81 121 161} do_execsql_test $tn.11x { SELECT a FROM t1 ORDER BY +b, a LIMIT 10 OFFSET 20; } {840 880 920 960 1000 1 41 81 121 161} do_execsql_test $tn.12 { SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 20; } {839 879 919 959 999 38 78 118 158 198} do_execsql_test $tn.12 { SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 20; } {839 879 919 959 999 38 78 118 158 198} do_execsql_test $tn.13 { SELECT a FROM t1 ORDER BY b, a DESC LIMIT 10 OFFSET 45; } {161 121 81 41 1 962 922 882 842 802} do_execsql_test $tn.13x { SELECT a FROM t1 ORDER BY +b, a DESC LIMIT 10 OFFSET 45; } {161 121 81 41 1 962 922 882 842 802} do_execsql_test $tn.14 { SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 45; } {838 878 918 958 998 37 77 117 157 197} do_execsql_test $tn.14x { SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 45; } {838 878 918 958 998 37 77 117 157 197} # Many test cases where the LIMIT+OFFSET window is in various # alignments with block-sort boundaries. # foreach {tx limit offset orderby} { 1 10 24 {+b,+a} 2 10 25 {+b,+a} 3 10 26 {+b,+a} 4 10 39 {+b,+a} 5 10 40 {+b,+a} 6 10 41 {+b,+a} 7 27 24 {+b,+a} 8 27 49 {+b,+a} 11 10 24 {+b DESC,+a} 12 10 25 {+b DESC,+a} 13 10 26 {+b DESC,+a} 14 10 39 {+b DESC,+a} 15 10 40 {+b DESC,+a} 16 10 41 {+b DESC,+a} 17 27 24 {+b DESC,+a} 18 27 49 {+b DESC,+a} 21 10 24 {+b,+a DESC} 22 10 25 {+b,+a DESC} 23 10 26 {+b,+a DESC} 24 10 39 {+b,+a DESC} 25 10 40 {+b,+a DESC} 26 10 41 {+b,+a DESC} 27 27 24 {+b,+a DESC} 28 27 49 {+b,+a DESC} 31 10 24 {+b DESC,+a DESC} 32 10 25 {+b DESC,+a DESC} 33 10 26 {+b DESC,+a DESC} 34 10 39 {+b DESC,+a DESC} 35 10 40 {+b DESC,+a DESC} 36 10 41 {+b DESC,+a DESC} 37 27 24 {+b DESC,+a DESC} 38 27 49 {+b DESC,+a DESC} } { set sql1 "SELECT a FROM t1 ORDER BY $orderby LIMIT $limit OFFSET $offset;" set sql2 [string map {+ {}} $sql1] # puts $sql2\n$sql1\n[db eval $sql2] do_test $tn.21.$tx {db eval $::sql2} [db eval $sql1] } ######################################################################## # A second test table, t2, has many columns open to sorting. do_test $tn.31 { db eval "CREATE TABLE t2(a,b,c,d,e,f,PRIMARY KEY(b,c,d,e,f)) $rowidclause;" db eval { WITH RECURSIVE cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<242) INSERT INTO t2 SELECT x, x%3, (x/3)%3, (x/9)%3, (x/27)%3, (x/81)%3 FROM cnt; } } {} do_execsql_test $tn.32 { SELECT a FROM t2 ORDER BY b,c,d,e,f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] do_execsql_test $tn.33 { SELECT a FROM t2 ORDER BY b,c,d,e,+f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] do_execsql_test $tn.34 { SELECT a FROM t2 ORDER BY b,c,d,+e,+f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] do_execsql_test $tn.35 { SELECT a FROM t2 ORDER BY b,c,+d,+e,+f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] do_execsql_test $tn.36 { SELECT a FROM t2 ORDER BY b,+c,+d,+e,+f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] do_execsql_test $tn.37 { SELECT a FROM t2 ORDER BY b,c,d,e,f DESC; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC;}] do_execsql_test $tn.38 { SELECT a FROM t2 ORDER BY b,c,d,e DESC,f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e DESC,+f;}] do_execsql_test $tn.39 { SELECT a FROM t2 ORDER BY b,c,d DESC,e,f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d DESC,+e,+f;}] do_execsql_test $tn.40 { SELECT a FROM t2 ORDER BY b,c DESC,d,e,f; } [db eval {SELECT a FROM t2 ORDER BY +b,+c DESC,+d,+e,+f;}] do_execsql_test $tn.41 { SELECT a FROM t2 ORDER BY b DESC,c,d,e,f; } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c,+d,+e,+f;}] do_execsql_test $tn.42 { SELECT a FROM t2 ORDER BY b DESC,c DESC,d,e,f LIMIT 31; } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c DESC,+d,+e,+f LIMIT 31}] do_execsql_test $tn.43 { SELECT a FROM t2 ORDER BY b,c,d,e,f DESC LIMIT 8 OFFSET 7; } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC LIMIT 8 OFFSET 7}] } finish_test |