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/where.test | 90 +++++++++++++++++++++++++++++++++++++--------------------
 1 file changed, 59 insertions(+), 31 deletions(-)

(limited to 'test/where.test')

diff --git a/test/where.test b/test/where.test
index 2dbc283..f560708 100644
--- a/test/where.test
+++ b/test/where.test
@@ -65,9 +65,9 @@ proc count sql {
 do_test where-1.1.1 {
   count {SELECT x, y, w FROM t1 WHERE w=10}
 } {3 121 10 3}
-do_test where-1.1.2 {
-  set sqlite_query_plan
-} {t1 i1w}
+do_eqp_test where-1.1.2 {
+  SELECT x, y, w FROM t1 WHERE w=10
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.1.3 {
   db status step
 } {0}
@@ -77,15 +77,15 @@ do_test where-1.1.4 {
 do_test where-1.1.5 {
   db status step
 } {99}
-do_test where-1.1.6 {
-  set sqlite_query_plan
-} {t1 {}}
+do_eqp_test where-1.1.6 {
+  SELECT x, y, w FROM t1 WHERE +w=10
+} {*SCAN TABLE t1*}
 do_test where-1.1.7 {
   count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
 } {3 121 10 3}
-do_test where-1.1.8 {
-  set sqlite_query_plan
-} {t1 i1w}
+do_eqp_test where-1.1.8 {
+  SELECT x, y, w AS abc FROM t1 WHERE abc=10
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.1.9 {
   db status step
 } {0}
@@ -104,21 +104,21 @@ do_test where-1.3.2 {
 do_test where-1.4.1 {
   count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
 } {11 3 144 3}
-do_test where-1.4.2 {
-  set sqlite_query_plan
-} {t1 i1w}
+do_eqp_test where-1.4.2 {
+  SELECT w, x, y FROM t1 WHERE 11=w AND x>2
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.4.3 {
   count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
 } {11 3 144 3}
-do_test where-1.4.4 {
-  set sqlite_query_plan
-} {t1 i1w}
+do_eqp_test where-1.4.4 {
+  SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.5 {
   count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
 } {3 144 3}
-do_test where-1.5.2 {
-  set sqlite_query_plan
-} {t1 i1w}
+do_eqp_test where-1.5.2 {
+  SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
+} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 do_test where-1.6 {
   count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
 } {3 144 3}
@@ -128,13 +128,12 @@ do_test where-1.7 {
 do_test where-1.8 {
   count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
 } {3 144 3}
-do_test where-1.8.2 {
-  set sqlite_query_plan
-} {t1 i1xy}
-do_test where-1.8.3 {
-  count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
-  set sqlite_query_plan
-} {{} i1xy}
+do_eqp_test where-1.8.2 {
+  SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
+} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
+do_eqp_test where-1.8.3 {
+  SELECT x, y FROM t1 WHERE y=144 AND x=3
+} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
 do_test where-1.9 {
   count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
 } {3 144 3}
@@ -238,10 +237,10 @@ do_test where-1.34 {
 } {97 99}
 do_test where-1.35 {
   count {SELECT w FROM t1 WHERE w<3}
-} {1 2 2}
+} {1 2 3}
 do_test where-1.36 {
   count {SELECT w FROM t1 WHERE w<=3}
-} {1 2 3 3}
+} {1 2 3 4}
 do_test where-1.37 {
   count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
 } {1 2 3 99}
@@ -605,7 +604,7 @@ do_test where-6.9.7 {
   cksort {
     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
   }
-} {1 100 4 sort}
+} {1 100 4 nosort}
 do_test where-6.9.8 {
   cksort {
     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
@@ -1126,9 +1125,9 @@ do_test where-13.12 {
 if {[permutation] != "no_optimization"} {
 do_test where-14.1 {
   execsql {
-    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
-    INSERT INTO t8 VALUES(1,'one');
-    INSERT INTO t8 VALUES(4,'four');
+    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100));
+    INSERT INTO t8(a,b) VALUES(1,'one');
+    INSERT INTO t8(a,b) VALUES(4,'four');
   }
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
@@ -1305,4 +1304,33 @@ do_test where-17.5 {
   }
 } {42 1 43 1}
 
+# Ticket [be84e357c035d068135f20bcfe82761bbf95006b]  2013-09-03
+# Segfault during query involving LEFT JOIN column in the ORDER BY clause.
+#
+do_execsql_test where-18.1 {
+  CREATE TABLE t181(a);
+  CREATE TABLE t182(b,c);
+  INSERT INTO t181 VALUES(1);
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
+} {1}
+do_execsql_test where-18.2 {
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
+} {1}
+do_execsql_test where-18.3 {
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
+} {1}
+do_execsql_test where-18.4 {
+  INSERT INTO t181 VALUES(1),(1),(1),(1);
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
+} {1}
+do_execsql_test where-18.5 {
+  INSERT INTO t181 VALUES(2);
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
+} {1 2}
+do_execsql_test where-18.6 {
+  INSERT INTO t181 VALUES(2);
+  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
+} {1 2}
+
+
 finish_test
-- 
cgit v1.2.3