From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- test/orderby6.test | 183 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 183 insertions(+) create mode 100644 test/orderby6.test (limited to 'test/orderby6.test') diff --git a/test/orderby6.test b/test/orderby6.test new file mode 100644 index 0000000..403250d --- /dev/null +++ b/test/orderby6.test @@ -0,0 +1,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 -- cgit v1.2.3