View Incident Report

Serious

Reported By: Paul Grimstrup
Reported On: 1/23/2001
For: Version 2.06 Build 1

# 718 Using Non-Join Expressions in SQL JOIN Conditions Causes Incorrect Results

The expected results from the SQL below are:

1,"Bob the Builder",$0.00,1,1,1/01/2000,TIMBER,"Construction Timber"
2,"Pete the Plumber",$499.99,,,, <No orders within the date range>
3,"Eric the Electrician",$599.98,,,, <No orders within the date range>
<#4 excluded because it fails the WHERE Clause>

But the actual results are:

1,"Bob the Builder",$0.00,1,1,1/01/2000,TIMBER,"Construction Timber"
<all records not matching the second part of the JOIN clause have been
excluded from the resultset>

You might think that the query could be acheived with a WHERE clause, which in fact it can't. This query answers the problem statement 'Give me customer activity for the month of January 2000, including all customers which have no activity but a positive Balance within that month'. The LEFT OUTER JOIN clause should filter the Orders table based on the entire JOIN condition.

The Actual Results set equates to the problem statement 'Give me customer activity for the month of January 2000'. Adding a WHERE clause to the effect of 'WHERE OrderDate BETWEEN a AND b' would filter out all records where there is no activity, as well as records where there is activity but not within the given date range.

DROP TABLE CUSTOMERS;
DROP TABLE ORDERS;

CREATE TABLE CUSTOMERS
(
  CustomerID INTEGER,
  Name VARCHAR(20),
  Balance MONEY
);

CREATE TABLE ORDERS
(
  CustomerID INTEGER,
  OrderID INTEGER,
  OrderDate TIMESTAMP,
  ItemCode VARCHAR(20),
  Narration VARCHAR(20)
);

INSERT INTO CUSTOMERS VALUES(1, 'Bob the Builder', 0);
INSERT INTO CUSTOMERS VALUES(2, 'Pete the Plumber', 499.99);
INSERT INTO CUSTOMERS VALUES(3, 'Eric the Electrician', 599.98);
INSERT INTO CUSTOMERS VALUES(4, 'Paul the PC Guy', 0);

INSERT INTO ORDERS VALUES(1, 1, '2000-01-01', 'TIMBER', 
'Construction
Timber');
INSERT INTO ORDERS VALUES(1, 1, '2000-02-01', 'NAILS', '50mm Nails');
INSERT INTO ORDERS VALUES(4, 1, '2000-03-01', 'PC', 'IBM PC');
INSERT INTO ORDERS VALUES(4, 1, '2000-04-01', 'MOUSE', 
'Serial Mouse');

SELECT *
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND O.
OrderDate
BETWEEN '2000-01-01' AND '2000-01-31'
WHERE C.Balance <> 0 OR O.CustomerID IS NOT NULL

Resolution

Fixed Problem on 2/5/2001 in version 2.07 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap