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 (AT) gmail (DOT) com> wrote:

--
Best regards,
Nikolay

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

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

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 (AT) alternize (DOT) com> wrote:
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...

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

that was done by the application with the returned recordset.

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

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 (AT) alternize (DOT) com> wrote:
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

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

"Thomas H." <me (AT) alternize (DOT) com> writes:
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:

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