1# 2014-04-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# 12# Test that ticket [b75a9ca6b0] has been fixed. 13# 14# Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY 15# and an ORDER BY. This code verifies that SQLite is able to 16# optimize out the ORDER BY in some circumstances, but retains the 17# ORDER BY when necessary. 18# 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22set testprefix tkt-b75a9ca6b0 23 24do_execsql_test 1 { 25 CREATE TABLE t1 (x, y); 26 INSERT INTO t1 VALUES (1, 3); 27 INSERT INTO t1 VALUES (2, 2); 28 INSERT INTO t1 VALUES (3, 1); 29} 30 31do_execsql_test 1.1 { 32 CREATE INDEX i1 ON t1(x, y); 33} 34 35set idxscan {SCAN t1 USING COVERING INDEX i1} 36set tblscan {SCAN t1} 37set grpsort {USE TEMP B-TREE FOR GROUP BY} 38set sort {USE TEMP B-TREE FOR ORDER BY} 39 40foreach {tn q res eqp} [subst -nocommands { 41 1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y" 42 {1 3 2 2 3 1} {$idxscan} 43 44 2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x" 45 {1 3 2 2 3 1} {$idxscan*$sort} 46 47 3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x" 48 {3 1 2 2 1 3} {$idxscan*$sort} 49 50 4 "SELECT * FROM t1 GROUP BY x ORDER BY x" 51 {1 3 2 2 3 1} {$idxscan} 52 53 5 "SELECT * FROM t1 GROUP BY y ORDER BY y" 54 {3 1 2 2 1 3} {$tblscan*$grpsort} 55 56 6 "SELECT * FROM t1 GROUP BY y ORDER BY x" 57 {1 3 2 2 3 1} {$tblscan*$grpsort*$sort} 58 59 7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC" 60 {1 3 2 2 3 1} {$idxscan*$sort} 61 62 8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC" 63 {3 1 2 2 1 3} {$idxscan} 64 65 9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC" 66 {1 3 2 2 3 1} {$idxscan} 67 68 10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y" 69 {1 3 2 2 3 1} {$idxscan*$sort} 70 71}] { 72 do_execsql_test 1.$tn.1 $q $res 73 do_eqp_test 1.$tn.2 $q $eqp 74} 75 76 77finish_test 78