Thread: BUG #6496: Why the SQL is not reported as incorrect? Is there a builtin column named "name"?

The following bug has been logged on the website:

Bug reference:      6496
Logged by:          Luciano Barcellos
Email address:      luciano@geocontrol.com.br
PostgreSQL version: 8.4.8
Operating system:   Debian Squeeze (2.6.32-5-amd64)
Description:=20=20=20=20=20=20=20=20

-- Create and populate table
CREATE TABLE public.is_this_a_bug ( "name" VARCHAR(50) );
INSERT INTO public.is_this_a_bug VALUES ( '270I' );
INSERT INTO public.is_this_a_bug VALUES ( '270V' );
INSERT INTO public.is_this_a_bug VALUES ( '520I' );
INSERT INTO public.is_this_a_bug VALUES ( '520V' );
INSERT INTO public.is_this_a_bug VALUES ( '900I' );
INSERT INTO public.is_this_a_bug VALUES ( '900V' );
-- Query table: Ok
SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3) FROM public.is_this_a_bug
bug;
-- Wrong SQL. Reports no syntax error but yields unexpected data
SELECT s."name" FROM (SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3)
FROM public.is_this_a_bug bug) s;
-- Fixed SQL.
SELECT s."name" FROM (SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3) AS
"name" FROM public.is_this_a_bug bug) s;

luciano@geocontrol.com.br writes:
> -- Wrong SQL. Reports no syntax error but yields unexpected data
> SELECT s."name" FROM (SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3)
> FROM public.is_this_a_bug bug) s;

This is not a bug, exactly, although I'll agree that it's surprising
behavior.  What is happening is that the system is taking s."name" as a
coercion from the subquery's composite rowtype to the string type
"name".  We got enough complaints about that that 9.1 no longer does it,
cf this release note entry:

    Disallow function-style and attribute-style data type casts for
    composite types (Tom Lane)

    For example, disallow composite_value.text and
    text(composite_value). Unintentional uses of this syntax have
    frequently resulted in bug reports; although it was not a bug,
    it seems better to go back to rejecting such expressions. The
    CAST and :: syntaxes are still available for use when a cast of
    an entire composite value is actually intended.

There are also some possibly illuminating details here:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=543d22fc7423747afd59fe7214f2ddf6259efc62

            regards, tom lane