Import SQLite 3.8.7.4.

third_party/sqlite/src/test/where.test

 # 2001 September 15
 #
 # 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.
 #
 #***********************************************************************
@@ skipping 47 matching lines @@
 }
 
 # Verify that queries use an index.  We are using the special variable
 # "sqlite_search_count" which tallys the number of executions of MoveTo
 # and Next operators in the VDBE.  By verifing that the search count is
 # small we can be assured that indices are being used properly.
 #
 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}
 do_test where-1.1.4 {
   db eval {SELECT x, y, w FROM t1 WHERE +w=10}
 } {3 121 10}
 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}
 do_test where-1.2.1 {
   count {SELECT x, y, w FROM t1 WHERE w=11}
 } {3 144 11 3}
 do_test where-1.2.2 {
   count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
 } {3 144 11 3}
 do_test where-1.3.1 {
   count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
 } {3 144 11 3}
 do_test where-1.3.2 {
   count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
 } {3 144 11 3}
 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}
 do_test where-1.7 {
   count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
 } {3 144 3}
 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}
 do_test where-1.10 {
   count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
 } {3 121 3}
 do_test where-1.11 {
   count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
 } {3 100 3}
 
@@ skipping 83 matching lines @@
   count {SELECT w FROM t1 WHERE w>96 AND w==97}
 } {97 2}
 do_test where-1.33.5  {
   count {SELECT w FROM t1 WHERE w==97 AND w==97}
 } {97 2}
 do_test where-1.34 {
   count {SELECT w FROM t1 WHERE w+1==98}
 } {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}
 
 do_test where-1.38 {
   count {SELECT (w) FROM t1 WHERE (w)>(97)}
 } {98 99 100 3}
 do_test where-1.39 {
   count {SELECT (w) FROM t1 WHERE (w)>=(97)}
 } {97 98 99 100 4}
@@ skipping 117 matching lines @@
   do_test where-5.1 {
     count {
       SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
     }
   } {1 0 4 2 1 9 3 1 16 4}
   do_test where-5.2 {
     count {
       SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
     }
   } {1 0 4 2 1 9 3 1 16 102}
-  do_test where-5.3 {
+  do_test where-5.3a {
     count {
       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
     }
-  } {1 0 4 2 1 9 3 1 16 14}
+  } {1 0 4 2 1 9 3 1 16 13}
+  do_test where-5.3b {
+    count {
+      SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
+    }
+  } {1 0 4 2 1 9 3 1 16 13}
+  do_test where-5.3c {
+    count {
+      SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
+    }
+  } {1 0 4 2 1 9 3 1 16 13}
+  do_test where-5.3d {
+    count {
+      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
+    }
+  } {3 1 16 2 1 9 1 0 4 12}
   do_test where-5.4 {
     count {
       SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
     }
   } {1 0 4 2 1 9 3 1 16 102}
   do_test where-5.5 {
     count {
       SELECT * FROM t1 WHERE rowid IN 
          (select rowid from t1 where rowid IN (-1,2,4))
       ORDER BY 1;
@@ skipping 48 matching lines @@
   do_test where-5.14 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
     }
   } {2 1 9 8}
   do_test where-5.15 {
     count {
       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
     }
   } {2 1 9 3 1 16 11}
+  do_test where-5.100 {
+    db eval {
+      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
+       ORDER BY x, y
+    }
+  } {2 1 9 54 5 3025 62 5 3969}
+  do_test where-5.101 {
+    db eval {
+      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
+       ORDER BY x DESC, y DESC
+    }
+  } {62 5 3969 54 5 3025 2 1 9}
+  do_test where-5.102 {
+    db eval {
+      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
+       ORDER BY x DESC, y
+    }
+  } {54 5 3025 62 5 3969 2 1 9}
+  do_test where-5.103 {
+    db eval {
+      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
+       ORDER BY x, y DESC
+    }
+  } {2 1 9 62 5 3969 54 5 3025}
 }
 
 # This procedure executes the SQL.  Then it checks to see if the OP_Sort
 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
 # to the result.  If no OP_Sort happened, then "nosort" is appended.
 #
 # This procedure is used to check to make sure sorting is or is not
 # occurring as expected.
 #
 proc cksort {sql} {
@@ skipping 39 matching lines @@
   cksort {
     SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
   }
 } {1 100 4 2 99 9 3 98 16 nosort}
 do_test where-6.7 {
   cksort {
     SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
   }
 } {1 100 4 2 99 9 3 98 16 nosort}
 ifcapable subquery {
-  do_test where-6.8 {
+  do_test where-6.8a {
     cksort {
       SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
     }
-  } {1 100 4 2 99 9 3 98 16 sort}
+  } {1 100 4 2 99 9 3 98 16 nosort}
+  do_test where-6.8b {
+    cksort {
+      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3
+    }
+  } {9 92 100 7 94 64 5 96 36 nosort}
 }
 do_test where-6.9.1 {
   cksort {
     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
   }
 } {1 100 4 nosort}
 do_test where-6.9.1.1 {
   cksort {
     SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
   }
@@ skipping 25 matching lines @@
 } {1 100 4 nosort}
 do_test where-6.9.6 {
   cksort {
     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
   }
 } {1 100 4 nosort}
 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
   }
 } {1 100 4 nosort}
 do_test where-6.9.9 {
   cksort {
     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
   }
 } {1 100 4 nosort}
@@ skipping 497 matching lines @@
   cksort {
     SELECT * FROM t7 ORDER BY a ASC, b DESC;
   }
 } {1 one 4 four nosort}
 
 # Ticket #2211.
 #
 # When optimizing out ORDER BY clauses, make sure that trailing terms
 # of the ORDER BY clause do not reference other tables in a join.
 #
+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
   } 
-} {1/4 1/1 4/4 4/1 sort}
+} {1/4 1/1 4/4 4/1 nosort}
 do_test where-14.2 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
   } 
-} {1/1 1/4 4/1 4/4 sort}
+} {1/1 1/4 4/1 4/4 nosort}
 do_test where-14.3 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
   } 
-} {1/1 1/4 4/1 4/4 nosort}
+} {1/4 1/1 4/4 4/1 nosort}
 do_test where-14.4 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
   } 
-} {1/1 1/4 4/1 4/4 nosort}
+} {1/4 1/1 4/4 4/1 nosort}
 do_test where-14.5 {
+  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
   } 
-} {4/1 4/4 1/1 1/4 nosort}
+} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
 do_test where-14.6 {
+  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
   } 
-} {4/1 4/4 1/1 1/4 nosort}
+} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
 do_test where-14.7 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
   } 
 } {4/1 4/4 1/1 1/4 sort}
 do_test where-14.7.1 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
   } 
 } {4/1 4/4 1/1 1/4 sort}
 do_test where-14.7.2 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
   } 
-} {4/1 4/4 1/1 1/4 nosort}
+} {4/4 4/1 1/4 1/1 nosort}
 do_test where-14.8 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
   } 
 } {4/4 4/1 1/4 1/1 sort}
 do_test where-14.9 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
   } 
 } {4/4 4/1 1/4 1/1 sort}
 do_test where-14.10 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
   } 
 } {4/1 4/4 1/1 1/4 sort}
 do_test where-14.11 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
   } 
 } {4/1 4/4 1/1 1/4 sort}
 do_test where-14.12 {
   cksort {
     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
   } 
 } {4/4 4/1 1/4 1/1 sort}
+} ;# {permutation != "no_optimization"}
 
 # Ticket #2445.
 #
 # There was a crash that could occur when a where clause contains an
 # alias for an expression in the result set, and that expression retrieves
 # a column of the second or subsequent table in a join.
 #
 do_test where-15.1 {
   execsql {
     CREATE TEMP TABLE t1 (a, b, c, d, e);
@@ skipping 83 matching lines @@
   }
 } {1.5 42}
 do_test where-17.5 {
   execsql {
     CREATE TABLE tother(a, b);
     INSERT INTO tother VALUES(1, 3.7);
     SELECT id, a FROM tbooking, tother WHERE id>a;
   }
 } {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