SQLite

Check-in [c058f483a5]
Login

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

Overview
Comment:Test file fixes for libaries compiled with various SQLITE_OMIT_ macros. (CVS 2961)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c058f483a52c8043178b6329aa5e58bedf0c8277
User & Date: danielk1977 2006-01-16 16:24:25.000
Context
2006-01-17
09:35
Test suite fixes for builds that define OMIT symbols. (CVS 2962) (check-in: 3a54a33dcb user: danielk1977 tags: trunk)
2006-01-16
16:24
Test file fixes for libaries compiled with various SQLITE_OMIT_ macros. (CVS 2961) (check-in: c058f483a5 user: danielk1977 tags: trunk)
15:32
Fix problems compiling with memory-management enabled. Ticket #1619. (CVS 2960) (check-in: 36b0325965 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/alter3.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter3.test,v 1.7 2005/12/30 16:28:02 danielk1977 Exp $
#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter3.test,v 1.8 2006/01/16 16:24:25 danielk1977 Exp $
#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
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
} {{CREATE TABLE t1(a, b, c)}}
do_test alter3-1.5 {
  execsql {
    ALTER TABLE t1 ADD d CHECK (a>d);
    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
  }
} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}

do_test alter3-1.6 {
  execsql {
    CREATE TABLE t2(a, b, UNIQUE(a, b));
    ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
    SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
  }
} {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}

do_test alter3-1.7 {
  execsql {
    CREATE TABLE t3(a, b, UNIQUE(a, b));
    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
    SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
  }
} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
do_test alter3-1.99 {




  execsql {
    DROP TABLE abc; 
    DROP TABLE t1; 
    DROP TABLE t2; 
    DROP TABLE t3; 
  }
} {}

do_test alter3-2.1 {
  execsql {
    CREATE TABLE t1(a, b);







>
|
|
|
|
|
|
|
>








>
>
>
>



<







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
} {{CREATE TABLE t1(a, b, c)}}
do_test alter3-1.5 {
  execsql {
    ALTER TABLE t1 ADD d CHECK (a>d);
    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
  }
} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
ifcapable foreignkey {
  do_test alter3-1.6 {
    execsql {
      CREATE TABLE t2(a, b, UNIQUE(a, b));
      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
      SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
    }
  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
}
do_test alter3-1.7 {
  execsql {
    CREATE TABLE t3(a, b, UNIQUE(a, b));
    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
    SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
  }
} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
do_test alter3-1.99 {
  catchsql {
    # May not exist if foriegn-keys are omitted at compile time.
    DROP TABLE t2; 
  }
  execsql {
    DROP TABLE abc; 
    DROP TABLE t1; 

    DROP TABLE t3; 
  }
} {}

do_test alter3-2.1 {
  execsql {
    CREATE TABLE t1(a, b);
Changes to test/attach.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.41 2005/11/29 03:13:22 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.42 2006/01/16 16:24:25 danielk1977 Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
# Check the error message if we try to access a database that has
# not been attached.
do_test attach-6.3 {
  catchsql {
    CREATE TABLE no_such_db.t1(a, b, c);
  }
} {1 {unknown database no_such_db}}
ifcapable !compound {
  do_test attach-6.4 {
    catchsql {
      CREATE TEMP TABLE db2.temp1(a, b, c);
    }
  } {1 {temporary table name must be unqualified}}
}
for {set i 2} {$i<=15} {incr i} {
  catch {db$i close}
}
db close
file delete -force test2.db
file delete -force no-such-file

finish_test







<
<
<
<
<
<
<








724
725
726
727
728
729
730







731
732
733
734
735
736
737
738
# Check the error message if we try to access a database that has
# not been attached.
do_test attach-6.3 {
  catchsql {
    CREATE TABLE no_such_db.t1(a, b, c);
  }
} {1 {unknown database no_such_db}}







for {set i 2} {$i<=15} {incr i} {
  catch {db$i close}
}
db close
file delete -force test2.db
file delete -force no-such-file

finish_test
Changes to test/auth.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.31 2006/01/16 15:14:28 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.32 2006/01/16 16:24:25 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
2206
2207
2208
2209
2210
2211
2212

2213
2214
2215
2216
2217
2218
2219
2220

2221
2222
2223
2224
2225
2226
2227
  execsql {
    SELECT count(a) AS cnt FROM t4 ORDER BY cnt
  }
} {1}

# Ticket #1607
#

do_test auth-5.2 {
  execsql {
    SELECT name FROM (
      SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
    WHERE type='table'
    ORDER BY name
  }
} {sqlite_stat1 t1 t2 t3 t4 tx v1chng}



rename proc {}
rename proc_real proc


finish_test







>
|
|
|
|
|
|
|
|
>







2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
  execsql {
    SELECT count(a) AS cnt FROM t4 ORDER BY cnt
  }
} {1}

# Ticket #1607
#
ifcapable compound {
  do_test auth-5.2 {
    execsql {
      SELECT name FROM (
        SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
      WHERE type='table'
      ORDER BY name
    }
  } {sqlite_stat1 t1 t2 t3 t4 tx v1chng}
}


rename proc {}
rename proc_real proc


finish_test
Changes to test/autovacuum.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
# 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 the SELECT statement.
#
# $Id: autovacuum.test,v 1.19 2005/09/16 09:52:30 danielk1977 Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum} {
  finish_test
  return
}

# Return a string $len characters long. The returned string is $char repeated
# over and over. For example, [make_str abc 8] returns "abcabcab".
proc make_str {char len} {













|






|







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
# 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 the SELECT statement.
#
# $Id: autovacuum.test,v 1.20 2006/01/16 16:24:25 danielk1977 Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {
  finish_test
  return
}

# Return a string $len characters long. The returned string is $char repeated
# over and over. For example, [make_str abc 8] returns "abcabcab".
proc make_str {char len} {
Changes to test/cast.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
# 2005 June 25
#
# 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 the CAST operator.
#
# $Id: cast.test,v 1.3 2005/11/01 15:48:25 drh Exp $

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

# Only run these tests if the build includes the CAST operator
ifcapable !cast {
  finish_test
  return
}

# Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
#

do_test cast-1.1 {
  execsql {SELECT x'616263'}
} abc
do_test cast-1.2 {
  execsql {SELECT typeof(x'616263')}
} blob
do_test cast-1.3 {
  execsql {SELECT CAST(x'616263' AS text)}
} abc
do_test cast-1.4 {
  execsql {SELECT typeof(CAST(x'616263' AS text))}
} text
do_test cast-1.5 {
  execsql {SELECT CAST(x'616263' AS numeric)}
} 0
do_test cast-1.6 {
  execsql {SELECT typeof(CAST(x'616263' AS numeric))}
} integer
do_test cast-1.7 {
  execsql {SELECT CAST(x'616263' AS blob)}
} abc
do_test cast-1.8 {
  execsql {SELECT typeof(CAST(x'616263' AS blob))}
} blob
do_test cast-1.9 {
  execsql {SELECT CAST(x'616263' AS integer)}
} 0
do_test cast-1.10 {
  execsql {SELECT typeof(CAST(x'616263' AS integer))}
} integer

do_test cast-1.11 {
  execsql {SELECT null}
} {{}}
do_test cast-1.12 {
  execsql {SELECT typeof(NULL)}
} null
do_test cast-1.13 {













|












>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







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
# 2005 June 25
#
# 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 the CAST operator.
#
# $Id: cast.test,v 1.4 2006/01/16 16:24:25 danielk1977 Exp $

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

# Only run these tests if the build includes the CAST operator
ifcapable !cast {
  finish_test
  return
}

# Tests for the CAST( AS blob), CAST( AS text) and CAST( AS numeric) built-ins
#
ifcapable bloblit {
  do_test cast-1.1 {
    execsql {SELECT x'616263'}
  } abc
  do_test cast-1.2 {
    execsql {SELECT typeof(x'616263')}
  } blob
  do_test cast-1.3 {
    execsql {SELECT CAST(x'616263' AS text)}
  } abc
  do_test cast-1.4 {
    execsql {SELECT typeof(CAST(x'616263' AS text))}
  } text
  do_test cast-1.5 {
    execsql {SELECT CAST(x'616263' AS numeric)}
  } 0
  do_test cast-1.6 {
    execsql {SELECT typeof(CAST(x'616263' AS numeric))}
  } integer
  do_test cast-1.7 {
    execsql {SELECT CAST(x'616263' AS blob)}
  } abc
  do_test cast-1.8 {
    execsql {SELECT typeof(CAST(x'616263' AS blob))}
  } blob
  do_test cast-1.9 {
    execsql {SELECT CAST(x'616263' AS integer)}
  } 0
  do_test cast-1.10 {
    execsql {SELECT typeof(CAST(x'616263' AS integer))}
  } integer
}
do_test cast-1.11 {
  execsql {SELECT null}
} {{}}
do_test cast-1.12 {
  execsql {SELECT typeof(NULL)}
} null
do_test cast-1.13 {
Changes to test/descidx1.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 December 21
#
# 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: descidx1.test,v 1.4 2006/01/12 01:56:44 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.













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 December 21
#
# 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: descidx1.test,v 1.5 2006/01/16 16:24:25 danielk1977 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.
166
167
168
169
170
171
172

173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224

225
226
227
228
229
230
231
do_test descidx1-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

# Create a table with indices that are descending on some terms and
# ascending on others.
#

do_test descidx1-4.1 {
  execsql {
    CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
    CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
    CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
    INSERT INTO t2 VALUES(1,'one',x'31',1.0);
    INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
    INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
    INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
    INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
    INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
    INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
    INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
    INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
    SELECT count(*) FROM t2;
  }
} {9}
do_test descidx1-4.2 {
  execsql {
    SELECT d FROM t2 ORDER BY a;
  }
} {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.3 {
  execsql {
    SELECT d FROM t2 WHERE a>=2;
  }
} {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.4 {
  execsql {
    SELECT d FROM t2 WHERE a>2;
  }
} {3.0 4.0 5.0 6.0}
do_test descidx1-4.5 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>'two';
  }
} {2.2}
do_test descidx1-4.6 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>='two';
  }
} {2.2 2.0 2.1}
do_test descidx1-4.7 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<'two';
  }
} {}
do_test descidx1-4.8 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<='two';
  }
} {2.0 2.1}


do_test descidx1-5.1 {
  execsql {
    CREATE TABLE t3(a,b,c,d);
    CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
    INSERT INTO t3 VALUES(0,0,0,0);
    INSERT INTO t3 VALUES(0,0,0,1);







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
do_test descidx1-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

# Create a table with indices that are descending on some terms and
# ascending on others.
#
ifcapable bloblit {
  do_test descidx1-4.1 {
    execsql {
      CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
      CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
      CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
      INSERT INTO t2 VALUES(1,'one',x'31',1.0);
      INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
      INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
      INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
      INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
      INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
      INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
      INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
      INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
      SELECT count(*) FROM t2;
    }
  } {9}
  do_test descidx1-4.2 {
    execsql {
      SELECT d FROM t2 ORDER BY a;
    }
  } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.3 {
    execsql {
      SELECT d FROM t2 WHERE a>=2;
    }
  } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.4 {
    execsql {
      SELECT d FROM t2 WHERE a>2;
    }
  } {3.0 4.0 5.0 6.0}
  do_test descidx1-4.5 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b>'two';
    }
  } {2.2}
  do_test descidx1-4.6 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b>='two';
    }
  } {2.2 2.0 2.1}
  do_test descidx1-4.7 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b<'two';
    }
  } {}
  do_test descidx1-4.8 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b<='two';
    }
  } {2.0 2.1}
}

do_test descidx1-5.1 {
  execsql {
    CREATE TABLE t3(a,b,c,d);
    CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
    INSERT INTO t3 VALUES(0,0,0,0);
    INSERT INTO t3 VALUES(0,0,0,1);
Changes to 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
# 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.2 2006/01/14 08:02:28 danielk1977 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













|




>
>
>
>
>







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
# 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.3 2006/01/16 16:24:25 danielk1977 Exp $
#

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

ifcapable !bloblit {
  finish_test
  return
}

# 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
Changes to test/hook.test.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#
# The focus of the tests in this file is the  following interface:
#
#      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
#      sqlite_update_hook    (tests hook-4-*)
#      sqlite_rollback_hook  (tests hook-5.*)
#
# $Id: hook.test,v 1.9 2006/01/05 23:42:52 drh Exp $

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

do_test hook-1.2 {
  db commit_hook
} {}







|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#
# The focus of the tests in this file is the  following interface:
#
#      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
#      sqlite_update_hook    (tests hook-4-*)
#      sqlite_rollback_hook  (tests hook-5.*)
#
# $Id: hook.test,v 1.10 2006/01/16 16:24:25 danielk1977 Exp $

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

do_test hook-1.2 {
  db commit_hook
} {}
205
206
207
208
209
210
211

212
213
214
215
216
217
218
219
220
221
222

223
224
225
226
227
228
229
    UPDATE main t1 1 \
    UPDATE main t1 2 \
    UPDATE main t1 3 \
    DELETE main t1 2 \
    DELETE main t1 3 \
]
set ::update_hook {}

do_test hook-4.3.2 {
  execsql {
    SELECT * FROM t1 UNION SELECT * FROM t3;
    SELECT * FROM t1 UNION ALL SELECT * FROM t3;
    SELECT * FROM t1 INTERSECT SELECT * FROM t3;
    SELECT * FROM t1 EXCEPT SELECT * FROM t3;
    SELECT * FROM t1 ORDER BY b;
    SELECT * FROM t1 GROUP BY b;
  }
  set ::update_hook
} [list]

db update_hook {}
#
#----------------------------------------------------------------------------

#----------------------------------------------------------------------------
# Test the rollback-hook. The rollback-hook is a bit more complicated than
# either the commit or update hooks because a rollback can happen 







>
|
|
|
|
|
|
|
|
|
|
|
>







205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
    UPDATE main t1 1 \
    UPDATE main t1 2 \
    UPDATE main t1 3 \
    DELETE main t1 2 \
    DELETE main t1 3 \
]
set ::update_hook {}
ifcapable compound {
  do_test hook-4.3.2 {
    execsql {
      SELECT * FROM t1 UNION SELECT * FROM t3;
      SELECT * FROM t1 UNION ALL SELECT * FROM t3;
      SELECT * FROM t1 INTERSECT SELECT * FROM t3;
      SELECT * FROM t1 EXCEPT SELECT * FROM t3;
      SELECT * FROM t1 ORDER BY b;
      SELECT * FROM t1 GROUP BY b;
    }
    set ::update_hook
  } [list]
}
db update_hook {}
#
#----------------------------------------------------------------------------

#----------------------------------------------------------------------------
# Test the rollback-hook. The rollback-hook is a bit more complicated than
# either the commit or update hooks because a rollback can happen 
Changes to test/insert.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 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 the INSERT statement.
#
# $Id: insert.test,v 1.27 2005/07/08 17:13:47 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 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 the INSERT statement.
#
# $Id: insert.test,v 1.28 2006/01/16 16:24:25 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  }
} {1 2 2}

# Ticket #1140:  Check for an infinite loop in the algorithm that tests
# to see if the right-hand side of an INSERT...SELECT references the left-hand
# side.
#
ifcapable subquery {
  do_test insert-8.1 {
    execsql {
      INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
    }
  } {}
}


integrity_check insert-99.0

finish_test







|











342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
  }
} {1 2 2}

# Ticket #1140:  Check for an infinite loop in the algorithm that tests
# to see if the right-hand side of an INSERT...SELECT references the left-hand
# side.
#
ifcapable subquery&&compound {
  do_test insert-8.1 {
    execsql {
      INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
    }
  } {}
}


integrity_check insert-99.0

finish_test
Changes to test/misc5.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.9 2006/01/14 08:02:28 danielk1977 Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.10 2006/01/16 16:24:25 danielk1977 Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520

# Ticket #1582.  Ensure that an unknown table in a LIMIT clause applied to
# a UNION ALL query causes an error, not a crash.
#
db close
file delete -force test.db
sqlite3 db test.db
ifcapable subquery {
  do_test misc5-6.1 {
    catchsql {
      SELECT * FROM sqlite_master 
      UNION ALL 
      SELECT * FROM sqlite_master
      LIMIT (SELECT count(*) FROM blah);
    }







|







506
507
508
509
510
511
512
513
514
515
516
517
518
519
520

# Ticket #1582.  Ensure that an unknown table in a LIMIT clause applied to
# a UNION ALL query causes an error, not a crash.
#
db close
file delete -force test.db
sqlite3 db test.db
ifcapable subquery&&compound {
  do_test misc5-6.1 {
    catchsql {
      SELECT * FROM sqlite_master 
      UNION ALL 
      SELECT * FROM sqlite_master
      LIMIT (SELECT count(*) FROM blah);
    }
Changes to test/shared.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 2005 December 30
#
# 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.
#
#***********************************************************************
#
# $Id: shared.test,v 1.15 2006/01/16 15:14:29 danielk1977 Exp $

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

ifcapable !shared_cache {
  finish_test











|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 2005 December 30
#
# 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.
#
#***********************************************************************
#
# $Id: shared.test,v 1.16 2006/01/16 16:24:25 danielk1977 Exp $

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

ifcapable !shared_cache {
  finish_test
332
333
334
335
336
337
338

339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357

358
359
360
361
362
363
364
# Open a write-transaction using handle 1 and modify the database schema.
# Then try to execute a compiled statement to read from the same 
# database via handle 2 (fails to get the lock on sqlite_master). Also
# try to compile a read of the same database using handle 2 (also fails).
# Finally, compile a read of the other database using handle 2. This
# should also fail.
#

do_test shared-$av.4.4.1.2 {
  # Sanity check 1: Check that the schema is what we think it is when viewed
  # via handle 1.
  execsql {
    CREATE TABLE test2.ghi(g, h, i);
    SELECT 'test.db:'||name FROM sqlite_master 
    UNION ALL
    SELECT 'test2.db:'||name FROM test2.sqlite_master;
  }
} {test.db:abc test.db:def test2.db:ghi}
do_test shared-$av.4.4.1.2 {
  # Sanity check 2: Check that the schema is what we think it is when viewed
  # via handle 2.
  execsql {
    SELECT 'test2.db:'||name FROM sqlite_master 
    UNION ALL
    SELECT 'test.db:'||name FROM test.sqlite_master;
  } db2
} {test2.db:ghi test.db:abc test.db:def}


do_test shared-$av.4.4.2 {
  set ::DB2 [sqlite3_connection_pointer db2]
  set sql {SELECT * FROM abc}
  set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
  execsql {
    BEGIN;







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
# Open a write-transaction using handle 1 and modify the database schema.
# Then try to execute a compiled statement to read from the same 
# database via handle 2 (fails to get the lock on sqlite_master). Also
# try to compile a read of the same database using handle 2 (also fails).
# Finally, compile a read of the other database using handle 2. This
# should also fail.
#
ifcapable compound {
  do_test shared-$av.4.4.1.2 {
    # Sanity check 1: Check that the schema is what we think it is when viewed
    # via handle 1.
    execsql {
      CREATE TABLE test2.ghi(g, h, i);
      SELECT 'test.db:'||name FROM sqlite_master 
      UNION ALL
      SELECT 'test2.db:'||name FROM test2.sqlite_master;
    }
  } {test.db:abc test.db:def test2.db:ghi}
  do_test shared-$av.4.4.1.2 {
    # Sanity check 2: Check that the schema is what we think it is when viewed
    # via handle 2.
    execsql {
      SELECT 'test2.db:'||name FROM sqlite_master 
      UNION ALL
      SELECT 'test.db:'||name FROM test.sqlite_master;
    } db2
  } {test2.db:ghi test.db:abc test.db:def}
}

do_test shared-$av.4.4.2 {
  set ::DB2 [sqlite3_connection_pointer db2]
  set sql {SELECT * FROM abc}
  set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY]
  execsql {
    BEGIN;
418
419
420
421
422
423
424

425
426
427
428
429

430
431
432
433
434
435
436
437
438
439
440
441
442
443



444
445
446
447

448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
  execsql {
    DROP INDEX i1;
    DROP VIEW v1;
    DROP TRIGGER trig1;
    DROP TABLE t1;
  } db2
} {}

do_test shared-$av.5.1.2 {
  execsql {
    SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
  } db1
} {}


#--------------------------------------------------------------------------
# Tests shared-6.* test that a query obtains all the read-locks it needs
# before starting execution of the query. This means that there is no chance
# some rows of data will be returned before a lock fails and SQLITE_LOCK
# is returned.
#
do_test shared-$av.6.1.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t2 VALUES(3, 4);
  } db1



  execsql {
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  } db2
} {1 2 3 4}

do_test shared-$av.6.1.2 {
  # Establish a write lock on table t2 via connection db2. Then make a 
  # UNION all query using connection db1 that first accesses t1, followed 
  # by t2. If the locks are grabbed at the start of the statement (as 
  # they should be), no rows are returned. If (as was previously the case)
  # they are grabbed as the tables are accessed, the t1 rows will be 
  # returned before the query fails.
  #
  execsql {
    BEGIN;
    INSERT INTO t2 VALUES(5, 6);
  } db2
  set ret [list]
  catch {
    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
      lappend ret $a $b
    }
  }
  set ret
} {}
do_test shared-$av.6.1.3 {
  execsql {
    COMMIT;
    BEGIN;
    INSERT INTO t1 VALUES(7, 8);
  } db2
  set ret [list]
  catch {







>
|
|
|
|
|
>














>
>
>
|
|
|
|
>
|



















|







420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
  execsql {
    DROP INDEX i1;
    DROP VIEW v1;
    DROP TRIGGER trig1;
    DROP TABLE t1;
  } db2
} {}
ifcapable compound {
  do_test shared-$av.5.1.2 {
    execsql {
      SELECT * FROM sqlite_master UNION ALL SELECT * FROM test1.sqlite_master
    } db1
  } {}
}

#--------------------------------------------------------------------------
# Tests shared-6.* test that a query obtains all the read-locks it needs
# before starting execution of the query. This means that there is no chance
# some rows of data will be returned before a lock fails and SQLITE_LOCK
# is returned.
#
do_test shared-$av.6.1.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t2 VALUES(3, 4);
  } db1
} {}
ifcapable compound {
  do_test shared-$av.6.1.2 {
    execsql {
      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    } db2
  } {1 2 3 4}
}
do_test shared-$av.6.1.3 {
  # Establish a write lock on table t2 via connection db2. Then make a 
  # UNION all query using connection db1 that first accesses t1, followed 
  # by t2. If the locks are grabbed at the start of the statement (as 
  # they should be), no rows are returned. If (as was previously the case)
  # they are grabbed as the tables are accessed, the t1 rows will be 
  # returned before the query fails.
  #
  execsql {
    BEGIN;
    INSERT INTO t2 VALUES(5, 6);
  } db2
  set ret [list]
  catch {
    db1 eval {SELECT * FROM t1 UNION ALL SELECT * FROM t2} {
      lappend ret $a $b
    }
  }
  set ret
} {}
do_test shared-$av.6.1.4 {
  execsql {
    COMMIT;
    BEGIN;
    INSERT INTO t1 VALUES(7, 8);
  } db2
  set ret [list]
  catch {
Changes to test/tkt1444.test.
12
13
14
15
16
17
18





19
20
21
22
23
24
25
#
# This file implements tests to verify that ticket #1444 has been
# fixed.  
#

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






# The use of a VIEW that contained an ORDER BY clause within a UNION ALL
# was causing problems.  See ticket #1444.
#
do_test tkt1444-1.1 {
  execsql {
    CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real);







>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#
# This file implements tests to verify that ticket #1444 has been
# fixed.  
#

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

ifcapable compound {
  finish_test
  return 
}

# The use of a VIEW that contained an ORDER BY clause within a UNION ALL
# was causing problems.  See ticket #1444.
#
do_test tkt1444-1.1 {
  execsql {
    CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real);
Changes to test/tkt1473.test.
12
13
14
15
16
17
18





19
20
21
22
23
24
25
#
# This file implements tests to verify that ticket #1473 has been
# fixed.  
#

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






do_test tkt1473-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1







>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#
# This file implements tests to verify that ticket #1473 has been
# fixed.  
#

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

ifcapable compound {
  finish_test
  return 
}

do_test tkt1473-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1
Changes to test/types3.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 the interaction of SQLite manifest types
# with Tcl dual-representations.
#
# $Id: types3.test,v 1.3 2005/11/14 22:29:06 drh Exp $
#

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

# A variable with only a string representation comes in as TEXT
do_test types3-1.1 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 the interaction of SQLite manifest types
# with Tcl dual-representations.
#
# $Id: types3.test,v 1.4 2006/01/16 16:24:25 danielk1977 Exp $
#

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

# A variable with only a string representation comes in as TEXT
do_test types3-1.1 {
52
53
54
55
56
57
58

59
60
61
62

63
64
65
66
67
68
69
  set V "abc"
  binary scan $V a3 x
  concat [tcl_variable_type V] [execsql {SELECT typeof($::V)}]
} {bytearray text}

# Check to make sure return values are of the right types.
#

do_test types3-2.1 {
  set V [db one {SELECT x'616263'}]
  tcl_variable_type V
} bytearray

do_test types3-2.2 {
  set V [db one {SELECT 123}]
  tcl_variable_type V
} int
do_test types3-2.3 {
  set V [db one {SELECT 1234567890123456}]
  tcl_variable_type V







>
|
|
|
|
>







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
  set V "abc"
  binary scan $V a3 x
  concat [tcl_variable_type V] [execsql {SELECT typeof($::V)}]
} {bytearray text}

# Check to make sure return values are of the right types.
#
ifcapable bloblit {
  do_test types3-2.1 {
    set V [db one {SELECT x'616263'}]
    tcl_variable_type V
  } bytearray
}
do_test types3-2.2 {
  set V [db one {SELECT 123}]
  tcl_variable_type V
} int
do_test types3-2.3 {
  set V [db one {SELECT 1234567890123456}]
  tcl_variable_type V
Changes to test/vacuum2.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
# 2005 February 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 the VACUUM statement.
#
# $Id: vacuum2.test,v 1.1 2005/02/16 03:27:08 drh Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#
ifcapable {!vacuum} {
  finish_test
  return
}
if $AUTOVACUUM {
  finish_test
  return
}













|







|







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
# 2005 February 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 the VACUUM statement.
#
# $Id: vacuum2.test,v 1.2 2006/01/16 16:24:25 danielk1977 Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#
ifcapable {!vacuum||!autoinc} {
  finish_test
  return
}
if $AUTOVACUUM {
  finish_test
  return
}
Changes to test/where2.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 the use of indices in WHERE clauses
# based on recent changes to the optimizer.
#
# $Id: where2.test,v 1.6 2006/01/14 08:02:29 danielk1977 Exp $

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

# Build some test data
#
do_test where2-1.0 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 the use of indices in WHERE clauses
# based on recent changes to the optimizer.
#
# $Id: where2.test,v 1.7 2006/01/16 16:24:25 danielk1977 Exp $

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

# Build some test data
#
do_test where2-1.0 {
142
143
144
145
146
147
148

149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164

165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

183
184
185
186
187
188
189
190

191
192
193
194
195
196
197
  do_test where2-4.3 {
    queryplan {
      SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
                       AND x>0 AND x<10
      ORDER BY w
    }
  } {99 6 10000 10006 sort t1 i1zyx}

  do_test where2-4.4 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                       AND y IN (10000,10201)
                       AND x>0 AND x<10
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  do_test where2-4.5 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                       AND y IN (SELECT 10000 UNION SELECT 10201)
                       AND x>0 AND x<10
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}

  do_test where2-4.6 {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY 2
    }
  } {99 6 10000 10006 sort t1 i1xy}

  # Duplicate entires on the RHS of an IN operator do not cause duplicate
  # output rows.
  #
  do_test where2-4.6 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}

  do_test where2-4.7 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (
         SELECT 10207 UNION ALL SELECT 10006
         UNION ALL SELECT 10006 UNION ALL SELECT 10207)
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}


} ;# ifcapable subquery

# The use of an IN operator disables the index as a sorter.
#
do_test where2-5.1 {
  queryplan {







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>


















>
|
|
|
|
|
|
|
|
>







142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
  do_test where2-4.3 {
    queryplan {
      SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
                       AND x>0 AND x<10
      ORDER BY w
    }
  } {99 6 10000 10006 sort t1 i1zyx}
  ifcapable compound {
    do_test where2-4.4 {
      queryplan {
        SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                         AND y IN (10000,10201)
                         AND x>0 AND x<10
        ORDER BY w
      }
    } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
    do_test where2-4.5 {
      queryplan {
        SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
                         AND y IN (SELECT 10000 UNION SELECT 10201)
                         AND x>0 AND x<10
        ORDER BY w
      }
    } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  }
  do_test where2-4.6 {
    queryplan {
      SELECT * FROM t1
       WHERE x IN (1,2,3,4,5,6,7,8)
         AND y IN (10000,10001,10002,10003,10004,10005)
       ORDER BY 2
    }
  } {99 6 10000 10006 sort t1 i1xy}

  # Duplicate entires on the RHS of an IN operator do not cause duplicate
  # output rows.
  #
  do_test where2-4.6 {
    queryplan {
      SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
      ORDER BY w
    }
  } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  ifcapable compound {
    do_test where2-4.7 {
      queryplan {
        SELECT * FROM t1 WHERE z IN (
           SELECT 10207 UNION ALL SELECT 10006
           UNION ALL SELECT 10006 UNION ALL SELECT 10207)
        ORDER BY w
      }
    } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  }

} ;# ifcapable subquery

# The use of an IN operator disables the index as a sorter.
#
do_test where2-5.1 {
  queryplan {