libsql/libsql-wal/tests/assets/fixtures/wherelimit2.test

  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 'f');
  INSERT INTO t1 VALUES(2, 'e');
  INSERT INTO t1 VALUES(3, 'd');
  INSERT INTO t1 VALUES(4, 'c');
  INSERT INTO t1 VALUES(5, 'b');
  INSERT INTO t1 VALUES(6, 'a');

  CREATE VIEW v1 AS SELECT a,b FROM t1;
  CREATE TABLE log(op, a);

  CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
    INSERT INTO log VALUES('delete', old.a);
  END;

  CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
    INSERT INTO log VALUES('update', old.a);
  END;


  DELETE FROM v1 ORDER BY a LIMIT 3;
  SELECT * FROM log; DELETE FROM log;


  DELETE FROM v1 ORDER BY b LIMIT 3;
  SELECT * FROM log; DELETE FROM log;


  UPDATE v1 SET b = 555 ORDER BY a LIMIT 3;
  SELECT * FROM log; DELETE FROM log;


  UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
  SELECT * FROM log; DELETE FROM log;


  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
  INSERT INTO t2 VALUES(1, 1, 'h');
  INSERT INTO t2 VALUES(1, 2, 'g');
  INSERT INTO t2 VALUES(2, 1, 'f');
  INSERT INTO t2 VALUES(2, 2, 'e');
  INSERT INTO t2 VALUES(3, 1, 'd');
  INSERT INTO t2 VALUES(3, 2, 'c');
  INSERT INTO t2 VALUES(4, 1, 'b');
  INSERT INTO t2 VALUES(4, 2, 'a');


  BEGIN;
    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
    SELECT c FROM t2 ORDER BY 1;
  ROLLBACK;


  BEGIN;
    UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
    SELECT a, b, c FROM t2;
  ROLLBACK;


  DROP TABLE t2;
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
  INSERT INTO t2 VALUES(1, 1, 'h');
  INSERT INTO t2 VALUES(2, 2, 'g');
  INSERT INTO t2 VALUES(3, 1, 'f');
  INSERT INTO t2 VALUES(4, 2, 'e');
  INSERT INTO t2 VALUES(5, 1, 'd');
  INSERT INTO t2 VALUES(6, 2, 'c');
  INSERT INTO t2 VALUES(7, 1, 'b');
  INSERT INTO t2 VALUES(8, 2, 'a');


  BEGIN;
    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
    SELECT c FROM t2 ORDER BY 1;
  ROLLBACK;


  BEGIN;
    UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
    SELECT a, b, c FROM t2;
  ROLLBACK;


    CREATE VIRTUAL TABLE ft USING fts5(x);
    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
  

    BEGIN;
      DELETE FROM ft ORDER BY rowid LIMIT 3;
      SELECT x FROM ft;
    ROLLBACK;
  

    BEGIN;
      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
      SELECT x FROM ft;
    ROLLBACK;
  

    BEGIN;
      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
      SELECT rowid FROM ft;
    ROLLBACK;
  

    BEGIN;
      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
      SELECT x FROM ft;
    ROLLBACK;
  

    BEGIN;
      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
      SELECT x FROM ft;
    ROLLBACK;
  

  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);
  CREATE INDEX x1bc ON x1(b, c);
  INSERT INTO x1 VALUES(1,1,1,1);
  INSERT INTO x1 VALUES(2,1,2,2);
  INSERT INTO x1 VALUES(3,2,1,3);
  INSERT INTO x1 VALUES(4,2,2,3);
  INSERT INTO x1 VALUES(5,3,1,2);
  INSERT INTO x1 VALUES(6,3,2,1);


  BEGIN;
    DELETE FROM x1 ORDER BY a LIMIT 2;
    SELECT a FROM x1;
  ROLLBACK;


  DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
  SELECT a FROM x1;


  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
  SELECT a, d FROM x1;


  CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
  CREATE INDEX xycd ON "x y"("c d");

  INSERT INTO "x y" VALUES('a', 'a');
  INSERT INTO "x y" VALUES('b', 'b');
  INSERT INTO "x y" VALUES('c', 'c');
  INSERT INTO "x y" VALUES('d', 'd');
  INSERT INTO "x y" VALUES('e', 'a');
  INSERT INTO "x y" VALUES('f', 'b');
  INSERT INTO "x y" VALUES('g', 'c');
  INSERT INTO "x y" VALUES('h', 'd');


  BEGIN;
    DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
    SELECT * FROM "x y" ORDER BY 1;
  ROLLBACK;


  BEGIN;
    UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
    SELECT * FROM "x y" ORDER BY 1;
  ROLLBACK;


  CREATE VIEW "v w" AS SELECT * FROM "x y";
  CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
    SELECT log(old."a b", old."c d");
  END;
  CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
    SELECT log(new."a b", new."c d");
  END;


  CREATE TABLE t2(x);  
  INSERT INTO t2(x) VALUES(1),(2),(3),(5),(8),(13);


  WITH t2 AS MATERIALIZED (VALUES(5))
  DELETE FROM t2 ORDER BY rank()OVER() LIMIT 2;


  SELECT * FROM t2;


  CREATE TABLE t1(a INT); INSERT INTO t1(a) VALUES(0);


  WITH t1(b) AS (SELECT * FROM (SELECT * FROM (VALUES(2))))
  UPDATE t1 SET a=3 LIMIT 1;


  SELECT * FROM t1;