# 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