/ Check-in [e70cfa28]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:New test case for block-sorting.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | orderby-planning
Files: files | file ages | folders
SHA1: e70cfa28aa393661ccc742ecd5e672d807bdd0a9
User & Date: drh 2014-03-21 15:24:07
Context
2014-03-21
18:45
Merge the OFFSET-on-query-without-FROM fix from trunk. check-in: 71e9ae72 user: drh tags: orderby-planning
15:24
New test case for block-sorting. check-in: e70cfa28 user: drh tags: orderby-planning
2014-03-20
20:56
Merge trunk fixes for "x IN (?)" handling. check-in: eca35871 user: drh tags: orderby-planning
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/orderby6.test.

            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  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set ::testprefix orderby6
           19  +
           20  +# Run all tests twice.  Once with a normal table and a second time
           21  +# with a WITHOUT ROWID table
           22  +#
           23  +foreach {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  +
          183  +finish_test