From 08119c361d1181b3e8f1abb429236e488a664753 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Tue, 13 Aug 2013 15:42:54 -0400 Subject: Imported Upstream version 2.2.1 --- test/orderby1.test | 438 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 438 insertions(+) create mode 100644 test/orderby1.test (limited to 'test/orderby1.test') 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 -- cgit v1.2.3