using NOLOCK cause Cartesian joins

[Roger Gordon]

Hi,



When we use WITH (NOLOCK) at the end of table list in a SELECT query, a
Cartesian join is returned (i.e. approximately 6,500 records instead of
57, which is distressing.



We are building a database-agnostic system; while I believe PostgreSQL
is using a multiversion locking model and does not need WITH (NOLOCK),
our application also must run against MS SQL Server and that uses a
concurrency control locking model. We are using PostgreSQL 7.3.2 on
RedHat 7.2.



Any suggestions?



Thanks in advance,

Roger

************************************************** ********************************

Privileged or Confidential Information may be contained within this message..
If you have received this message in error, you are kindly requested to destroy
it immediately and inform the sender by return. You are prohibited from using
or forwarding the contents in any way whatsoever.
Opinions carried within this message that run contrary to the official business
of Emojo are solely the individual opinions of the sender, and are not sanctioned
or endorsed by the company.

************************************************** ********************************

################################################## ###############
This e-mail message has been scanned for Viruses and Content
and cleared by Trend Micro InterScan
################################################## ###############

[Greg Stark]

"Roger Gordon" <Ro***@emojo.com> writes:

When we use WITH (NOLOCK) at the end of table list in a SELECT query, a
Cartesian join is returned (i.e. approximately 6,500 records instead of
57, which is distressing.

You'll have to post an actual query and output, preferably with an "explain
analyze" before anyone can help much. I certainly don't see this problem.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

On Wed, 12 Nov 2003, Roger Gordon wrote:

When we use WITH (NOLOCK) at the end of table list in a SELECT query, a
Cartesian join is returned (i.e. approximately 6,500 records instead of
57, which is distressing.

I'm guessing you mean something like:
select *
from table1, table2 WITH (NOLOCK)
where table1.foo=table2.bar;
?

If so, it should be giving you a notice like:
adding missing FROM-clause entry for table "table2"
because you've aliased table2 as the name WITH and the first column
of said table as NOLOCK (if I'm remembering the details correctly)
so table2 isn't in scope in the where clause.

As far as we can tell a query like the above is technically illegal in
SQL but 7.3 and earlier will always add an extra entry as if you had
done from table1, table2 WITH (NOLOCK), table2. 7.4 has an option to
instead error.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly