1# 2012 Sept 27 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 when the natural order of a query is correct. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix orderby2 20 21# Generate test data for a join. Verify that the join gets the 22# 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 PLAN 42 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 PLAN 54 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 PLAN 66 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.test 71# 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, t34 85 WHERE c=b AND e=d AND g=f 86 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, t34 92 WHERE c=b AND e=d AND g=f 93 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, t34 99 WHERE c=b AND e=d AND g=f 100 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 off 105 db cache flush 106 db eval { 107 SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34 108 WHERE c=b AND e=d AND g=f 109 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 on 113db cache flush 114 115 116 117finish_test 118