gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/orderbyA.test (about) 1 # 2019-09-21 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. 12 # 13 # Specifically, it tests cases where the expressions in a GROUP BY 14 # clause are the same as those in the ORDER BY clause. 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set ::testprefix orderbyA 20 21 proc do_sortcount_test {tn sql cnt res} { 22 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 23 set rcnt [regexp -all {USE TEMP} $eqp] 24 uplevel [list do_test $tn.1 [list set {} $rcnt] $cnt] 25 uplevel [list do_execsql_test $tn.2 $sql $res] 26 } 27 28 do_execsql_test 1.0 { 29 CREATE TABLE t1(a, b, c); 30 INSERT INTO t1 VALUES('one', 1, 11); 31 INSERT INTO t1 VALUES('three', 7, 11); 32 INSERT INTO t1 VALUES('one', 2, 11); 33 INSERT INTO t1 VALUES('one', 3, 11); 34 INSERT INTO t1 VALUES('two', 4, 11); 35 INSERT INTO t1 VALUES('two', 6, 11); 36 INSERT INTO t1 VALUES('three', 8, 11); 37 INSERT INTO t1 VALUES('two', 5, 11); 38 INSERT INTO t1 VALUES('three', 9, 11); 39 } 40 41 foreach {tn idx} { 42 1 {} 43 2 {CREATE INDEX i1 ON t1(a)} 44 3 {CREATE INDEX i1 ON t1(a DESC)} 45 } { 46 execsql { DROP INDEX IF EXISTS i1 } 47 execsql $idx 48 49 # $match is the number of temp-table sorts we expect if the GROUP BY 50 # can use the same sort order as the ORDER BY. $nomatch is the number 51 # of expected sorts if the GROUP BY and ORDER BY are not compatible. 52 set match 1 53 set nomatch 2 54 if {$tn>=2} { 55 set match 0 56 set nomatch 1 57 } 58 59 do_sortcount_test 1.$tn.1.1 { 60 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a 61 } $match {one 6 three 24 two 15} 62 do_sortcount_test 1.$tn.1.2 { 63 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC 64 } $match {two 15 three 24 one 6} 65 66 do_sortcount_test 1.$tn.2.1 { 67 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||'' 68 } $nomatch {one 6 three 24 two 15} 69 do_sortcount_test 1.$tn.2.2 { 70 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||'' DESC 71 } $nomatch {two 15 three 24 one 6} 72 73 do_sortcount_test 1.$tn.3.1 { 74 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a NULLS LAST 75 } $nomatch {one 6 three 24 two 15} 76 do_sortcount_test 1.$tn.3.2 { 77 SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC NULLS FIRST 78 } $nomatch {two 15 three 24 one 6} 79 } 80 81 #------------------------------------------------------------------------- 82 do_execsql_test 2.0 { 83 CREATE TABLE t2(a, b, c); 84 INSERT INTO t2 VALUES(1, 'one', 1); 85 INSERT INTO t2 VALUES(1, 'two', 2); 86 INSERT INTO t2 VALUES(1, 'one', 3); 87 INSERT INTO t2 VALUES(1, 'two', 4); 88 INSERT INTO t2 VALUES(1, 'one', 5); 89 INSERT INTO t2 VALUES(1, 'two', 6); 90 91 INSERT INTO t2 VALUES(2, 'one', 7); 92 INSERT INTO t2 VALUES(2, 'two', 8); 93 INSERT INTO t2 VALUES(2, 'one', 9); 94 INSERT INTO t2 VALUES(2, 'two', 10); 95 INSERT INTO t2 VALUES(2, 'one', 11); 96 INSERT INTO t2 VALUES(2, 'two', 12); 97 98 INSERT INTO t2 VALUES(NULL, 'one', 13); 99 INSERT INTO t2 VALUES(NULL, 'two', 14); 100 INSERT INTO t2 VALUES(NULL, 'one', 15); 101 INSERT INTO t2 VALUES(NULL, 'two', 16); 102 INSERT INTO t2 VALUES(NULL, 'one', 17); 103 INSERT INTO t2 VALUES(NULL, 'two', 18); 104 } 105 106 foreach {tn idx} { 107 1 {} 108 109 2 { CREATE INDEX i2 ON t2(a, b) } 110 3 { CREATE INDEX i2 ON t2(a DESC, b DESC) } 111 112 4 { CREATE INDEX i2 ON t2(a, b DESC) } 113 5 { CREATE INDEX i2 ON t2(a DESC, b) } 114 } { 115 execsql { DROP INDEX IF EXISTS i2 } 116 execsql $idx 117 118 119 set nSort [expr ($tn==2 || $tn==3) ? 0 : 1] 120 do_sortcount_test 2.$tn.1.1 { 121 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b; 122 } $nSort {{} one 45 {} two 48 1 one 9 1 two 12 2 one 27 2 two 30} 123 do_sortcount_test 2.$tn.1.2 { 124 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b DESC; 125 } $nSort {2 two 30 2 one 27 1 two 12 1 one 9 {} two 48 {} one 45} 126 127 set nSort [expr ($tn==4 || $tn==5) ? 0 : 1] 128 do_sortcount_test 2.$tn.2.1 { 129 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC; 130 } $nSort { {} two 48 {} one 45 1 two 12 1 one 9 2 two 30 2 one 27 } 131 do_sortcount_test 2.$tn.2.2 { 132 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b; 133 } $nSort { 2 one 27 2 two 30 1 one 9 1 two 12 {} one 45 {} two 48 } 134 135 # ORDER BY can never piggyback on the GROUP BY sort if it uses 136 # non-standard NULLS behaviour. 137 set nSort [expr $tn==1 ? 2 : 1] 138 do_sortcount_test 2.$tn.3.1 { 139 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC NULLS FIRST; 140 } $nSort { {} two 48 {} one 45 1 two 12 1 one 9 2 two 30 2 one 27 } 141 do_sortcount_test 2.$tn.3.2 { 142 SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b NULLS LAST; 143 } $nSort { 2 one 27 2 two 30 1 one 9 1 two 12 {} one 45 {} two 48 } 144 } 145 146 147 finish_test