Hi.

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")

6. Execute SQL (cloned from 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")) OR /* !!! OR !!! */
"T1"."F1" IN (SELECT ALL "F1" AS "F1" FROM "T2" WHERE ("T1"."F2" <> "T2"."F2" OR -------------
"T1"."F3" <> "T2"."F3") ORDER BY "F1")

7. Next 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")

--------------------------------------------
Note, that EDB-1.01b1 has not such problems.
--------------------------------------------

Best regards,
Andrey

Andrey,

<< 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). >>

We push NOT conditions out of the expressions in order to allow for easier
query optimizations since NOT conditions tend to complicate things.
However, there does seem to be an error with the outer OR being converted to
an AND.   I will check it out.

--
Tim Young
Elevate Software
www.elevatesoft.com