Import SQLite 3.8.7.4.

third_party/sqlite/src/test/collate4.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 42 matching lines @@
 # collate4-6.*      INTEGER PRIMARY KEY indices.
 #
 
 #
 # These tests - collate4-1.* - check that indices are correctly
 # selected or not selected to implement ORDER BY clauses when 
 # user defined collation sequences are involved. 
 #
 # Because these tests also exercise all the different ways indices 
 # can be created, they also serve to verify that indices are correctly 
-# initialised with user-defined collation sequences when they are
+# initialized with user-defined collation sequences when they are
 # created.
 #
 # Tests named collate4-1.1.* use indices with a single column. Tests
 # collate4-1.2.* use indices with two columns.
 #
 do_test collate4-1.1.0 {
   execsql {
     CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
     INSERT INTO collate4t1 VALUES( 'a', 'a' );
     INSERT INTO collate4t1 VALUES( 'b', 'b' );
@@ skipping 13 matching lines @@
 do_test collate4-1.1.3 {
   cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
 } {{} A B a b sort}
 do_test collate4-1.1.4 {
   cksort {SELECT b FROM collate4t1 ORDER BY b}
 } {{} A B a b nosort}
 do_test collate4-1.1.5 {
   cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
 } {{} A B a b nosort}
 do_test collate4-1.1.6 {
-  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
+  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid}
 } {{} a A b B sort}
 
 do_test collate4-1.1.7 {
   execsql {
     CREATE TABLE collate4t2(
       a PRIMARY KEY COLLATE NOCASE, 
       b UNIQUE COLLATE TEXT
     );
     INSERT INTO collate4t2 VALUES( 'a', 'a' );
     INSERT INTO collate4t2 VALUES( NULL, NULL );
@@ skipping 56 matching lines @@
     INSERT INTO collate4t4 VALUES( 'a', 'a' );
     INSERT INTO collate4t4 VALUES( 'b', 'b' );
     INSERT INTO collate4t4 VALUES( NULL, NULL );
     INSERT INTO collate4t4 VALUES( 'B', 'B' );
     INSERT INTO collate4t4 VALUES( 'A', 'A' );
     CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
     CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
   }
 } {}
 do_test collate4-1.1.22 {
-  cksort {SELECT a FROM collate4t4 ORDER BY a}
+  cksort {SELECT a FROM collate4t4 ORDER BY a, rowid}
 } {{} a A b B sort}
 do_test collate4-1.1.23 {
-  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
+  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid}
 } {{} a A b B sort}
 do_test collate4-1.1.24 {
-  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
+  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid}
 } {{} A B a b nosort}
 do_test collate4-1.1.25 {
   cksort {SELECT b FROM collate4t4 ORDER BY b}
 } {{} A B a b sort}
 do_test collate4-1.1.26 {
   cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
 } {{} A B a b sort}
 do_test collate4-1.1.27 {
   cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
 } {{} a A b B nosort}
@@ skipping 24 matching lines @@
 do_test collate4-1.2.2 {
   cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
 } {{} A a B b nosort}
 do_test collate4-1.2.3 {
   cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
 } {{} A B a b sort}
 do_test collate4-1.2.4 {
   cksort {SELECT a FROM collate4t1 ORDER BY a, b}
 } {{} A a B b nosort}
 do_test collate4-1.2.5 {
-  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
+  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid}
 } {{} a A b B sort}
 do_test collate4-1.2.6 {
   cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
 } {{} A a B b nosort}
 
 do_test collate4-1.2.7 {
   execsql {
     CREATE TABLE collate4t2(
       a COLLATE NOCASE, 
       b COLLATE TEXT, 
@@ skipping 28 matching lines @@
     CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);
     INSERT INTO collate4t3 VALUES( 'a', 'a' );
     INSERT INTO collate4t3 VALUES( 'b', 'b' );
     INSERT INTO collate4t3 VALUES( NULL, NULL );
     INSERT INTO collate4t3 VALUES( 'B', 'B' );
     INSERT INTO collate4t3 VALUES( 'A', 'A' );
     CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
   }
 } {}
 do_test collate4-1.2.15 {
-  cksort {SELECT a FROM collate4t3 ORDER BY a}
+  cksort {SELECT a FROM collate4t3 ORDER BY a, rowid}
 } {{} a A b B sort}
 do_test collate4-1.2.16 {
-  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
+  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid}
 } {{} a A b B sort}
 do_test collate4-1.2.17 {
   cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
 } {{} A B a b nosort}
 do_test collate4-1.2.18 {
   cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
 } {{} A B a b sort}
 do_test collate4-1.2.19 {
   cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}
 } {{} A B a b nosort}
@@ skipping 69 matching lines @@
   count {
     SELECT * FROM collate4t2, collate4t1 WHERE b = a;
   }
 } {A A 19}
 do_test collate4-2.1.4 {
   execsql {
     DROP INDEX collate4i1;
     CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
   }
   count {
-    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
+    SELECT * FROM collate4t2, collate4t1 WHERE a = b
+     ORDER BY collate4t2.rowid, collate4t1.rowid
   }
 } {A a A A 19}
 do_test collate4-2.1.5 {
   count {
     SELECT * FROM collate4t2, collate4t1 WHERE b = a;
   }
 } {A A 4}
 ifcapable subquery {
   do_test collate4-2.1.6 {
     count {
-      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
+      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
+       ORDER BY rowid
     }
   } {a A 10}
   do_test collate4-2.1.7 {
     execsql {
       DROP INDEX collate4i1;
       CREATE INDEX collate4i1 ON collate4t1(a);
     }
     count {
-      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
+      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
+       ORDER BY rowid
     }
   } {a A 6}
   do_test collate4-2.1.8 {
     count {
       SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
     }
   } {a A 5}
   do_test collate4-2.1.9 {
     execsql {
       DROP INDEX collate4i1;
       CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
     }
     count {
-      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
+      SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid;
     }
   } {a A 9}
 }
 do_test collate4-2.1.10 {
   execsql {
     DROP TABLE collate4t1;
     DROP TABLE collate4t2;
   }
 } {}
 
@@ skipping 51 matching lines @@
 do_test collate4-3.0 {
   execsql {
     CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
   }
 } {}
 do_test collate4-3.1 {
   catchsql {
     INSERT INTO collate4t1 VALUES('abc');
     INSERT INTO collate4t1 VALUES('ABC');
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.2 {
   execsql {
     SELECT * FROM collate4t1;
   }
 } {abc}
 do_test collate4-3.3 {
   catchsql {
     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.4 {
   catchsql {
     INSERT INTO collate4t1 VALUES(1);
     UPDATE collate4t1 SET a = 'abc';
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.5 {
   execsql {
     DROP TABLE collate4t1;
     CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
   }
 } {}
 do_test collate4-3.6 {
   catchsql {
     INSERT INTO collate4t1 VALUES('abc');
     INSERT INTO collate4t1 VALUES('ABC');
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.7 {
   execsql {
     SELECT * FROM collate4t1;
   }
 } {abc}
 do_test collate4-3.8 {
   catchsql {
     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.9 {
   catchsql {
     INSERT INTO collate4t1 VALUES(1);
     UPDATE collate4t1 SET a = 'abc';
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.10 {
   execsql {
     DROP TABLE collate4t1;
     CREATE TABLE collate4t1(a);
     CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
   }
 } {}
 do_test collate4-3.11 {
   catchsql {
     INSERT INTO collate4t1 VALUES('abc');
     INSERT INTO collate4t1 VALUES('ABC');
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.12 {
   execsql {
     SELECT * FROM collate4t1;
   }
 } {abc}
 do_test collate4-3.13 {
   catchsql {
     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 do_test collate4-3.14 {
   catchsql {
     INSERT INTO collate4t1 VALUES(1);
     UPDATE collate4t1 SET a = 'abc';
   }
-} {1 {column a is not unique}}
+} {1 {UNIQUE constraint failed: collate4t1.a}}
 
 do_test collate4-3.15 {
   execsql {
     DROP TABLE collate4t1;
   }
 } {}
 
 # Mimic the SQLite 2 collation type NUMERIC.
 db collate numeric numeric_collate
 proc numeric_collate {lhs rhs} {
@@ skipping 135 matching lines @@
     SELECT * FROM collate4t1 ORDER BY oid;
   }
 } {10 15 101 nosort}
 do_test collate4-6.3 {
   cksort {
     SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
   }
 } {10 101 15 sort}
 
 finish_test