From b406626c64313ae348996c243a0a05d3f6ed2c3c Mon Sep 17 00:00:00 2001
From: Jan Mercl <0xjnml@gmail.com>
Date: Sun, 26 Jul 2020 22:36:18 +0200
Subject: release v1.4.0-beta1

---
 testdata/tcl/wherelimit.test | 330 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 330 insertions(+)
 create mode 100644 testdata/tcl/wherelimit.test

(limited to 'testdata/tcl/wherelimit.test')

diff --git a/testdata/tcl/wherelimit.test b/testdata/tcl/wherelimit.test
new file mode 100644
index 0000000..8db7a0c
--- /dev/null
+++ b/testdata/tcl/wherelimit.test
@@ -0,0 +1,330 @@
+# 2008 October 6
+#
+# 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 the LIMIT ... OFFSET ... clause
+#  of UPDATE and DELETE statements.
+#
+# $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+proc create_test_data {size} {
+  # Build some test data
+  #
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    CREATE TABLE t1(x int, y int);
+    BEGIN;
+  }
+  for {set i 1} {$i<=$size} {incr i} {
+    for {set j 1} {$j<=$size} {incr j} {
+      execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])"
+    }
+  }
+  execsql {
+    COMMIT;
+  }
+  return {}
+}
+
+ifcapable {update_delete_limit} {
+
+  execsql { CREATE TABLE t1(x, y) }
+
+  # check syntax error support
+  do_test wherelimit-0.1 {
+    catchsql {DELETE FROM t1 ORDER BY x}
+  } {1 {ORDER BY without LIMIT on DELETE}}
+  do_test wherelimit-0.2 {
+    catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
+  } {1 {ORDER BY without LIMIT on DELETE}}
+  do_test wherelimit-0.3 {
+    catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
+  } {1 {ORDER BY without LIMIT on UPDATE}}
+
+  # no AS on table sources
+  #
+  # UPDATE: As of version 3.24, AS clauses are allowed as part of
+  # UPDATE or DELETE statements.
+  do_test wherelimit-0.4 {
+    catchsql {DELETE FROM t1 AS a WHERE a.x=1}
+  } {0 {}}
+  do_test wherelimit-0.5.1 {
+    catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
+  } {0 {}}
+  do_test wherelimit-0.5.2 {
+    catchsql {UPDATE t1 AS a SET y=1 WHERE t1.x=1}
+  } {1 {no such column: t1.x}}
+
+  # OFFSET w/o LIMIT
+  do_test wherelimit-0.6 {
+    catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2}
+  } {1 {near "OFFSET": syntax error}}
+  do_test wherelimit-0.7 {
+    catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2}
+  } {1 {near "OFFSET": syntax error}}
+
+  execsql { DROP TABLE t1 }
+
+  # check deletes w/o where clauses but with limit/offsets
+  create_test_data 5
+  do_test wherelimit-1.0 {
+    execsql {SELECT count(*) FROM t1}
+  } {25}
+  do_test wherelimit-1.1 {
+    execsql {DELETE FROM t1}
+    execsql {SELECT count(*) FROM t1}
+  } {0}
+  create_test_data 5
+  do_test wherelimit-1.2 {
+    execsql {DELETE FROM t1 LIMIT 5}
+    execsql {SELECT count(*) FROM t1}
+  } {20}
+  do_test wherelimit-1.3 {
+    # limit 5
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 5}
+    execsql {SELECT count(*) FROM t1}
+  } {15}
+  do_test wherelimit-1.4 {
+    # limit 5, offset 2
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
+    execsql {SELECT count(*) FROM t1}
+  } {10}
+  do_test wherelimit-1.5 {
+    # limit 5, offset -2
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2}
+    execsql {SELECT count(*) FROM t1}
+  } {5}
+  do_test wherelimit-1.6 {
+    # limit -5 (no limit), offset 2
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5}
+    execsql {SELECT count(*) FROM t1}
+  } {2}
+  do_test wherelimit-1.7 {
+    # limit 5, offset -2 (no offset)
+    execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5}
+    execsql {SELECT count(*) FROM t1}
+  } {0}
+  create_test_data 5
+  do_test wherelimit-1.8 {
+    # limit -5 (no limit), offset -2 (no offset)
+    execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5}
+    execsql {SELECT count(*) FROM t1}
+  } {0}
+  create_test_data 3
+  do_test wherelimit-1.9 {
+    # limit 5, offset 2
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5}
+    execsql {SELECT count(*) FROM t1}
+  } {4}
+  do_test wherelimit-1.10 {
+    # limit 5, offset 5
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
+    execsql {SELECT count(*) FROM t1}
+  } {4}
+  do_test wherelimit-1.11 {
+    # limit 50, offset 30
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
+    execsql {SELECT count(*) FROM t1}
+  } {4}
+  do_test wherelimit-1.12 {
+    # limit 50, offset 30
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50}
+    execsql {SELECT count(*) FROM t1}
+  } {4}
+  do_test wherelimit-1.13 {
+    execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
+    execsql {SELECT count(*) FROM t1}
+  } {4}
+
+
+  create_test_data 6
+  do_test wherelimit-2.0 {
+    execsql {SELECT count(*) FROM t1}
+  } {36}
+  do_test wherelimit-2.1 {
+    execsql {DELETE FROM t1 WHERE x=1}
+    execsql {SELECT count(*) FROM t1}
+  } {30}
+  create_test_data 6
+  do_test wherelimit-2.2 {
+    execsql {DELETE FROM t1 WHERE x=1 LIMIT 5}
+    execsql {SELECT count(*) FROM t1}
+  } {31}
+  do_test wherelimit-2.3 {
+    # limit 5
+    execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5}
+    execsql {SELECT count(*) FROM t1}
+  } {30}
+  do_test wherelimit-2.4 {
+    # limit 5, offset 2
+    execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
+    execsql {SELECT count(*) FROM t1}
+  } {26}
+  do_test wherelimit-2.5 {
+    # limit 5, offset -2
+    execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
+    execsql {SELECT count(*) FROM t1}
+  } {24}
+  do_test wherelimit-2.6 {
+    # limit -5 (no limit), offset 2
+    execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5}
+    execsql {SELECT count(*) FROM t1}
+  } {20}
+  do_test wherelimit-2.7 {
+    # limit 5, offset -2 (no offset)
+    execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5}
+    execsql {SELECT count(*) FROM t1}
+  } {18}
+  do_test wherelimit-2.8 {
+    # limit -5 (no limit), offset -2 (no offset)
+    execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5}
+    execsql {SELECT count(*) FROM t1}
+  } {12}
+  create_test_data 6
+  do_test wherelimit-2.9 {
+    # limit 5, offset 2
+    execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5}
+    execsql {SELECT count(*) FROM t1}
+  } {32}
+  do_test wherelimit-2.10 {
+    # limit 5, offset 5
+    execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
+    execsql {SELECT count(*) FROM t1}
+  } {31}
+  do_test wherelimit-2.11 {
+    # limit 50, offset 30
+    execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
+    execsql {SELECT count(*) FROM t1}
+  } {31}
+  do_test wherelimit-2.12 {
+    # limit 50, offset 30
+    execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50}
+    execsql {SELECT count(*) FROM t1}
+  } {31}
+  do_test wherelimit-2.13 {
+    execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
+    execsql {SELECT count(*) FROM t1}
+  } {31}
+
+
+  create_test_data 6
+  do_test wherelimit-3.0 {
+    execsql {SELECT count(*) FROM t1}
+  } {36}
+  do_test wherelimit-3.1 {
+    execsql {UPDATE t1 SET y=1 WHERE x=1}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {11}
+  create_test_data 6
+  do_test wherelimit-3.2 {
+    execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {10}
+  do_test wherelimit-3.3 {
+    # limit 5
+    execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5}
+    execsql {SELECT count(*) FROM t1 WHERE y=2}
+  } {9}
+  create_test_data 6
+  do_test wherelimit-3.4 {
+    # limit 5, offset 2
+    execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {6}
+  do_test wherelimit-3.5 {
+    # limit 5, offset -2
+    execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {5}
+  do_test wherelimit-3.6 {
+    # limit -5 (no limit), offset 2
+    execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5}
+    execsql {SELECT count(*) FROM t1 WHERE y=3}
+  } {8}
+  do_test wherelimit-3.7 {
+    # limit 5, offset -2 (no offset)
+    execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5}
+    execsql {SELECT count(*) FROM t1 WHERE y=3}
+  } {10}
+
+  do_test wherelimit-3.8 {
+    # limit -5 (no limit), offset -2 (no offset)
+    execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5}
+    execsql {SELECT count(*) FROM t1 WHERE y=4}
+  } {9}
+  create_test_data 6
+  do_test wherelimit-3.9 {
+    # limit 5, offset 2
+    execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5}
+    execsql {SELECT count(*) FROM t1 WHERE y=4}
+  } {9}
+  do_test wherelimit-3.10 {
+    # limit 5, offset 5
+    execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {6}
+  do_test wherelimit-3.11 {
+    # limit 50, offset 30
+    execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {6}
+  do_test wherelimit-3.12 {
+    # limit 50, offset 30
+    execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {6}
+  do_test wherelimit-3.13 {
+    execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
+    execsql {SELECT count(*) FROM t1 WHERE y=1}
+  } {6}
+
+  # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table
+  # or a VIEW.  (We should fix this someday).
+  #
+  db close
+  sqlite3 db :memory:
+  do_execsql_test wherelimit-4.1 {
+    CREATE TABLE t1(a int);
+    INSERT INTO t1 VALUES(1);
+    INSERT INTO t1 VALUES(2);
+    INSERT INTO t1 VALUES(3);
+    CREATE TABLE t2(a int);
+    INSERT INTO t2 SELECT a+100 FROM t1;
+    CREATE VIEW tv(r,a) AS
+       SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1;
+    CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv
+    BEGIN
+      DELETE FROM t1 WHERE rowid=old.r;
+      DELETE FROM t2 WHERE rowid=old.r;
+    END;
+  } {}
+  do_catchsql_test wherelimit-4.2 {
+    DELETE FROM tv WHERE 1 LIMIT 2;
+  } {0 {}}
+  do_catchsql_test wherelimit-4.3 {
+    DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2;
+  } {0 {}}
+  do_execsql_test wherelimit-4.10 {
+    CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
+    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12);
+  } {}
+  do_catchsql_test wherelimit-4.11 {
+    DELETE FROM t3 WHERE a=5 LIMIT 2;
+  } {0 {}}
+  do_execsql_test wherelimit-4.12 {
+    SELECT a,b,c,d FROM t3 ORDER BY 1;
+  } {1 2 3 4 9 10 11 12}
+
+}
+
+finish_test
-- 
cgit v1.2.3-70-g09d2