473,686 Members | 3,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Mysterious mystery -- 2002-11-08/2002-11-09

I have function that returns a table of information about properties. The
data comes from three different tables -- addresses (called PropertyID),
property characteristics , and events concerning those properties (sales,
appraisals, etc.), plus a table that maps one representation of property
types into another. The records are selected on the basis of location
(longitude & latitude), property type, event type, and a range of
event dates (upper and lower date specified). There are tens of millions
of records of all types, and almost any location, property type, event type
and date range will yield records.

The heart of it is a cursor that selects records from joins on this basis:

SELECT <a bunch of fields>
FROM Property d
JOIN PropTypeMap ptm ON ptm.PropertyTyp eID = d.PropertyTypeI D
JOIN PropertyID a ON a.PropID = d.PropID
JOIN Event e1 ON e1.PropID = d.PropID
LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE
d.LastSaleDate >= @LoDate
AND a.GeoLongitude BETWEEN @LowerLon AND @UpperLon
AND a.GeoLatitude BETWEEN @LowerLat AND @UpperLat
AND ptm.PropCategor y = @PropType
AND a.GeoMatch <= @MinGeoQuality

AND e1.EventTypeID = @SaleEventType
AND e1.TransactionT ype = 'R'
AND e1.EventDt BETWEEN @LoDate AND @HiDate
AND e1.EventAmt > 0

AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

Each property has one PropertyID record, one Property record, and N Event
records (average perhaps five).

What is the mystery? If @HiDate, which is the upper end of the time window,
is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,
oodles of records are found. I get the same query plan for either one, and
based on the content of the data, they should return almost exactly the same
set of records -- exactly the same set in almost all cases, in fact.

Is 2002-11-08/09 some sort of magic dividing point? I have replicated this
on the large database and on a smaller test version on another SQL Server.
(SQL Server 2000) I dropped the indexes and tried it, and
the same thing happened. This is driving me crazy!
Jul 20 '05 #1
3 1788
[posted and mailed, please reply in news]

Jim Geissman (ji**********@c ountrywide.com) writes:
What is the mystery? If @HiDate, which is the upper end of the time
window, is 2002-11-08 or earlier, nothing is returned. If it's
2002-11-09 or later, oodles of records are found. I get the same query
plan for either one, and based on the content of the data, they should
return almost exactly the same set of records -- exactly the same set in
almost all cases, in fact.

Is 2002-11-08/09 some sort of magic dividing point? I have replicated
this on the large database and on a smaller test version on another SQL
Server. (SQL Server 2000) I dropped the indexes and tried it, and the
same thing happened. This is driving me crazy!


Without access to the database, or a script that reproduces the problem,
there is much left for me, but to guess.

And the obvious guess is that the rows you find are from 2002-11-08, but
have a time porttion, and thus are > '2002-11-08 00:00:00'.

However, I notice one thing with your query which may not be correct:

LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.

Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.

There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Thanks, Erland. You hit the nail on the head. I was naively assuming that
the right side of the Left Outer Join didn't matter, but when I removed the
second condition in the excerpt below, the records all appeared. As you
said, it was because the FROM was being evaluated before the WHERE. Moving
the condition to ON fixed it. Thanks. I wonder if I have any other
scripts that make this mistake....
However, I notice one thing with your query which may not be correct:

LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.

Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.

There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.

Jul 20 '05 #3
Jim Geissman (ji**********@c ountrywide.com) writes:
Thanks, Erland. You hit the nail on the head. I was naively assuming
that the right side of the Left Outer Join didn't matter, but when I
removed the second condition in the excerpt below, the records all
appeared. As you said, it was because the FROM was being evaluated
before the WHERE. Moving the condition to ON fixed it. Thanks. I
wonder if I have any other scripts that make this mistake....


A very common mistake to make. I recall that I did it myself a few
times when I switched to the new syntax from the old *= crap.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3417
by: Viorel | last post by:
For me is a little bit mysterious how work encoding and decoding functions, what is underneath of their calling? Encoding1.GetBytes(string1); in particularly ASCII.GetBytes(string1) Encoding1.GetChars(string1); Encoding1.GetChars(arrayofbytes1);
7
6236
by: jmm-list-gn | last post by:
Hello, <http://www.asaom.edu> There are some curious spacing issues with the top bar (gray) and the main navigation bar. The most significant is how the nav bar looks in Opera v6 (win2k): the text portion is has extra space at the top that I cannot remove. As a result the text portion is pushed below the background bar by a few pixels. This does not occur in Mozilla/Firefox, or IE6. (I have ordered Opera v7.) Is this a known issue? The...
0
1549
by: public heath vb developer | last post by:
We have a solution with 29 projects including a main menu and 28 dlls. One of the projects (Project B) was created by copying an existing project (Project A), making changes including the assembly name, root namespace, form name, and internal code. When the new project (Project B) was added to the solution and a reference to it was attempted to be added to the main menu project (Project MainMenu), the new project (Project B) reference did not...
0
1763
by: William Wisnieski | last post by:
Hello Everyone: I'm having a very strange problem occurring with my Access 2000 database. I call it the "mystery record." Here's the story: I have a query by form that returns a record set in a datasheet. The user double clicks on a row in that datasheet and a main form (pop up) opens bound to a table with a continuous subform bound to a query.
115
7596
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical transform function. When compiling under gcc on my big-endian PowerPC (Mac OS X), declaring this array as "static" DECREASES the transform throughput by around 5%. However, declaring it as "static" on gcc/Linux/Intel INCREASES the throughput by...
14
2784
by: jojoba | last post by:
Hi, I hope this post is ok for this group. Here's my deal: I have two computers on my LAN at home. One desktop. One laptop. Both computers are wireless enabled (and wired enabled too). I have running a fairly simple HTTP server (written in python) that i
2
2060
by: Yannick Turgeon | last post by:
Hello, I'm using A97 (french version) on XP (english version). Since this afternoon, all the Access built-in french-equivalent function are generating a "Sub or Function not defined" error. An exemple: IIf(1=1,"bob","joe") is ok but it's french equivalent VraiFaux(1=1,"bob","joe") generates the error. It's not linked to the PC since other applications with this function
5
3975
by: Sergey | last post by:
Hi everyone, It looks like a mystery, but I hope there should be some explanation to the issue I experience. Once in a blue moon a random stored procedure stops working the way it was designed. The stored procedure code looks unchanged. Recompiling, altering the code do not help. It looks like it is simply does not execute some part of it or does not run at all. However it returns no errors. One time a procedure entered into infinite...
0
1106
by: Clarence | last post by:
I'm having problems with JPype and am trying to change the way it creates Python classes as proxies for Java classes and interfaces. I'm trying to get around "inconsistent mro" problems, but in doing so, I've run into a real mystery. Here's the original code. It first makes a metaclass, then makes a class using that metaclass. ############## metaclass = type.__new__(type, name+"$$Static", tuple(meta_bases),
1
2384
by: Phil Clingenpeel | last post by:
Hi, I wrote a program to translate a binary file (for which I have the text version) into another binary file (I needed a new format) and text file. There is no division involved, just some nested loops with data being transferred from one buffer to another, to simplify the creation of the desired output files. The outer (main) loop functions properly 9,947 times and then 'something' happens in the middle of an interior loop,...
0
9050
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8932
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7598
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5795
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4307
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4528
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.