Alexandre,

[snip]

I opened a thread on 26.06.2003 in firebird-devel with a subject
"Possibly a bug: "Expression evaluation not supported" that matches
exactly your test case. I repost here Claudio's last message about
that issue. Don't know if his suggestion ever went into the code
base.

<quote>
Hi, Thomas. The data types involves don't matter, provided that you have a
date/time field in the optional table and that you call extract() on this
field.
I managed to produce it with:

select t1.id2, t1.date1, t2.date1 from t1 left join t2
on t1.id2 = t2.id2
where extract(month from t2.date1) <> 1;

As you can see, the key is to call extract() in a field that can be null,
but in the outer join. I made a simpler statement and put one row in t1 and
no rows in t2, enough to have a null row in t2 with the outer join... I
always try to find the simplest case to debug:

select t1.id2, extract(month from t2.date1)
from t1 left join t2 on t1.id2 = t2.id2;

>>> So it seems that using extract(month from t_2.date1) in
>>> conjunction with the LEFT OUTER JOIN fails.
>>> This applies to FB 1.0.2, 1.0.3 and FB 1.5 RC3.
>>> Reported by Andreas Pohl in a german newsgroup.

Andreas only found another head of the hydra named "null signaling in outer
joins", courtesy of Borland, probably the change that most bugs has caused.
In GDML there's no concept of outer join. You can produce it with two nested
loops. Therefore, the engine didn't have an internal notion of outer joins.
Borland introduced it between v4 and v5 to satisfy SQL requirements. The way
they used to signal null in the optional table (when there's no record to
match the one in the mandatory table) is a hack.

I debugged the v1 engine because you said it's reproducible in all versions
and currently the HEAD branch in CVS is an unbuildable bloody mess IMO.
The workaround is the following. Attention Dmitry:

evl.c/evl.cpp
EVL_expr(...)
case nod_extract:
...
if (!value)
{
request->req_flags |= req_null;
impure->vlu_misc.vlu_short = 0;
return &impure->vlu_desc;
}

The test becomes
if (!value || request->req_flags & req_null)

Furthermore, I'm going to kill two birds at once: UDF's have the same
problem with null fields in outer joins, they can't detect the presence of
null and get garbage instead if descriptors are used:

fun.c/fun.cpp
FUN_evaluate(...)
...
if (tail->fun_mechanism == FUN_descriptor)
{
*ap++ = (SLONG*) input;
arg_ptr = (SLONG*) ap;
continue;
}

The first line in the body becomes
*ap++ = (request->req_flags & req_null) ? 0 : (SLONG*) input;

I think the first bug (extract v/s outer join) isn't in our tracker but the
second one is registered.

So much for bizarre outer joins copyright Borland.
:-)

C.
--
Claudio Valderrama C.
Consultant, SW developer.
www.cvalde.com - www.firebirdSql.org
</quote>