On Apr 28, 2009, at 2:27 PM, Tom Boutell wrote:
>
> One more note about writing IN clauses that everybody seems to miss...
> if $myids is an empty list, MySQL will produce a syntax error. "Empty
> equals evil and scary and different" is an annoying property that both
> MySQL and PHP tend to display in many situations. Simply skipping the
> IN clause completely would always match everything, when of course you
> should match nothing. A simple and correct workaround is to replace
> the entire IN clause with FALSE when the list is empty.
That's because it is a syntax error according to the SQL-92 standard.
As easy as it is to pick on MySQL, it isn't their fault. This is what
we're stuck with in all dbms that follow SQL-92, for better or worse.
> 8.4 <in predicate>
>
> Function
>
> Specify a quantified comparison.
>
> Format
>
> <in predicate> ::=
> <row value constructor>
> [ NOT ] IN <in predicate value>
>
> <in predicate value> ::=
> <table subquery>
> | <left paren> <in value list> <right paren>
>
> <in value list> ::=
> <value expression> { <comma> <value expression> }...
Notice how an <in value list> MUST start with a <value expression> and
a <value expression> cannot be empty.
IN (false) isn't technically the proper behavior for what you're
looking for. Consider the case: SELECT true WHERE false IN (false).
(Yeah it's a pathological case but something a framework would need to
worry about.) You should be using IN (null) since that shouldn't
match any rows with a standards-compliant DBMS. I know PostgreSQL and
mysql 5 both behave correctly.
--
Jacob Coby
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
