Postgres Pro
Downloads
Home   >   mailing lists

Thread: using NOLOCK cause Cartesian joins

using NOLOCK cause Cartesian joins

From
"Roger Gordon"
Date:

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

Re: using NOLOCK cause Cartesian joins

From
Greg Stark
Date:
"Roger Gordon" <Roger@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

Re: using NOLOCK cause Cartesian joins

From
Stephan Szabo
Date:
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.