Re: select :all

+ Harald Hanche-Olsen <[email protected]>:

> + Patrick May <[email protected]>:
> 
> > 	What is the meaning of the :all keyword in select?  Is it necessary to
> > 	ensure that all rows are returned from the database?
> 
> CLSQL manual: The keyword arguments all, distinct, from, group-by,
> having, limit, offset, order-by, set-operation and where are used to
> specify, using the symbolic SQL syntax, the corresponding components
> of the SQL query generated by the call to select.
> 
> SQL syntax
> (http://www.postgresql.org/docs/8.4/static/sql-select.html):
> In connection with this part of the SELECT syntax:
> { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
> In all three cases, duplicate rows are eliminated unless ALL is
> specified.

Er, I found another, more likely candidate in the above web page.
Serves me right for being a smartass.
The select syntax begins (more or less) thus:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

DISTINCT eliminates duplicate rows from the result. DISTINCT ON
eliminates rows that match on all the specified expressions. ALL (the
default) will return all candidate rows, including duplicates. (See
DISTINCT Clause below.)

So my guess is that specifying :all t will turn on the ALL keyword in
the query, which does nothing since it's already default. Unless, of
course, you also use :distinct, in which case you should end up with
an SQL syntax error. In either case, it seems useless.

But why guess, when clsql holds the answer?

cl-user> (clsql:enable-sql-reader-syntax)
; No value
cl-user> (clsql:sql [select [*] :from [foo]])
"SELECT * FROM FOO"
cl-user> (clsql:sql [select [*] :all t :from [foo]])
"SELECT ALL * FROM FOO"
cl-user> (clsql:sql [select [*] :all nil :from [foo]])
"SELECT * FROM FOO"
cl-user> (clsql:sql [select [*] :distinct '([bar] [quux]) :from [foo]])
"SELECT DISTINCT ON (BAR,QUUX) * FROM FOO"
cl-user> (clsql:sql [select [*] :all t :distinct '([bar] [quux]) :from [foo]])
"SELECT ALL * FROM FOO"

So selecting :all t negates the :distinct keyword. Interesting.

- Harald