Adding upstream version 3.40.1.upstream/3.40.1upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 202 insertions, 0 deletions

diff --git a/test/offset1.test b/test/offset1.test
new file mode 100644
index 0000000..5b04bd8
--- /dev/null
+++ b/test/offset1.test
@@ -0,0 +1,202 @@
+# 2015-10-06
+#
+# 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 test cases for the [b65cb2c8d91f6685841d7d1e13b6]
+# bug:  Correct handling of LIMIT and OFFSET on a UNION ALL query where
+# the right-hand SELECT contains an ORDER BY in a subquery.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !compound {
+  finish_test
+  return
+}
+
+do_execsql_test offset1-1.1 {
+  CREATE TABLE t1(a,b);
+  INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
+  CREATE TABLE t2(x,y);
+  INSERT INTO t2 VALUES(8,'y'),(9,'z'),(6,'w'),(7,'x');
+  SELECT count(*) FROM t1, t2;
+} {20}
+
+do_execsql_test offset1-1.2.0 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 0;
+} {1 a 2 b 3 c}
+do_execsql_test offset1-1.2.1 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 1;
+} {2 b 3 c 4 d}
+do_execsql_test offset1-1.2.2 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 2;
+} {3 c 4 d 5 e}
+do_execsql_test offset1-1.2.3 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 3;
+} {4 d 5 e 6 w}
+do_execsql_test offset1-1.2.4 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 4;
+} {5 e 6 w 7 x}
+do_execsql_test offset1-1.2.5 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 5;
+} {6 w 7 x 8 y}
+do_execsql_test offset1-1.2.6 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 6;
+} {7 x 8 y 9 z}
+do_execsql_test offset1-1.2.7 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 7;
+} {8 y 9 z}
+do_execsql_test offset1-1.2.8 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 8;
+} {9 z}
+do_execsql_test offset1-1.2.9 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 9;
+} {}
+
+do_execsql_test offset1-1.3.0 {
+  SELECT * FROM t1 LIMIT 0;
+} {}
+
+do_execsql_test offset1-1.4.0 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 0 OFFSET 1;
+} {}
+do_execsql_test offset1-1.4.1 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 1 OFFSET 1;
+} {2 b}
+do_execsql_test offset1-1.4.2 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 2 OFFSET 1;
+} {2 b 3 c}
+do_execsql_test offset1-1.4.3 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 3 OFFSET 1;
+} {2 b 3 c 4 d}
+do_execsql_test offset1-1.4.4 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 4 OFFSET 1;
+} {2 b 3 c 4 d 5 e}
+do_execsql_test offset1-1.4.5 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 5 OFFSET 1;
+} {2 b 3 c 4 d 5 e 6 w}
+do_execsql_test offset1-1.4.6 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 6 OFFSET 1;
+} {2 b 3 c 4 d 5 e 6 w 7 x}
+do_execsql_test offset1-1.4.7 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 7 OFFSET 1;
+} {2 b 3 c 4 d 5 e 6 w 7 x 8 y}
+do_execsql_test offset1-1.4.8 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 8 OFFSET 1;
+} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}
+do_execsql_test offset1-1.4.9 {
+  SELECT a, b FROM t1
+  UNION ALL
+  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
+  LIMIT 9 OFFSET 1;
+} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}
+
+# 2022-08-04
+# https://sqlite.org/forum/forumpost/6b5e9188f0657616
+#
+do_execsql_test offset1-2.0 {
+  CREATE TABLE employees (
+    id integer primary key,
+    name text,
+    city text,
+    department text,
+    salary integer
+  );
+  INSERT INTO employees VALUES
+    (11,'Diane','London','hr',70),
+    (12,'Bob','London','hr',78),
+    (21,'Emma','London','it',84),
+    (22,'Grace','Berlin','it',90),
+    (23,'Henry','London','it',104),
+    (24,'Irene','Berlin','it',104),
+    (25,'Frank','Berlin','it',120),
+    (31,'Cindy','Berlin','sales',96),
+    (32,'Dave','London','sales',96),
+    (33,'Alice','Berlin','sales',100);
+  CREATE VIEW v AS
+    SELECT * FROM (
+      SELECT * FROM employees 
+       WHERE salary < 100
+       ORDER BY salary desc)
+     UNION ALL 
+     SELECT * FROM (
+       SELECT * FROM employees
+       WHERE salary >= 100
+       ORDER BY salary asc);
+} {}
+do_execsql_test offset1-2.1 {
+  SELECT * FROM v LIMIT 5 OFFSET 2;
+} {
+  22  Grace  Berlin  it      90
+  21  Emma   London  it      84
+  12  Bob    London  hr      78
+  11  Diane  London  hr      70
+  33  Alice  Berlin  sales   100
+}
+
+finish_test