[postmodern-devel] query with keyword :on

[Jens Teich]

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