/ Check-in [2622c524]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional tests for descending indices. Comment changes. (CVS 2850)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2622c5242b0cba5bc19f190a7c209ab9ed8f57e0
User & Date: drh 2006-01-02 18:24:40
Context
2006-01-02
20:00
Add the xInMutex method to the os-layer switch for testing whether or not mutexes are used correctly. (CVS 2851) check-in: a582b159 user: drh tags: trunk
18:24
Additional tests for descending indices. Comment changes. (CVS 2850) check-in: 2622c524 user: drh tags: trunk
2005-12-30
16:31
Repair typo in previous commit. (CVS 2849) check-in: a4aa0911 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbeaux.c.

1492
1493
1494
1495
1496
1497
1498


1499
1500
1501
1502
1503
1504
1505
**      7                     8            IEEE float
**      8                     0            Integer constant 0
**      9                     0            Integer constant 1
**     10,11                               reserved for expansion
**    N>=12 and even       (N-12)/2        BLOB
**    N>=13 and odd        (N-13)/2        text
**


*/

/*
** Return the serial-type for the value stored in pMem.
*/
u32 sqlite3VdbeSerialType(Mem *pMem, int file_format){
  int flags = pMem->flags;







>
>







1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
**      7                     8            IEEE float
**      8                     0            Integer constant 0
**      9                     0            Integer constant 1
**     10,11                               reserved for expansion
**    N>=12 and even       (N-12)/2        BLOB
**    N>=13 and odd        (N-13)/2        text
**
** The 8 and 9 types were added in 3.3.0, file format 4.  Prior versions
** of SQLite will not understand those serial types.
*/

/*
** Return the serial-type for the value stored in pMem.
*/
u32 sqlite3VdbeSerialType(Mem *pMem, int file_format){
  int flags = pMem->flags;

Added test/descidx3.test.

































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
# 2006 January 02
#
# 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 script is descending indices.
#
# $Id: descidx3.test,v 1.1 2006/01/02 18:24:40 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# This procedure returns the value of the file-format in file 'test.db'.
# 
proc get_file_format {{fname test.db}} {
  set bt [btree_open $fname 10 0]
  set meta [btree_get_meta $bt]
  btree_close $bt
  lindex $meta 2
}

# Verify that the file format starts as 4.
#
do_test descidx3-1.1 {
  execsql {
    CREATE TABLE t1(i INTEGER PRIMARY KEY,a,b,c,d);
    CREATE INDEX t1i1 ON t1(a DESC, b ASC, c DESC);
    CREATE INDEX t1i2 ON t1(b DESC, c ASC, d DESC);
  }
  get_file_format
} {4}

# Put some information in the table and verify that the descending
# index actually works.
#
do_test descidx3-2.1 {
  execsql {
    INSERT INTO t1 VALUES(1, NULL, NULL, NULL, NULL);
    INSERT INTO t1 VALUES(2, 2, 2, 2, 2);
    INSERT INTO t1 VALUES(3, 3, 3, 3, 3);
    INSERT INTO t1 VALUES(4, 2.5, 2.5, 2.5, 2.5);
    INSERT INTO t1 VALUES(5, -5, -5, -5, -5);
    INSERT INTO t1 VALUES(6, 'six', 'six', 'six', 'six');
    INSERT INTO t1 VALUES(7, x'77', x'77', x'77', x'77');
    INSERT INTO t1 VALUES(8, 'eight', 'eight', 'eight', 'eight');
    INSERT INTO t1 VALUES(9, x'7979', x'7979', x'7979', x'7979');
    SELECT count(*) FROM t1;
  }
} 9
do_test descidx3-2.2 {
  execsql {
    SELECT i FROM t1 ORDER BY a;
  }
} {1 5 2 4 3 8 6 7 9}
do_test descidx3-2.3 {
  execsql {
    SELECT i FROM t1 ORDER BY a DESC;
  }
} {9 7 6 8 3 4 2 5 1}

# The "natural" order for the index is decreasing
do_test descidx3-2.4 {
  execsql {
    SELECT i FROM t1 WHERE a<=x'7979';
  }
} {9 7 6 8 3 4 2 5}
do_test descidx3-2.5 {
  execsql {
    SELECT i FROM t1 WHERE a>-99;
  }
} {9 7 6 8 3 4 2 5}

# Even when all values of t1.a are the same, sorting by A returns
# the rows in reverse order because this the natural order of the
# index.
#
do_test descidx3-3.1 {
  execsql {
    UPDATE t1 SET a=1;
    SELECT i FROM t1 ORDER BY a;
  }
} {9 7 6 8 3 4 2 5 1}
do_test descidx3-3.2 {
  execsql {
    SELECT i FROM t1 WHERE a=1 AND b>0 AND b<'zzz'
  }
} {2 4 3 8 6}
do_test descidx3-3.3 {
  execsql {
    SELECT i FROM t1 WHERE b>0 AND b<'zzz'
  }
} {6 8 3 4 2}
do_test descidx3-3.4 {
  execsql {
    SELECT i FROM t1 WHERE a=1 AND b>-9999 AND b<x'ffffffff'
  }
} {5 2 4 3 8 6 7 9}
do_test descidx3-3.5 {
  execsql {
    SELECT i FROM t1 WHERE b>-9999 AND b<x'ffffffff'
  }
} {9 7 6 8 3 4 2 5}

do_test descidx3-4.1 {
  execsql {
    UPDATE t1 SET a=2 WHERE i<6;
    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
  }
} {8 6 2 4 3}
do_test descidx3-4.2 {
  execsql {
    UPDATE t1 SET a=1;
    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
  }
} {2 4 3 8 6}
do_test descidx3-4.3 {
  execsql {
    UPDATE t1 SET b=2;
    SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
  }
} {9 7 6 8 3 4 2 5 1}

finish_test