| # 2019-09-21 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. |
| # |
| # Specifically, it tests cases where the expressions in a GROUP BY |
| # clause are the same as those in the ORDER BY clause. |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set ::testprefix orderbyA |
| |
| proc do_sortcount_test {tn sql cnt res} { |
| set eqp [execsql "EXPLAIN QUERY PLAN $sql"] |
| set rcnt [regexp -all {USE TEMP} $eqp] |
| uplevel [list do_test $tn.1 [list set {} $rcnt] $cnt] |
| uplevel [list do_execsql_test $tn.2 $sql $res] |
| } |
| |
| do_execsql_test 1.0 { |
| CREATE TABLE t1(a, b, c); |
| INSERT INTO t1 VALUES('one', 1, 11); |
| INSERT INTO t1 VALUES('three', 7, 11); |
| INSERT INTO t1 VALUES('one', 2, 11); |
| INSERT INTO t1 VALUES('one', 3, 11); |
| INSERT INTO t1 VALUES('two', 4, 11); |
| INSERT INTO t1 VALUES('two', 6, 11); |
| INSERT INTO t1 VALUES('three', 8, 11); |
| INSERT INTO t1 VALUES('two', 5, 11); |
| INSERT INTO t1 VALUES('three', 9, 11); |
| } |
| |
| foreach {tn idx} { |
| 1 {} |
| 2 {CREATE INDEX i1 ON t1(a)} |
| 3 {CREATE INDEX i1 ON t1(a DESC)} |
| } { |
| execsql { DROP INDEX IF EXISTS i1 } |
| execsql $idx |
| |
| # $match is the number of temp-table sorts we expect if the GROUP BY |
| # can use the same sort order as the ORDER BY. $nomatch is the number |
| # of expected sorts if the GROUP BY and ORDER BY are not compatible. |
| set match 1 |
| set nomatch 2 |
| if {$tn>=2} { |
| set match 0 |
| set nomatch 1 |
| } |
| |
| do_sortcount_test 1.$tn.1.1 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a |
| } $match {one 6 three 24 two 15} |
| do_sortcount_test 1.$tn.1.2 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC |
| } $match {two 15 three 24 one 6} |
| |
| do_sortcount_test 1.$tn.2.1 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||'' |
| } $nomatch {one 6 three 24 two 15} |
| do_sortcount_test 1.$tn.2.2 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a||'' DESC |
| } $nomatch {two 15 three 24 one 6} |
| |
| do_sortcount_test 1.$tn.3.1 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a NULLS LAST |
| } $nomatch {one 6 three 24 two 15} |
| do_sortcount_test 1.$tn.3.2 { |
| SELECT a, sum(b) FROM t1 GROUP BY a ORDER BY a DESC NULLS FIRST |
| } $nomatch {two 15 three 24 one 6} |
| } |
| |
| #------------------------------------------------------------------------- |
| do_execsql_test 2.0 { |
| CREATE TABLE t2(a, b, c); |
| INSERT INTO t2 VALUES(1, 'one', 1); |
| INSERT INTO t2 VALUES(1, 'two', 2); |
| INSERT INTO t2 VALUES(1, 'one', 3); |
| INSERT INTO t2 VALUES(1, 'two', 4); |
| INSERT INTO t2 VALUES(1, 'one', 5); |
| INSERT INTO t2 VALUES(1, 'two', 6); |
| |
| INSERT INTO t2 VALUES(2, 'one', 7); |
| INSERT INTO t2 VALUES(2, 'two', 8); |
| INSERT INTO t2 VALUES(2, 'one', 9); |
| INSERT INTO t2 VALUES(2, 'two', 10); |
| INSERT INTO t2 VALUES(2, 'one', 11); |
| INSERT INTO t2 VALUES(2, 'two', 12); |
| |
| INSERT INTO t2 VALUES(NULL, 'one', 13); |
| INSERT INTO t2 VALUES(NULL, 'two', 14); |
| INSERT INTO t2 VALUES(NULL, 'one', 15); |
| INSERT INTO t2 VALUES(NULL, 'two', 16); |
| INSERT INTO t2 VALUES(NULL, 'one', 17); |
| INSERT INTO t2 VALUES(NULL, 'two', 18); |
| } |
| |
| foreach {tn idx} { |
| 1 {} |
| |
| 2 { CREATE INDEX i2 ON t2(a, b) } |
| 3 { CREATE INDEX i2 ON t2(a DESC, b DESC) } |
| |
| 4 { CREATE INDEX i2 ON t2(a, b DESC) } |
| 5 { CREATE INDEX i2 ON t2(a DESC, b) } |
| } { |
| execsql { DROP INDEX IF EXISTS i2 } |
| execsql $idx |
| |
| |
| set nSort [expr ($tn==2 || $tn==3) ? 0 : 1] |
| do_sortcount_test 2.$tn.1.1 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b; |
| } $nSort {{} one 45 {} two 48 1 one 9 1 two 12 2 one 27 2 two 30} |
| do_sortcount_test 2.$tn.1.2 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b DESC; |
| } $nSort {2 two 30 2 one 27 1 two 12 1 one 9 {} two 48 {} one 45} |
| |
| set nSort [expr ($tn==4 || $tn==5) ? 0 : 1] |
| do_sortcount_test 2.$tn.2.1 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC; |
| } $nSort { {} two 48 {} one 45 1 two 12 1 one 9 2 two 30 2 one 27 } |
| do_sortcount_test 2.$tn.2.2 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b; |
| } $nSort { 2 one 27 2 two 30 1 one 9 1 two 12 {} one 45 {} two 48 } |
| |
| # ORDER BY can never piggyback on the GROUP BY sort if it uses |
| # non-standard NULLS behaviour. |
| set nSort [expr $tn==1 ? 2 : 1] |
| do_sortcount_test 2.$tn.3.1 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a, b DESC NULLS FIRST; |
| } $nSort { {} two 48 {} one 45 1 two 12 1 one 9 2 two 30 2 one 27 } |
| do_sortcount_test 2.$tn.3.2 { |
| SELECT a, b, sum(c) FROM t2 GROUP BY a, b ORDER BY a DESC, b NULLS LAST; |
| } $nSort { 2 one 27 2 two 30 1 one 9 1 two 12 {} one 45 {} two 48 } |
| } |
| |
| |
| finish_test |