Обсуждение: When did this behavior change (and where else might it bite me)?

In prepping for an upgrade to 9.2.3, I stumbled across this:

CREATE TABLE foo
(
  myint integer,
  string1 text,
  string2 text
)
WITH (
  OIDS=FALSE
);

insert into foo values (12345,'Y','N');

 select * from foo f where f.myint = 12345 or f.name='Y'

In 9.2.3, this returns:
ERROR:  column f.name does not exist
LINE 1:  select * from foo f where myint = 12345 or f.name='Y'

in 8.4.6 ,this returns no error (and gives me the row from the table)

It looks like the parser is short-circuiting in 8.4.6 before stumbling upon the invalid column name - EXCEPT when the
columnname is NOT a reserved word  (although according to
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html,'name' is not a reserved word). 

Example - in 8.4.6, this WILL return an error:
 select * from foo f where f.myint = 12345 or f.poopy='Y'
ERROR:  column f.poopy does not exist
LINE 2:   select * from foo f where f.myint = 12345 or f.poopy='Y'
                                                       ^

NOTE:  The problem (assuming the problem is in 8.4.6) only manifests itself when I use table aliases .
 select * from foo f where myint = 12345 or name='Y'

gives an error I would expect:
ERROR:  column "name" does not exist
LINE 2:    select * from foo f where myint = 12345 or name='Y'
                                                      ^


Any insight into what change (I poured through the release notes and couldn't find anything) may have 'fixed'  this
behaviorso that I might better head these off before my conversion? 

(yes, my example was contrived - and I did have an bug where the wrong column name was used)

On Mar 18, 2013, at 9:49 AM, Jeff Amiel <becauseimjeff@yahoo.com> wrote:

> In prepping for an upgrade to 9.2.3, I stumbled across this:
>
> CREATE TABLE foo
> (
>   myint integer,
>   string1 text,
>   string2 text
> )
> WITH (
>   OIDS=FALSE
> );
>
> insert into foo values (12345,'Y','N');
>
>  select * from foo f where f.myint = 12345 or f.name='Y'
>
> In 9.2.3, this returns:
> ERROR:  column f.name does not exist
> LINE 1:  select * from foo f where myint = 12345 or f.name='Y'
>
> in 8.4.6 ,this returns no error (and gives me the row from the table)

That's (unintentionally) an attribute style data type cast - bar.name is the same as name(bar), and tries to cast bar
totype "name" (an internal-use string type)  

Try "select foo from foo", "select name(foo::text) from foo" and "select name(foo) from foo" to see what's going on.

That was tightened up in 9.1, I think:

    Casting

    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
frequentlyresulted in bug reports; although it was not a bug, it seems better to go back to rejecting such expressions.
TheCASTand :: syntaxes are still available for use when a cast of an entire composite value is actually intended. 

Cheers,
  Steve

Jeff Amiel <becauseimjeff@yahoo.com> writes:
> select * from foo f where f.myint = 12345 or f.name='Y'

> In 9.2.3, this returns:
> ERROR:� column f.name does not exist
> LINE 1:� select * from foo f where myint = 12345 or f.name='Y'

> in 8.4.6 ,this returns no error (and gives me the row from the table)

That changed in this 9.1 patch:

commit 543d22fc7423747afd59fe7214f2ddf6259efc62
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Sun Nov 7 13:03:19 2010 -0500

    Prevent invoking I/O conversion casts via functional/attribute notation.

    PG 8.4 added a built-in feature for casting pretty much any data type to
    string types (text, varchar, etc).  We allowed this to work in any of the
    historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or
    x.text.  However, multiple complaints have shown that it's too easy to
    invoke such casts unintentionally in the latter two styles, particularly
    field selection.  To cure the problem with the narrowest possible change
    of behavior, disallow use of I/O conversion casts from composite types to
    string types via functional/attribute syntax.  The new functionality is
    still available via cast syntax.

    In passing, document the equivalence of functional and attribute syntax
    in a more visible place.

It's not that "name" is a reserved word or not, it's that it's the name
of a datatype that's considered to be of string category; so you can
cast just about anything to a name.

            regards, tom lane