[sqlite] Use of DELETE LIMIT OFFSET with ENABLE_UPDATE_DELETE_LIMIT

[Jan Nijtmans]

2015-12-19 21:57 GMT+01:00 Jeremy Boy <jeremy.boy at student.uni-luebeck.de>:
> I would like to use the optional LIMIT and ORDER BY clause for DELETE 
> statements in SQLite. I can verify that SQLite was built with 
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT:

This doesn't work when sqlite is buillt from the amalamation, which is
clearly stated
in the docs. You need to build it from the sources, then it will work.
The problem is
that the produced amalgamation is different depending on this flag.

My solution would be the patch below (to the SQLite sources). With this
patch in place, the resulting amalgamation can be build either with or without
SQLITE_ENABLE_UPDATE_DELETE_LIMIT, both work as expected. The
produced amalgamation doesn't depend on the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
flag any more.

I'm donating this patch to the public domain, in case anyone is interested

Needless to say: All unit-test (in the SQLite sources) run fine when
this patch is applied.

Could I add this to the 2016 wish-list?    ;-)

Regards,
         Jan Nijtmans

===========================================
--- src/parse.y
+++ src/parse.y
@@ -740,54 +740,56 @@
 limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
                                       {A.pOffset = X.pExpr; A.pLimit
= Y.pExpr;}

 /////////////////////////// The DELETE statement /////////////////////////////
 //
-%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
 cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
         orderby_opt(O) limit_opt(L). {
   sqlite3WithPush(pParse, C, 1);
   sqlite3SrcListIndexedBy(pParse, X, &I);
+#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
!defined(SQLITE_OMIT_SUBQUERY)
   W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
-  sqlite3DeleteFrom(pParse,X,W);
-}
-%endif
-%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
-  sqlite3WithPush(pParse, C, 1);
-  sqlite3SrcListIndexedBy(pParse, X, &I);
+#else
+  if( O || L.pLimit ){
+    sqlite3ErrorMsg(pParse, "%s on DELETE not supported", O?"ORDER
BY":"LIMIT");
+    sqlite3ExprDelete(pParse->db, W);
+    sqlite3ExprListDelete(pParse->db, O);
+    sqlite3ExprDelete(pParse->db, L.pLimit);
+    sqlite3ExprDelete(pParse->db, L.pOffset);
+    W = 0;
+  }
+#endif
   sqlite3DeleteFrom(pParse,X,W);
 }
-%endif

 %type where_opt {Expr*}
 %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}

 where_opt(A) ::= .                    {A = 0;}
 where_opt(A) ::= WHERE expr(X).       {A = X.pExpr;}

 ////////////////////////// The UPDATE command ////////////////////////////////
 //
-%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
 cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
         where_opt(W) orderby_opt(O) limit_opt(L).  {
   sqlite3WithPush(pParse, C, 1);
   sqlite3SrcListIndexedBy(pParse, X, &I);
   sqlite3ExprListCheckLength(pParse,Y,"set list");
+#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
!defined(SQLITE_OMIT_SUBQUERY)
   W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
-  sqlite3Update(pParse,X,Y,W,R);
-}
-%endif
-%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
-        where_opt(W).  {
-  sqlite3WithPush(pParse, C, 1);
-  sqlite3SrcListIndexedBy(pParse, X, &I);
-  sqlite3ExprListCheckLength(pParse,Y,"set list");
+#else
+  if( O || L.pLimit ){
+    sqlite3ErrorMsg(pParse, "%s on UPDATE not supported", O?"ORDER
BY":"LIMIT");
+    sqlite3ExprDelete(pParse->db, W);
+    sqlite3ExprListDelete(pParse->db, O);
+    sqlite3ExprDelete(pParse->db, L.pLimit);
+    sqlite3ExprDelete(pParse->db, L.pOffset);
+    W = 0;
+  }
+#endif
   sqlite3Update(pParse,X,Y,W,R);
 }
-%endif

 %type setlist {ExprList*}
 %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}

 setlist(A) ::= setlist(Z) COMMA nm(X) EQ expr(Y). {