AryaWu/sqlite

1# 2014-03-212#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this file is testing that the block-sort optimization.13#14 15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set ::testprefix orderby619 20# Run all tests twice.  Once with a normal table and a second time21# with a WITHOUT ROWID table22#23foreach {tn rowidclause} {1 {} 2 {WITHOUT ROWID}} {24 25  # Construct a table with 1000 rows and a split primary key26  #27  reset_db28  do_test $tn.1 {29    db eval "CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c)) $rowidclause;"30    db eval {31      WITH RECURSIVE32       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 enforced39  # 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 various85  # 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 RECURSIVE133       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)%3135                      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_test184