1#
2# 2001 September 15
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.
13#
14# This file implements tests for miscellanous features that were
15# left out of other test files.
16#
17# $Id: misc1.test,v 1.23 2003/08/05 13:13:39 drh Exp $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Test the creation and use of tables that have a large number
23# of columns.
24#
25do_test misc1-1.1 {
26  set cmd "CREATE TABLE manycol(x0 text"
27  for {set i 1} {$i<=99} {incr i} {
28    append cmd ",x$i text"
29  }
30  append cmd ")";
31  execsql $cmd
32  set cmd "INSERT INTO manycol VALUES(0"
33  for {set i 1} {$i<=99} {incr i} {
34    append cmd ",$i"
35  }
36  append cmd ")";
37  execsql $cmd
38  execsql "SELECT x99 FROM manycol"
39} 99
40do_test misc1-1.2 {
41  execsql {SELECT x0, x10, x25, x50, x75 FROM manycol}
42} {0 10 25 50 75}
43do_test misc1-1.3.1 {
44  for {set j 100} {$j<=1000} {incr j 100} {
45    set cmd "INSERT INTO manycol VALUES($j"
46    for {set i 1} {$i<=99} {incr i} {
47      append cmd ",[expr {$i+$j}]"
48    }
49    append cmd ")"
50    execsql $cmd
51  }
52  execsql {SELECT x50 FROM manycol ORDER BY x80+0}
53} {50 150 250 350 450 550 650 750 850 950 1050}
54do_test misc1-1.3.2 {
55  execsql {SELECT x50 FROM manycol ORDER BY x80}
56} {1050 150 250 350 450 550 650 750 50 850 950}
57do_test misc1-1.4 {
58  execsql {SELECT x75 FROM manycol WHERE x50=350}
59} 375
60do_test misc1-1.5 {
61  execsql {SELECT x50 FROM manycol WHERE x99=599}
62} 550
63do_test misc1-1.6 {
64  execsql {CREATE INDEX manycol_idx1 ON manycol(x99)}
65  execsql {SELECT x50 FROM manycol WHERE x99=899}
66} 850
67do_test misc1-1.7 {
68  execsql {SELECT count(*) FROM manycol}
69} 11
70do_test misc1-1.8 {
71  execsql {DELETE FROM manycol WHERE x98=1234}
72  execsql {SELECT count(*) FROM manycol}
73} 11
74do_test misc1-1.9 {
75  execsql {DELETE FROM manycol WHERE x98=998}
76  execsql {SELECT count(*) FROM manycol}
77} 10
78do_test misc1-1.10 {
79  execsql {DELETE FROM manycol WHERE x99=500}
80  execsql {SELECT count(*) FROM manycol}
81} 10
82do_test misc1-1.11 {
83  execsql {DELETE FROM manycol WHERE x99=599}
84  execsql {SELECT count(*) FROM manycol}
85} 9
86
87# Check GROUP BY expressions that name two or more columns.
88#
89do_test misc1-2.1 {
90  execsql {
91    BEGIN TRANSACTION;
92    CREATE TABLE agger(one text, two text, three text, four text);
93    INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
94    INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
95    INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes');
96    INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes');
97    INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes');
98    INSERT INTO agger VALUES(6, 'two', 'hello', 'no');
99    COMMIT
100  }
101  execsql {SELECT count(*) FROM agger}
102} 6
103do_test misc1-2.2 {
104  execsql {SELECT sum(one), two, four FROM agger
105           GROUP BY two, four ORDER BY sum(one) desc}
106} {8 two no 6 one yes 4 two yes 3 thr yes}
107do_test misc1-2.3 {
108  execsql {SELECT sum((one)), (two), (four) FROM agger
109           GROUP BY (two), (four) ORDER BY sum(one) desc}
110} {8 two no 6 one yes 4 two yes 3 thr yes}
111
112# Here's a test for a bug found by Joel Lucsy.  The code below
113# was causing an assertion failure.
114#
115do_test misc1-3.1 {
116  set r [execsql {
117    CREATE TABLE t1(a);
118    INSERT INTO t1 VALUES('hi');
119    PRAGMA full_column_names=on;
120    SELECT rowid, * FROM t1;
121  }]
122  lindex $r 1
123} {hi}
124
125# Here's a test for yet another bug found by Joel Lucsy.  The code
126# below was causing an assertion failure.
127#
128do_test misc1-4.1 {
129  execsql {
130    BEGIN;
131    CREATE TABLE t2(a);
132    INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
133    UPDATE t2 SET a=a||a||a||a;
134    INSERT INTO t2 SELECT '1 - ' || a FROM t2;
135    INSERT INTO t2 SELECT '2 - ' || a FROM t2;
136    INSERT INTO t2 SELECT '3 - ' || a FROM t2;
137    INSERT INTO t2 SELECT '4 - ' || a FROM t2;
138    INSERT INTO t2 SELECT '5 - ' || a FROM t2;
139    INSERT INTO t2 SELECT '6 - ' || a FROM t2;
140    COMMIT;
141    SELECT count(*) FROM t2;
142  }
143} {64}
144
145# Make sure we actually see a semicolon or end-of-file in the SQL input
146# before executing a command.  Thus if "WHERE" is misspelled on an UPDATE,
147# the user won't accidently update every record.
148#
149do_test misc1-5.1 {
150  catchsql {
151    CREATE TABLE t3(a,b);
152    INSERT INTO t3 VALUES(1,2);
153    INSERT INTO t3 VALUES(3,4);
154    UPDATE t3 SET a=0 WHEREwww b=2;
155  }
156} {1 {near "WHEREwww": syntax error}}
157do_test misc1-5.2 {
158  execsql {
159    SELECT * FROM t3 ORDER BY a;
160  }
161} {1 2 3 4}
162
163# Certain keywords (especially non-standard keywords like "REPLACE") can
164# also be used as identifiers.  The way this works in the parser is that
165# the parser first detects a syntax error, the error handling routine
166# sees that the special keyword caused the error, then replaces the keyword
167# with "ID" and tries again.
168#
169# Check the operation of this logic.
170#
171do_test misc1-6.1 {
172  catchsql {
173    CREATE TABLE t4(
174      abort, asc, begin, cluster, conflict, copy, delimiters, desc, end,
175      explain, fail, ignore, key, offset, pragma, replace, temp,
176      vacuum, view
177    );
178  }
179} {0 {}}
180do_test misc1-6.2 {
181  catchsql {
182    INSERT INTO t4
183       VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
184  }
185} {0 {}}
186do_test misc1-6.3 {
187  execsql {
188    SELECT * FROM t4
189  }
190} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19}
191do_test misc1-6.4 {
192  execsql {
193    SELECT abort+asc,max(key,pragma,temp) FROM t4
194  }
195} {3 17}
196
197# Test for multi-column primary keys, and for multiple primary keys.
198#
199do_test misc1-7.1 {
200  catchsql {
201    CREATE TABLE error1(
202      a TYPE PRIMARY KEY,
203      b TYPE PRIMARY KEY
204    );
205  }
206} {1 {table "error1" has more than one primary key}}
207do_test misc1-7.2 {
208  catchsql {
209    CREATE TABLE error1(
210      a INTEGER PRIMARY KEY,
211      b TYPE PRIMARY KEY
212    );
213  }
214} {1 {table "error1" has more than one primary key}}
215do_test misc1-7.3 {
216  execsql {
217    CREATE TABLE t5(a,b,c,PRIMARY KEY(a,b));
218    INSERT INTO t5 VALUES(1,2,3);
219    SELECT * FROM t5 ORDER BY a;
220  }
221} {1 2 3}
222do_test misc1-7.4 {
223  catchsql {
224    INSERT INTO t5 VALUES(1,2,4);
225  }
226} {1 {columns a, b are not unique}}
227do_test misc1-7.5 {
228  catchsql {
229    INSERT INTO t5 VALUES(0,2,4);
230  }
231} {0 {}}
232do_test misc1-7.6 {
233  execsql {
234    SELECT * FROM t5 ORDER BY a;
235  }
236} {0 2 4 1 2 3}
237
238do_test misc1-8.1 {
239  catchsql {
240    SELECT *;
241  }
242} {1 {no tables specified}}
243do_test misc1-8.2 {
244  catchsql {
245    SELECT t1.*;
246  }
247} {1 {no such table: t1}}
248
249execsql {
250  DROP TABLE t1;
251  DROP TABLE t2;
252  DROP TABLE t3;
253  DROP TABLE t4;
254}
255
256# If an integer is too big to be represented as a 32-bit machine integer,
257# then treat it as a string.
258#
259do_test misc1-9.1 {
260  catchsql {
261    CREATE TABLE t1(a unique not null, b unique not null);
262    INSERT INTO t1 VALUES('a',12345678901234567890);
263    INSERT INTO t1 VALUES('b',12345678911234567890);
264    INSERT INTO t1 VALUES('c',12345678921234567890);
265    SELECT * FROM t1;
266  }
267} {0 {a 12345678901234567890 b 12345678911234567890 c 12345678921234567890}}
268
269# A WHERE clause is not allowed to contain more than 99 terms.  Check to
270# make sure this limit is enforced.
271#
272do_test misc1-10.0 {
273  execsql {SELECT count(*) FROM manycol}
274} {9}
275do_test misc1-10.1 {
276  set ::where {WHERE x0>=0}
277  for {set i 1} {$i<=99} {incr i} {
278    append ::where " AND x$i<>0"
279  }
280  catchsql "SELECT count(*) FROM manycol $::where"
281} {0 9}
282do_test misc1-10.2 {
283  catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
284} {1 {WHERE clause too complex - no more than 100 terms allowed}}
285do_test misc1-10.3 {
286  regsub "x0>=0" $::where "x0=0" ::where
287  catchsql "DELETE FROM manycol $::where"
288} {0 {}}
289do_test misc1-10.4 {
290  execsql {SELECT count(*) FROM manycol}
291} {8}
292do_test misc1-10.5 {
293  catchsql "DELETE FROM manycol $::where AND rowid>0"
294} {1 {WHERE clause too complex - no more than 100 terms allowed}}
295do_test misc1-10.6 {
296  execsql {SELECT x1 FROM manycol WHERE x0=100}
297} {101}
298do_test misc1-10.7 {
299  regsub "x0=0" $::where "x0=100" ::where
300  catchsql "UPDATE manycol SET x1=x1+1 $::where"
301} {0 {}}
302do_test misc1-10.8 {
303  execsql {SELECT x1 FROM manycol WHERE x0=100}
304} {102}
305do_test misc1-10.9 {
306  catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
307} {1 {WHERE clause too complex - no more than 100 terms allowed}}
308do_test misc1-10.10 {
309  execsql {SELECT x1 FROM manycol WHERE x0=100}
310} {102}
311
312# Make sure the initialization works even if a database is opened while
313# another process has the database locked.
314#
315do_test misc1-11.1 {
316  execsql {BEGIN}
317  sqlite db2 test.db
318  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
319  lappend rc $msg
320} {1 {database is locked}}
321do_test misc1-11.2 {
322  execsql {COMMIT}
323  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
324  db2 close
325  lappend rc $msg
326} {0 3}
327
328# Make sure string comparisons really do compare strings in format4+.
329# Similar tests in the format3.test file show that for format3 and earlier
330# all comparisions where numeric if either operand looked like a number.
331#
332do_test misc1-12.1 {
333  execsql {SELECT '0'=='0.0'}
334} {0}
335do_test misc1-12.2 {
336  execsql {SELECT '0'==0.0}
337} {1}
338do_test misc1-12.3 {
339  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
340} {0}
341do_test misc1-12.4 {
342  execsql {
343    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
344    INSERT INTO t6 VALUES('0','0.0');
345    SELECT * FROM t6;
346  }
347} {0 0.0}
348do_test misc1-12.5 {
349  execsql {
350    INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
351    SELECT * FROM t6;
352  }
353} {0 0.0}
354do_test misc1-12.6 {
355  execsql {
356    INSERT OR IGNORE INTO t6 VALUES('y',0);
357    SELECT * FROM t6;
358  }
359} {0 0.0 y 0}
360do_test misc1-12.7 {
361  execsql {
362    CREATE TABLE t7(x INTEGER, y TEXT, z);
363    INSERT INTO t7 VALUES(0,0,1);
364    INSERT INTO t7 VALUES(0.0,0,2);
365    INSERT INTO t7 VALUES(0,0.0,3);
366    INSERT INTO t7 VALUES(0.0,0.0,4);
367    SELECT DISTINCT x, y FROM t7 ORDER BY z;
368  }
369} {0 0 0 0.0}
370do_test misc1-12.8 {
371  execsql {
372    SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1;
373  }
374} {1 4 4}
375do_test misc1-12.9 {
376  execsql {
377    SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
378  }
379} {1 2 2 3 4 2}
380
381# This used to be an error.  But we changed the code so that arbitrary
382# identifiers can be used as a collating sequence.  Collation is by text
383# if the identifier contains "text", "blob", or "clob" and is numeric
384# otherwise.
385do_test misc1-12.10 {
386  catchsql {
387    SELECT * FROM t6 ORDER BY a COLLATE unknown;
388  }
389} {0 {0 0.0 y 0}}
390do_test misc1-12.11 {
391  execsql {
392    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
393    INSERT INTO t8 VALUES(0,0,1);
394    INSERT INTO t8 VALUES(0.0,0,2);
395    INSERT INTO t8 VALUES(0,0.0,3);
396    INSERT INTO t8 VALUES(0.0,0.0,4);
397    SELECT DISTINCT x, y FROM t8 ORDER BY z;
398  }
399} {0 0 0 0.0}
400do_test misc1-12.12 {
401  execsql {
402    SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
403  }
404} {1 4 4}
405do_test misc1-12.13 {
406  execsql {
407    SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
408  }
409} {1 2 2 3 4 2}
410
411# There was a problem with realloc() in the OP_MemStore operation of
412# the VDBE.  A buffer was being reallocated but some pointers into
413# the old copy of the buffer were not being moved over to the new copy.
414# The following code tests for the problem.
415#
416do_test misc1-13.1 {
417   execsql {
418     CREATE TABLE t9(x,y);
419     INSERT INTO t9 VALUES('one',1);
420     INSERT INTO t9 VALUES('two',2);
421     INSERT INTO t9 VALUES('three',3);
422     INSERT INTO t9 VALUES('four',4);
423     INSERT INTO t9 VALUES('five',5);
424     INSERT INTO t9 VALUES('six',6);
425     INSERT INTO t9 VALUES('seven',7);
426     INSERT INTO t9 VALUES('eight',8);
427     INSERT INTO t9 VALUES('nine',9);
428     INSERT INTO t9 VALUES('ten',10);
429     INSERT INTO t9 VALUES('eleven',11);
430     SELECT y FROM t9
431     WHERE x=(SELECT x FROM t9 WHERE y=1)
432        OR x=(SELECT x FROM t9 WHERE y=2)
433        OR x=(SELECT x FROM t9 WHERE y=3)
434        OR x=(SELECT x FROM t9 WHERE y=4)
435        OR x=(SELECT x FROM t9 WHERE y=5)
436        OR x=(SELECT x FROM t9 WHERE y=6)
437        OR x=(SELECT x FROM t9 WHERE y=7)
438        OR x=(SELECT x FROM t9 WHERE y=8)
439        OR x=(SELECT x FROM t9 WHERE y=9)
440        OR x=(SELECT x FROM t9 WHERE y=10)
441        OR x=(SELECT x FROM t9 WHERE y=11)
442        OR x=(SELECT x FROM t9 WHERE y=12)
443        OR x=(SELECT x FROM t9 WHERE y=13)
444        OR x=(SELECT x FROM t9 WHERE y=14)
445     ;
446   }
447} {1 2 3 4 5 6 7 8 9 10 11}
448
449# Make sure a database connection still works after changing the
450# working directory.
451#
452do_test misc1-14.1 {
453  file mkdir tempdir
454  cd tempdir
455  execsql {BEGIN}
456  file exists ./test.db-journal
457} {0}
458do_test misc1-14.2 {
459  file exists ../test.db-journal
460} {1}
461do_test misc1-14.3 {
462  cd ..
463  file delete tempdir
464  execsql {COMMIT}
465  file exists ./test.db-journal
466} {0}
467
468# A failed create table should not leave the table in the internal
469# data structures.  Ticket #238.
470#
471do_test misc1-15.1 {
472  catchsql {
473    CREATE TABLE t10 AS SELECT c1;
474  }
475} {1 {no such column: c1}}
476do_test misc1-15.2 {
477  catchsql {
478    CREATE TABLE t10 AS SELECT 1;
479  }
480  # The bug in ticket #238 causes the statement above to fail with
481  # the error "table t10 alread exists"
482} {0 {}}
483
484# Test for memory leaks when a CREATE TABLE containing a primary key
485# fails.  Ticket #249.
486#
487do_test misc1-16.1 {
488  catchsql {SELECT name FROM sqlite_master LIMIT 1}
489  catchsql {
490    CREATE TABLE test(a integer, primary key(a));
491  }
492} {0 {}}
493do_test misc1-16.2 {
494  catchsql {
495    CREATE TABLE test(a integer, primary key(a));
496  }
497} {1 {table test already exists}}
498do_test misc1-16.3 {
499  catchsql {
500    CREATE TABLE test2(a text primary key, b text, primary key(a,b));
501  }
502} {1 {table "test2" has more than one primary key}}
503do_test misc1-16.4 {
504  execsql {
505    INSERT INTO test VALUES(1);
506    SELECT rowid, a FROM test;
507  }
508} {1 1}
509do_test misc1-16.5 {
510  execsql {
511    INSERT INTO test VALUES(5);
512    SELECT rowid, a FROM test;
513  }
514} {1 1 5 5}
515do_test misc1-16.6 {
516  execsql {
517    INSERT INTO test VALUES(NULL);
518    SELECT rowid, a FROM test;
519  }
520} {1 1 5 5 6 6}
521
522# Ticket #333: Temp triggers that modify persistent tables.
523#
524do_test misc1-17.1 {
525  execsql {
526    BEGIN;
527    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
528    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
529    CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
530      INSERT INTO RealTable(TestString)
531         SELECT new.TestString FROM TempTable LIMIT 1;
532    END;
533    INSERT INTO TempTable(TestString) VALUES ('1');
534    INSERT INTO TempTable(TestString) VALUES ('2');
535    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID IN (1, 2);
536    COMMIT;
537    SELECT TestString FROM RealTable ORDER BY 1;
538  }
539} {2 3}
540
541finish_test
542