Also check that the mov_id column exists in the table/view that you are
running the SELECT DISTINCT against.
Pgsql does not throw an error (at least prior to 8.2) if the column
referenced by the select statement for the IN clause does not exist. It
will run only SELECT * FROM movies.names in this case.
Mike
On Tue, 2006-12-19 at 06:01 +0100, Thomas H. wrote:
> >> >> SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id
> >> >> WHERE
> >> >> mov_name like '%, %' LIMIT 2)
> >>
> >> 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?
>
> the data is damaged so the result isn't the same... regenearting it now from
> a backup.
>
> from first tests i would say it returned records with names that match the
> WHERE in the subselect. i guess what happened is: it took each record in
> movies.names, then run the subquery for that record which resulted in "WHERE
> mov_id IN (mov_id)" = true for records with a ', ' in the name and "WHERE
> mov_id IN ()" = false for all others.
>
> - thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend