1 files changed, 438 insertions, 0 deletions
diff --git a/test/orderby1.test b/test/orderby1.test
new file mode 100644
index 0000000..f459fc8
--- /dev/null
+++ b/test/orderby1.test
@@ -0,0 +1,438 @@
+# 2012 Sept 27
+#
+# 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 optimizations that disable
+# ORDER BY clauses when the natural order of a query is correct.
+#
+
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set ::testprefix orderby1
+
+# Generate test data for a join.  Verify that the join gets the
+# correct answer.
+#
+do_test 1.0 {
+  db eval {
+    BEGIN;
+    CREATE TABLE album(
+      aid INTEGER PRIMARY KEY,
+      title TEXT UNIQUE NOT NULL
+    );
+    CREATE TABLE track(
+      tid INTEGER PRIMARY KEY,
+      aid INTEGER NOT NULL REFERENCES album,
+      tn INTEGER NOT NULL,
+      name TEXT,
+      UNIQUE(aid, tn)
+    );
+    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
+    INSERT INTO track VALUES
+        (NULL, 1, 1, 'one-a'),
+        (NULL, 2, 2, 'two-b'),
+        (NULL, 3, 3, 'three-c'),
+        (NULL, 1, 3, 'one-c'),
+        (NULL, 2, 1, 'two-a'),
+        (NULL, 3, 1, 'three-a');
+    COMMIT;
+  }
+} {}
+do_test 1.1a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+
+# Verify that the ORDER BY clause is optimized out
+#
+do_test 1.1b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized out
+
+# The same query with ORDER BY clause optimization disabled via + operators
+# should give exactly the same answer.
+#
+do_test 1.2a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+
+# The output is sorted manually in this case.
+#
+do_test 1.2b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
+
+# The same query with ORDER BY optimizations turned off via built-in test.
+#
+do_test 1.3a {
+  optimization_control db order-by-idx-join 0
+  db cache flush
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+do_test 1.3b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
+optimization_control db all 1
+db cache flush
+
+# Reverse order sorts
+#
+do_test 1.4a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {three-a three-c two-a two-b one-a one-c}
+do_test 1.4b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
+  }
+} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
+do_test 1.4c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {~/ORDER BY/}  ;# optimized out
+
+
+do_test 1.5a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}
+do_test 1.5b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
+do_test 1.5c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {~/ORDER BY/}  ;# optimized out
+
+do_test 1.6a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}
+do_test 1.6b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
+do_test 1.6c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized-out
+
+
+# Reconstruct the test data to use indices rather than integer primary keys.
+#
+do_test 2.0 {
+  db eval {
+    BEGIN;
+    DROP TABLE album;
+    DROP TABLE track;
+    CREATE TABLE album(
+      aid INT PRIMARY KEY,
+      title TEXT NOT NULL
+    );
+    CREATE INDEX album_i1 ON album(title, aid);
+    CREATE TABLE track(
+      aid INTEGER NOT NULL REFERENCES album,
+      tn INTEGER NOT NULL,
+      name TEXT,
+      UNIQUE(aid, tn)
+    );
+    INSERT INTO album VALUES(1, '1-one'), (20, '2-two'), (3, '3-three');
+    INSERT INTO track VALUES
+        (1,  1, 'one-a'),
+        (20, 2, 'two-b'),
+        (3,  3, 'three-c'),
+        (1,  3, 'one-c'),
+        (20, 1, 'two-a'),
+        (3,  1, 'three-a');
+    COMMIT;
+  }
+} {}
+do_test 2.1a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+
+# Verify that the ORDER BY clause is optimized out
+#
+do_test 2.1b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized out
+
+do_test 2.1c {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+do_test 2.1d {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized out
+
+# The same query with ORDER BY clause optimization disabled via + operators
+# should give exactly the same answer.
+#
+do_test 2.2a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+
+# The output is sorted manually in this case.
+#
+do_test 2.2b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
+
+# The same query with ORDER BY optimizations turned off via built-in test.
+#
+do_test 2.3a {
+  optimization_control db order-by-idx-join 0
+  db cache flush
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+do_test 2.3b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
+optimization_control db all 1
+db cache flush
+
+# Reverse order sorts
+#
+do_test 2.4a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {three-a three-c two-a two-b one-a one-c}
+do_test 2.4b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
+  }
+} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
+do_test 2.4c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {~/ORDER BY/}  ;# optimized out
+
+
+do_test 2.5a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}
+do_test 2.5b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
+do_test 2.5c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {~/ORDER BY/}  ;# optimized out
+
+do_test 2.6a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}
+do_test 2.6b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
+do_test 2.6c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized out
+
+
+# Generate another test dataset, but this time using mixed ASC/DESC indices.
+#
+do_test 3.0 {
+  db eval {
+    BEGIN;
+    DROP TABLE album;
+    DROP TABLE track;
+    CREATE TABLE album(
+      aid INTEGER PRIMARY KEY,
+      title TEXT UNIQUE NOT NULL
+    );
+    CREATE TABLE track(
+      tid INTEGER PRIMARY KEY,
+      aid INTEGER NOT NULL REFERENCES album,
+      tn INTEGER NOT NULL,
+      name TEXT,
+      UNIQUE(aid ASC, tn DESC)
+    );
+    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
+    INSERT INTO track VALUES
+        (NULL, 1, 1, 'one-a'),
+        (NULL, 2, 2, 'two-b'),
+        (NULL, 3, 3, 'three-c'),
+        (NULL, 1, 3, 'one-c'),
+        (NULL, 2, 1, 'two-a'),
+        (NULL, 3, 1, 'three-a');
+    COMMIT;
+  }
+} {}
+do_test 3.1a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}
+
+# Verify that the ORDER BY clause is optimized out
+#
+do_test 3.1b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {~/ORDER BY/}  ;# ORDER BY optimized out
+
+# The same query with ORDER BY clause optimization disabled via + operators
+# should give exactly the same answer.
+#
+do_test 3.2a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}
+
+# The output is sorted manually in this case.
+#
+do_test 3.2b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
+
+# The same query with ORDER BY optimizations turned off via built-in test.
+#
+do_test 3.3a {
+  optimization_control db order-by-idx-join 0
+  db cache flush
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {one-c one-a two-b two-a three-c three-a}
+do_test 3.3b {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
+  }
+} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
+optimization_control db all 1
+db cache flush
+
+# Without the mixed ASC/DESC on ORDER BY
+#
+do_test 3.4a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {one-a one-c two-a two-b three-a three-c}
+do_test 3.4b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
+  }
+} {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
+do_test 3.4c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
+  }
+} {~/ORDER BY/}  ;# optimized out
+
+
+do_test 3.5a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}
+do_test 3.5b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
+  }
+} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
+do_test 3.5c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
+  }
+} {~/ORDER BY/}  ;# optimzed out
+
+
+do_test 3.6a {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {three-a three-c two-a two-b one-a one-c}
+do_test 3.6b {
+  db eval {
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
+  }
+} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
+do_test 3.6c {
+  db eval {
+    EXPLAIN QUERY PLAN
+    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
+  }
+} {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out
+
+
+finish_test