SEO

Following query is considered as correct, no "missing from" error has
been reported (so, entire table will be updated and "on update"
triggers will be fired for every row):

update item set obj_id = obj_id
where obj_id in (select obj_id where item_point is null order by
obj_modified limit 10)

Is it a bug? If no, maybe to produce warning in such cases?

--
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

ok, sorry, I've realized that it's yet another example of "outer
reference", Tom will say "read any SQL book" again :-)

http://archives.postgresql.org/pgsql...2/msg00115.php

On 12/19/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> Following query is considered as correct, no "missing from" error has
> been reported (so, entire table will be updated and "on update"
> triggers will be fired for every row):
>
> update item set obj_id = obj_id
> where obj_id in (select obj_id where item_point is null order by
> obj_modified limit 10)
>
> Is it a bug? If no, maybe to produce warning in such cases?
>
> --
> Best regards,
> Nikolay
>


--
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> On 12/19/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> > Following query is considered as correct, no "missing from" error has
> > been reported (so, entire table will be updated and "on update"
> > triggers will be fired for every row):
> >
> > update item set obj_id = obj_id
> > where obj_id in (select obj_id where item_point is null order by
> > obj_modified limit 10)
> >
> > Is it a bug? If no, maybe to produce warning in such cases?
>
On 12/18/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> ok, sorry, I've realized that it's yet another example of "outer
> reference", Tom will say "read any SQL book" again :-)
>
> http://archives.postgresql.org/pgsql...2/msg00115.php
>

not really... AFAIK, the FROM clause is mandatory per SQL... older
releases of postgres fill the missing from clause if it was easy to
determine, in recent releases it's mandatory unless you specify the
opposite in postgresql.conf with the add_missing_from parameter

--
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

>> > Is it a bug? If no, maybe to produce warning in such cases?

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... no clue which
ones :-/

- thomas



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 12/18/06, Thomas H. <me@alternize.com> wrote:
> >> > Is it a bug? If no, maybe to produce warning in such cases?
>
> 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?

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...

> no clue which ones :-/
>

LIMIT is often meaningfull only in conjuction with ORDER BY

--
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>> 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...

> LIMIT is often meaningfull only in conjuction with ORDER BY

yep but not here. all i wanted to do is to get names from 2 movies and run
an *observed* edit on them.

what did pgsql actually do with that subquery? did it return all records for
which mov_name match '%, %'?

- thomas



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Thomas H." <me@alternize.com> writes:
> 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

It didn't "ignore" anything. Each execution of the sub-select returned
1 row, containing the current mov_id from the outer query. So basically
this would've selected everything passing the LIKE condition.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

mike <mike@thegodshalls.com> writes:
> 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.

My, there's a lot of misinformation in this thread.

The reason there's no error thrown is that the reference to mov_id in
the sub-SELECT is a perfectly legal outer reference to the mov_id column
available from the upper SELECT. If the column truly did not exist
anywhere in the tables used in the query, it would have thrown an error.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings