[sql] Import SQLite 3.17.0.

third_party/sqlite/src/test/indexexpr1.test

 # 2015-08-31
 #
 # 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 305 matching lines @@
   CREATE INDEX i ON t0(a in(0,1));
   INSERT INTO t0 VALUES(0,1,2),(2,3,4),(5,6,7);
   UPDATE t0 SET b=99 WHERE (a in(0,1))=0;
   SELECT *, '|' FROM t0 ORDER BY +a;
 } {0 1 2 | 2 99 4 | 5 99 7 |}
 do_execsql_test indexexpr1-1010 {
   UPDATE t0 SET b=88 WHERE (a in(0,1))=1;
   SELECT *, '|' FROM t0 ORDER BY +a;
 } {0 88 2 | 2 99 4 | 5 99 7 |}
 
+# 2016-10-10
+# Make sure indexes on expressions skip over initial NULL values in the
+# index as they are suppose to do.
+# Ticket https://www.sqlite.org/src/tktview/4baa46491212947
+#
+do_execsql_test indexexpr1-1100 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(a);
+  INSERT INTO t1 VALUES(NULL),(1);
+  SELECT '1:', typeof(a), a FROM t1 WHERE a<10;
+  SELECT '2:', typeof(a), a FROM t1 WHERE a+0<10;
+  CREATE INDEX t1x1 ON t1(a);
+  CREATE INDEX t1x2 ON t1(a+0);
+  SELECT '3:', typeof(a), a FROM t1 WHERE a<10;
+  SELECT '4:', typeof(a), a FROM t1 WHERE a+0<10;
+} {1: integer 1 2: integer 1 3: integer 1 4: integer 1}
+
+do_execsql_test indexexpr1-1200 {
+  CREATE TABLE t10(a int, b int, c int, d int);
+  INSERT INTO t10(a, b, c, d) VALUES(0, 0, 2, 2);
+  INSERT INTO t10(a, b, c, d) VALUES(0, 0, 0, 0);
+  INSERT INTO t10(a, b, c, d) VALUES(0, 0, 1, 1);
+  INSERT INTO t10(a, b, c, d) VALUES(1, 1, 1, 1);
+  INSERT INTO t10(a, b, c, d) VALUES(1, 1, 0, 0);
+  INSERT INTO t10(a, b, c, d) VALUES(2, 2, 0, 0);
+
+  SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
+} {
+  0 0 0 2 0 4 2 0 2 2 4 0
+}
+do_execsql_test indexexpr1-1200.1 {
+  CREATE INDEX t10_ab ON t10(a+b);
+}
+do_execsql_test indexexpr1-1200.2 {
+  SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
+} {
+  0 0 0 2 0 4 2 0 2 2 4 0
+}
+do_execsql_test indexexpr1-1200.3 {
+  CREATE INDEX t10_abcd ON t10(a+b,c+d);
+}
+do_execsql_test indexexpr1-1200.4 {
+  SELECT a+b, c+d FROM t10 ORDER BY a+b, c+d;
+} {
+  0 0 0 2 0 4 2 0 2 2 4 0
+}
+
+# Ticket https://www.sqlite.org/src/tktview/eb703ba7b50c1a
+# Incorrect result using an index on an expression with a collating function
+#
+do_execsql_test indexexpr1-1300.1 {
+  CREATE TABLE t1300(a INTEGER PRIMARY KEY, b);
+  INSERT INTO t1300 VALUES(1,'coffee'),(2,'COFFEE'),(3,'stress'),(4,'STRESS');
+  CREATE INDEX t1300bexpr ON t1300( substr(b,4) );
+  SELECT a FROM t1300 WHERE substr(b,4)='ess' COLLATE nocase ORDER BY +a;
+} {3 4}
 
 finish_test