Add //third_party/sqlite to dirs_to_snapshot, remove net_sql.patch

third_party/sqlite/sqlite-src-3080704/test/whereG.test

+# 2013-09-05
+#
+# 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 cases for query planning decisions and the likely(), unlikely(), and
+# likelihood() functions.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix whereG
+
+do_execsql_test whereG-1.0 {
+  CREATE TABLE composer(
+    cid INTEGER PRIMARY KEY,
+    cname TEXT
+  );
+  CREATE TABLE album(
+    aid INTEGER PRIMARY KEY,
+    aname TEXT
+  );
+  CREATE TABLE track(
+    tid INTEGER PRIMARY KEY,
+    cid INTEGER REFERENCES composer,
+    aid INTEGER REFERENCES album,
+    title TEXT
+  );
+  CREATE INDEX track_i1 ON track(cid);
+  CREATE INDEX track_i2 ON track(aid);
+  INSERT INTO composer VALUES(1, 'W. A. Mozart');
+  INSERT INTO composer VALUES(2, 'Beethoven');
+  INSERT INTO composer VALUES(3, 'Thomas Tallis');
+  INSERT INTO composer VALUES(4, 'Joseph Hayden');
+  INSERT INTO composer VALUES(5, 'Thomas Weelkes');
+  INSERT INTO composer VALUES(6, 'J. S. Bach');
+  INSERT INTO composer VALUES(7, 'Orlando Gibbons');
+  INSERT INTO composer VALUES(8, 'Josquin des Prés');
+  INSERT INTO composer VALUES(9, 'Byrd');
+  INSERT INTO composer VALUES(10, 'Francis Poulenc');
+  INSERT INTO composer VALUES(11, 'Mendelsshon');
+  INSERT INTO composer VALUES(12, 'Zoltán Kodály');
+  INSERT INTO composer VALUES(13, 'Handel');
+  INSERT INTO album VALUES(100, 'Kodály: Missa Brevis');
+  INSERT INTO album VALUES(101, 'Messiah');
+  INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65');
+  INSERT INTO album VALUES(103, 'The complete English anthems');
+  INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232');
+  INSERT INTO track VALUES(10005, 12, 100, 'Sanctus');
+  INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei');
+  INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs');
+  INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death');
+  INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei');
+  INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me');
+  INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus');
+  INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis');
+} {}
+do_eqp_test whereG-1.1 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE unlikely(cname LIKE '%bach%')
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {/.*composer.*track.*album.*/}
+do_execsql_test whereG-1.2 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE unlikely(cname LIKE '%bach%')
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {{Mass in B Minor, BWV 232}}
+
+do_eqp_test whereG-1.3 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE likelihood(cname LIKE '%bach%', 0.5)
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {/.*track.*composer.*album.*/}
+do_execsql_test whereG-1.4 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE likelihood(cname LIKE '%bach%', 0.5)
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {{Mass in B Minor, BWV 232}}
+
+do_eqp_test whereG-1.5 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE cname LIKE '%bach%'
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {/.*track.*(composer.*album|album.*composer).*/}
+do_execsql_test whereG-1.6 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE cname LIKE '%bach%'
+     AND composer.cid=track.cid
+     AND album.aid=track.aid;
+} {{Mass in B Minor, BWV 232}}
+
+do_eqp_test whereG-1.7 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE cname LIKE '%bach%'
+     AND unlikely(composer.cid=track.cid)
+     AND unlikely(album.aid=track.aid);
+} {/.*track.*(composer.*album|album.*composer).*/}
+do_execsql_test whereG-1.8 {
+  SELECT DISTINCT aname
+    FROM album, composer, track
+   WHERE cname LIKE '%bach%'
+     AND unlikely(composer.cid=track.cid)
+     AND unlikely(album.aid=track.aid);
+} {{Mass in B Minor, BWV 232}}
+
+do_test whereG-2.1 {
+  catchsql {
+    SELECT DISTINCT aname
+      FROM album, composer, track
+     WHERE likelihood(cname LIKE '%bach%', -0.01)
+       AND composer.cid=track.cid
+       AND album.aid=track.aid;
+  }
+} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
+do_test whereG-2.2 {
+  catchsql {
+    SELECT DISTINCT aname
+      FROM album, composer, track
+     WHERE likelihood(cname LIKE '%bach%', 1.01)
+       AND composer.cid=track.cid
+       AND album.aid=track.aid;
+  }
+} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
+do_test whereG-2.3 {
+  catchsql {
+    SELECT DISTINCT aname
+      FROM album, composer, track
+     WHERE likelihood(cname LIKE '%bach%', track.cid)
+       AND composer.cid=track.cid
+       AND album.aid=track.aid;
+  }
+} {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
+
+# Commuting a term of the WHERE clause should not change the query plan
+#
+do_execsql_test whereG-3.0 {
+  CREATE TABLE a(a1 PRIMARY KEY, a2);
+  CREATE TABLE b(b1 PRIMARY KEY, b2);
+} {}
+do_eqp_test whereG-3.1 {
+  SELECT * FROM a, b WHERE b1=a1 AND a2=5;
+} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
+do_eqp_test whereG-3.2 {
+  SELECT * FROM a, b WHERE a1=b1 AND a2=5;
+} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
+do_eqp_test whereG-3.3 {
+  SELECT * FROM a, b WHERE a2=5 AND b1=a1;
+} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
+do_eqp_test whereG-3.4 {
+  SELECT * FROM a, b WHERE a2=5 AND a1=b1;
+} {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
+
+# Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]:
+# Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy
+# where an OP_Copy was needed.
+#
+do_execsql_test whereG-4.0 {
+  CREATE TABLE t4(x);
+  INSERT INTO t4 VALUES('right'),('wrong');
+  SELECT DISTINCT x
+   FROM (SELECT x FROM t4 GROUP BY x)
+   WHERE x='right'
+   ORDER BY x;
+} {right}
+
+#-------------------------------------------------------------------------
+# Test that likelihood() specifications on indexed terms are taken into 
+# account by various forms of loops.
+#
+#   5.1.*: open ended range scans
+#   5.2.*: skip-scans
+#
+reset_db
+
+do_execsql_test 5.1 {
+  CREATE TABLE t1(a, b, c);
+  CREATE INDEX i1 ON t1(a, b);
+}
+do_eqp_test 5.1.2 {
+  SELECT * FROM t1 WHERE a>?
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
+do_eqp_test 5.1.3 {
+  SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
+} {0 0 0 {SCAN TABLE t1}}
+do_eqp_test 5.1.4 {
+  SELECT * FROM t1 WHERE likely(a>?)
+} {0 0 0 {SCAN TABLE t1}}
+
+do_test 5.2 {
+  for {set i 0} {$i < 100} {incr i} {
+    execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
+  }
+  execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
+  execsql { ANALYZE }
+} {}
+do_eqp_test 5.2.2 {
+  SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}}
+do_eqp_test 5.2.3 {
+  SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
+} {0 0 0 {SCAN TABLE t1}}
+do_eqp_test 5.2.4 {
+  SELECT * FROM t1 WHERE likely(b>?)
+} {0 0 0 {SCAN TABLE t1}}
+
+do_eqp_test 5.3.1 {
+  SELECT * FROM t1 WHERE a=?
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
+do_eqp_test 5.3.2 {
+  SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
+} {0 0 0 {SCAN TABLE t1}}
+do_eqp_test 5.3.3 {
+  SELECT * FROM t1 WHERE likely(a=?)
+} {0 0 0 {SCAN TABLE t1}}
+
+finish_test