Thread: Why does this SQL work?

On Tue, May 12, 2015 at 12:26:15AM +0800, Anil Menon wrote:
> manualscan=> select count(*) From msgtxt where msgid in (
> manualscan(>         select msgid From courier where org_id=3
> manualscan(>         )
> manualscan->  ;
>  count
> -------
>  10225
> (1 row)
> manualscan=> select count(*) From public.msgtxt where msgid in (select
> msgid From ver736.courier where org_id=3);
>  count
> -------
>  10225
> (1 row)
> Please note, there is no msgid col in courier table. Which brings the
> question why does this SQL work? An "select msgid From courier where
> org_id=3" by itself gives error column "msgid" does not exist.

This works because this is correlated subquery.

You should have always use aliases to avoid such errors. Like here:
select count(*) From msgtxt as m where m.msgid in (
    select c.msgid from courier c where c.org_id = 3
);

Your query is equivalent to:
select count(*) From msgtxt as m where m.msgid in (
    select m.msgid from courier c where c.org_id = 3
);
which returns all rows from msgtxt if there is at least one row in
courier with org_id = 3.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Tue, May 12, 2015 at 04:07:52PM +0800, Anil Menon wrote:
> Thank you very much - looks like I will have to prefix all cols.

You should anyway.
Queries with unaliased columns make it impossible to analyze without
in-depth knowledge of the database.

Consider:

select c1, c2, c3, c4, c5
from t1 join t2 using (c6)
where c7 = 'a' and c8 < now() and c9;

which fields belong to which tables? what indexes make sense? it's
impossible to tell. if the column references were prefixed with table
name/alias - it would become possible, and easy, even, to figure out
what's going on.

depesz