gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/orderby2.test (about) 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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set ::testprefix orderby2 20 21 # Generate test data for a join. Verify that the join gets the 22 # correct answer. 23 # 24 do_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 34 do_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} 39 do_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 46 do_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} 51 do_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 58 do_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} 63 do_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 # 72 do_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} 89 do_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} 96 do_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} 103 do_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} 112 optimization_control db all on 113 db cache flush 114 115 116 117 finish_test