Re: select :all

On Mar 28, 2010, at 10:11 PM, Harald Hanche-Olsen wrote:
> + 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.

	Thanks for the follow up; I should have thought to look at the generated SQL myself.

	And no worries -- I also come from a long line of smartasses.  ;-)

Regards,

Patrick

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql