aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/index6.test21
-rw-r--r--test/pragma2.test75
-rw-r--r--test/resolver01.test173
-rw-r--r--test/tkt2822.test5
4 files changed, 262 insertions, 12 deletions
diff --git a/test/index6.test b/test/index6.test
index e9ea570b8..d70e86aef 100644
--- a/test/index6.test
+++ b/test/index6.test
@@ -144,12 +144,21 @@ do_test index6-2.2 {
SELECT * FROM t2 WHERE a=5;
}
} {/.* TABLE t2 USING INDEX t2a1 .*/}
-do_test index6-2.3 {
- execsql {
- EXPLAIN QUERY PLAN
- SELECT * FROM t2 WHERE a IS NOT NULL;
- }
-} {/.* TABLE t2 USING INDEX t2a1 .*/}
+ifcapable stat3 {
+ do_test index6-2.3stat3 {
+ execsql {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM t2 WHERE a IS NOT NULL;
+ }
+ } {/.* TABLE t2 USING INDEX t2a1 .*/}
+} else {
+ do_test index6-2.3stat3 {
+ execsql {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;
+ }
+ } {/.* TABLE t2 USING INDEX t2a1 .*/}
+}
do_test index6-2.4 {
execsql {
EXPLAIN QUERY PLAN
diff --git a/test/pragma2.test b/test/pragma2.test
index 1111a984b..4a9fe8d94 100644
--- a/test/pragma2.test
+++ b/test/pragma2.test
@@ -22,6 +22,7 @@ source $testdir/tester.tcl
# pragma2-1.*: Test freelist_count pragma on the main database.
# pragma2-2.*: Test freelist_count pragma on an attached database.
# pragma2-3.*: Test trying to write to the freelist_count is a no-op.
+# pragma2-4.*: Tests for PRAGMA cache_spill
#
ifcapable !pragma||!schema_pragmas {
@@ -116,4 +117,78 @@ ifcapable attach {
} {9 9}
}
+# Default setting of PRAGMA cache_spill is always ON
+#
+db close
+delete_file test.db test.db-journal
+delete_file test2.db test2.db-journal
+sqlite3 db test.db
+do_execsql_test pragma2-4.1 {
+ PRAGMA cache_spill;
+ PRAGMA main.cache_spill;
+ PRAGMA temp.cache_spill;
+} {1 1 1}
+do_execsql_test pragma2-4.2 {
+ PRAGMA cache_spill=OFF;
+ PRAGMA cache_spill;
+ PRAGMA main.cache_spill;
+ PRAGMA temp.cache_spill;
+} {0 0 0}
+do_execsql_test pragma2-4.3 {
+ PRAGMA page_size=1024;
+ PRAGMA cache_size=50;
+ BEGIN;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
+ INSERT INTO t1 VALUES(1, randomblob(400), 1, randomblob(400));
+ INSERT INTO t1 SELECT a+1, randomblob(400), a+1, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
+ INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
+ COMMIT;
+ ATTACH 'test2.db' AS aux1;
+ CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
+ INSERT INTO t2 SELECT * FROM t1;
+ DETACH aux1;
+ PRAGMA cache_spill=ON;
+} {}
+do_test pragma2-4.4 {
+ db eval {
+ BEGIN;
+ UPDATE t1 SET c=c+1;
+ PRAGMA lock_status;
+ }
+} {main exclusive temp unknown} ;# EXCLUSIVE lock due to cache spill
+do_test pragma2-4.5 {
+ db eval {
+ COMMIT;
+ PRAGMA cache_spill=OFF;
+ BEGIN;
+ UPDATE t1 SET c=c-1;
+ PRAGMA lock_status;
+ }
+} {main reserved temp unknown} ;# No cache spill, so no exclusive lock
+
+# Verify that newly attached databases inherit the cache_spill=OFF
+# setting.
+#
+do_execsql_test pragma2-4.6 {
+ COMMIT;
+ ATTACH 'test2.db' AS aux1;
+ PRAGMA aux1.cache_size=50;
+ BEGIN;
+ UPDATE t2 SET c=c+1;
+ PRAGMA lock_status;
+} {main unlocked temp unknown aux1 reserved}
+do_execsql_test pragma2-4.7 {
+ COMMIT;
+ PRAGMA cache_spill=ON; -- Applies to all databases
+ BEGIN;
+ UPDATE t2 SET c=c-1;
+ PRAGMA lock_status;
+} {main unlocked temp unknown aux1 exclusive}
+
+
finish_test
diff --git a/test/resolver01.test b/test/resolver01.test
index 3ca6acec4..7d95a2132 100644
--- a/test/resolver01.test
+++ b/test/resolver01.test
@@ -13,10 +13,18 @@
# figures out what identifiers in the SQL statement refer to) that
# were fixed by ticket [2500cdb9be]
#
+# See also tickets [1c69be2daf] and [f617ea3125] from 2013-08-14.
+#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
+# "ORDER BY y" binds to the output result-set column named "y"
+# if available. If no output column is named "y", then try to
+# bind against an input column named "y".
+#
+# This is classical SQL92 behavior.
+#
do_test resolver01-1.1 {
catchsql {
CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22);
@@ -26,14 +34,175 @@ do_test resolver01-1.1 {
} {0 1}
do_test resolver01-1.2 {
catchsql {
+ SELECT 1 AS yy FROM t1, t2 ORDER BY y;
+ }
+} {1 {ambiguous column name: y}}
+do_test resolver01-1.3 {
+ catchsql {
+ CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22);
+ SELECT x AS y FROM t3 ORDER BY y;
+ }
+} {0 {11 33}}
+do_test resolver01-1.4 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY y;
+ }
+} {0 {33 11}}
+
+# SQLite allows the WHERE clause to reference output columns if there is
+# no other way to resolve the name.
+#
+do_test resolver01-1.5 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY yy;
+ }
+} {0 {11 33}}
+do_test resolver01-1.6 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY 1;
+ }
+} {0 {11 33}}
+
+# The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y".
+# The "y" binds more tightly to output columns than to input columns.
+#
+# This is for compatibility with SQL92 and with historical SQLite behavior.
+# Note that PostgreSQL considers "y COLLATE nocase" to be an expression
+# and thus PostgreSQL treats this case as if it where the 3.x case below.
+#
+do_test resolver01-2.1 {
+ catchsql {
SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase;
}
} {0 2}
-do_test resolver01-1.3 {
+do_test resolver01-2.2 {
+ catchsql {
+ SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase;
+ }
+} {1 {ambiguous column name: y}}
+do_test resolver01-2.3 {
+ catchsql {
+ SELECT x AS y FROM t3 ORDER BY y COLLATE nocase;
+ }
+} {0 {11 33}}
+do_test resolver01-2.4 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase;
+ }
+} {0 {33 11}}
+do_test resolver01-2.5 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase;
+ }
+} {0 {11 33}}
+do_test resolver01-2.6 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase;
+ }
+} {0 {11 33}}
+
+# But if the form is "ORDER BY expr" then bind more tightly to the
+# the input column names and only use the output column names if no
+# input column name matches.
+#
+# This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL.
+# Note that Oracle works differently.
+#
+do_test resolver01-3.1 {
catchsql {
SELECT 3 AS y FROM t1, t2 ORDER BY +y;
}
-} {0 3}
+} {1 {ambiguous column name: y}}
+do_test resolver01-3.2 {
+ catchsql {
+ SELECT 2 AS yy FROM t1, t2 ORDER BY +y;
+ }
+} {1 {ambiguous column name: y}}
+do_test resolver01-3.3 {
+ catchsql {
+ SELECT x AS y FROM t3 ORDER BY +y;
+ }
+} {0 {33 11}}
+do_test resolver01-3.4 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY +y;
+ }
+} {0 {33 11}}
+do_test resolver01-3.5 {
+ catchsql {
+ SELECT x AS yy FROM t3 ORDER BY +yy
+ }
+} {0 {11 33}}
+
+# This is the test case given in ticket [f617ea3125e9] (with table name
+# changed from "t1" to "t4". The behavior of (1) and (3) match with
+# PostgreSQL, but we intentionally break with PostgreSQL to provide
+# SQL92 behavior for case (2).
+#
+do_execsql_test resolver01-4.1 {
+ CREATE TABLE t4(m CHAR(2));
+ INSERT INTO t4 VALUES('az');
+ INSERT INTO t4 VALUES('by');
+ INSERT INTO t4 VALUES('cx');
+ SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
+ SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
+ SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
+} {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}
+
+##########################################################################
+# Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds
+# more tightly to the input tables in all cases.
+#
+# This first case case has been wrong in SQLite for time out of mind.
+# For SQLite version 3.7.17 the answer was two rows, which is wrong.
+#
+do_execsql_test resolver01-5.1 {
+ CREATE TABLE t5(m CHAR(2));
+ INSERT INTO t5 VALUES('ax');
+ INSERT INTO t5 VALUES('bx');
+ INSERT INTO t5 VALUES('cy');
+ SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2;
+} {1 x 1 x 1 y}
+
+# This case is unambiguous and has always been correct.
+#
+do_execsql_test resolver01-5.2 {
+ SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2;
+} {1 x 1 x 1 y}
+
+# This case is not allowed in standard SQL, but SQLite allows and does
+# the sensible thing.
+#
+do_execsql_test resolver01-5.3 {
+ SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2;
+} {1 y 2 x}
+do_execsql_test resolver01-5.4 {
+ SELECT count(*), substr(m,2,1) AS mx FROM t5
+ GROUP BY substr(m,2,1) ORDER BY 1, 2;
+} {1 y 2 x}
+
+# These test case weere provided in the 2013-08-14 email from Rob Golsteijn
+# that originally reported the problem of ticket [1c69be2dafc28].
+#
+do_execsql_test resolver01-6.1 {
+ CREATE TABLE t61(name);
+ SELECT min(name) FROM t61 GROUP BY lower(name);
+} {}
+do_execsql_test resolver01-6.2 {
+ SELECT min(name) AS name FROM t61 GROUP BY lower(name);
+} {}
+do_execsql_test resolver01-6.3 {
+ CREATE TABLE t63(name);
+ INSERT INTO t63 VALUES (NULL);
+ INSERT INTO t63 VALUES ('abc');
+ SELECT count(),
+ NULLIF(name,'abc') AS name
+ FROM t63
+ GROUP BY lower(name);
+} {1 {} 1 {}}
+
+
+
finish_test
diff --git a/test/tkt2822.test b/test/tkt2822.test
index d3512d303..d0b16338c 100644
--- a/test/tkt2822.test
+++ b/test/tkt2822.test
@@ -208,15 +208,12 @@ do_test tkt2822-5.4 {
# In "ORDER BY +b" the term is now an expression rather than
# a label. It therefore matches by rule (3) instead of rule (2).
-#
-# 2013-04-13: This is busted. Changed to conform to PostgreSQL and
-# MySQL and Oracle behavior.
#
do_test tkt2822-5.5 {
execsql {
SELECT a AS b FROM t3 ORDER BY +b;
}
-} {1 9}
+} {9 1}
# Tests for rule 2 in compound queries
#
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 23:33:21 +0000