Marco Bommeljé wrote:

> Employee base table. This shows more clearly when the "e.emp_no IS
> NULL" predicate is added to the OUTER JOIN's search condition:
>
> FROM job j LEFT OUTER JOIN employee e
> ON e.job_code = j.job_code
> AND e.job_grade = j.job_grade
> AND e.job_country = j.job_country
> AND e.emp_no IS NULL ;
>
> The query result table suggests that in this case e.emp_no values are
> read from the base table.
>

By specifying

AND e.emp_no IS NULL

in the JOIN clause the optimizer seems clever enough to completely
ignore the employee table. Despite producing this plan

PLAN JOIN (E NATURAL,J INDEX (RDB$PRIMARY2))

it would appear that employee is read neither sequentially, nor via an
index. This is certainly an interesting anomaly.

> * Is this behaviour documented anywhere?
> * Does the SQL standards say anything on this subject?

I shall bow to the wisdom of others on these two questions (or go and
research it, if necessary).

> * How can I best explain this to students in my Ib/Fb courses?

Skip lightly past it? Compare this with another database implementation?

> * Is this a feature or a bug?

That rather depends. It is probably a bug, but top marks to the
optimizer for initiative! It would certainly speed up a query if the
employee table was large and the job table was small.

> * Or is it a case for the Firebird documentation project?
>

Or the Firebird Gotcha project?


Paul
--

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird and InterBase