Re: No error when FROM is missing in subquery

On 12/18/06, Thomas H. <me@alternize.com> wrote:
> >> oups. just thumbled over this as well when i forgot a FROM in a WHERE ...
> >> IN
> >> (....) and damaged quite some data. the bad query went like this:
> >>
> >> SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE
> >> mov_name like '%, %' LIMIT 2)
> >>
> >> the subselect is missing a FROM <table>. in that case, pgsql seemed to
> >> also
> >> ignore the LIMIT 2 and returned 3706 records out of ~130000...
> >
> > and the UPDATE was?
>
> that was done by the application with the returned recordset.
>
> > also the limit applies only to the subselect, it has nothing to do
> > with the upper query so the upper query can return more than number of
> > rows specified in the subselect...
>
> IF the subquery would only have returned 2 ids, then there would be at most
> like +/-10 records affected. each mov_id can hold one or more (usuals up to
> 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids,
> thus around 37000 names where damaged by the following programmatical
> updates instead of only a hands full...
>

have you tested the query in psql?
what results do you get?


--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook