Modelpublic
AryaWu/sqlite

sourceHugging Faceupdated 9mo agoView on Hugging Face
0likes
collate4.test705 linesDownload Raw Back to test
1#2# 2001 September 153#4# The author disclaims copyright to this source code.  In place of5# a legal notice, here is a blessing:6#7#    May you do good and not evil.8#    May you find forgiveness for yourself and forgive others.9#    May you share freely, never taking more than you give.10#11#***********************************************************************12# This file implements regression tests for SQLite library.  The13# focus of this script is page cache subsystem.14#15# $Id: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20db collate TEXT text_collate21proc text_collate {a b} {22  return [string compare $a $b]23}24 25# Do an SQL statement.  Append the search count to the end of the result.26#27proc count sql {28  set ::sqlite_search_count 029  return [concat [execsql $sql] $::sqlite_search_count]30}31 32# This procedure executes the SQL.  Then it checks the generated program33# for the SQL and appends a "nosort" to the result if the program contains the34# SortCallback opcode.  If the program does not contain the SortCallback35# opcode it appends "sort"36#37proc cksort {sql} {38  set ::sqlite_sort_count 039  set data [execsql $sql]40  if {$::sqlite_sort_count} {set x sort} {set x nosort}41  lappend data $x42  return $data43}44 45# 46# Test cases are organized roughly as follows:47#48# collate4-1.*      ORDER BY.49# collate4-2.*      WHERE clauses.50# collate4-3.*      constraints (primary key, unique).51# collate4-4.*      simple min() or max() queries.52# collate4-5.*      REINDEX command53# collate4-6.*      INTEGER PRIMARY KEY indices.54#55 56#57# These tests - collate4-1.* - check that indices are correctly58# selected or not selected to implement ORDER BY clauses when 59# user defined collation sequences are involved. 60#61# Because these tests also exercise all the different ways indices 62# can be created, they also serve to verify that indices are correctly 63# initialized with user-defined collation sequences when they are64# created.65#66# Tests named collate4-1.1.* use indices with a single column. Tests67# collate4-1.2.* use indices with two columns.68#69do_test collate4-1.1.0 {70  execsql {71    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);72    INSERT INTO collate4t1 VALUES( 'a', 'a' );73    INSERT INTO collate4t1 VALUES( 'b', 'b' );74    INSERT INTO collate4t1 VALUES( NULL, NULL );75    INSERT INTO collate4t1 VALUES( 'B', 'B' );76    INSERT INTO collate4t1 VALUES( 'A', 'A' );77    CREATE INDEX collate4i1 ON collate4t1(a);78    CREATE INDEX collate4i2 ON collate4t1(b);79  }80} {}81do_test collate4-1.1.1 {82  cksort {SELECT a FROM collate4t1 ORDER BY a}83} {{} a A b B nosort}84do_test collate4-1.1.2 {85  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}86} {{} a A b B nosort}87do_test collate4-1.1.3 {88  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}89} {{} A B a b sort}90do_test collate4-1.1.4 {91  cksort {SELECT b FROM collate4t1 ORDER BY b}92} {{} A B a b nosort}93do_test collate4-1.1.5 {94  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}95} {{} A B a b nosort}96do_test collate4-1.1.6 {97  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid}98} {{} a A b B sort}99 100do_test collate4-1.1.7 {101  execsql {102    CREATE TABLE collate4t2(103      a PRIMARY KEY COLLATE NOCASE, 104      b UNIQUE COLLATE TEXT105    );106    INSERT INTO collate4t2 VALUES( 'a', 'a' );107    INSERT INTO collate4t2 VALUES( NULL, NULL );108    INSERT INTO collate4t2 VALUES( 'B', 'B' );109  }110} {}111do_test collate4-1.1.8 {112  cksort {SELECT a FROM collate4t2 ORDER BY a}113} {{} a B nosort}114do_test collate4-1.1.9 {115  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}116} {{} a B nosort}117do_test collate4-1.1.10 {118  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}119} {{} B a sort}120do_test collate4-1.1.11 {121  cksort {SELECT b FROM collate4t2 ORDER BY b}122} {{} B a nosort}123do_test collate4-1.1.12 {124  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}125} {{} B a nosort}126do_test collate4-1.1.13 {127  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}128} {{} a B sort}129 130do_test collate4-1.1.14 {131  execsql {132    CREATE TABLE collate4t3(133      b COLLATE TEXT,  134      a COLLATE NOCASE, 135      UNIQUE(a), PRIMARY KEY(b)136    );137    INSERT INTO collate4t3 VALUES( 'a', 'a' );138    INSERT INTO collate4t3 VALUES( NULL, NULL );139    INSERT INTO collate4t3 VALUES( 'B', 'B' );140  }141} {}142do_test collate4-1.1.15 {143  cksort {SELECT a FROM collate4t3 ORDER BY a}144} {{} a B nosort}145do_test collate4-1.1.16 {146  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}147} {{} a B nosort}148do_test collate4-1.1.17 {149  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}150} {{} B a sort}151do_test collate4-1.1.18 {152  cksort {SELECT b FROM collate4t3 ORDER BY b}153} {{} B a nosort}154do_test collate4-1.1.19 {155  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}156} {{} B a nosort}157do_test collate4-1.1.20 {158  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}159} {{} a B sort}160 161do_test collate4-1.1.21 {162  execsql {163    CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);164    INSERT INTO collate4t4 VALUES( 'a', 'a' );165    INSERT INTO collate4t4 VALUES( 'b', 'b' );166    INSERT INTO collate4t4 VALUES( NULL, NULL );167    INSERT INTO collate4t4 VALUES( 'B', 'B' );168    INSERT INTO collate4t4 VALUES( 'A', 'A' );169    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);170    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);171  }172} {}173do_test collate4-1.1.22 {174  cksort {SELECT a FROM collate4t4 ORDER BY a, rowid}175} {{} a A b B sort}176do_test collate4-1.1.23 {177  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid}178} {{} a A b B sort}179do_test collate4-1.1.24 {180  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid}181} {{} A B a b nosort}182do_test collate4-1.1.25 {183  cksort {SELECT b FROM collate4t4 ORDER BY b}184} {{} A B a b sort}185do_test collate4-1.1.26 {186  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}187} {{} A B a b sort}188do_test collate4-1.1.27 {189  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}190} {{} a A b B nosort}191 192do_test collate4-1.1.30 {193  execsql {194    DROP TABLE collate4t1;195    DROP TABLE collate4t2;196    DROP TABLE collate4t3;197    DROP TABLE collate4t4;198  }199} {}200 201do_test collate4-1.2.0 {202  execsql {203    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);204    INSERT INTO collate4t1 VALUES( 'a', 'a' );205    INSERT INTO collate4t1 VALUES( 'b', 'b' );206    INSERT INTO collate4t1 VALUES( NULL, NULL );207    INSERT INTO collate4t1 VALUES( 'B', 'B' );208    INSERT INTO collate4t1 VALUES( 'A', 'A' );209    CREATE INDEX collate4i1 ON collate4t1(a, b);210  }211} {}212do_test collate4-1.2.1 {213  cksort {SELECT a FROM collate4t1 ORDER BY a}214} {{} A a B b nosort}215do_test collate4-1.2.2 {216  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}217} {{} A a B b nosort}218do_test collate4-1.2.3 {219  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}220} {{} A B a b sort}221do_test collate4-1.2.4 {222  cksort {SELECT a FROM collate4t1 ORDER BY a, b}223} {{} A a B b nosort}224do_test collate4-1.2.5 {225  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid}226} {{} a A b B sort}227do_test collate4-1.2.6 {228  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}229} {{} A a B b nosort}230 231do_test collate4-1.2.7 {232  execsql {233    CREATE TABLE collate4t2(234      a COLLATE NOCASE, 235      b COLLATE TEXT, 236      PRIMARY KEY(a, b)237    );238    INSERT INTO collate4t2 VALUES( 'a', 'a' );239    INSERT INTO collate4t2 VALUES( NULL, NULL );240    INSERT INTO collate4t2 VALUES( 'B', 'B' );241  }242} {}243do_test collate4-1.2.8 {244  cksort {SELECT a FROM collate4t2 ORDER BY a}245} {{} a B nosort}246do_test collate4-1.2.9 {247  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}248} {{} a B nosort}249do_test collate4-1.2.10 {250  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}251} {{} B a sort}252do_test collate4-1.2.11 {253  cksort {SELECT a FROM collate4t2 ORDER BY a, b}254} {{} a B nosort}255do_test collate4-1.2.12 {256  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}257} {{} a B sort}258do_test collate4-1.2.13 {259  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}260} {{} a B nosort}261 262do_test collate4-1.2.14 {263  execsql {264    CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);265    INSERT INTO collate4t3 VALUES( 'a', 'a' );266    INSERT INTO collate4t3 VALUES( 'b', 'b' );267    INSERT INTO collate4t3 VALUES( NULL, NULL );268    INSERT INTO collate4t3 VALUES( 'B', 'B' );269    INSERT INTO collate4t3 VALUES( 'A', 'A' );270    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);271  }272} {}273do_test collate4-1.2.15 {274  cksort {SELECT a FROM collate4t3 ORDER BY a, rowid}275} {{} a A b B sort}276do_test collate4-1.2.16 {277  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid}278} {{} a A b B sort}279do_test collate4-1.2.17 {280  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}281} {{} A B a b nosort}282do_test collate4-1.2.18 {283  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}284} {{} A B a b sort}285do_test collate4-1.2.19 {286  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}287} {{} A B a b nosort}288do_test collate4-1.2.20 {289  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}290} {{} A B a b sort}291do_test collate4-1.2.21 {292  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}293} {b a B A {} nosort}294do_test collate4-1.2.22 {295  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}296} {b a B A {} sort}297do_test collate4-1.2.23 {298  cksort {SELECT a FROM collate4t3 299            ORDER BY a COLLATE text DESC, b COLLATE nocase}300} {b a B A {} sort}301do_test collate4-1.2.24 {302  cksort {SELECT a FROM collate4t3 303            ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}304} {b a B A {} nosort}305 306do_test collate4-1.2.25 {307  execsql {308    DROP TABLE collate4t1;309    DROP TABLE collate4t2;310    DROP TABLE collate4t3;311  }312} {}313 314#315# These tests - collate4-2.* - check that indices are correctly316# selected or not selected to implement WHERE clauses when user 317# defined collation sequences are involved. 318#319# Indices may optimise WHERE clauses using <, >, <=, >=, = or IN320# operators.321#322do_test collate4-2.1.0 {323  execsql {324    PRAGMA automatic_index=OFF;325    CREATE TABLE collate4t1(a COLLATE NOCASE);326    CREATE TABLE collate4t2(b COLLATE TEXT);327 328    INSERT INTO collate4t1 VALUES('a');329    INSERT INTO collate4t1 VALUES('A');330    INSERT INTO collate4t1 VALUES('b');331    INSERT INTO collate4t1 VALUES('B');332    INSERT INTO collate4t1 VALUES('c');333    INSERT INTO collate4t1 VALUES('C');334    INSERT INTO collate4t1 VALUES('d');335    INSERT INTO collate4t1 VALUES('D');336    INSERT INTO collate4t1 VALUES('e');337    INSERT INTO collate4t1 VALUES('D');338 339    INSERT INTO collate4t2 VALUES('A');340    INSERT INTO collate4t2 VALUES('Z');341  }342} {}343do_test collate4-2.1.1 {344  count {345    SELECT * FROM collate4t2, collate4t1 WHERE a = b;346  }347} {A a A A 19}348do_test collate4-2.1.2 {349  execsql {350    CREATE INDEX collate4i1 ON collate4t1(a);351  }352  count {353    SELECT * FROM collate4t2, collate4t1 WHERE a = b;354  }355} {A a A A 4}356do_test collate4-2.1.3 {357  count {358    SELECT * FROM collate4t2, collate4t1 WHERE b = a;359  }360} {A A 19}361do_test collate4-2.1.4 {362  execsql {363    DROP INDEX collate4i1;364    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);365  }366  count {367    SELECT * FROM collate4t2, collate4t1 WHERE a = b368     ORDER BY collate4t2.rowid, collate4t1.rowid369  }370} {A a A A 19}371do_test collate4-2.1.5 {372  count {373    SELECT * FROM collate4t2, collate4t1 WHERE b = a;374  }375} {A A 3}376ifcapable subquery {377  do_test collate4-2.1.6 {378    count {379      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)380       ORDER BY rowid381    }382  } {a A 10}383  do_test collate4-2.1.7 {384    execsql {385      DROP INDEX collate4i1;386      CREATE INDEX collate4i1 ON collate4t1(a);387    }388    count {389      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)390       ORDER BY rowid391    }392  } {a A 5}393  do_test collate4-2.1.8 {394    count {395      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');396    }397  } {a A 4}398  do_test collate4-2.1.9 {399    execsql {400      DROP INDEX collate4i1;401      CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);402    }403    count {404      SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid;405    }406  } {a A 9}407}408do_test collate4-2.1.10 {409  execsql {410    DROP TABLE collate4t1;411    DROP TABLE collate4t2;412  }413} {}414 415do_test collate4-2.2.0 {416  execsql {417    CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);418    CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);419 420    INSERT INTO collate4t1 VALUES('0', '0', '0');421    INSERT INTO collate4t1 VALUES('0', '0', '1');422    INSERT INTO collate4t1 VALUES('0', '1', '0');423    INSERT INTO collate4t1 VALUES('0', '1', '1');424    INSERT INTO collate4t1 VALUES('1', '0', '0');425    INSERT INTO collate4t1 VALUES('1', '0', '1');426    INSERT INTO collate4t1 VALUES('1', '1', '0');427    INSERT INTO collate4t1 VALUES('1', '1', '1');428    insert into collate4t2 SELECT * FROM collate4t1;429  }430} {}431do_test collate4-2.2.1 {432  count {433    SELECT * FROM collate4t2 NOT INDEXED NATURAL JOIN collate4t1 NOT INDEXED;434  }435} {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 63}436do_test collate4-2.2.1b {437  execsql {438    CREATE INDEX collate4i1 ON collate4t1(a, b, c);439  }440  count {441    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;442  }443} {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 29}444do_test collate4-2.2.2 {445  execsql {446    DROP INDEX collate4i1;447    CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);448  }449  count {450    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;451  }452} {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 22}453 454do_test collate4-2.2.10 {455  execsql {456    DROP TABLE collate4t1;457    DROP TABLE collate4t2;458  }459} {}460 461#462# These tests - collate4-3.* verify that indices that implement463# UNIQUE and PRIMARY KEY constraints operate correctly with user464# defined collation sequences.465#466do_test collate4-3.0 {467  execsql {468    CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);469  }470} {}471do_test collate4-3.1 {472  catchsql {473    INSERT INTO collate4t1 VALUES('abc');474    INSERT INTO collate4t1 VALUES('ABC');475  }476} {1 {UNIQUE constraint failed: collate4t1.a}}477do_test collate4-3.2 {478  execsql {479    SELECT * FROM collate4t1;480  }481} {abc}482do_test collate4-3.3 {483  catchsql {484    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;485  }486} {1 {UNIQUE constraint failed: collate4t1.a}}487do_test collate4-3.4 {488  catchsql {489    INSERT INTO collate4t1 VALUES(1);490    UPDATE collate4t1 SET a = 'abc';491  }492} {1 {UNIQUE constraint failed: collate4t1.a}}493do_test collate4-3.5 {494  execsql {495    DROP TABLE collate4t1;496    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);497  }498} {}499do_test collate4-3.6 {500  catchsql {501    INSERT INTO collate4t1 VALUES('abc');502    INSERT INTO collate4t1 VALUES('ABC');503  }504} {1 {UNIQUE constraint failed: collate4t1.a}}505do_test collate4-3.7 {506  execsql {507    SELECT * FROM collate4t1;508  }509} {abc}510do_test collate4-3.8 {511  catchsql {512    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;513  }514} {1 {UNIQUE constraint failed: collate4t1.a}}515do_test collate4-3.9 {516  catchsql {517    INSERT INTO collate4t1 VALUES(1);518    UPDATE collate4t1 SET a = 'abc';519  }520} {1 {UNIQUE constraint failed: collate4t1.a}}521do_test collate4-3.10 {522  execsql {523    DROP TABLE collate4t1;524    CREATE TABLE collate4t1(a);525    CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);526  }527} {}528do_test collate4-3.11 {529  catchsql {530    INSERT INTO collate4t1 VALUES('abc');531    INSERT INTO collate4t1 VALUES('ABC');532  }533} {1 {UNIQUE constraint failed: collate4t1.a}}534do_test collate4-3.12 {535  execsql {536    SELECT * FROM collate4t1;537  }538} {abc}539do_test collate4-3.13 {540  catchsql {541    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;542  }543} {1 {UNIQUE constraint failed: collate4t1.a}}544do_test collate4-3.14 {545  catchsql {546    INSERT INTO collate4t1 VALUES(1);547    UPDATE collate4t1 SET a = 'abc';548  }549} {1 {UNIQUE constraint failed: collate4t1.a}}550 551do_test collate4-3.15 {552  execsql {553    DROP TABLE collate4t1;554  }555} {}556 557# Mimic the SQLite 2 collation type NUMERIC.558db collate numeric numeric_collate559proc numeric_collate {lhs rhs} {560  if {$lhs == $rhs} {return 0} 561  return [expr ($lhs>$rhs)?1:-1]562}563 564#565# These tests - collate4-4.* check that min() and max() only ever 566# use indices constructed with built-in collation type numeric.567#568# CHANGED:  min() and max() now use the collation type. If there569# is an indice that can be used, it is used.570#571do_test collate4-4.0 {572  execsql {573    CREATE TABLE collate4t1(a COLLATE TEXT);574    INSERT INTO collate4t1 VALUES('2');575    INSERT INTO collate4t1 VALUES('10');576    INSERT INTO collate4t1 VALUES('20');577    INSERT INTO collate4t1 VALUES('104');578  }579} {}580do_test collate4-4.1 {581  count {582    SELECT max(a) FROM collate4t1583  }584} {20 3}585do_test collate4-4.2 {586  count {587    SELECT min(a) FROM collate4t1588  }589} {10 3}590do_test collate4-4.3 {591  # Test that the index with collation type TEXT is used.592  execsql {593    CREATE INDEX collate4i1 ON collate4t1(a);594  }595  count {596    SELECT min(a) FROM collate4t1;597  }598} {10 1}599do_test collate4-4.4 {600  count {601    SELECT max(a) FROM collate4t1;602  }603} {20 0}604do_test collate4-4.5 {605  # Test that the index with collation type NUMERIC is not used.606  execsql {607    DROP INDEX collate4i1;608    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);609  }610  count {611    SELECT min(a) FROM collate4t1;612  }613} {10 3}614do_test collate4-4.6 {615  count {616    SELECT max(a) FROM collate4t1;617  }618} {20 3}619do_test collate4-4.7 {620  execsql {621    DROP TABLE collate4t1;622  }623} {}624 625# Also test the scalar min() and max() functions.626#627do_test collate4-4.8 {628  execsql {629    CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);630    INSERT INTO collate4t1 VALUES('11', '101');631    INSERT INTO collate4t1 VALUES('101', '11')632  }633} {}634do_test collate4-4.9 {635  execsql {636    SELECT max(a, b) FROM collate4t1;637  }638} {11 11}639do_test collate4-4.10 {640  execsql {641    SELECT max(b, a) FROM collate4t1;642  }643} {101 101}644do_test collate4-4.11 {645  execsql {646    SELECT max(a, '101') FROM collate4t1;647  }648} {11 101}649do_test collate4-4.12 {650  execsql {651    SELECT max('101', a) FROM collate4t1;652  }653} {11 101}654do_test collate4-4.13 {655  execsql {656    SELECT max(b, '101') FROM collate4t1;657  }658} {101 101}659do_test collate4-4.14 {660  execsql {661    SELECT max('101', b) FROM collate4t1;662  }663} {101 101}664 665do_test collate4-4.15 {666  execsql {667    DROP TABLE collate4t1;668  }669} {}670 671#672# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY 673# indices do not confuse collation sequences. 674#675# These indices are never used for sorting in SQLite. And you can't676# create another index on an INTEGER PRIMARY KEY column, so we don't have 677# to test that.678# (Revised 2004-Nov-22):  The ROWID can be used for sorting now.679#680do_test collate4-6.0 {681  execsql {682    CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);683    INSERT INTO collate4t1 VALUES(101);684    INSERT INTO collate4t1 VALUES(10);685    INSERT INTO collate4t1 VALUES(15);686  }687} {}688do_test collate4-6.1 {689  cksort {690    SELECT * FROM collate4t1 ORDER BY 1;691  }692} {10 15 101 nosort}693do_test collate4-6.2 {694  cksort {695    SELECT * FROM collate4t1 ORDER BY oid;696  }697} {10 15 101 nosort}698do_test collate4-6.3 {699  cksort {700    SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;701  }702} {10 101 15 sort}703 704finish_test705