1# 2014-03-21 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 block-sort optimization. 13# 14 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set ::testprefix orderby6 19 20# Run all tests twice. Once with a normal table and a second time 21# with a WITHOUT ROWID table 22# 23foreach {tn rowidclause} {1 {} 2 {WITHOUT ROWID}} { 24 25 # Construct a table with 1000 rows and a split primary key 26 # 27 reset_db 28 do_test $tn.1 { 29 db eval "CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c)) $rowidclause;" 30 db eval { 31 WITH RECURSIVE 32 cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000) 33 INSERT INTO t1 SELECT x, x%40, x/40 FROM cnt; 34 } 35 } {} 36 37 # Run various ORDER BY queries that can benefit from block-sort. 38 # Compare the output to the same output using a full-sort enforced 39 # by adding + to each term of the ORDER BY clause. 40 # 41 do_execsql_test $tn.2 { 42 SELECT b,a,c FROM t1 ORDER BY b,a,c; 43 } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+a,+c}] 44 do_execsql_test $tn.3 { 45 SELECT b,a,c FROM t1 ORDER BY b,c DESC,a; 46 } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+c DESC,+a}] 47 do_execsql_test $tn.4 { 48 SELECT b,a,c FROM t1 ORDER BY b DESC,c,a; 49 } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+c,+a}] 50 do_execsql_test $tn.5 { 51 SELECT b,a,c FROM t1 ORDER BY b DESC,a,c; 52 } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+a,+c}] 53 54 # LIMIT and OFFSET clauses on block-sort queries. 55 # 56 do_execsql_test $tn.11 { 57 SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20; 58 } {840 880 920 960 1000 1 41 81 121 161} 59 do_execsql_test $tn.11x { 60 SELECT a FROM t1 ORDER BY +b, a LIMIT 10 OFFSET 20; 61 } {840 880 920 960 1000 1 41 81 121 161} 62 63 do_execsql_test $tn.12 { 64 SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 20; 65 } {839 879 919 959 999 38 78 118 158 198} 66 do_execsql_test $tn.12 { 67 SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 20; 68 } {839 879 919 959 999 38 78 118 158 198} 69 70 do_execsql_test $tn.13 { 71 SELECT a FROM t1 ORDER BY b, a DESC LIMIT 10 OFFSET 45; 72 } {161 121 81 41 1 962 922 882 842 802} 73 do_execsql_test $tn.13x { 74 SELECT a FROM t1 ORDER BY +b, a DESC LIMIT 10 OFFSET 45; 75 } {161 121 81 41 1 962 922 882 842 802} 76 77 do_execsql_test $tn.14 { 78 SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 45; 79 } {838 878 918 958 998 37 77 117 157 197} 80 do_execsql_test $tn.14x { 81 SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 45; 82 } {838 878 918 958 998 37 77 117 157 197} 83 84 # Many test cases where the LIMIT+OFFSET window is in various 85 # alignments with block-sort boundaries. 86 # 87 foreach {tx limit offset orderby} { 88 1 10 24 {+b,+a} 89 2 10 25 {+b,+a} 90 3 10 26 {+b,+a} 91 4 10 39 {+b,+a} 92 5 10 40 {+b,+a} 93 6 10 41 {+b,+a} 94 7 27 24 {+b,+a} 95 8 27 49 {+b,+a} 96 11 10 24 {+b DESC,+a} 97 12 10 25 {+b DESC,+a} 98 13 10 26 {+b DESC,+a} 99 14 10 39 {+b DESC,+a} 100 15 10 40 {+b DESC,+a} 101 16 10 41 {+b DESC,+a} 102 17 27 24 {+b DESC,+a} 103 18 27 49 {+b DESC,+a} 104 21 10 24 {+b,+a DESC} 105 22 10 25 {+b,+a DESC} 106 23 10 26 {+b,+a DESC} 107 24 10 39 {+b,+a DESC} 108 25 10 40 {+b,+a DESC} 109 26 10 41 {+b,+a DESC} 110 27 27 24 {+b,+a DESC} 111 28 27 49 {+b,+a DESC} 112 31 10 24 {+b DESC,+a DESC} 113 32 10 25 {+b DESC,+a DESC} 114 33 10 26 {+b DESC,+a DESC} 115 34 10 39 {+b DESC,+a DESC} 116 35 10 40 {+b DESC,+a DESC} 117 36 10 41 {+b DESC,+a DESC} 118 37 27 24 {+b DESC,+a DESC} 119 38 27 49 {+b DESC,+a DESC} 120 } { 121 set sql1 "SELECT a FROM t1 ORDER BY $orderby LIMIT $limit OFFSET $offset;" 122 set sql2 [string map {+ {}} $sql1] 123 # puts $sql2\n$sql1\n[db eval $sql2] 124 do_test $tn.21.$tx {db eval $::sql2} [db eval $sql1] 125 } 126 127 ######################################################################## 128 # A second test table, t2, has many columns open to sorting. 129 do_test $tn.31 { 130 db eval "CREATE TABLE t2(a,b,c,d,e,f,PRIMARY KEY(b,c,d,e,f)) $rowidclause;" 131 db eval { 132 WITH RECURSIVE 133 cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<242) 134 INSERT INTO t2 SELECT x, x%3, (x/3)%3, (x/9)%3, (x/27)%3, (x/81)%3 135 FROM cnt; 136 } 137 } {} 138 139 do_execsql_test $tn.32 { 140 SELECT a FROM t2 ORDER BY b,c,d,e,f; 141 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] 142 do_execsql_test $tn.33 { 143 SELECT a FROM t2 ORDER BY b,c,d,e,+f; 144 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] 145 do_execsql_test $tn.34 { 146 SELECT a FROM t2 ORDER BY b,c,d,+e,+f; 147 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] 148 do_execsql_test $tn.35 { 149 SELECT a FROM t2 ORDER BY b,c,+d,+e,+f; 150 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] 151 do_execsql_test $tn.36 { 152 SELECT a FROM t2 ORDER BY b,+c,+d,+e,+f; 153 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] 154 155 do_execsql_test $tn.37 { 156 SELECT a FROM t2 ORDER BY b,c,d,e,f DESC; 157 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC;}] 158 do_execsql_test $tn.38 { 159 SELECT a FROM t2 ORDER BY b,c,d,e DESC,f; 160 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e DESC,+f;}] 161 do_execsql_test $tn.39 { 162 SELECT a FROM t2 ORDER BY b,c,d DESC,e,f; 163 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d DESC,+e,+f;}] 164 do_execsql_test $tn.40 { 165 SELECT a FROM t2 ORDER BY b,c DESC,d,e,f; 166 } [db eval {SELECT a FROM t2 ORDER BY +b,+c DESC,+d,+e,+f;}] 167 do_execsql_test $tn.41 { 168 SELECT a FROM t2 ORDER BY b DESC,c,d,e,f; 169 } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c,+d,+e,+f;}] 170 171 do_execsql_test $tn.42 { 172 SELECT a FROM t2 ORDER BY b DESC,c DESC,d,e,f LIMIT 31; 173 } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c DESC,+d,+e,+f LIMIT 31}] 174 do_execsql_test $tn.43 { 175 SELECT a FROM t2 ORDER BY b,c,d,e,f DESC LIMIT 8 OFFSET 7; 176 } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC LIMIT 8 OFFSET 7}] 177 178 179} 180 181 182 183finish_test 184