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

[Jim Geissman]

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.PropertyTypeID = d.PropertyTypeID
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.PropCategory = @PropType
AND a.GeoMatch <= @MinGeoQuality

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

AND e2.EventTypeID = @AssessmentEventType
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!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jim Geissman (ji**********@countrywide.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 = @AssessmentEventType
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

[Jim Geissman]

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 = @AssessmentEventType
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]

Jim Geissman (ji**********@countrywide.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