Skip site navigation (1) Skip section navigation (2)

Re: No error when FROM is missing in subquery

From: "Thomas H(dot)" <me(at)alternize(dot)com>
To: "Jaime Casanova" <systemguards(at)gmail(dot)com>
Cc: <nikolay(at)samokhvalov(dot)com>,<pgsql-bugs(at)postgresql(dot)org>
Subject: Re: No error when FROM is missing in subquery
Date: 2006-12-19 03:57:31
Message-ID: 079a01c72321$cfb950f0$6601a8c0@iwing (view raw or flat)
Thread:
Lists: pgsql-bugs
>> 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 



In response to

Responses

pgsql-bugs by date

Next:From: Jaime CasanovaDate: 2006-12-19 04:09:44
Subject: Re: No error when FROM is missing in subquery
Previous:From: Jaime CasanovaDate: 2006-12-19 03:43:31
Subject: Re: No error when FROM is missing in subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group