1#
2# 2007 June 20
3#
4# The author disclaims copyright to this source code.  In place of
5# 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.  The
13# focus of this script is making sure collations pass through the
14# unary + operator.
15#
16# $Id: collate8.test,v 1.2 2008/08/25 12:14:09 drh Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21do_test collate8-1.1 {
22  execsql {
23    CREATE TABLE t1(a TEXT COLLATE nocase);
24    INSERT INTO t1 VALUES('aaa');
25    INSERT INTO t1 VALUES('BBB');
26    INSERT INTO t1 VALUES('ccc');
27    INSERT INTO t1 VALUES('DDD');
28    SELECT a FROM t1 ORDER BY a;
29  }
30} {aaa BBB ccc DDD}
31do_test collate8-1.2 {
32  execsql {
33    SELECT rowid FROM t1 WHERE a<'ccc' ORDER BY 1
34  }
35} {1 2}
36do_test collate8-1.3 {
37  execsql {
38    SELECT rowid FROM t1 WHERE a<'ccc' COLLATE binary ORDER BY 1
39  }
40} {1 2 4}
41do_test collate8-1.4 {
42  execsql {
43    SELECT rowid FROM t1 WHERE +a<'ccc' ORDER BY 1
44  }
45} {1 2}
46do_test collate8-1.5 {
47  execsql {
48    SELECT a FROM t1 ORDER BY +a
49  }
50} {aaa BBB ccc DDD}
51do_test collate8-1.11 {
52  execsql {
53    SELECT a AS x FROM t1 ORDER BY "x";
54  }
55} {aaa BBB ccc DDD}
56do_test collate8-1.12 {
57  execsql {
58    SELECT a AS x FROM t1 WHERE x<'ccc' ORDER BY 1
59  }
60} {aaa BBB}
61do_test collate8-1.13 {
62  execsql {
63    SELECT a AS x FROM t1 WHERE x<'ccc' COLLATE binary ORDER BY [x]
64  }
65} {aaa BBB DDD}
66do_test collate8-1.14 {
67  execsql {
68    SELECT a AS x FROM t1 WHERE +x<'ccc' ORDER BY 1
69  }
70} {aaa BBB}
71do_test collate8-1.15 {
72  execsql {
73    SELECT a AS x FROM t1 ORDER BY +x
74  }
75} {aaa BBB ccc DDD}
76
77
78# When a result-set column is aliased into a WHERE clause, make sure the
79# collating sequence logic works correctly.
80#
81do_test collate8-2.1 {
82  execsql {
83    CREATE TABLE t2(a);
84    INSERT INTO t2 VALUES('abc');
85    INSERT INTO t2 VALUES('ABC');
86    SELECT a AS x FROM t2 WHERE x='abc';
87  }
88} {abc}
89do_test collate8-2.2 {
90  execsql {
91    SELECT a AS x FROM t2 WHERE x='abc' COLLATE nocase;
92  }
93} {abc ABC}
94do_test collate8-2.3 {
95  execsql {
96    SELECT a AS x FROM t2 WHERE (x COLLATE nocase)='abc';
97  }
98} {abc ABC}
99do_test collate8-2.4 {
100  execsql {
101    SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc';
102  }
103} {abc ABC}
104do_test collate8-2.5 {
105  execsql {
106    SELECT a COLLATE nocase AS x FROM t2 WHERE (x COLLATE binary)='abc';
107  }
108} {abc}
109do_test collate8-2.6 {
110  execsql {
111    SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc' COLLATE binary;
112  }
113} {abc ABC}
114do_test collate8-2.7 {
115  execsql {
116    SELECT * FROM t2 WHERE (a COLLATE nocase)='abc' COLLATE binary;
117  }
118} {abc ABC}
119do_test collate8-2.8 {
120  execsql {
121    SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary;
122  }
123} {abc}
124
125finish_test
126