AryaWu/sqlite

1# 2012 Sept 272#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 when the natural order of a query is correct.14#15 16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set ::testprefix orderby220 21# Generate test data for a join.  Verify that the join gets the22# correct answer.23#24do_test 1.0 {25  db eval {26    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);27    INSERT INTO t1 VALUES(1,11), (2,22);28    CREATE TABLE t2(d, e, UNIQUE(d,e));29    INSERT INTO t2 VALUES(10, 'ten'), (11,'eleven'), (12,'twelve'),30                         (11, 'oneteen');31  }32} {}33 34do_test 1.1a {35  db eval {36    SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e;37  }38} {eleven oneteen}39do_test 1.1b {40  db eval {41    EXPLAIN QUERY PLAN42    SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e;43  }44} {~/ORDER BY/}45 46do_test 1.2a {47  db eval {48    SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e;49  }50} {eleven oneteen}51do_test 1.2b {52  db eval {53    EXPLAIN QUERY PLAN54    SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e;55  }56} {~/ORDER BY/}57 58do_test 1.3a {59  db eval {60    SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;61  }62} {ten 11 eleven 11 oneteen 11 twelve 11}63do_test 1.3b {64  db eval {65    EXPLAIN QUERY PLAN66    SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;67  }68} {~/ORDER BY/}69 70# The following tests derived from TH3 test module cov1/where34.test71#72do_test 2.0 {73  db eval {74    CREATE TABLE t31(a,b); CREATE INDEX t31ab ON t31(a,b);75    CREATE TABLE t32(c,d); CREATE INDEX t32cd ON t32(c,d);76    CREATE TABLE t33(e,f); CREATE INDEX t33ef ON t33(e,f);77    CREATE TABLE t34(g,h); CREATE INDEX t34gh ON t34(g,h);78    79    INSERT INTO t31 VALUES(1,4), (2,3), (1,3);80    INSERT INTO t32 VALUES(4,5), (3,6), (3,7), (4,8);81    INSERT INTO t33 VALUES(5,9), (7,10), (6,11), (8,12), (8,13), (7,14);82    INSERT INTO t34 VALUES(11,20), (10,21), (12,22), (9,23), (13,24),83                          (14,25), (12,26);84    SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t3485     WHERE c=b AND e=d AND g=f86     ORDER BY a ASC, c ASC, e DESC, g ASC;87  }88} {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}89do_test 2.1 {90  db eval {91    SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t3492     WHERE c=b AND e=d AND g=f93     ORDER BY +a ASC, +c ASC, +e DESC, +g ASC;94  }95} {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}96do_test 2.2 {97  db eval {98    SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t3499     WHERE c=b AND e=d AND g=f100     ORDER BY a ASC, c ASC, e ASC, g ASC;101  }102} {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7,10 2,3,7,14}103do_test 2.3 {104  optimization_control db cover-idx-scan off105  db cache flush106  db eval {107    SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34108     WHERE c=b AND e=d AND g=f109     ORDER BY a ASC, c ASC, e ASC, g ASC;110  }111} {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7,10 2,3,7,14}  112optimization_control db all on113db cache flush114 115 116 117finish_test118