Icon View Incident Report

Serious Serious
Reported By: Michael Reisch
Reported On: 11/16/2009
For: Version 2.03 Build 6
# 3103 ElevateDB Incorrectly Re-Writing LEFT OUTER JOINs that Don't Contain Join Conditions

I use the following query. The two values was normally as parameters not as constants.

In EDB 1.09 it works fine, field pt.GUID_ID has the value 'E08E2...' that I expect, also the correct CreateDate. In EDB 2 it is a completely other value 'A5BEF...' and its corresponding CreateDate. Its also a valid record in Protocol Table.

See below the execution plan, I wonder about the green marked part. Why is it made a 'where clause' from my left outer join ON????

Original query:

SELECT P.*,
       pt.GUID_ID AS Protocol_GUID,
       pt.CreateDate AS PT_CreateDate
FROM "ProjectCompany" P
  LEFT OUTER JOIN "Protocol" pt ON (pt.GUID_ID = 'E08E2E609842E50333C63E618129EEFC')
WHERE P.Project_GUID = '2683A42884994A1397CF69FD3A388623'

Execution plan:

================================================================================
SQL Query (Executed by ElevateDB 2.03 Build 6)
 
Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================
 
SELECT ALL
"P"."GUID_ID" AS "GUID_ID",
"P"."GUID_Parent" AS "GUID_Parent",
"P"."Project_GUID" AS "Project_GUID",
"P"."Address_GUID" AS "Address_GUID",
"P"."Contact_GUID" AS "Contact_GUID",
"P"."PPLFile_GUID" AS "PPLFile_GUID",
"P"."RowPos" AS "RowPos",
"P"."Description" AS "Description",
"P"."ObjType" AS "ObjType",
"P"."InstructState" AS "InstructState",
"P"."Source" AS "Source",
"P"."SourceID" AS "SourceID",
"P"."StartDate" AS "StartDate",
"P"."EndDate" AS "EndDate",
"P"."RealStartDate" AS "RealStartDate",
"P"."RealCheckDate" AS "RealCheckDate",
"P"."RealProgress" AS "RealProgress",
"P"."FileName" AS "FileName",
"P"."FilePath" AS "FilePath",
"P"."CreateDate" AS "CreateDate",
"P"."ModifyDate" AS "ModifyDate",
"pt"."GUID_ID" AS "Protocol_GUID",
"pt"."CreateDate" AS "PT_CreateDate"
FROM "ProjectCompany" AS "P", "Protocol" AS "pt"
WHERE "P"."Project_GUID" = '2683A42884994A1397CF69FD3A388623' AND
("pt"."GUID_ID" = 'E08E2E609842E50333C63E618129EEFC')
 
Source Tables
-------------
 
ProjectCompany (P): 3168 rows
Protocol (pt): 724 rows
 
Result Set
----------
 
The result set was insensitive and read-only
The result set consisted of zero or more rows
 
Filtering
---------
 
The following filter condition was applied to the ProjectCompany table:
 
"P"."Project_GUID" = '2683A42884994A1397CF69FD3A388623' [Index scan
(ProjectCompany.idxProject):, 21 keys, 4096 bytes estimated cost]
 
The following filter condition was applied to the Protocol table:
 
("pt"."GUID_ID" = 'E08E2E609842E50333C63E618129EEFC' [Index scan
(Protocol.PrimaryKey):, 1 keys, 4096 bytes estimated cost])
 
Joins
-----
 
The driver table was the ProjectCompany (P) table
 
The optimizer attempted to re-order the joins to a more optimal order
 
The joins were already in the most optimal order
 

Result set I/O statistics
-------------------------
 
Total rows visited: 21
 
Row buffer manager
 
Max buffer size: 395264 Buffer size: 32424
 
Hits: 21   Misses: 0   Hit ratio: 1
 
Reads: 0   Bytes read: 0
 
Writes: 0   Bytes written: 0
 
Index page buffer manager
 
Max buffer size: 65536 Buffer size: 4096
 
Hits: 21   Misses: 0   Hit ratio: 1
 
Reads: 0   Bytes read: 0
 
Writes: 0   Bytes written: 0
 
================================================================================
21 row(s) returned in 0 secs
================================================================================



Comments Comments and Workarounds
This was an optimization added in EDB 2.03 - if a left outer join expression does not contain an actual join expression (Column <Comparison> Column) and the only column references are those of the target table in the join, then EDB will move the expression to the WHERE clause because it is equivalent to what would be produced by the left outer join.

However, there is a small error in this processing - if there is only one condition in the JOIN, such as the case with your JOIN,
then this optimization is not setting the join type back to a CROSS JOIN properly, so the joins are not actually being executed properly, resulting in incorrect results.

You can use this query as a workaround:

SELECT P.*,
pt.GUID_ID AS Protocol_GUID,
pt.CreateDate AS PT_CreateDate
FROM "ProjectCompany" P, "Protocol" pt
WHERE (pt.GUID_ID = 'E08E2E609842E50333C63E618129EEFC') AND
(P.Project_GUID = '2683A42884994A1397CF69FD3A388623')


Resolution Resolution
Fixed Problem on 11/17/2009 in version 2.03 build 7


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

Image