Kbase P102645: SQL-92 problem with EXISTS together with OR in where clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/04/2005 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1D
OpenEdge 10.0B
Windows NT 32 Intel/Windows 2000
SYMPTOM(s):
SQL-92 problem with EXISTS together with OR in where clause
OR with EXISTS in where clause no results are returned
The following query does not return results:
SELECT * FROM PUB.SKU
WHERE ( EXISTS ( SELECT * FROM PUB.SKULookup WHERE SKULookup.SKUId = SKU.SKUId AND SKULookup.SKULId LIKE '3360372045%' )
OR SKU.SKUId LIKE '3360372045%'
OR SKU.SKUShortDesc LIKE '3360372045%'
OR SKU.SKUDesc LIKE '3360372045%'
)
The following same query without OR returns results:
SELECT * FROM PUB.SKU
WHERE ( EXISTS ( SELECT * FROM PUB.SKULookup WHERE SKULookup.SKUId = SKU.SKUId AND SKULookup.SKULId LIKE '3360372045%' )
)
CAUSE:
This is a known issue being investigated by Development
FIX:
Workaround by adding an AND which compares 2 bit values to the where clause of the EXISTS.
Example:
The following same query which in the symptoms did not return results returns now results because of the added AND 1=1 :
SELECT * FROM PUB.SKU
WHERE ( ( EXISTS ( SELECT * FROM PUB.SKULookup WHERE SKULookup.SKUId = SKU.SKUId AND SKULookup.SKULId LIKE '3360372045%' )
AND (1 = 1)
)
OR SKU.SKUId LIKE '3360372045%'
OR SKU.SKUShortDesc LIKE '3360372045%'
OR SKU.SKUDesc LIKE '3360372045%'
)