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)
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 |