[sql] Import SQLite 3.17.0.

third_party/sqlite/src/test/tabfunc01.test

 # 2015-08-19
 #
 # 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.
 #
 #***********************************************************************
 # 
 # This file implements tests for table-valued-functions implemented using
 # eponymous virtual tables.
 #
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 set testprefix tabfunc01
 
 ifcapable !vtab {
   finish_test
   return
 }
 load_static_extension db series
+load_static_extension db carray
+load_static_extension db remember
 
 do_execsql_test tabfunc01-1.1 {
   SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2;
 } {1 | 3 | 5 | 7 | 9 |}
 do_execsql_test tabfunc01-1.2 {
   SELECT *, '|' FROM generate_series LIMIT 5;
 } {0 | 1 | 2 | 3 | 4 |}
 do_catchsql_test tabfunc01-1.3 {
   CREATE VIRTUAL TABLE t1 USING generate_series;
 } {1 {no such module: generate_series}}
@@ skipping 78 matching lines @@
 } {1 2 3 4}
 do_catchsql_test tabfunc01-4.2 {
   SELECT * FROM temp.generate_series(1,4)
 } {1 {no such table: temp.generate_series}}
 do_catchsql_test tabfunc01-4.3 {
   ATTACH ':memory:' AS aux1;
   CREATE TABLE aux1.t1(a,b,c);
   SELECT * FROM aux1.generate_series(1,4)
 } {1 {no such table: aux1.generate_series}}
 
+# The next series of tests is verifying that virtual table are able
+# to optimize the IN operator, even on terms that are not marked "omit".
+# When the generate_series virtual table is compiled for the testfixture,
+# the special -DSQLITE_SERIES_CONSTRAINT_VERIFY=1 option is used, which
+# causes the xBestIndex method of generate_series to leave the
+# sqlite3_index_constraint_usage.omit flag set to 0, which should cause
+# the SQLite core to verify the start=, stop=, and step= constraints on
+# each step of output.  At one point, the IN operator could not be used
+# by virtual tables unless omit was set.
+#
+do_execsql_test tabfunc01-500 {
+  SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
+  ORDER BY +1;
+} {1 7 11 17}
+
+# Table-valued functions on the RHS of an IN operator
+#
+do_execsql_test tabfunc01-600 {
+  CREATE TABLE t600(a INTEGER PRIMARY KEY, b TEXT);
+  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
+    INSERT INTO t600(a,b) SELECT x, printf('(%03d)',x) FROM c;
+  SELECT b FROM t600 WHERE a IN generate_series(2,52,10);
+} {(002) (012) (022) (032) (042) (052)}
+
+
+do_test tabfunc01-700 {
+  set PTR1 [intarray_addr 5 7 13 17 23]
+  db eval {
+    SELECT b FROM t600, carray($PTR1,5) WHERE a=value;
+  }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-701 {
+  db eval {
+    SELECT b FROM t600 WHERE a IN carray($PTR1,5,'int32');
+  }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-702 {
+  db eval {
+    SELECT b FROM t600 WHERE a IN carray($PTR1,4,'int32');
+  }
+} {(005) (007) (013) (017)}
+do_catchsql_test tabfunc01-710 {
+  SELECT b FROM t600 WHERE a IN carray($PTR1,5,'int33');
+} {1 {unknown datatype: 'int33'}}
+
+do_test tabfunc01-720 {
+  set PTR2 [int64array_addr 5 7 13 17 23]
+  db eval {
+    SELECT b FROM t600, carray($PTR2,5,'int64') WHERE a=value;
+  }
+} {(005) (007) (013) (017) (023)}
+do_test tabfunc01-721 {
+  db eval {
+    SELECT remember(123,$PTR2);
+    SELECT value FROM carray($PTR2,5,'int64');
+  }
+} {123 123 7 13 17 23}
+do_test tabfunc01-722 {
+  set PTR3 [expr {$PTR2+16}]
+  db eval {
+    SELECT remember(987,$PTR3);
+    SELECT value FROM carray($PTR2,5,'int64');
+  }
+} {987 123 7 987 17 23}
+
+do_test tabfunc01-730 {
+  set PTR4 [doublearray_addr 5.0 7.0 13.0 17.0 23.0]
+  db eval {
+    SELECT b FROM t600, carray($PTR4,5,'double') WHERE a=value;
+  }
+} {(005) (007) (013) (017) (023)}
+
+do_test tabfunc01-740 {
+  set PTR5 [textarray_addr x5 x7 x13 x17 x23]
+  db eval {
+    SELECT b FROM t600, carray($PTR5,5,'char*') WHERE a=trim(value,'x');
+  }
+} {(005) (007) (013) (017) (023)}
+
+do_test tabfunc01-750 {
+  db eval {
+    SELECT aa.value, bb.value, '|'
+      FROM carray($PTR4,5,'double') AS aa
+      JOIN carray($PTR5,5,'char*') AS bb ON aa.rowid=bb.rowid;
+  }
+} {5.0 x5 | 7.0 x7 | 13.0 x13 | 17.0 x17 | 23.0 x23 |}
+
+# Free up memory allocations
+intarray_addr
+int64array_addr
+doublearray_addr
+textarray_addr
+
 finish_test