#121 Wrong SQL generated for non-required reference attributes

[Nando Dessena]

The following IQL query:

SELECT * FROM TPERSON WHERE NAME LIKE 'J%' ORDER BY Employer.Id

translates into SQL as:

SELECT t1.Class AS Class, t1.Id AS Id FROM Contact t1, Person t2
WHERE (t1.Class = t2.Class AND t1.Id = t2.Id AND t1.Class = 'TPerson') AND (t1.Name LIKE 'J%')
ORDER BY t2.EmployerId

which is correct. However, the IQL query:

SELECT * FROM TPERSON WHERE NAME LIKE 'J%' ORDER BY Employer.Name

which only changes the ordering criteria, is translated as:

SELECT t1.Class AS Class, t1.Id AS Id FROM Contact t1, Person t2, Contact t3
WHERE (t1.Class = t2.Class AND t1.Id = t2.Id AND t2.EmployerClass = t3.Class AND t2.EmployerId = t3.Id AND t1.Class = 'TPerson') AND (t1.Name LIKE 'J%')
ORDER BY t3.Name

which is wrong, as the inner join implied in the FROM clause will cut off all employees without an employer from the result set.

Generating explicit SQL inner and outer joins (instead of the current implied inner joins) should fix the problem:

SELECT t1.Class AS Class, t1.Id AS Id
FROM Contact t1 JOIN Person t2 ON (t1.Class = t2.Class AND t1.Id = t2.Id)
LEFT OUTER JOIN Contact t3 ON (t2.EmployerClass = t3.Class AND t2.EmployerId = t3.Id)
WHERE (t1.Class = 'TPerson') AND (t1.Name LIKE 'J%')
ORDER BY t3.Name