View Incident Report

Serious

Reported By: Ralf Mimoun
Reported On: 10/15/2004
For: Version 4.12 Build 1

# 1875 Non-Join OR Conditions in an SQL JOIN Clause Cause Incorrect Results

I have the following SQL statement, and it returns 3 records. I don't see any problems to put that UPPER condition part to the INNER JOIN line, but when I do that I get exactly 0 records back. I repaired and reindexed the tables, but that didn't help.

Good:

SELECT ID
FROM Vorgang v
INNER JOIN Kunden k on k.id=v.kontaktid
WHERE v.ID > 23199 AND
((UPPER(k.Vorname) LIKE UPPER('Hoff%')) OR (UPPER(k.Nachname) LIKE UPPER('Hoff%')))

Bad:

SELECT ID
FROM Vorgang v
INNER JOIN Kunden k on k.id=v.kontaktid AND
((UPPER(k.Vorname) LIKE UPPER('Hoff%')) OR (UPPER(k.Nachname) LIKE UPPER('Hoff%')))
WHERE v.ID > 23199

Comments and Workarounds

The same update in 3.30 that caused this issue was also present in 4.x. The workaround is to keep the non-join clauses in the WHERE clause instead.

Resolution

Fixed Problem on 10/18/2004 in version 4.13 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap