Thread: BUG #2755: strange select behavior

The following bug has been logged online:

Bug reference:      2755
Logged by:          Basil Evseenko
Email address:      evseenko@sysoft.ru
PostgreSQL version: 8.1.4
Operating system:   Linux 2.6
Description:        strange select behavior
Details:

# \d tables.cart

   Column    |           Type           |
Modifiers
-------------+--------------------------+-----------------------------------
--------------------------------
 id          | integer                  | not null  default
nextval('tables.cart_id_seq'::regclass)
 user_id     | integer                  | not null
 track_id    | integer                  | not null
 price       | numeric(12,2)            | default 0.0
 size        | integer                  | default 0
 expire_date | timestamp with time zone | default (now() + '1
day'::interval)
 timestamp   | timestamp with time zone | default now()
 ordered     | integer                  | not null default 0
 album_id    | integer

# \d tables.download
 Column   |           Type           |
Modifiers
-----------+--------------------------+-------------------------------------
----------------------------------
 id        | integer                  | not null default
nextval('tables.download_id_seq'::regclass)
 cart_id   | integer                  | not null
 http_info | character varying        |
 size      | integer                  | default 0
 timestamp | timestamp with time zone | default now()

# SELECT count(1) from tables.download where cart_id in (select cart_id from
tables.cart where user_id=1);
 count
-------
  2860
(1 row)

# select cart_id from tables.cart where user_id=1;
ERROR:  column "cart_id" does not exist


Why in the first case select doestn't raise syntax error, but use cart_id
from tables.download?
Is this bug or a feature?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, Nov 12, 2006 at 04:49:33PM +0000, Basil Evseenko wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2755
[...]
> # \d tables.cart
[no cart_id field]

> # \d tables.download
[cart_id field]

> # SELECT count(1) from tables.download where cart_id in (select cart_id from
[...]
> # select cart_id from tables.cart where user_id=1;
> ERROR:  column "cart_id" does not exist

> Why in the first case select doestn't raise syntax error, but use cart_id
> from tables.download?
> Is this bug or a feature?

AFAIK this is SQL spec. The unqualified column name in the first query
is resolved to the FROM in the outer query (because the inner query has
no table with such a column). So no, I don't think it's a bug.

In the second query there is no table with a "cart_id" column.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFWAWwBcgs9XrR2kYRAthwAJ9ksfDABoH+A8KGJh3/kwwsQsdItwCdHsBT
Yl2kyzm7pblE4fyeLPlKarI=
=7r9p
-----END PGP SIGNATURE-----