test/orderbyA.test

   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