xref: /sqlite-3.40.0/test/orderby3.test (revision 97a3e09f) 
1# 2013 January 09
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 optimizations that disable
13# ORDER BY clauses work correctly on a 3-way join.  See ticket
14# http://www.sqlite.org/src/956e4d7f89
15#
16
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20set ::testprefix orderby3
21
22# Generate test data for a join.  Verify that the join gets the
23# correct answer.
24#
25do_execsql_test 1.0 {
26  CREATE TABLE t1(a INTEGER PRIMARY KEY);
27  CREATE TABLE t2(b INTEGER PRIMARY KEY, c INTEGER);
28  CREATE TABLE t3(d INTEGER);
29
30  INSERT INTO t1 VALUES(1),(2),(3);
31
32  INSERT INTO t2 VALUES(3, 1);
33  INSERT INTO t2 VALUES(4, 2);
34  INSERT INTO t2 VALUES(5, 3);
35
36  INSERT INTO t3 VALUES(4),(3),(5);
37} {}
38do_execsql_test 1.1.asc {
39  SELECT t1.a
40    FROM t1, t2, t3
41   WHERE t1.a=t2.c AND t2.b=t3.d
42   ORDER BY t1.a;
43} {1 2 3}
44do_execsql_test 1.1.desc {
45  SELECT t1.a
46    FROM t1, t2, t3
47   WHERE t1.a=t2.c AND t2.b=t3.d
48   ORDER BY t1.a DESC;
49} {3 2 1}
50do_execsql_test 1.123.asc {
51  SELECT t1.a
52    FROM t1 CROSS JOIN t2 CROSS JOIN t3
53   WHERE t1.a=t2.c AND t2.b=t3.d
54   ORDER BY t1.a;
55} {1 2 3}
56do_execsql_test 1.123.desc {
57  SELECT t1.a
58    FROM t1 CROSS JOIN t2 CROSS JOIN t3
59   WHERE t1.a=t2.c AND t2.b=t3.d
60   ORDER BY t1.a DESC;
61} {3 2 1}
62do_execsql_test 1.132.asc {
63  SELECT t1.a
64    FROM t1 CROSS JOIN t3 CROSS JOIN t2
65   WHERE t1.a=t2.c AND t2.b=t3.d
66   ORDER BY t1.a;
67} {1 2 3}
68do_execsql_test 1.132.desc {
69  SELECT t1.a
70    FROM t1 CROSS JOIN t3 CROSS JOIN t2
71   WHERE t1.a=t2.c AND t2.b=t3.d
72   ORDER BY t1.a DESC;
73} {3 2 1}
74do_execsql_test 1.213.asc {
75  SELECT t1.a
76    FROM t2 CROSS JOIN t1 CROSS JOIN t3
77   WHERE t1.a=t2.c AND t2.b=t3.d
78   ORDER BY t1.a;
79} {1 2 3}
80do_execsql_test 1.213.desc {
81  SELECT t1.a
82    FROM t2 CROSS JOIN t1 CROSS JOIN t3
83   WHERE t1.a=t2.c AND t2.b=t3.d
84   ORDER BY t1.a DESC;
85} {3 2 1}
86do_execsql_test 1.231.asc {
87  SELECT t1.a
88    FROM t2 CROSS JOIN t3 CROSS JOIN t1
89   WHERE t1.a=t2.c AND t2.b=t3.d
90   ORDER BY t1.a;
91} {1 2 3}
92do_execsql_test 1.231.desc {
93  SELECT t1.a
94    FROM t2 CROSS JOIN t3 CROSS JOIN t1
95   WHERE t1.a=t2.c AND t2.b=t3.d
96   ORDER BY t1.a DESC;
97} {3 2 1}
98do_execsql_test 1.312.asc {
99  SELECT t1.a
100    FROM t3 CROSS JOIN t1 CROSS JOIN t2
101   WHERE t1.a=t2.c AND t2.b=t3.d
102   ORDER BY t1.a;
103} {1 2 3}
104do_execsql_test 1.312.desc {
105  SELECT t1.a
106    FROM t3 CROSS JOIN t1 CROSS JOIN t2
107   WHERE t1.a=t2.c AND t2.b=t3.d
108   ORDER BY t1.a DESC;
109} {3 2 1}
110do_execsql_test 1.321.asc {
111  SELECT t1.a
112    FROM t3 CROSS JOIN t2 CROSS JOIN t1
113   WHERE t1.a=t2.c AND t2.b=t3.d
114   ORDER BY t1.a;
115} {1 2 3}
116do_execsql_test 1.321.desc {
117  SELECT t1.a
118    FROM t3 CROSS JOIN t2 CROSS JOIN t1
119   WHERE t1.a=t2.c AND t2.b=t3.d
120   ORDER BY t1.a DESC;
121} {3 2 1}
122
123finish_test
124