Обсуждение: 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

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

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