summaryrefslogtreecommitdiffstats
path: root/test/selectH.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
commit63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree01c7571c7c762ceee70638549a99834fdd7c411b /test/selectH.test
parentInitial commit. (diff)
downloadsqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
Adding upstream version 3.45.1.upstream/3.45.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'test/selectH.test')
-rw-r--r--test/selectH.test145
1 files changed, 145 insertions, 0 deletions
diff --git a/test/selectH.test b/test/selectH.test
new file mode 100644
index 0000000..41f0999
--- /dev/null
+++ b/test/selectH.test
@@ -0,0 +1,145 @@
+# 2023-02-16
+#
+# 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.
+#
+#***********************************************************************
+#
+# Test cases for the omit-unused-subquery-column optimization.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix selectH
+
+do_execsql_test 1.1 {
+ CREATE TABLE t1(
+ c0, c1, c2, c3, c4, c5, c6, c7, c8, c9,
+ c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
+ c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
+ c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
+ c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
+ c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
+ c60, c61, c62, c63, c64, c65
+ );
+ INSERT INTO t1 VALUES(
+ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
+ 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
+ 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
+ 30, 31, 32, 33, 34, 35, 36, 37, 38, 39,
+ 40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
+ 50, 51, 52, 53, 54, 55, 56, 57, 58, 59,
+ 60, 61, 62, 63, 64, 65
+ );
+ CREATE INDEX t1c60 ON t1(c60);
+}
+
+# The SQL counter(N) function adjusts the value of the global
+# TCL variable ::selectH_cnt by the value N and returns the new
+# value. By putting calls to counter(N) as unused columns in a
+# view or subquery, we can check to see if the counter gets incremented,
+# and if not that means that the unused column was omitted.
+#
+unset -nocomplain selectH_cnt
+set selectH_cnt 0
+proc selectH_counter {amt} {
+ global selectH_cnt
+ incr selectH_cnt $amt
+ return $selectH_cnt
+}
+db func counter selectH_counter
+
+do_execsql_test 1.2 {
+ SELECT DISTINCT c44 FROM (
+ SELECT c0 AS a, *, counter(1) FROM t1
+ UNION ALL
+ SELECT c1 AS a, *, counter(1) FROM t1
+ ) WHERE c60=60;
+} {44}
+do_test 1.3 {
+ set ::selectH_cnt
+} {0}
+
+do_execsql_test 2.1 {
+ SELECT a FROM (
+ SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1
+ UNION ALL
+ SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1
+ ORDER BY b
+ );
+} {16 15}
+do_test 2.2 {
+ set ::selectH_cnt
+} {0}
+
+do_execsql_test 3.1 {
+ CREATE VIEW v1 AS
+ SELECT c16 AS a, *, counter(1) AS x FROM t1
+ UNION ALL
+ SELECT c17 AS a, *, counter(1) AS x FROM t1
+ UNION ALL
+ SELECT c18 AS a, *, counter(1) AS x FROM t1
+ UNION ALL
+ SELECT c19 AS a, *, counter(1) AS x FROM t1;
+ SELECT count(*) FROM v1 WHERE c60=60;
+} {4}
+do_test 3.2 {
+ set ::selectH_cnt
+} {0}
+do_execsql_test 3.3 {
+ SELECT count(a) FROM v1 WHERE c60=60;
+} {4}
+do_execsql_test 3.4 {
+ SELECT a FROM v1 WHERE c60=60;
+} {16 17 18 19}
+do_test 3.5 {
+ set ::selectH_cnt
+} {0}
+do_execsql_test 3.6 {
+ SELECT x FROM v1 WHERE c60=60;
+} {1 2 3 4}
+do_test 3.7 {
+ set ::selectH_cnt
+} {4}
+
+# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15
+#
+do_execsql_test 4.1 {
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
+ SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema
+ UNION ALL SELECT a FROM t1);
+} {1 1}
+
+do_execsql_test 4.2 {
+ SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema
+ UNION ALL
+ SELECT a FROM t1
+} {v1 t1}
+
+#-------------------------------------------------------------------------
+# forum post https://sqlite.org/forum/forumpost/b83c7b2168
+#
+reset_db
+do_execsql_test 5.0 {
+ CREATE TABLE t1 (val1);
+ INSERT INTO t1 VALUES(4);
+ INSERT INTO t1 VALUES(5);
+ CREATE TABLE t2 (val2);
+}
+do_execsql_test 5.1 {
+ SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2;
+} {
+ 4 5
+}
+do_execsql_test 5.2 {
+ SELECT count(1234) FROM (
+ SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2
+ )
+} {2}
+
+finish_test