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

---

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

---

 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