AryaWu/sqlite

1# 2013 January 092#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this file is testing that the optimizations that disable13# ORDER BY clauses work correctly on a 3-way join.  See ticket14# http://sqlite.org/src/956e4d7f8915#16 17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set ::testprefix orderby321 22# Generate test data for a join.  Verify that the join gets the23# 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.a40    FROM t1, t2, t341   WHERE t1.a=t2.c AND t2.b=t3.d42   ORDER BY t1.a;43} {1 2 3}44do_execsql_test 1.1.desc {45  SELECT t1.a46    FROM t1, t2, t347   WHERE t1.a=t2.c AND t2.b=t3.d48   ORDER BY t1.a DESC;49} {3 2 1}50do_execsql_test 1.123.asc {51  SELECT t1.a52    FROM t1 CROSS JOIN t2 CROSS JOIN t353   WHERE t1.a=t2.c AND t2.b=t3.d54   ORDER BY t1.a;55} {1 2 3}56do_execsql_test 1.123.desc {57  SELECT t1.a58    FROM t1 CROSS JOIN t2 CROSS JOIN t359   WHERE t1.a=t2.c AND t2.b=t3.d60   ORDER BY t1.a DESC;61} {3 2 1}62do_execsql_test 1.132.asc {63  SELECT t1.a64    FROM t1 CROSS JOIN t3 CROSS JOIN t265   WHERE t1.a=t2.c AND t2.b=t3.d66   ORDER BY t1.a;67} {1 2 3}68do_execsql_test 1.132.desc {69  SELECT t1.a70    FROM t1 CROSS JOIN t3 CROSS JOIN t271   WHERE t1.a=t2.c AND t2.b=t3.d72   ORDER BY t1.a DESC;73} {3 2 1}74do_execsql_test 1.213.asc {75  SELECT t1.a76    FROM t2 CROSS JOIN t1 CROSS JOIN t377   WHERE t1.a=t2.c AND t2.b=t3.d78   ORDER BY t1.a;79} {1 2 3}80do_execsql_test 1.213.desc {81  SELECT t1.a82    FROM t2 CROSS JOIN t1 CROSS JOIN t383   WHERE t1.a=t2.c AND t2.b=t3.d84   ORDER BY t1.a DESC;85} {3 2 1}86do_execsql_test 1.231.asc {87  SELECT t1.a88    FROM t2 CROSS JOIN t3 CROSS JOIN t189   WHERE t1.a=t2.c AND t2.b=t3.d90   ORDER BY t1.a;91} {1 2 3}92do_execsql_test 1.231.desc {93  SELECT t1.a94    FROM t2 CROSS JOIN t3 CROSS JOIN t195   WHERE t1.a=t2.c AND t2.b=t3.d96   ORDER BY t1.a DESC;97} {3 2 1}98do_execsql_test 1.312.asc {99  SELECT t1.a100    FROM t3 CROSS JOIN t1 CROSS JOIN t2101   WHERE t1.a=t2.c AND t2.b=t3.d102   ORDER BY t1.a;103} {1 2 3}104do_execsql_test 1.312.desc {105  SELECT t1.a106    FROM t3 CROSS JOIN t1 CROSS JOIN t2107   WHERE t1.a=t2.c AND t2.b=t3.d108   ORDER BY t1.a DESC;109} {3 2 1}110do_execsql_test 1.321.asc {111  SELECT t1.a112    FROM t3 CROSS JOIN t2 CROSS JOIN t1113   WHERE t1.a=t2.c AND t2.b=t3.d114   ORDER BY t1.a;115} {1 2 3}116do_execsql_test 1.321.desc {117  SELECT t1.a118    FROM t3 CROSS JOIN t2 CROSS JOIN t1119   WHERE t1.a=t2.c AND t2.b=t3.d120   ORDER BY t1.a DESC;121} {3 2 1}122 123finish_test124