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
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set ::testprefix orderbyA
20
21proc 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
28do_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
41foreach {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#-------------------------------------------------------------------------
82do_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
106foreach {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
147finish_test
148