Postgres Pro
Downloads
Home   >   mailing lists

Thread:

From
Oliver Dizon
Date:
Hi Guys,

I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.

All records were deleted in a table even if the subquery in the where clause has a missing a column.

--from a certain script where table_id is not yet existing on table_sample

delete from main_table_sample where table_id in (select table_id from table_sample);

Im expecting that postgres will return an ERROR message like
'column "table_id" does not exist on table table_sample'

But it did not returned any error and deleted all the records instead.

Version: psql (8.4.9, server 9.2.5)


Thanks and Regards,
______________________
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoliver@rtzassociates.com

Re:

From
Albe Laurenz
Date:
Oliver Dizon wrote:
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.
> 
> All records were deleted in a table even if the subquery in the where clause has a missing a column.
> 
> --from a certain script where table_id is not yet existing on table_sample
> 
> delete from main_table_sample where table_id in (select table_id from table_sample);
> 
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
> 
> But it did not returned any error and deleted all the records instead.
> 
> Version: psql (8.4.9, server 9.2.5)

That seems unlikely.

Can you produce a reproducible test case?

Yours,
Laurenz Albe

Re: (unknown)

From
Thomas Kellerer
Date:
Oliver Dizon schrieb am 27.01.2015 um 11:46:
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.
>
> All records were deleted in a table even if the subquery in the where clause has a missing a column.
>
> --from a certain script where table_id is not yet existing on table_sample
>
> delete from main_table_sample where table_id in (select table_id from table_sample);
>
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
>
> But it did not returned any error and deleted all the records instead.
>
> Version: psql (8.4.9, server 9.2.5)

No, this is correct and complies with the SQL standard. If you reference a column in a sub-select that is available in
theouter query but not in the sub-select, then it's automatically a reference to the column from the outer query.  

So in your query, the column "table_id" in the sub-query references the main_table_sample.table_id.

I agree this is confusing, but it's the way it was specified in the SQL standard.

Thomas

Re:

From
Tom Lane
Date:
Oliver Dizon <johnoliver@rtzassociates.com> writes:
> Hi Guys,
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.

> All records were deleted in a table even if the subquery in the where clause has a missing a column.

> --from a certain script where table_id is not yet existing on table_sample

> delete from main_table_sample where table_id in (select table_id from table_sample);

> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'

Read up on "outer references" in SQL sub-selects.  This query is entirely
legal, although it doesn't do what you want.

            regards, tom lane

Re:

From
Oliver Dizon
Date:
Oliver Dizon <johnoliver@rtzassociates.com> writes:
> Hi Guys,
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.

> All records were deleted in a table even if the subquery in the where clause has a missing a column.

> --from a certain script where table_id is not yet existing on table_sample

> delete from main_table_sample where table_id in (select table_id from table_sample);

> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'

    > Read up on "outer references" in SQL sub-selects.  This query is entirely
    > legal, although it doesn't do what you want.

    >        regards, tom lane

It is indeed an example of outer references. I guess I should used aliases for tables in constructing queries with
sub-selectsto avoid this instance. 

Thanks!

Re: (unknown)

From
Oliver Dizon
Date:
Oliver Dizon schrieb am 27.01.2015 um 11:46:
> I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario.
>
> All records were deleted in a table even if the subquery in the where clause has a missing a column.
>
> --from a certain script where table_id is not yet existing on table_sample
>
> delete from main_table_sample where table_id in (select table_id from table_sample);
>
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
>
> But it did not returned any error and deleted all the records instead.
>
> Version: psql (8.4.9, server 9.2.5)

   >No, this is correct and complies with the SQL standard. If you reference a column in a sub-select that is available
inthe outer query but   not in the sub-select, then it's automatically a reference to the column from the outer query.  

   >So in your query, the column "table_id" in the sub-query references the main_table_sample.table_id.

   >I agree this is confusing, but it's the way it was specified in the SQL standard.

   >Thomas

Yea. I've realized that it's yet another example of "outer
reference". I guess I really need to read another SQL book again :-)

Thanks!

______________________
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoliver@rtzassociates.com