AryaWu/sqlite

1# 2019-09-212#3# The author disclaims copyright to this source code.  In place of4# 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.tcl19set ::testprefix orderbyA20 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 $idx48 49  # $match is the number of temp-table sorts we expect if the GROUP BY50  # can use the same sort order as the ORDER BY. $nomatch is the number51  # of expected sorts if the GROUP BY and ORDER BY are not compatible.52  set match   153  set nomatch 254  if {$tn>=2} {55    set match   056    set nomatch 157  }58 59  do_sortcount_test 1.$tn.1.1 {60    SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a61  } $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 DESC64  } $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||'' DESC71  } $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 LAST75  } $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 FIRST78  } $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 $idx117 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_test148