When did this behavior change (and where else might it bite me)?

From: Jeff Amiel <becauseimjeff(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: When did this behavior change (and where else might it bite me)?
Date: 2013-03-18 16:49:23
Message-ID: 1363625363.24898.YahooMailNeo@web161403.mail.bf1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 column name 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 behavior so 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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2013-03-18 17:37:48 Re: When did this behavior change (and where else might it bite me)?
Previous Message Steve Erickson 2013-03-18 16:44:04 Concurrent updates