From 7cdd27f6a65387461baea7099a9e674e42ba26d4 Mon Sep 17 00:00:00 2001 From: Ryan Davis Date: Mon, 26 Mar 2012 15:52:13 -0400 Subject: [PATCH] fix SQL syntax errors when using subqueries in WHERE clauses of DELETE or UPDATE statements. A similar issue was fixed for SELECTs in b06efa82, this patch pulls WHERE clause printing into a seperate function called by the different output-sql methods. --- sql/expressions.lisp | 28 ++++++++++++++++------------ 1 file changed, 16 insertions(+), 12 deletions(-) diff --git a/sql/expressions.lisp b/sql/expressions.lisp index 4a6eb63..281da96 100644 --- a/sql/expressions.lisp +++ b/sql/expressions.lisp @@ -673,6 +673,20 @@ uninclusive, and the args from that keyword to the end." :group-by group-by :having having :order-by order-by :inner-join inner-join :on on)))))) +(defun output-sql-where-clause (where database) + "ensure that we do not output a \"where\" sql keyword when we will + not output a clause. Also sets *in-subselect* to use SQL + parentheticals as needed." + (when where + (let ((where-out (string-trim + '(#\newline #\space #\tab #\return) + (with-output-to-string (*sql-stream*) + (let ((*in-subselect* t)) + (output-sql where database)))))) + (when (> (length where-out) 0) + (write-string " WHERE " *sql-stream*) + (write-string where-out *sql-stream*))))) + (defmethod output-sql ((query sql-query) database) (with-slots (distinct selections from where group-by having order-by limit offset inner-join on all set-operation) @@ -712,15 +726,7 @@ uninclusive, and the args from that keyword to the end." (when on (write-string " ON " *sql-stream*) (output-sql on database)) - (when where - (let ((where-out (string-trim - '(#\newline #\space #\tab #\return) - (with-output-to-string (*sql-stream*) - (let ((*in-subselect* t)) - (output-sql where database)))))) - (when (> (length where-out) 0) - (write-string " WHERE " *sql-stream*) - (write-string where-out *sql-stream*)))) + (output-sql-where-clause where database) (when group-by (write-string " GROUP BY " *sql-stream*) (if (listp group-by) @@ -832,9 +838,7 @@ uninclusive, and the args from that keyword to the end." (typecase from ((or symbol string) (write-string (sql-escape from) *sql-stream*)) (t (output-sql from database))) - (when where - (write-string " WHERE " *sql-stream*) - (output-sql where database))) + (output-sql-where-clause where database)) t) ;; UPDATE -- 2.34.1