Marijn Haverbeke wrote:
Aha, I see the syntax error now -- might be some slight difference in the way LOOP works on LispWorks. But I'm on a public computer somewhere, and will not be near any machines running Lisp in the next weeks, so I can't really debug this for you. If you want to solve this, you can try digging into the code in s-sql/s-sql.lisp that produces such queries, and see where it goes wrong (shouldn't be too hard). Or maybe someone else on the list wants to take a quick look?
Cheers, Marijn
Hi Marijn,
I looked into the code and found the function expand-join to be a candidate for cause of the problems:
(defun expand-joins (args) "Helper for the select operator. Turns the part following :from into the proper SQL syntax for joining tables." (labels ((is-join (x) (member x '(:left-join :right-join :inner-join :cross-join)))) (when (null args) (error "Empty :from clause in select")) (when (is-join (car args)) (error ":from clause starts with a join: ~A" args)) (loop :for table :on args :for first = t :then nil :append (cond ((is-join (car table)) (destructuring-bind (join name on clause) (subseq table 0 4) (setf table (cdddr table)) ;; debug start (print (list "table" table)) ;; debug end (unless (and (eq on :on) clause) (error "Incorrect join form in select.")) `(" " ,(ecase join (:left-join "LEFT") (:right-join "RIGHT") (:inner-join "INNER") (:cross-join "CROSS")) " JOIN " ,@(sql-expand name) " ON " ,@(sql-expand clause)))) (t `(,@(if first () '(", ")) ,@(sql-expand (car table))))))))
I traced it:
S-SQL 68 > (trace expand-joins) (EXPAND-JOINS)
S-SQL 69 > (sql (:select '* :from 't1 :inner-join 't2 :on (:= 't1.id 't2.id))) 0 EXPAND-JOINS > ...
ARGS : ((QUOTE T1) :INNER-JOIN (QUOTE T2) :ON (:= (QUOTE T1.ID) (QUOTE T2.ID)))
0 EXPAND-JOINS < ... << VALUE-0 : ("t1" " " "INNER" " JOIN " "t2" " ON " "(" "t1.id" " = " "t2.id" ")" ", " "t2" ", " "on" ", " "(" "t1.id" " = " "t2.id" ")") "(SELECT * FROM t1 INNER JOIN t2 ON (t1.id = t2.id), t2, on, (t1.id = t2.id))"
For me it looks a bit strange to declare a loop variable | :for table :on args and later on to manipulate this variable with setf | (setf table (cdddr table))
and indeed LispWorks and SBCL behave differently with such an expression
LW> (loop for item on '(1 2 3 4 5) collect (progn (setf item (cddr item)) item)) => ((3 4 5) (4 5) (5) NIL NIL)
SBCL> (loop for item on '(1 2 3 4 5) collect (progn (setf item (cddr item)) item)) => ((3 4 5) NIL)
where LispWorks produces 'too much' output as in the original problem.
Jens