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)
