sqlite source (unix build) added to libraries

1 files changed, 617 insertions, 0 deletions

diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/select4.test b/libraries/sqlite/unix/sqlite-3.5.1/test/select4.test
new file mode 100644
index 0000000..5c3b808
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/select4.test
@@ -0,0 +1,617 @@
+# 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.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing UNION, INTERSECT and EXCEPT operators
+# in SELECT statements.
+#
+# $Id: select4.test,v 1.20 2006/06/20 11:01:09 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Most tests in this file depend on compound-select. But there are a couple
+# right at the end that test DISTINCT, so we cannot omit the entire file.
+#
+ifcapable compound {
+
+# Build some test data
+#
+execsql {
+  CREATE TABLE t1(n int, log int);
+  BEGIN;
+}
+for {set i 1} {$i<32} {incr i} {
+  for {set j 0} {pow(2,$j)<$i} {incr j} {}
+  execsql "INSERT INTO t1 VALUES($i,$j)"
+}
+execsql {
+  COMMIT;
+}
+
+do_test select4-1.0 {
+  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
+} {0 1 2 3 4 5}
+
+# Union All operator
+#
+do_test select4-1.1a {
+  lsort [execsql {SELECT DISTINCT log FROM t1}]
+} {0 1 2 3 4 5}
+do_test select4-1.1b {
+  lsort [execsql {SELECT n FROM t1 WHERE log=3}]
+} {5 6 7 8}
+do_test select4-1.1c {
+  execsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {0 1 2 3 4 5 5 6 7 8}
+do_test select4-1.1d {
+  execsql {
+    CREATE TABLE t2 AS
+      SELECT DISTINCT log FROM t1
+      UNION ALL
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log;
+    SELECT * FROM t2;
+  }
+} {0 1 2 3 4 5 5 6 7 8}
+execsql {DROP TABLE t2}
+do_test select4-1.1e {
+  execsql {
+    CREATE TABLE t2 AS
+      SELECT DISTINCT log FROM t1
+      UNION ALL
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log DESC;
+    SELECT * FROM t2;
+  }
+} {8 7 6 5 5 4 3 2 1 0}
+execsql {DROP TABLE t2}
+do_test select4-1.1f {
+  execsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=2
+  }
+} {0 1 2 3 4 5 3 4}
+do_test select4-1.1g {
+  execsql {
+    CREATE TABLE t2 AS 
+      SELECT DISTINCT log FROM t1
+      UNION ALL
+      SELECT n FROM t1 WHERE log=2;
+    SELECT * FROM t2;
+  }
+} {0 1 2 3 4 5 3 4}
+execsql {DROP TABLE t2}
+ifcapable subquery {
+  do_test select4-1.2 {
+    execsql {
+      SELECT log FROM t1 WHERE n IN 
+        (SELECT DISTINCT log FROM t1 UNION ALL
+         SELECT n FROM t1 WHERE log=3)
+      ORDER BY log;
+    }
+  } {0 1 2 2 3 3 3 3}
+}
+do_test select4-1.3 {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1 ORDER BY log
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY clause should come after UNION ALL not before}}
+
+# Union operator
+#
+do_test select4-2.1 {
+  execsql {
+    SELECT DISTINCT log FROM t1
+    UNION
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {0 1 2 3 4 5 6 7 8}
+ifcapable subquery {
+  do_test select4-2.2 {
+    execsql {
+      SELECT log FROM t1 WHERE n IN 
+        (SELECT DISTINCT log FROM t1 UNION
+         SELECT n FROM t1 WHERE log=3)
+      ORDER BY log;
+    }
+  } {0 1 2 2 3 3 3 3}
+}
+do_test select4-2.3 {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1 ORDER BY log
+    UNION
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY clause should come after UNION not before}}
+
+# Except operator
+#
+do_test select4-3.1.1 {
+  execsql {
+    SELECT DISTINCT log FROM t1
+    EXCEPT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {0 1 2 3 4}
+do_test select4-3.1.2 {
+  execsql {
+    CREATE TABLE t2 AS 
+      SELECT DISTINCT log FROM t1
+      EXCEPT
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log;
+    SELECT * FROM t2;
+  }
+} {0 1 2 3 4}
+execsql {DROP TABLE t2}
+do_test select4-3.1.3 {
+  execsql {
+    CREATE TABLE t2 AS 
+      SELECT DISTINCT log FROM t1
+      EXCEPT
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log DESC;
+    SELECT * FROM t2;
+  }
+} {4 3 2 1 0}
+execsql {DROP TABLE t2}
+ifcapable subquery {
+  do_test select4-3.2 {
+    execsql {
+      SELECT log FROM t1 WHERE n IN 
+        (SELECT DISTINCT log FROM t1 EXCEPT
+         SELECT n FROM t1 WHERE log=3)
+      ORDER BY log;
+    }
+  } {0 1 2 2}
+}
+do_test select4-3.3 {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1 ORDER BY log
+    EXCEPT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY clause should come after EXCEPT not before}}
+
+# Intersect operator
+#
+do_test select4-4.1.1 {
+  execsql {
+    SELECT DISTINCT log FROM t1
+    INTERSECT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {5}
+
+do_test select4-4.1.2 {
+  execsql {
+    SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
+    INTERSECT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {5 6}
+do_test select4-4.1.3 {
+  execsql {
+    CREATE TABLE t2 AS
+      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
+      INTERSECT
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log;
+    SELECT * FROM t2;
+  }
+} {5 6}
+execsql {DROP TABLE t2}
+do_test select4-4.1.4 {
+  execsql {
+    CREATE TABLE t2 AS
+      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
+      INTERSECT
+      SELECT n FROM t1 WHERE log=3
+      ORDER BY log DESC;
+    SELECT * FROM t2;
+  }
+} {6 5}
+execsql {DROP TABLE t2}
+ifcapable subquery {
+  do_test select4-4.2 {
+    execsql {
+      SELECT log FROM t1 WHERE n IN 
+        (SELECT DISTINCT log FROM t1 INTERSECT
+         SELECT n FROM t1 WHERE log=3)
+      ORDER BY log;
+    }
+  } {3}
+}
+do_test select4-4.3 {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1 ORDER BY log
+    INTERSECT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY clause should come after INTERSECT not before}}
+
+# Various error messages while processing UNION or INTERSECT
+#
+do_test select4-5.1 {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t2
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {no such table: t2}}
+do_test select4-5.2 {
+  set v [catch {execsql {
+    SELECT DISTINCT log AS "xyzzy" FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY xyzzy;
+  }} msg]
+  lappend v $msg
+} {0 {0 1 2 3 4 5 5 6 7 8}}
+do_test select4-5.2b {
+  set v [catch {execsql {
+    SELECT DISTINCT log AS xyzzy FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY 'xyzzy';
+  }} msg]
+  lappend v $msg
+} {0 {0 1 2 3 4 5 5 6 7 8}}
+do_test select4-5.2c {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY 'xyzzy';
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY term number 1 does not match any result column}}
+do_test select4-5.2d {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1
+    INTERSECT
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY 'xyzzy';
+  }} msg]
+  lappend v $msg
+} {1 {ORDER BY term number 1 does not match any result column}}
+do_test select4-5.2e {
+  set v [catch {execsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY n;
+  }} msg]
+  lappend v $msg
+} {0 {0 1 2 3 4 5 5 6 7 8}}
+do_test select4-5.2f {
+  catchsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }
+} {0 {0 1 2 3 4 5 5 6 7 8}}
+do_test select4-5.2g {
+  catchsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY 1;
+  }
+} {0 {0 1 2 3 4 5 5 6 7 8}}
+do_test select4-5.2h {
+  catchsql {
+    SELECT DISTINCT log FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY 2;
+  }
+} {1 {ORDER BY position 2 should be between 1 and 1}}
+do_test select4-5.2i {
+  catchsql {
+    SELECT DISTINCT 1, log FROM t1
+    UNION ALL
+    SELECT 2, n FROM t1 WHERE log=3
+    ORDER BY 2, 1;
+  }
+} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
+do_test select4-5.2j {
+  catchsql {
+    SELECT DISTINCT 1, log FROM t1
+    UNION ALL
+    SELECT 2, n FROM t1 WHERE log=3
+    ORDER BY 1, 2 DESC;
+  }
+} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
+do_test select4-5.2k {
+  catchsql {
+    SELECT DISTINCT 1, log FROM t1
+    UNION ALL
+    SELECT 2, n FROM t1 WHERE log=3
+    ORDER BY n, 1;
+  }
+} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
+do_test select4-5.3 {
+  set v [catch {execsql {
+    SELECT DISTINCT log, n FROM t1
+    UNION ALL
+    SELECT n FROM t1 WHERE log=3
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
+do_test select4-5.4 {
+  set v [catch {execsql {
+    SELECT log FROM t1 WHERE n=2
+    UNION ALL
+    SELECT log FROM t1 WHERE n=3
+    UNION ALL
+    SELECT log FROM t1 WHERE n=4
+    UNION ALL
+    SELECT log FROM t1 WHERE n=5
+    ORDER BY log;
+  }} msg]
+  lappend v $msg
+} {0 {1 2 2 3}}
+
+do_test select4-6.1 {
+  execsql {
+    SELECT log, count(*) as cnt FROM t1 GROUP BY log
+    UNION
+    SELECT log, n FROM t1 WHERE n=7
+    ORDER BY cnt, log;
+  }
+} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
+do_test select4-6.2 {
+  execsql {
+    SELECT log, count(*) FROM t1 GROUP BY log
+    UNION
+    SELECT log, n FROM t1 WHERE n=7
+    ORDER BY count(*), log;
+  }
+} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
+
+# NULLs are indistinct for the UNION operator.
+# Make sure the UNION operator recognizes this
+#
+do_test select4-6.3 {
+  execsql {
+    SELECT NULL UNION SELECT NULL UNION
+    SELECT 1 UNION SELECT 2 AS 'x'
+    ORDER BY x;
+  }
+} {{} 1 2}
+do_test select4-6.3.1 {
+  execsql {
+    SELECT NULL UNION ALL SELECT NULL UNION ALL
+    SELECT 1 UNION ALL SELECT 2 AS 'x'
+    ORDER BY x;
+  }
+} {{} {} 1 2}
+
+# Make sure the DISTINCT keyword treats NULLs as indistinct.
+#
+ifcapable subquery {
+  do_test select4-6.4 {
+    execsql {
+      SELECT * FROM (
+         SELECT NULL, 1 UNION ALL SELECT NULL, 1
+      );
+    }
+  } {{} 1 {} 1}
+  do_test select4-6.5 {
+    execsql {
+      SELECT DISTINCT * FROM (
+         SELECT NULL, 1 UNION ALL SELECT NULL, 1
+      );
+    }
+  } {{} 1}
+  do_test select4-6.6 {
+    execsql {
+      SELECT DISTINCT * FROM (
+         SELECT 1,2  UNION ALL SELECT 1,2
+      );
+    }
+  } {1 2}
+}
+
+# Test distinctness of NULL in other ways.
+#
+do_test select4-6.7 {
+  execsql {
+    SELECT NULL EXCEPT SELECT NULL
+  }
+} {}
+
+
+# Make sure column names are correct when a compound select appears as
+# an expression in the WHERE clause.
+#
+do_test select4-7.1 {
+  execsql {
+    CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
+    SELECT * FROM t2 ORDER BY x;
+  }
+} {0 1 1 1 2 2 3 4 4 8 5 15}  
+ifcapable subquery {
+  do_test select4-7.2 {
+    execsql2 {
+      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
+      ORDER BY n
+    }
+  } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
+  do_test select4-7.3 {
+    execsql2 {
+      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
+      ORDER BY n LIMIT 2
+    }
+  } {n 6 log 3 n 7 log 3}
+  do_test select4-7.4 {
+    execsql2 {
+      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
+      ORDER BY n LIMIT 2
+    }
+  } {n 1 log 0 n 2 log 1}
+} ;# ifcapable subquery
+
+} ;# ifcapable compound
+
+# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
+do_test select4-8.1 {
+  execsql {
+    BEGIN;
+    CREATE TABLE t3(a text, b float, c text);
+    INSERT INTO t3 VALUES(1, 1.1, '1.1');
+    INSERT INTO t3 VALUES(2, 1.10, '1.10');
+    INSERT INTO t3 VALUES(3, 1.10, '1.1');
+    INSERT INTO t3 VALUES(4, 1.1, '1.10');
+    INSERT INTO t3 VALUES(5, 1.2, '1.2');
+    INSERT INTO t3 VALUES(6, 1.3, '1.3');
+    COMMIT;
+  }
+  execsql {
+    SELECT DISTINCT b FROM t3 ORDER BY c;
+  }
+} {1.1 1.2 1.3}
+do_test select4-8.2 {
+  execsql {
+    SELECT DISTINCT c FROM t3 ORDER BY c;
+  }
+} {1.1 1.10 1.2 1.3}
+
+# Make sure the names of columns are takenf rom the right-most subquery
+# right in a compound query.  Ticket #1721
+#
+ifcapable compound {
+
+do_test select4-9.1 {
+  execsql2 {
+    SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
+  }
+} {x 0 y 1}
+do_test select4-9.2 {
+  execsql2 {
+    SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
+  }
+} {x 0 y 1}
+do_test select4-9.3 {
+  execsql2 {
+    SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
+  }
+} {x 0 y 1}
+do_test select4-9.4 {
+  execsql2 {
+    SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
+  }
+} {x 0 y 1}
+do_test select4-9.5 {
+  execsql2 {
+    SELECT 0 AS x, 1 AS y
+    UNION
+    SELECT 2 AS p, 3 AS q
+    UNION
+    SELECT 4 AS a, 5 AS b
+    ORDER BY x LIMIT 1
+  }
+} {x 0 y 1}
+
+ifcapable subquery {
+do_test select4-9.6 {
+  execsql2 {
+    SELECT * FROM (
+      SELECT 0 AS x, 1 AS y
+      UNION
+      SELECT 2 AS p, 3 AS q
+      UNION
+      SELECT 4 AS a, 5 AS b
+    ) ORDER BY 1 LIMIT 1;
+  }
+} {x 0 y 1}
+do_test select4-9.7 {
+  execsql2 {
+    SELECT * FROM (
+      SELECT 0 AS x, 1 AS y
+      UNION
+      SELECT 2 AS p, 3 AS q
+      UNION
+      SELECT 4 AS a, 5 AS b
+    ) ORDER BY x LIMIT 1;
+  }
+} {x 0 y 1}
+} ;# ifcapable subquery
+
+do_test select4-9.8 {
+  execsql2 {
+    SELECT 0 AS x, 1 AS y
+    UNION
+    SELECT 2 AS y, -3 AS x
+    ORDER BY x LIMIT 1;
+  }
+} {x 0 y 1}
+do_test select4-9.9.1 {
+  execsql2 {
+    SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
+  }
+} {a 1 b 2 a 3 b 4}
+
+ifcapable subquery {
+do_test select4-9.9.2 {
+  execsql2 {
+    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
+     WHERE b=3
+  }
+} {}
+do_test select4-9.10 {
+  execsql2 {
+    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
+     WHERE b=2
+  }
+} {a 1 b 2}
+do_test select4-9.11 {
+  execsql2 {
+    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
+     WHERE b=2
+  }
+} {a 1 b 2}
+do_test select4-9.12 {
+  execsql2 {
+    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
+     WHERE b>0
+  }
+} {a 1 b 2 a 3 b 4}
+} ;# ifcapable subquery
+
+} ;# ifcapable compound
+
+finish_test