From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Thu, 16 Oct 2014 22:51:35 -0400 Subject: Imported Upstream version 3.2.0 --- test/without_rowid1.test | 281 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 281 insertions(+) create mode 100644 test/without_rowid1.test (limited to 'test/without_rowid1.test') diff --git a/test/without_rowid1.test b/test/without_rowid1.test new file mode 100644 index 0000000..9d7a643 --- /dev/null +++ b/test/without_rowid1.test @@ -0,0 +1,281 @@ +# 2013-10-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. +# +#*********************************************************************** +# +# This file implements regression tests for SQLite library. The +# focus of this file is testing WITHOUT ROWID tables. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix without_rowid1 + +# Create and query a WITHOUT ROWID table. +# +do_execsql_test without_rowid1-1.0 { + CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID; + CREATE INDEX t1bd ON t1(b, d); + INSERT INTO t1 VALUES('journal','sherman','ammonia','helena'); + INSERT INTO t1 VALUES('dynamic','juliet','flipper','command'); + INSERT INTO t1 VALUES('journal','sherman','gamma','patriot'); + INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena'); + SELECT *, '|' FROM t1 ORDER BY c, a; +} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} + +integrity_check without_rowid1-1.0ic + +do_execsql_test without_rowid1-1.1 { + SELECT *, '|' FROM t1 ORDER BY +c, a; +} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} + +do_execsql_test without_rowid1-1.2 { + SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC; +} {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |} + +do_execsql_test without_rowid1-1.11 { + SELECT *, '|' FROM t1 ORDER BY b, d; +} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} + +do_execsql_test without_rowid1-1.12 { + SELECT *, '|' FROM t1 ORDER BY +b, d; +} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} + +# Trying to insert a duplicate PRIMARY KEY fails. +# +do_test without_rowid1-1.21 { + catchsql { + INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard'); + } +} {1 {UNIQUE constraint failed: t1.c, t1.a}} + +# REPLACE INTO works, however. +# +do_execsql_test without_rowid1-1.22 { + REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard'); + SELECT *, '|' FROM t1 ORDER BY c, a; +} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |} + +do_execsql_test without_rowid1-1.23 { + SELECT *, '|' FROM t1 ORDER BY b, d; +} {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} + +# UPDATE statements. +# +do_execsql_test without_rowid1-1.31 { + UPDATE t1 SET d=3.1415926 WHERE a='journal'; + SELECT *, '|' FROM t1 ORDER BY c, a; +} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |} +do_execsql_test without_rowid1-1.32 { + SELECT *, '|' FROM t1 ORDER BY b, d; +} {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} + +do_execsql_test without_rowid1-1.35 { + UPDATE t1 SET a=1250 WHERE b='phone'; + SELECT *, '|' FROM t1 ORDER BY c, a; +} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |} +integrity_check without_rowid1-1.36 + +do_execsql_test without_rowid1-1.37 { + SELECT *, '|' FROM t1 ORDER BY b, d; +} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} + +do_execsql_test without_rowid1-1.40 { + VACUUM; + SELECT *, '|' FROM t1 ORDER BY b, d; +} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} +integrity_check without_rowid1-1.41 + +# Verify that ANALYZE works +# +do_execsql_test without_rowid1-1.50 { + ANALYZE; + SELECT * FROM sqlite_stat1 ORDER BY idx; +} {t1 t1 {4 2 1} t1 t1bd {4 2 2}} +ifcapable stat3 { + do_execsql_test without_rowid1-1.51 { + SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx; + } {t1 t1 t1 t1bd} +} +ifcapable stat4 { + do_execsql_test without_rowid1-1.52 { + SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx; + } {t1 t1 t1 t1bd} +} + +#---------- + +do_execsql_test 2.1.1 { + CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID; + INSERT INTO t4 VALUES('abc', 'def'); + SELECT * FROM t4; +} {abc def} +do_execsql_test 2.1.2 { + UPDATE t4 SET a = 'ABC'; + SELECT * FROM t4; +} {ABC def} + +do_execsql_test 2.2.1 { + DROP TABLE t4; + CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID; + INSERT INTO t4(a, b) VALUES('abc', 'def'); + SELECT * FROM t4; +} {def abc} + +do_execsql_test 2.2.2 { + UPDATE t4 SET a = 'ABC', b = 'xyz'; + SELECT * FROM t4; +} {xyz ABC} + +do_execsql_test 2.3.1 { + CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID; + INSERT INTO t5(a, b) VALUES('abc', 'def'); + UPDATE t5 SET a='abc', b='def'; +} {} + +do_execsql_test 2.4.1 { + CREATE TABLE t6 ( + a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a) + ) WITHOUT ROWID; + + INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi'); + UPDATE t6 SET a='ABC', c='ghi'; +} {} + +do_execsql_test 2.4.2 { + SELECT * FROM t6 ORDER BY b, a; + SELECT * FROM t6 ORDER BY c; +} {ABC def ghi ABC def ghi} + +#------------------------------------------------------------------------- +# Unless the destination table is completely empty, the xfer optimization +# is disabled for WITHOUT ROWID tables. The following tests check for +# some problems that might occur if this were not the case. +# +reset_db +do_execsql_test 3.1.1 { + CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; + CREATE UNIQUE INDEX i1 ON t1(b); + + CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID; + CREATE UNIQUE INDEX i2 ON t2(b); + + INSERT INTO t1 VALUES('one', 'two'); + INSERT INTO t2 VALUES('three', 'two'); +} + +do_execsql_test 3.1.2 { + INSERT OR REPLACE INTO t1 SELECT * FROM t2; + SELECT * FROM t1; +} {three two} + +do_execsql_test 3.1.3 { + DELETE FROM t1; + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; +} {three two} + +do_catchsql_test 3.1.4 { + INSERT INTO t2 VALUES('four', 'four'); + INSERT INTO t2 VALUES('six', 'two'); + INSERT INTO t1 SELECT * FROM t2; +} {1 {UNIQUE constraint failed: t2.b}} + +do_execsql_test 3.1.5 { + CREATE TABLE t3(a PRIMARY KEY); + CREATE TABLE t4(a PRIMARY KEY); + + INSERT INTO t4 VALUES('i'); + INSERT INTO t4 VALUES('ii'); + INSERT INTO t4 VALUES('iii'); + + INSERT INTO t3 SELECT * FROM t4; + SELECT * FROM t3; +} {i ii iii} + +############################################################################ +# Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc] +# Name resolution issue with WITHOUT ROWID +# +do_execsql_test 4.1 { + CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID; + INSERT INTO t41 VALUES('abc'); + CREATE TABLE t42(x); + INSERT INTO t42 VALUES('xyz'); + SELECT t42.rowid FROM t41, t42; +} {1} +do_execsql_test 4.2 { + SELECT t42.rowid FROM t42, t41; +} {1} + + +#-------------------------------------------------------------------------- +# The following tests verify that the trailing PK fields added to each +# entry in an index on a WITHOUT ROWID table are used correctly. +# +do_execsql_test 5.0 { + CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE INDEX i45 ON t45(b); + + INSERT INTO t45 VALUES(2, 'one', 'x'); + INSERT INTO t45 VALUES(4, 'one', 'x'); + INSERT INTO t45 VALUES(6, 'one', 'x'); + INSERT INTO t45 VALUES(8, 'one', 'x'); + INSERT INTO t45 VALUES(10, 'one', 'x'); + + INSERT INTO t45 VALUES(1, 'two', 'x'); + INSERT INTO t45 VALUES(3, 'two', 'x'); + INSERT INTO t45 VALUES(5, 'two', 'x'); + INSERT INTO t45 VALUES(7, 'two', 'x'); + INSERT INTO t45 VALUES(9, 'two', 'x'); +} + +do_eqp_test 5.1 { + SELECT * FROM t45 WHERE b=? AND a>? +} {/*USING INDEX i45 (b=? AND a>?)*/} + +do_execsql_test 5.2 { + SELECT * FROM t45 WHERE b='two' AND a>4 +} {5 two x 7 two x 9 two x} + +do_execsql_test 5.3 { + SELECT * FROM t45 WHERE b='one' AND a<8 +} { 2 one x 4 one x 6 one x } + +do_execsql_test 5.4 { + CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; + WITH r(x) AS ( + SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100 + ) + INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r; +} + +set queries { + 1 2 "c = 5 AND a = 1" {/*i46 (c=? AND a=?)*/} + 2 6 "c = 4 AND a < 3" {/*i46 (c=? AND a= 3" {/*i46 (c=? AND a>?)*/} + 4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b5" {/*i46 (c=? AND a=? AND b>?)*/} +} + +foreach {tn cnt where eqp} $queries { + do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt +} + +do_execsql_test 5.6 { + CREATE INDEX i46 ON t46(c); +} + +foreach {tn cnt where eqp} $queries { + do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt + do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp +} + + +finish_test -- cgit v1.2.3