# 2012 April 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.
#
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple
do_execsql_test 1.0 {
PRAGMA table_info = sqlite_master
} {
0 type text 0 {} 0
1 name text 0 {} 0
2 tbl_name text 0 {} 0
3 rootpage integer 0 {} 0
4 sql text 0 {} 0
}
do_execsql_test 1.1 { SELECT * FROM sqlite_master } {}
#explain { CREATE TABLE t1(a, b) }
#execsql { PRAGMA kv_trace = 1 }
#execsql { PRAGMA vdbe_trace = 1 }
do_execsql_test 1.2 {
CREATE TABLE t1(a, b);
PRAGMA table_info = t1;
} {
0 a {} 0 {} 0
1 b {} 0 {} 0
}
do_execsql_test 1.3 {
CREATE TABLE t2(x, y);
PRAGMA table_info = t2;
} {
0 x {} 0 {} 0
1 y {} 0 {} 0
}
do_execsql_test 1.4 {
CREATE TABLE t3(k PRIMARY KEY, v);
PRAGMA table_info = t3;
} {
0 k {} 1 {} 1
1 v {} 0 {} 0
}
do_execsql_test 1.5 {
SELECT name, rootpage FROM sqlite_master
} {t1 2 t2 3 t3 4}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.1.1 { CREATE TABLE t1(k PRIMARY KEY, v) } {}
do_execsql_test 2.1.2 { CREATE TABLE t2(x, y) } {}
do_execsql_test 2.2.1 { INSERT INTO t1 VALUES('a', 'AAA') }
do_execsql_test 2.2.2 { SELECT * FROM t1 } {a AAA}
do_execsql_test 2.2.3 { INSERT INTO t1 VALUES('b', 'BBB') }
do_execsql_test 2.2.4 { SELECT * FROM t1 } {a AAA b BBB}
do_execsql_test 2.3.1 { INSERT INTO t2 VALUES('123', '456') }
do_execsql_test 2.3.2 { SELECT * FROM t2 } {123 456}
do_execsql_test 2.3.3 { INSERT INTO t2 VALUES('789', '0ab') }
do_execsql_test 2.3.4 { SELECT * FROM t2 } {123 456 789 0ab}
do_catchsql_test 2.2.5 {
INSERT INTO t1 VALUES('a', 'CCC')
} {1 {PRIMARY KEY must be unique}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.1 { CREATE TABLE t1(k PRIMARY KEY, v UNIQUE) }
do_execsql_test 3.2 {
SELECT * FROM sqlite_master
} {
table t1 t1 2 {CREATE TABLE t1(k PRIMARY KEY, v UNIQUE)}
index sqlite_t1_unique2 t1 3 {}
}
#explain { INSERT INTO t1 VALUES('one', '111') }
#execsql { PRAGMA vdbe_trace = 1 }
#execsql { PRAGMA kv_trace = 1 }
#
do_execsql_test 3.3 { INSERT INTO t1 VALUES('one', '111') } {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.1 { CREATE TABLE t1(k PRIMARY KEY, v) }
do_execsql_test 4.2 { CREATE INDEX i1 ON t1(v) }
do_execsql_test 4.3 {
SELECT * FROM sqlite_master
} {
table t1 t1 2 {CREATE TABLE t1(k PRIMARY KEY, v)}
index i1 t1 3 {CREATE INDEX i1 ON t1(v)}
}
do_execsql_test 4.4 { INSERT INTO t1 VALUES('one', '111') } {}
do_execsql_test 4.5 { SELECT * FROM t1 } {one 111}
do_execsql_test 4.6 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.1 { CREATE TABLE t1(k, v UNIQUE) }
do_execsql_test 5.2 { CREATE INDEX i1 ON t1(v) }
do_execsql_test 5.3 {
SELECT * FROM sqlite_master
} {
table t1 t1 3 {CREATE TABLE t1(k, v UNIQUE)}
index sqlite_t1_unique1 t1 2 {}
index i1 t1 4 {CREATE INDEX i1 ON t1(v)}
}
do_execsql_test 5.3 { INSERT INTO t1 VALUES('one', '111') } {}
do_execsql_test 5.4 { SELECT * FROM t1 } {one 111}
do_execsql_test 5.5 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.1 {
CREATE TABLE t1(k PRIMARY KEY, v);
CREATE INDEX i1 ON t1(v);
INSERT INTO t1 VALUES('one', 1);
INSERT INTO t1 VALUES('two', 2);
INSERT INTO t1 VALUES('three', 3);
INSERT INTO t1 VALUES('four', 4);
INSERT INTO t1 VALUES('five', 5);
}
do_execsql_test 6.2 {
SELECT * FROM t1
} {five 5 four 4 one 1 three 3 two 2}
do_execsql_test 6.3 {
CREATE TABLE t2(x PRIMARY KEY, y);
INSERT INTO t2 SELECT v, k FROM t1;
SELECT * FROM t2
} {1 one 2 two 3 three 4 four 5 five}
do_execsql_test 6.4 { PRAGMA integrity_check } {ok}
do_execsql_test 6.5 {
CREATE TABLE t3(a, b);
INSERT INTO t3 SELECT k, v FROM t1;
SELECT * FROM t3
} {five 5 four 4 one 1 three 3 two 2}
do_execsql_test 6.6 {
INSERT INTO t3 SELECT a, b FROM t3;
SELECT * FROM t3;
} {five 5 four 4 one 1 three 3 two 2 five 5 four 4 one 1 three 3 two 2}
do_execsql_test 6.7 { PRAGMA integrity_check } {ok}
do_execsql_test 6.8 { CREATE INDEX i2 ON t3(a) }
do_execsql_test 6.9 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.1 {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(a);
}
do_execsql_test 7.2.1 { INSERT INTO t1 VALUES('xyz', '123') }
do_execsql_test 7.2.2 { INSERT INTO t1 VALUES('xyz', '123') }
do_execsql_test 7.2.3 { INSERT INTO t1 VALUES('xyz', '123') }
do_execsql_test 7.3 {
SELECT * FROM t1;
} {xyz 123 xyz 123 xyz 123}
do_execsql_test 7.4 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 8.1 {
CREATE TABLE t1(a PRIMARY KEY, b);
INSERT INTO t1 VALUES('a', 'b');
}
do_execsql_test 8.2 { DELETE FROM t1 WHERE b = 'b' }
do_execsql_test 8.3 { SELECT * FROM t1 } {}
do_execsql_test 8.4 {
INSERT INTO t1 VALUES('a', 'A');
INSERT INTO t1 VALUES('b', 'B');
INSERT INTO t1 VALUES('c', 'A');
INSERT INTO t1 VALUES('d', 'B');
INSERT INTO t1 VALUES('e', 'A');
INSERT INTO t1 VALUES('f', 'B');
}
do_execsql_test 8.5 { DELETE FROM t1 WHERE b = 'B' }
do_execsql_test 8.6 { SELECT * FROM t1 } {a A c A e A}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 9.1 {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(b);
}
do_execsql_test 9.2 {
INSERT INTO t1 VALUES('a', 'A');
INSERT INTO t1 VALUES('b', 'B');
INSERT INTO t1 VALUES('c', 'A');
INSERT INTO t1 VALUES('d', 'B');
INSERT INTO t1 VALUES('e', 'A');
INSERT INTO t1 VALUES('f', 'B');
}
do_execsql_test 9.3 { DELETE FROM t1 WHERE +b = 'B' }
do_execsql_test 9.4 { SELECT * FROM t1 } {a A c A e A}
do_execsql_test 9.5 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 10.1 {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(b);
}
do_execsql_test 10.2 {
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
}
do_execsql_test 10.3 { UPDATE t1 SET b = 10 WHERE a=3 }
do_execsql_test 10.4 { SELECT * FROM t1 } {1 2 3 10}
do_execsql_test 10.5 { PRAGMA integrity_check } {ok}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 11.1 {
CREATE TABLE t1(a, b, c, UNIQUE(a));
INSERT INTO t1 VALUES(1,2,3);
}
do_catchsql_test 11.2 {
INSERT INTO t1 VALUES(1,2,4)
} {1 {column a is not unique}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 12.1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(3, 'three');
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(2, 'two');
}
do_execsql_test 12.2 { SELECT * FROM t1 ORDER BY a } {1 one 2 two 3 three}
do_execsql_test 12.3 { SELECT * FROM t1 ORDER BY b } {1 one 3 three 2 two}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(3, 'three');
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(2, 'two');
}
do_execsql_test 13.2 { SELECT a FROM t1 } {3 1 2}
do_execsql_test 13.3 { CREATE TABLE t2(x, y) }
do_execsql_test 13.4 { SELECT a FROM t1 } {3 1 2}
do_execsql_test 13.5 { DROP TABLE t2 }
do_execsql_test 13.6 { SELECT a FROM t1 } {3 1 2}
do_execsql_test 13.7 { CREATE TABLE t2 AS SELECT * FROM t1 }
do_execsql_test 13.8 { SELECT a FROM t2 } {3 1 2}
do_execsql_test 13.9 { DROP TABLE t1 }
do_execsql_test 13.10 { SELECT a FROM t2 } {3 1 2}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 14.1 {
CREATE TABLE t1(a,b,c NOT NULL DEFAULT 5);
CREATE TABLE t2(a,b,c);
CREATE TABLE t3(x);
INSERT INTO t2 VALUES(1,2,1);
INSERT INTO t2 VALUES(2,3,2);
INSERT INTO t2 VALUES(3,4,1);
INSERT INTO t2 VALUES(4,5,4);
INSERT INTO t3 VALUES(1);
}
do_execsql_test 14.2 { DROP TABLE t1 }
do_execsql_test 14.3 { SELECT * FROM t3 } 1
#-------------------------------------------------------------------------
reset_db
do_execsql_test 15.1.1 { CREATE TABLE t1(x PRIMARY KEY) }
do_execsql_test 15.1.2 {
BEGIN;
INSERT INTO t1 VALUES('rollback is not implemented yet');
}
do_execsql_test 15.1.3 { ROLLBACK }
do_execsql_test 15.1.4 { SELECT * FROM t1 } {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 16.1.1 {
PRAGMA foreign_keys = ON;
CREATE TABLE p1(x PRIMARY KEY);
CREATE TABLE c1(y REFERENCES p1);
INSERT INTO p1 VALUES(2);
INSERT INTO p1 VALUES(4);
INSERT INTO p1 VALUES(6);
}
do_execsql_test 16.1.2 { INSERT INTO c1 VALUES(2) }
do_catchsql_test 16.1.3 {
INSERT INTO c1 VALUES(3)
} {1 {foreign key constraint failed}}
do_execsql_test 16.1.4 { SELECT * FROM c1 } {2}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 17.1 {
PRAGMA foreign_keys = ON;
CREATE TABLE t1(x PRIMARY KEY);
CREATE TABLE t2(a PRIMARY KEY, b);
INSERT INTO t1 VALUES('X');
INSERT INTO t2 VALUES(1, 'A');
INSERT INTO t2 VALUES(2, 'B');
INSERT INTO t2 VALUES(3, 'C');
INSERT INTO t2 VALUES(4, 'D');
INSERT INTO t2 VALUES(5, 'A');
}
do_catchsql_test 17.2 {
INSERT INTO t1 SELECT b FROM t2;
} {1 {PRIMARY KEY must be unique}}
do_execsql_test 17.3 { SELECT * FROM t1 } {X}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 18.1 {
CREATE TABLE t1(a, b, c, UNIQUE(a,b) ON CONFLICT IGNORE);
CREATE TABLE t2(x);
INSERT INTO t1 VALUES(1,2,3);
BEGIN;
INSERT INTO t2 VALUES(1);
INSERT INTO t1 VALUES(1,2,4);
COMMIT;
}
do_execsql_test 18.2 { SELECT * FROM t1 } {1 2 3}
do_execsql_test 18.3 { SELECT * FROM t2 } {1}
#-------------------------------------------------------------------------
reset_db
do_test 19.1 {
catchsql {
CREATE TABLE t4(x);
CREATE UNIQUE INDEX t4x ON t4(x);
BEGIN;
INSERT INTO t4 VALUES(1);
INSERT OR ROLLBACK INTO t4 VALUES(1);
}
execsql { SELECT * FROM t4 }
} {}
# Check the above closed the transaction.
do_execsql_test 19.2 { BEGIN }
do_execsql_test 19.3 { COMMIT }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 20.1 {
CREATE TABLE def(d, e, f);
BEGIN;
INSERT INTO def VALUES('a', 'b', 'c');
INSERT INTO def SELECT * FROM def;
INSERT INTO def SELECT * FROM def;
INSERT INTO def SELECT * FROM def;
INSERT INTO def SELECT * FROM def;
INSERT INTO def SELECT * FROM def;
SELECT count(*) FROM def;
} {32}
do_execsql_test 20.2 { ROLLBACK }
do_execsql_test 20.3 { SELECT count(*) FROM def } 0
#-------------------------------------------------------------------------
reset_db
do_execsql_test 21.1 {
PRAGMA foreign_keys = on;
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(c REFERENCES t1(a), d);
}
do_execsql_test 21.2 {
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t2 VALUES(1, 3);
INSERT INTO t2 VALUES(NULL, 4);
}
do_catchsql_test 21.3 {
UPDATE t2 SET c=2 WHERE d=4;
} {1 {foreign key constraint failed}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 22.1 {
CREATE TABLE t1(x PRIMARY KEY);
INSERT INTO t1 VALUES('abc');
}
do_execsql_test 22.2 { UPDATE t1 SET x = 'abc' }
do_execsql_test 22.3 { SELECT * FROM t1 } {abc}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 23.1 {
PRAGMA foreign_keys = on;
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(c REFERENCES t1(a), d);
}
proc out {} {
set t1 [execsql {SELECT a FROM t1}]
set t2 [execsql {SELECT c FROM t2}]
puts "t1: $t1 t2: $t2"
}
do_test 23.2 {
catchsql "BEGIN; INSERT INTO t2 VALUES(1, 3)" ; execsql COMMIT
} {}
do_test 23.3 {
catchsql "BEGIN; INSERT INTO t1 VALUES(1, 2)" ; execsql COMMIT
} {}
do_test 23.4 {
catchsql "BEGIN; INSERT INTO t2 VALUES(1, 3)" ; execsql COMMIT
} {}
do_test 23.5 {
catchsql "BEGIN; INSERT INTO t2 VALUES(2, 4)" ; execsql COMMIT
} {}
do_test 23.6 {
catchsql "BEGIN; INSERT INTO t2 VALUES(NULL, 4)" ; execsql COMMIT
} {}
do_test 23.7 {
catchsql "BEGIN; UPDATE t2 SET c=2 WHERE d=4" ; execsql COMMIT
} {}
do_test 23.8 {
catchsql "BEGIN; UPDATE t2 SET c=1 WHERE d=4" ; execsql COMMIT
} {}
do_test 23.9 {
catchsql "BEGIN; UPDATE t2 SET c=1 WHERE d=4" ; execsql COMMIT
} {}
do_test 23.10 {
catchsql "BEGIN; UPDATE t2 SET c=NULL WHERE d=4" ; execsql COMMIT
} {}
do_test 23.11 {
execsql BEGIN
catchsql "DELETE FROM t1 WHERE a=1"
execsql COMMIT
} {}
do_catchsql_test 23.3 {
BEGIN;
UPDATE t1 SET a = 2;
COMMIT;
} {1 {foreign key constraint failed}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 24.1 {
CREATE TABLE p(x INTEGER);
INSERT INTO p VALUES(35.0);
SELECT typeof(x) FROM p;
} {integer}
do_execsql_test 24.2 {
CREATE TABLE p2(x INTEGER PRIMARY KEY);
INSERT INTO p2 VALUES(35.0);
SELECT typeof(x) FROM p2;
} {integer}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 25.1 {
PRAGMA foreign_keys = on;
CREATE TABLE p(x INT PRIMARY KEY);
CREATE TABLE c(y REFERENCES p);
INSERT INTO p VALUES(35);
INSERT INTO c VALUES(35.0);
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 26.1 {
PRAGMA foreign_keys = on;
CREATE TABLE p(x INT PRIMARY KEY);
CREATE TABLE c(y REFERENCES p);
INSERT INTO p VALUES(35.0);
INSERT INTO c VALUES(35.0);
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 27.1 {
CREATE TABLE t1(a, b);
CREATE TABLE log(x);
CREATE TRIGGER BEFORE UPDATE ON t1 BEGIN
INSERT INTO log VALUES(old.b || ' -> ' || new.b);
END;
INSERT INTO t1 VALUES(1, 'abc');
UPDATE t1 SET b = 'xyz';
}
do_execsql_test 27.2 { SELECT * FROM log } {{abc -> xyz}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 28.1 {
CREATE TABLE t1(a, b);
CREATE TABLE log(x);
CREATE TRIGGER BEFORE UPDATE ON t1 BEGIN
INSERT INTO log VALUES('rowid=' || old.rowid);
END;
INSERT INTO t1 VALUES(1, 'abc');
}
do_execsql_test 28.2 { SELECT rowid FROM t1 } 1
do_execsql_test 28.3 { UPDATE t1 SET b = 'xyz'; }
do_execsql_test 28.4 { SELECT * FROM log } {{rowid=1}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 29.1 {
CREATE TABLE t1(a, b);
CREATE TABLE log(x,y,z);
CREATE TRIGGER tr BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES(new.rowid, new.a, new.b);
END;
}
do_execsql_test 29.2 { INSERT INTO t1 VALUES('one', 'abc') }
do_execsql_test 29.3 { SELECT * FROM log } {-1 one abc}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 30.1 {
CREATE TABLE t1(a, b);
CREATE TABLE log(x,y,z);
CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
INSERT INTO log VALUES(new.rowid, new.a, new.b);
END;
}
do_execsql_test 30.2 { INSERT INTO t1 VALUES('one', 'abc') }
do_execsql_test 30.3 { SELECT * FROM log } {1 one abc}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 31.1 {
CREATE TABLE tbl(a PRIMARY KEY, b, c);
CREATE TRIGGER tr AFTER INSERT ON tbl BEGIN
UPDATE tbl SET b = '';
END;
INSERT INTO tbl VALUES(1, 2, 3);
}
do_execsql_test 31.2 { SELECT * FROM tbl } {1 {} 3}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 32.1 {
CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES(1, 2, 3);
}
do_execsql_test 32.2 { SELECT a, b, c FROM t1 } {1 2 3}
do_execsql_test 32.3 {
DROP TABLE t1;
CREATE TABLE t1(c, b, a);
INSERT INTO t1 VALUES(1, 2, 3);
}
do_execsql_test 32.4 { SELECT a, b, c FROM t1 } {3 2 1}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 33.1 { CREATE TABLE t1(a, b, c) }
do_execsql_test 33.2 { CREATE TABLE t2(a, b, c) }
do_execsql_test 33.3 { CREATE TABLE t3(a, b, c) }
do_execsql_test 33.4 { CREATE TABLE t4(a, b, c) }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 34.1 { CREATE TABLE t1(x PRIMARY KEY) }
do_execsql_test 34.2 { INSERT INTO t1 VALUES('123') }
do_test 34.3 { db changes } 1
do_execsql_test 34.4 { UPDATE t1 SET x = '456' }
do_test 34.5 { db changes } 1
do_execsql_test 34.6 { UPDATE t1 SET x = '456' WHERE x = '123' }
do_test 34.7 { db changes } 0
#-------------------------------------------------------------------------
reset_db
do_execsql_test 35.1 {
CREATE TABLE tbl (a primary key, b, c);
INSERT INTO tbl VALUES(1, 2, 3);
INSERT INTO tbl VALUES(2, 2, 3);
CREATE TRIGGER ai_tbl AFTER INSERT ON tbl WHEN new.c!=0 BEGIN
INSERT OR IGNORE INTO tbl values (new.a, 0, 0);
END;
}
do_execsql_test 35.2 { INSERT OR REPLACE INTO tbl values (2, 2, 3) }
do_execsql_test 35.3 { SELECT * from tbl } {1 2 3 2 0 0}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 36.1 {
CREATE TABLE tbl (a primary key, b, c);
CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN
UPDATE OR IGNORE tbl SET a = new.a, c = 10;
END;
BEGIN;
INSERT INTO tbl VALUES(1, 3, 10);
INSERT INTO tbl VALUES(2, 3, 4);
}
do_catchsql_test 36.2 {
UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
} {1 {PRIMARY KEY must be unique}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 37.1 {
CREATE TABLE t1(a PRIMARY KEY, b);
INSERT INTO t1 VALUES('x', 'xxx');
INSERT INTO t1 VALUES('y', 'yyy');
}
do_execsql_test 37.2 {
BEGIN;
DELETE FROM t1 WHERE a='y';
INSERT INTO t1 VALUES('y', 'yyy');
DELETE FROM t1 WHERE a='y';
INSERT INTO t1 VALUES('y', 'yyy');
ROLLBACK;
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 38.1 {
CREATE TABLE t1(a, b);
CREATE TABLE log(a, b);
-- INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
}
do_execsql_test 38.2 {
CREATE VIEW v1 AS SELECT a, b FROM t1;
CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN
INSERT INTO log VALUES(old.b, old.a);
END;
}
do_execsql_test 38.3 {
SELECT * FROM v1;
} {3 4}
do_execsql_test 38.4 {
DELETE FROM v1 WHERE a = 3;
}
do_execsql_test 38.5 {
SELECT * FROM log;
} {4 3}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 39.1 {
CREATE TABLE t1(a PRIMARY KEY, b);
}
do_catchsql_test 39.2 {
INSERT INTO t1 VALUES(NULL, 'xyz');
} {1 {t1.a may not be NULL}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 40.1 {
CREATE TABLE abc(a, b, c, PRIMARY KEY(a, b));
INSERT INTO abc VALUES(1, 1, 1);
SELECT * FROM abc;
} {1 1 1}
do_execsql_test 40.2 { SELECT max(a) FROM abc } {1}
do_execsql_test 40.3 {
SELECT a+(select max(a) FROM abc),
b+(select max(a) FROM abc),
c+(select max(a) FROM abc)
FROM abc
} {2 2 2}
do_execsql_test 40.4 {
INSERT INTO abc SELECT
a+(select max(a) FROM abc),
b+(select max(a) FROM abc),
c+(select max(a) FROM abc)
FROM abc;
}
do_execsql_test 40.5 { SELECT * FROM abc } {1 1 1 2 2 2}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 41.1 {
CREATE TABLE x(a, b);
INSERT INTO x VALUES(1, 'one');
INSERT INTO x VALUES(2, 'two');
INSERT INTO x VALUES(1, 'three');
}
do_execsql_test 41.2 {
SELECT * FROM x ORDER BY a;
} {1 one 1 three 2 two}
#-------------------------------------------------------------------------
reset_db
proc populate_t1 {} {
db eval {
INSERT INTO t1(a, b) VALUES(4, 'four');
INSERT INTO t1(a, b) VALUES(9, 'nine');
INSERT INTO t1(a, b) VALUES(5, 'five');
INSERT INTO t1(a, b) VALUES(1, 'one');
INSERT INTO t1(a, b) VALUES(7, 'seven');
INSERT INTO t1(a, b) VALUES(8, 'eight');
INSERT INTO t1(a, b) VALUES(2, 'two');
INSERT INTO t1(a, b) VALUES(3, 'three');
INSERT INTO t1(a, b) VALUES(6, 'six');
INSERT INTO t1(a, b) VALUES(10, 'ten');
}
}
foreach {t schema} {
1 "CREATE TABLE t1(a, b)"
2 "CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a);"
3 "CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(b);"
4 "CREATE TABLE t1(a PRIMARY KEY, b)"
} {
do_test 42.$t.0 {
reset_db
execsql $schema
populate_t1
} {}
foreach {u sql res} {
1 "SELECT * FROM t1 WHERE a = 7" {7 seven}
2 "SELECT * FROM t1 WHERE b = 'seven'" {7 seven}
} {
do_execsql_test 42.$t.$u $sql $res
}
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 43.1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES('a', 1);
INSERT INTO t1 VALUES('b', 4);
INSERT INTO t1 VALUES('a', 2);
INSERT INTO t1 VALUES('b', 5);
}
do_execsql_test 43.2 {
SELECT a, sum(b) FROM t1 GROUP BY a;
} {a 3 b 9}
#-------------------------------------------------------------------------
# Test sorting numeric values.
#
reset_db
do_execsql_test 44.1 { CREATE TABLE t1(x PRIMARY KEY, y) }
do_test 44.2 {
set lVal [list]
set val 5.0
for {set i 0} {$i < 10} {incr i ; set val [expr {$val*5.0}] } {
lappend lVal $val
}
set val 5
for {set i 0} {$i < 10} {incr i ; set val [expr {$val*5}] } {
lappend lVal [expr $val-1]
set val [expr {$val*5}]
}
set val 5.0
for {set i 0} {$i < 10} {incr i ; set val [expr {$val/5.0}] } {
lappend lVal $val
set val [expr {$val/5.0}]
}
set lVal2 [list]
foreach val $lVal { lappend lVal2 [expr $val * -1] }
set lVal [concat $lVal $lVal2]
foreach v $lVal {
execsql "INSERT INTO t1 VALUES(randomblob(16), $v)"
}
} {}
do_execsql_test 44.3 {
SELECT y FROM t1 ORDER BY y;
} [lsort -real $lVal]
do_execsql_test 44.4 {
SELECT count(*) FROM t1;
} [llength $lVal]
do_execsql_test 44.5 {
CREATE INDEX i1 ON t1(y);
SELECT y FROM t1 ORDER BY y;
} [lsort -real $lVal]
do_execsql_test 44.6 {
DROP INDEX i1;
CREATE INDEX i1 ON t1(y DESC);
}
do_execsql_test 44.7 {
SELECT y FROM t1 ORDER BY y;
} [lsort -real $lVal]
do_execsql_test 44.8 {
SELECT y FROM t1 ORDER BY y DESC;
} [lsort -decreasing -real $lVal]
#-------------------------------------------------------------------------
reset_db
do_execsql_test 45.1 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
INSERT INTO t1 VALUES( 2, 4, 6);
INSERT INTO t1 VALUES( 8, 10, 12);
INSERT INTO t1 VALUES(14, 16, 18);
INSERT INTO t2 VALUES(3, 6, 9);
INSERT INTO t2 VALUES(12, 15, 18);
INSERT INTO t2 VALUES(21, 24, 27);
}
do_execsql_test 45.2 {
SELECT d FROM t2 EXCEPT SELECT d FROM t2;
} {}
do_execsql_test 45.3 {
SELECT d FROM t2
EXCEPT
SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
} {}
do_execsql_test 45.4 {
SELECT c, d FROM t1 LEFT JOIN t2 ON (c=d)
} {6 {} 12 12 18 {}}
do_execsql_test 45.5 {
SELECT a AS x, b AS y FROM t1
UNION ALL
SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
UNION ALL
SELECT a*100, b*100 FROM t1
ORDER BY 1;
} {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 46.1 {
CREATE TABLE t1(x);
CREATE UNIQUE INDEX i1 ON t1(x);
}
do_execsql_test 46.2 { INSERT INTO t1 VALUES(NULL) }
do_execsql_test 46.3 { INSERT INTO t1 VALUES(NULL) }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 46.1 {
CREATE TABLE t1(x, y COLLATE nocase);
CREATE UNIQUE INDEX i1 ON t1(y);
}
do_catchsql_test 46.2 {
INSERT INTO t1 VALUES(1, 'ABC');
INSERT INTO t1 VALUES(2, 'abc');
} {1 {column y is not unique}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 47.1 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('D');
INSERT INTO t1 VALUES('a');
INSERT INTO t1 VALUES('c');
INSERT INTO t1 VALUES('B');
}
do_execsql_test 47.2 {
SELECT x FROM t1 ORDER BY x;
} {B D a c}
do_execsql_test 47.3 {
SELECT x FROM t1 ORDER BY x COLLATE nocase;
} {a B c D}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 48.1 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
BEGIN;
INSERT INTO t1 VALUES(1, 'one', 'I');
INSERT INTO t1 VALUES(3, NULL, NULL);
INSERT INTO t1 VALUES(5, 'five', 'V');
INSERT INTO t1 VALUES(7, 'seven', 'VII');
INSERT INTO t1 VALUES(9, NULL, NULL);
INSERT INTO t1 VALUES(2, 'two', 'II');
INSERT INTO t1 VALUES(4, 'four', 'IV');
INSERT INTO t1 VALUES(6, NULL, NULL);
INSERT INTO t1 VALUES(8, 'eight', 'VIII');
INSERT INTO t1 VALUES(10, 'ten', 'X');
INSERT INTO t2 VALUES(1, 'two', 'IV');
INSERT INTO t2 VALUES(2, 'four', 'VIII');
INSERT INTO t2 VALUES(3, NULL, NULL);
INSERT INTO t2 VALUES(4, 'eight', 'XVI');
INSERT INTO t2 VALUES(5, 'ten', 'XX');
INSERT INTO t2 VALUES(6, NULL, NULL);
INSERT INTO t2 VALUES(7, 'fourteen', 'XXVIII');
INSERT INTO t2 VALUES(8, 'sixteen', 'XXXII');
INSERT INTO t2 VALUES(9, NULL, NULL);
INSERT INTO t2 VALUES(10, 'twenty', 'XL');
COMMIT;
}
do_execsql_test 48.2 {
SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1
} {
1 one 1 two 2 two 2 four 3
{} 3 {} 4 four 4 eight 5
five 5 ten 6 {} 6 {} 7
seven 7 fourteen 8 eight 8 sixteen 9
{} 9 {} 10 ten 10 twenty
}
proc reverse_cmp {lhs rhs} {
return [string compare $rhs $lhs]
}
proc reverse_mkkey {zIn} {
set res ""
binary scan $zIn c* lChar
foreach c $lChar {
append res [format "%02X" [expr 255-$c]]
}
set res
}
db collate reverse reverse_cmp reverse_mkkey
do_execsql_test 48.3 {
SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5
ORDER BY 2 COLLATE reverse, 1
} {
3 {} {} 6 {} {} 9 {} {}
2 two II 10 twenty XL 5 ten XX
8 sixteen XXXII 1 one I 7 fourteen XXVIII
4 four IV
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 49.1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 5);
INSERT INTO t1 VALUES(2, 4);
INSERT INTO t1 VALUES(3, 3);
INSERT INTO t1 VALUES(4, 2);
INSERT INTO t1 VALUES(5, 1);
}
do_execsql_test 49.2 {
SELECT a, b FROM t1 ORDER BY a LIMIT 2 OFFSET 2
} {3 3 4 2}
do_execsql_test 49.3 {
SELECT * FROM (
SELECT a, b FROM t1 ORDER BY a LIMIT 2 OFFSET 2
) ORDER BY 2;
} {4 2 3 3}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 50.1 {
CREATE TABLE t1(x, y);
CREATE INDEX i1 ON t1(x);
} {}
do_execsql_test 50.2 {
INSERT INTO t1 VALUES(0.0003, 'three');
INSERT INTO t1 VALUES(0.0001, 'one');
INSERT INTO t1 VALUES(0.0002, 'two');
}
do_execsql_test 50.3 {
SELECT * FROM t1 ORDER BY x;
} {0.0001 one 0.0002 two 0.0003 three}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 51.1 {
CREATE TABLE t1(x, y);
CREATE INDEX i1 ON t1(x);
} {}
do_execsql_test 51.2 {
INSERT INTO t1 VALUES(-1, 'one');
INSERT INTO t1 VALUES(-2, 'two');
}
do_execsql_test 51.3 {
SELECT * FROM t1 ORDER BY x;
} {-2 two -1 one}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 52.1 {
CREATE TABLE t1(x PRIMARY KEY, y, z);
} {}
do_execsql_test 52.2 {
CREATE INDEX i1 ON t1(y DESC, z ASC);
}
do_execsql_test 52.3 {
INSERT INTO t1 VALUES('a', 'one', 'before');
INSERT INTO t1 VALUES('c', 'two', 'before');
INSERT INTO t1 VALUES('b', 'one', 'after');
INSERT INTO t1 VALUES('d', 'two', 'after');
}
do_execsql_test 52.4 {
SELECT x FROM t1 ORDER BY y DESC, z ASC;
} {d c b a}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 53.1 {
CREATE TABLE t1(x PRIMARY KEY, y);
} {}
do_execsql_test 53.2 {
CREATE INDEX i1 ON t1(y DESC);
}
do_execsql_test 53.3 {
INSERT INTO t1 VALUES('a', -0.0000001);
INSERT INTO t1 VALUES('b', +0.0000001);
INSERT INTO t1 VALUES('c', 10);
INSERT INTO t1 VALUES('d', -10);
}
do_execsql_test 53.4 {
SELECT x FROM t1 ORDER BY y DESC;
} {c b a d}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 54.1 {
CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
CREATE TABLE t2(x, y);
} {}
foreach {tn sql res} {
1 { SELECT rowid FROM t1 } {1 {no such column: rowid}}
2 { SELECT rowid FROM t2 } {0 {}}
3 { SELECT rOWId FROM t1 } {1 {no such column: rOWId}}
4 { SELECT rOWId FROM t2 } {0 {}}
5 { SELECT oid FROM t1 } {1 {no such column: oid}}
6 { SELECT oid FROM t2 } {1 {no such column: oid}}
7 { SELECT _rowid_ FROM t1 } {1 {no such column: _rowid_}}
8 { SELECT _rowid_ FROM t2 } {1 {no such column: _rowid_}}
} {
do_catchsql_test 54.2.$tn $sql $res
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 55.1 {
CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(3, 'three');
} {}
do_execsql_test 55.2 {
BEGIN;
INSERT INTO t1 VALUES(2, 'too');
UPDATE t1 SET y = 'two' WHERE x=2;
ROLLBACK;
}
do_execsql_test 55.3 { SELECT * FROM t1 } {1 one 3 three}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 56.1 {
CREATE TABLE t1(x, y);
CREATE UNIQUE INDEX i1 ON t1(x);
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(2, 'two');
} {}
do_execsql_test 56.2 {
INSERT OR REPLACE INTO t1 VALUES(2, 'three');
}
do_execsql_test 55.3 { SELECT * FROM t1 } {1 one 2 three}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 57.1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e);
INSERT INTO t1 VALUES(1, 2, 3, 4, 5);
INSERT INTO t1 VALUES(6, 7, 8, 9, 10);
} {}
do_execsql_test 57.2 {
UPDATE t1 SET a = 11 WHERE a=1;
SELECT * FROM t1;
} {6 7 8 9 10 11 2 3 4 5 }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 58.1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
INSERT INTO t2 SELECT * FROM t1;
} {}
do_test 57.2 {
execsql { DELETE FROM t1 WHERE a!=5 }
db changes
} {2}
do_test 57.3 {
execsql { DELETE FROM t2 }
db changes
} {2}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 59.1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
INSERT INTO t1 VALUES(5, 6);
INSERT INTO t1 VALUES(7, 8);
} {}
do_test 59.2 {
set res [list]
db eval { SELECT a, b FROM t1 ORDER BY a } {
if {$a==3} { db eval "DELETE FROM t1" }
lappend res $a $b
}
set res
} {1 2 3 4}
do_execsql_test 59.3 {
SELECT * FROM t1;
} {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 60.1 {
CREATE TABLE t1(a PRIMARY KEY, b , c);
CREATE UNIQUE INDEX i1 ON t1(b);
INSERT INTO t1 VALUES(1, 'abc', 'xxx');
INSERT INTO t1 VALUES(2, 'def', 'jkl');
INSERT INTO t1 VALUES(3, 'ghi', 'abc');
INSERT INTO t1 VALUES(4, 'jkl', 'yyy');
}
do_execsql_test 60.2 {
SELECT a FROM t1 WHERE c IN (SELECT b FROM t1);
} {2 3}
do_execsql_test 60.3 {
SELECT a FROM t1 WHERE b IN (SELECT c FROM t1);
} {1 4}
do_execsql_test 60.4 {
SELECT a FROM t1 WHERE c IN (SELECT c FROM t1);
} {1 2 3 4}
do_execsql_test 60.5 {
SELECT a FROM t1 WHERE b IN (SELECT b FROM t1);
} {1 2 3 4}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 61.1 {
CREATE TABLE t2(y,a);
INSERT INTO t2 VALUES(22,2);
}
do_execsql_test 61.2 { SELECT count(*) FROM t2 GROUP BY a } 1
#-------------------------------------------------------------------------
# TODO: See ticket [f3d711b1e5]
#reset_db
#do_execsql_test 62.1 {
# CREATE TABLE t2(x int, y int, z int);
# CREATE INDEX i2x ON t2(x);
#}
#
#set r1 [expr {rand()}]
#set r2 [expr {rand()}]
#set r3 [expr {rand()}]
#
#set sql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
#execsql $sql
#integrity_check 62.2
#-------------------------------------------------------------------------
#reset_db
#set r1 [expr 0.684377705997032]
#do_execsql_test 63.1 { CREATE TABLE t1(x UNIQUE); }
#execsql { PRAGMA trace = 1 }
#breakpoint
#do_execsql_test 63.2 "INSERT INTO t1 VALUES($r1)"
##do_execsql_test 63.3 "SELECT * FROM t1" $r1
#integrity_check 63.4
#execsql { PRAGMA kvdump }
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
# reset_db
# do_execsql_test 64.1 {
# CREATE TABLE t1(x PRIMARY KEY);
# }
# do_execsql_test 64.2 {
# PRAGMA trace = 1;
# INSERT INTO t1 VALUES(18446744073709551615);
# INSERT INTO t1 VALUES(18446744073709551616);
# }
#-------------------------------------------------------------------------
reset_db
do_execsql_test 65.1 {
CREATE TABLE t1(x);
CREATE TABLE t2(y);
INSERT INTO t1 VALUES('a');
INSERT INTO t1 VALUES('b');
INSERT INTO t1 VALUES('c');
INSERT INTO t2 VALUES('d');
INSERT INTO t2 VALUES('e');
}
do_execsql_test 65.2 { SELECT DISTINCT t1.rowid FROM t1 } {1 2 3}
do_execsql_test 65.3 { SELECT DISTINCT t1.rowid FROM t1, t2 } {1 2 3}
#-------------------------------------------------------------------------
# The three CREATE TABLE statements below should be equivalent.
#
reset_db
do_execsql_test 66.1 {
CREATE TABLE t1(x, y UNIQUE PRIMARY KEY);
CREATE TABLE t2(x, y PRIMARY KEY UNIQUE);
CREATE TABLE t3(x, y PRIMARY KEY);
}
do_execsql_test 66.2 {
PRAGMA table_info=t1;
} {
0 x {} 0 {} 0
1 y {} 1 {} 1
}
do_execsql_test 66.3 {
PRAGMA table_info=t2;
} {
0 x {} 0 {} 0
1 y {} 1 {} 1
}
do_execsql_test 66.4 {
PRAGMA table_info=t3;
} {
0 x {} 0 {} 0
1 y {} 1 {} 1
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 67.1 {
CREATE TABLE t1(x PRIMARY KEY);
CREATE TABLE t2(y);
}
do_execsql_test 67.2 {
CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
INSERT INTO t2 VALUES(old.rowid);
END;
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 68.1 {
CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p);
}
do_execsql_test 68.2 {
INSERT INTO p VALUES(1, 2, 3);
INSERT INTO c VALUES('w', 2, 3);
INSERT INTO c VALUES('x', 'x', NULL);
INSERT INTO c VALUES('y', NULL, 'x');
INSERT INTO c VALUES('z', NULL, NULL);
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 69.1 {
CREATE TABLE t4(x, y);
INSERT INTO t4 VALUES(1, 'one');
} {}
do_execsql_test 69.2 { SELECT x FROM t4 ORDER BY x LIMIT 1 } {1}
do_execsql_test 69.3 { SELECT (SELECT x FROM t4 ORDER BY x) } {1}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 70.1 {
CREATE TABLE maintable( id integer);
CREATE TABLE joinme(id_int integer, id_text text);
CREATE INDEX joinme_id_text_idx on joinme(id_text);
CREATE INDEX joinme_id_int_idx on joinme(id_int);
}
do_catchsql_test 70.2 {
select * from maintable as m inner join
joinme as j indexed by joinme_id_text_idx
on ( m.id = j.id_int)
} {1 {no query solution}}
do_catchsql_test 70.3 {
select * from maintable, joinme INDEXED by joinme_id_text_idx
} {1 {no query solution}}
#-------------------------------------------------------------------------
# This is testing that the "phantom" runs feature works.
#
# UPDATE: Said feature was dropped early in development. But the test
# remains valid.
reset_db
do_execsql_test 71.1 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(randomblob(1024)); -- 1
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 2
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 4
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 8
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 16
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 32
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 64
}
do_execsql_test 71.2 { SELECT count(*) FROM t1 } 64
db close
sqlite4 db test.db
do_execsql_test 71.3 { SELECT count(*) FROM t1 } 64
do_test 71.4 {
expr {[file size test.db] <= 256*1024}
} {1}
#-------------------------------------------------------------------------
# This is testing that the "phantom" runs feature works with mmap.
#
# UPDATE: Said feature was dropped early in development. But the test
# remains valid.
reset_db
#do_test 72.0.1 { sqlite4_lsm_config db main mmap } 0
#do_test 72.0.2 { sqlite4_lsm_config db main mmap 1 } 1
#do_test 72.0.3 { sqlite4_lsm_config db main mmap } 1
do_execsql_test 72.1 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(randomblob(1024)); -- 1
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 2
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 4
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 8
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 16
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 32
INSERT INTO t1 SELECT randomblob(1024) FROM t1; -- 64
}
do_execsql_test 72.2 { SELECT count(*) FROM t1 } 64
db close
sqlite4 db test.db
do_execsql_test 72.3 { SELECT count(*) FROM t1 } 64
do_test 72.4 {
expr {[file size test.db] <= 2*1024*1024}
} {1}
#-------------------------------------------------------------------------
#
forcedelete test2.db
reset_db
sqlite4 db2 [file join . test.db]
sqlite4 db3 [file join [pwd] test.db]
sqlite4 db4 [file join .. [file tail [pwd]] test.db]
do_execsql_test 73.1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
}
do_test 73.2 { db2 eval { SELECT * FROM t1 } } {1 2 3 4}
do_test 73.3 { db3 eval { SELECT * FROM t1 } } {1 2 3 4}
do_test 73.4 { db4 eval { SELECT * FROM t1 } } {1 2 3 4}
db2 close
db3 close
db4 close
sqlite4 db2 test2.db
do_test 73.5 { catch { db2 eval { SELECT * FROM t1 } } } {1}
db2 close
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 74.1 { CREATE TABLE t1(a PRIMARY KEY, b) }
do_test 74.2 {
for {set i 0} {$i<30} {incr i} {
db close
sqlite4 db test.db
execsql "INSERT INTO t1 VALUES('k$i', randstr(500,500))"
}
} {}
db close
#-------------------------------------------------------------------------
# Index on a blob.
#
reset_db
do_execsql_test 75.1 {
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
INSERT INTO t1 VALUES(x'12345678', x'12345678');
}
do_execsql_test 75.2 {
SELECT count(*) FROM t1 WHERE a = x'12345678'
} 1
do_execsql_test 75.3 {
SELECT count(*) FROM t1 WHERE b = x'12345678'
} 1
#-------------------------------------------------------------------------
# Real vs. integer values.
#
reset_db
do_execsql_test 76.1 {
CREATE TABLE t1(a REAL);
CREATE TABLE log(x);
CREATE TRIGGER BEFORE INSERT ON t1 BEGIN
INSERT INTO log VALUES('value = ' || new.a);
END;
}
do_execsql_test 76.2 { INSERT INTO t1 VALUES(-23) }
do_execsql_test 76.3 {
SELECT * FROM log;
} {{value = -23.0}}
do_execsql_test 76.4 {
CREATE TABLE t2(a REAL, str);
}
do_execsql_test 76.5 {
INSERT INTO t2 VALUES(0.0012345, '');
}
do_execsql_test 76.6 { SELECT cast(a AS TEXT) FROM t2 } {0.0012345}
#-------------------------------------------------------------------------
# Integer keys.
#
reset_db
do_execsql_test 77.1 { CREATE TABLE t1(x) }
do_test 77.2 {
for {set i 0} {$i < 99} {incr i} {
execsql { INSERT INTO t1 VALUES(NULL) }
}
} {}
do_execsql_test 77.3 { INSERT INTO t1 VALUES(NULL) }
do_execsql_test 77.4 { INSERT INTO t1 VALUES(NULL) }
#-------------------------------------------------------------------------
#
reset_db
do_test 78.1 {
execsql {
CREATE TABLE t1 (id INTEGER PRIMARY KEY, v);
INSERT INTO t1 VALUES(42, 3);
}
} {}
do_execsql_test 78.2 {
SELECT id, v FROM t1 WHERE id>1.5;
} {42 3}
#-------------------------------------------------------------------------
# Ticket f3d7aab1e5
#
reset_db
do_test 79.1 {
set r1 [expr 0.684377705997032]
execsql "CREATE TABLE t1(x UNIQUE)"
execsql "INSERT INTO t1 VALUES($r1)"
execsql "PRAGMA integrity_check"
} {ok}
reset_db
do_execsql_test 80.1 {
CREATE TABLE t1(tbl, idx, nEq, nLt, nDLt, sample);
INSERT INTO t1 VALUES('t1', 't1i3', 1, 0, 0, x'1802');
INSERT INTO t1 VALUES('t1', 't1i3', 1, 1, 1, x'1802');
INSERT INTO t1 VALUES('t1', 't1i2', 1, 0, 0, x'1804');
INSERT INTO t1 VALUES('t1', 't1i2', 1, 1, 1, x'1806');
INSERT INTO t1 VALUES('t1', 't1i1', 1, 0, 0, x'1802');
INSERT INTO t1 VALUES('t1', 't1i1', 1, 1, 1, x'1802');
INSERT INTO t1 VALUES('t1', 't1', 1, 0, 0, x'1802');
INSERT INTO t1 VALUES('t1', 't1', 1, 1, 1, x'1804');
}
do_execsql_test 80.2 {
SELECT idx, count(*), sum(length(sample)) FROM t1 GROUP BY idx
} {t1 2 4 t1i1 2 4 t1i2 2 4 t1i3 2 4}
#-------------------------------------------------------------------------
reset_db
do_test 81.1 {
execsql {
CREATE TABLE t1(a TEXT);
INSERT INTO t1 VALUES(5.4e-08);
SELECT a FROM t1;
}
} {5.4e-8}
#-------------------------------------------------------------------------
reset_db
do_test 82.1 {
execsql { CREATE TABLE t1(a TEXT) }
execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
do_test 82.2 {
execsql { DROP TABLE t1 }
execsql { CREATE TABLE t1(a TEXT) }
execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
#-------------------------------------------------------------------------
reset_db
do_test 83.1 {
execsql { SELECT CAST('2.12e-01ABC' AS INT) }
} {2}
do_test 83.2 {
execsql { SELECT CAST(' -2.12e-01ABC' AS INT) }
} {-2}
do_test 83.3 {
execsql { SELECT CAST('45.0' AS NUMERIC) }
} {45}
do_test 83.4 {
execsql { SELECT CAST(0.0 AS TEXT) }
} {0.0}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 84.1 {
SELECT 1e600 * 1e600 * 0.0;
} {{}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 85.1 {
CREATE TABLE t1(x INT, y COLLATE NOCASE);
INSERT INTO t1(x,y) VALUES(2,CAST(x'02' AS TEXT));
CREATE TABLE t3(x INT, y COLLATE NOCASE);
INSERT INTO t3 SELECT x, 'abc' || y || 'xyz' FROM t1;
CREATE INDEX i3 ON t3(y);
SELECT x FROM t3 WHERE y LIKE 'abcX%';
} {}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 86.0 {
SELECT * FROM sqlite_master;
} {}
do_execsql_test 86.1 {
CREATE TABLE t1(a PRIMARY KEY, b);
}
do_execsql_test 86.2 {
INSERT INTO t1 VALUES(1, 'one');
}
do_execsql_test 86.3 {
SELECT * FROM t1;
} {1 one}
do_execsql_test 86.4 {
SELECT * FROM t1 WHERE a = 1;
} {1 one}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 87.1 {
CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
CREATE INDEX t6i1 ON t6(b);
} {}
do_eqp_test 87.2 {
SELECT * FROM t6 ORDER BY b, a;
} {0 0 0 {SCAN TABLE t6 USING INDEX t6i1}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 88.1 {
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT);
CREATE UNIQUE INDEX t8i ON t8(b);
}
do_eqp_test 88.2 {
SELECT * FROM t8 x ORDER BY x.b, x.a, x.b||x.a
} {0 0 0 {SCAN TABLE t8 AS x USING INDEX t8i}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 89.1 {
CREATE TABLE t1(a COLLATE NOCASE);
CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
#-------------------------------------------------------------------------
proc nEphemeral {sql} {
set nEph 0
foreach op [execsql "EXPLAIN $sql"] {
if {$op eq "OpenEphemeral"} {incr nEph}
}
set nEph
}
foreach {tn schema} {
1 {
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE t2(x PRIMARY KEY, y);
}
2 {
CREATE TABLE t1(b, a PRIMARY KEY);
CREATE TABLE t2(x PRIMARY KEY, y);
}
3 {
CREATE TABLE t1(b, a);
CREATE UNIQUE INDEX i1 ON t1(a) COVERING (b, a);
CREATE TABLE t2(x PRIMARY KEY, y);
}
4 {
CREATE TABLE t1(b, a);
CREATE UNIQUE INDEX i1 ON t1(a) COVERING (a, b);
CREATE TABLE t2(y, x PRIMARY KEY);
}
} {
reset_db
do_execsql_test 90.$tn.0 $schema
do_execsql_test 90.$tn.1 {
INSERT INTO t2(x, y) VALUES(1, 'one');
INSERT INTO t2(x, y) VALUES(2, 'two');
INSERT INTO t2(x, y) VALUES(3, 'three');
INSERT INTO t2(x, y) VALUES(4, 'four');
INSERT INTO t2(x, y) VALUES(5, 'five');
INSERT INTO t2(x, y) VALUES(6, 'six');
INSERT INTO t1(a, b) VALUES(2, 'two');
INSERT INTO t1(a, b) VALUES(3, 'three');
INSERT INTO t1(a, b) VALUES(5, 'five');
}
do_execsql_test 90.$tn.2 {
SELECT y FROM t2 WHERE x IN (SELECT a FROM t1);
} {two three five}
do_test 90.$tn.3 {
nEphemeral "SELECT y FROM t2 WHERE x IN (SELECT a FROM t1)"
} 0
}
finish_test