From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001
From: Hans-Christoph Steiner <hans@eds.org>
Date: Thu, 16 Oct 2014 22:51:35 -0400
Subject: Imported Upstream version 3.2.0

---
 test/tkt-b75a9ca6b0.test | 77 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 77 insertions(+)
 create mode 100644 test/tkt-b75a9ca6b0.test

(limited to 'test/tkt-b75a9ca6b0.test')

diff --git a/test/tkt-b75a9ca6b0.test b/test/tkt-b75a9ca6b0.test
new file mode 100644
index 0000000..0c81a53
--- /dev/null
+++ b/test/tkt-b75a9ca6b0.test
@@ -0,0 +1,77 @@
+# 2014-04-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.
+#
+#*************************************************************************
+#
+# Test that ticket [b75a9ca6b0] has been fixed.
+#
+# Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY
+# and an ORDER BY.  This code verifies that SQLite is able to
+# optimize out the ORDER BY in some circumstances, but retains the
+# ORDER BY when necessary.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix tkt-b75a9ca6b0
+
+do_execsql_test 1 {
+  CREATE TABLE t1 (x, y);
+  INSERT INTO t1 VALUES (1, 3); 
+  INSERT INTO t1 VALUES (2, 2);
+  INSERT INTO t1 VALUES (3, 1);
+}
+
+do_execsql_test 1.1 {
+  CREATE INDEX i1 ON t1(x, y);
+} 
+
+set idxscan {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
+set tblscan {0 0 0 {SCAN TABLE t1}}
+set grpsort {0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
+set sort    {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
+
+foreach {tn q res eqp} [subst -nocommands {
+  1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y"
+  {1 3  2 2  3 1} {$idxscan}
+
+  2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x"
+  {1 3  2 2  3 1} {$idxscan $sort}
+
+  3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x"
+  {3 1  2 2  1 3} {$idxscan $sort}
+  
+  4 "SELECT * FROM t1 GROUP BY x ORDER BY x"
+  {1 3  2 2  3 1} {$idxscan}
+
+  5 "SELECT * FROM t1 GROUP BY y ORDER BY y"
+  {3 1  2 2  1 3} {$tblscan $grpsort}
+
+  6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
+  {1 3  2 2  3 1} {$tblscan $grpsort $sort}
+
+  7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
+  {1 3  2 2  3 1} {$idxscan $sort}
+
+  8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
+  {3 1  2 2  1 3} {$idxscan $sort}
+
+  9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
+  {1 3  2 2  3 1} {$idxscan}
+
+  10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
+  {1 3  2 2  3 1} {$idxscan $sort}
+
+}] {
+  do_execsql_test 1.$tn.1 $q $res
+  do_eqp_test     1.$tn.2 $q $eqp
+}
+
+
+finish_test
-- 
cgit v1.2.3