Icon View Incident Report

Serious Serious
Reported By: Andrey Lesitsyn
Reported On: 4/16/2007
For: Version 1.02 Build 1
# 2329 Combining Correlated Sub-Queries with OR Operator Causes Improper Query Execution

EDB-1.02b1 instead of EDB-1.01b1 has problems with execution plan building. The problem must be clean with next example (using edbmgr.exe).

1. CREATE TABLE T1
(F1 VARCHAR(10),
 F2 VARCHAR(10),
 F3 VARCHAR(10))

2. INSERT INTO T1 (F1, F2, F3) VALUES('1', '2', '3')

3. CREATE TABLE T2 
(F1 VARCHAR(10),
 F2 VARCHAR(10),
 F3 VARCHAR(10))

4. Execute SQL:
SELECT T1.F1, T1.F2, T1.F3 FROM T1
WHERE NOT(T1.F1 IN (SELECT F1 from T2))
OR T1.F1 IN (SELECT F1 FROM T2
             WHERE NOT (T1.F2 = T2.F2 AND T1.F3 = T2.F3))
-- note the "OR" operand at the beginning of last line

5. Look at the execution plan:
SELECT ALL
"T1"."F1" AS "F1",
"T1"."F2" AS "F2",
"T1"."F3" AS "F3"
FROM "T1"
WHERE ("T1"."F1" NOT IN
(SELECT ALL "F1" AS "F1" FROM "T2" ORDER BY "F1"))
AND /* must be "OR" */
"T1"."F1" IN (SELECT ALL "F1" AS "F1" FROM "T2"
              WHERE ("T1"."F2" <> "T2"."F2" OR 
                     "T1"."F3" <> "T2"."F3") ORDER BY "F1")



Resolution Resolution
Fixed Problem on 4/18/2007 in version 1.03 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image