Re: sub query reference error or user error

On Nov 6, 2012, at 12:36, sivakumar krishnamurthy <sivakumar(dot)mailinglist(at)gmail(dot)com> wrote:

> Hi All,
> For the below test case shouldn't the update statement throw error because the sub query projects a column which is not existing in the table. For ex in inner select nos1 column exists in t1 and not t2.
> Is this a bug or an OP error?
>
> template1=# SELECT version();
> version
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
> (1 row)
> template1=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+---------+-----------
> nos1 | integer |
>
> template1=# \d t2
> Table "public.t2"
> Column | Type | Modifiers
> --------+---------+-----------
> nos2 | integer |
>
> template1=# UPDATE t1 set nos1=2 where nos1 in (select nos1 from t2);
> UPDATE 0
> template1=#
>
> Thanks,
> Sivakumar.K
>

In a correlated sub-query any column from the parent or sub-query can appear. In this case anything in t1or t2. In this case the effect is a constant true evaluation since for each row in t1 the value of nos1 is constant. The use of t2 simply causes the constant to be repeated once for each row in t2. As a safety feature you should prefix your columns in sub-queries with the proper table name. t2.nos1 would then throw the desired error.

Dave J.