Thread: BUG #4618: nolock changes first column name of query result set to 'nolock'
The following bug has been logged online: Bug reference: 4618 Logged by: brian Email address: b4kramer@yahoo.com PostgreSQL version: 8.3.5 build1400 Operating system: windows xp Description: nolock changes first column name of query result set to 'nolock' Details: query1 works fine, first column in table is called movieid1: select * from netflix.ratings where movieid = 1 query2 is a bug and should work just fine, but gives error: ERROR: column "movieid" does not exist LINE 2: select * from netflix.ratings with (nolock) where movieid = ... ^ ********** Error ********** ERROR: column "movieid" does not exist SQL state: 42703 Character: 52' select * from netflix.ratings with (nolock) where movieid = 1 query3 is the similar bug, this query works but should not since field name is 'movieid' not 'nolock', result set displays 'nolock' instead of 'movieid' as well: select * from netflix.ratings with (nolock) where nolock = 1 query4 changes first column name to 'nolock' and leaves all other column names as normal: select * from netflix.ratings with (nolock) limit 1
Re: BUG #4618: nolock changes first column name of query result set to 'nolock'
From
Magnus Hagander
Date:
brian wrote: > The following bug has been logged online: > > Bug reference: 4618 > Logged by: brian > Email address: b4kramer@yahoo.com > PostgreSQL version: 8.3.5 build1400 > Operating system: windows xp > Description: nolock changes first column name of query result set to > 'nolock' > Details: > > query1 works fine, first column in table is called movieid1: > select * from netflix.ratings where movieid = 1 > > > > > query2 is a bug and should work just fine, but gives error: > ERROR: column "movieid" does not exist > LINE 2: select * from netflix.ratings with (nolock) where movieid = ... I don't know where you got "with (nolock)" from, but that's not PostgreSQL syntax - and AFAIK it's not standard SQL at all. Just remove it and you should be fine. //Magnus
Re: BUG #4618: nolock changes first column name of query result set to 'nolock'
From
Michael Fuhr
Date:
On Fri, Jan 16, 2009 at 01:22:48PM +0100, Magnus Hagander wrote: > brian wrote: > > query1 works fine, first column in table is called movieid1: > > select * from netflix.ratings where movieid = 1 > > > > query2 is a bug and should work just fine, but gives error: > > ERROR: column "movieid" does not exist > > LINE 2: select * from netflix.ratings with (nolock) where movieid = ... > > I don't know where you got "with (nolock)" from, but that's not > PostgreSQL syntax - and AFAIK it's not standard SQL at all. Just remove > it and you should be fine. "with (nolock)" is a SQL Server-ism for improving performance by using dirty reads. Thanks to MVCC, PostgreSQL doesn't need such nonsense. The second query created a table alias for netflix.ratings named "with", whose first column (presumably movieid) is aliased as "nolock". The following query probably would have worked (not that I'm recommending it): select * from netflix.ratings with (nolock) where nolock = ... -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes: > On Fri, Jan 16, 2009 at 01:22:48PM +0100, Magnus Hagander wrote: >> I don't know where you got "with (nolock)" from, but that's not >> PostgreSQL syntax - and AFAIK it's not standard SQL at all. Just remove >> it and you should be fine. > "with (nolock)" is a SQL Server-ism for improving performance by > using dirty reads. Egad :-(. All of a sudden I don't feel so bad about WITH becoming a reserved word in 8.4. At least we'll throw a syntax error for this bit of silliness, instead of silently doing something that MSSQL users won't expect. Nonetheless, it was royally stupid of MS to adopt an extension syntax that looks exactly like a table alias to a parser that doesn't think that WITH is reserved. regards, tom lane