Subject RE: [firebird-support] strange problem with extract(month from "date")
Author Alexandre Benson Smith
At 09:59 29/01/2004 +1100, you wrote:

>No, what is happening with you is that you are getting an exception message
>from trying to pass a null to EXTRACT(). That was Claudio's fix. An
>exception is preferable to a crash. AFAIK, there is not yet any fix
>implemented to silently return null under these circumstances, but you
>should ask on fb-devel.

No Helen, sorry if I can't express my ideas clearly.... my english is bad...

I do not have problems on extract on null fields....

if I do:

drop table test1;
create table test1(id integer, adate date);
insert into test1 values(1, null);
select id, adate, extract(month from adate) from test1;

============ =========== =======

1 <null> <null>

this WORKS OK !

if I create another table

create table test2(id integer);
insert into test2 values(2);
extract(month from test1.adate)
test2 left join
test1 on ( =;

I got the error....

SQL> select
CON> extract(month from test1.adate)
CON> from
CON> test2 left join
CON> test1 on ( =;

Statement failed, SQLCODE = -833

expression evaluation not supported

The problem ocurrs when a field is null because of an outer join....

The reply Thomas sent, was EXACTLY what happens with me... I will go to
firebird-devel and asks Claudio...

> >I take another look on source forge bug tracker and did not found anithing
> >relevant...
> >
> >Did some googling with no sucess...
> >
> >But as you said the fellow developers knows what is happening... So I think
> >this will be fixed when they have time and oportunity... I think before 1.5
> >final release... I have considered it a bug, but post it here to see if
> >someone have knowledge about it... And know some workaround...
>I guess the only workarounds for Fb 1.0.3 would be to use a selectable SP,
>so that you can intercept the nulls and deal with them before calling
>EXTRACT()...or to use correlated subqueries instead of the left join.

I will try...

Thank you very much for spent your time with this, and sorry if I couldn't
make clear what is happening with me...

Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil


Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004

[Non-text portions of this message have been removed]