AryaWu/sqlite

1# 2014-04-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#12# Test that ticket [b75a9ca6b0] has been fixed.13#14# Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY15# and an ORDER BY.  This code verifies that SQLite is able to16# optimize out the ORDER BY in some circumstances, but retains the17# ORDER BY when necessary.18#19 20set testdir [file dirname $argv0]21source $testdir/tester.tcl22set testprefix tkt-b75a9ca6b023 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 $res73  do_eqp_test     1.$tn.2 $q $eqp74}75 76 77finish_test78