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