Hi there,

I am using FB 1.5 SS RC8 on WinXP home edition

I have a problem...

When I do some left joins between tables and try to use extract(month from
date_field) and the field contains null I get the following message.

Statement failed, SQLCODE = -833

expression evaluation not supported

the problematic queries are...

select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date,
Extract(Month from t3.test_date), Extract(Year from t3.test_date)
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id);

select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id)
where
t2.test_month = extract(month from t3.test_date);


if I do just a select from one table and extract from the fields with null
I got no problem.

I tooked a look on firebird WhatsNew.txt and found:

* Fixed bug SF #538201.
Crash with extract from null as date.
Contributor(s):
Claudio Varderrama <cvalde at usa.net>

The server does not crash, I don't loose the connection, but the error is
reported and the fetch stops....

I run the problematic query on IBOConsole, IBConsole and ISQL all with the
same error...


As far I can see the problems occurs when I try to extract the month from
the t3's date field and t3 has no records for the parent on t1 (left
join)... Maybe I am short-sighted....

If all t1 records have a correspondence on t3 no error occurs...

bellow are some examples.... sorry a bit long, but you can copy and paste
ate ISQL to sse the results...

commit;
drop table test_1;
drop table test_2;
drop table test_3;
commit;

create table test_1 (id integer, foo varchar(10));
commit;

insert into test_1 (id, foo) values (1, 'dog');
insert into test_1 (id, foo) values (2, 'cat');
insert into test_1 (id, foo) values (3, 'fish');
insert into test_1 (id, foo) values (4, 'monkey');
insert into test_1 (id, foo) values (5, 'horse');
commit;

create table test_2 (id integer, test_month integer);
commit;

insert into test_2 (id, test_month) values (1, 1);
insert into test_2 (id, test_month) values (2, 1);
insert into test_2 (id, test_month) values (3, 1);
commit;

Create Table Test_3 (id integer, test_date timestamp);
commit;

insert into test_3 (id, test_date) values (1, '2004-01-01');
insert into test_3 (id, test_date) values (2, '2004-01-31');
insert into test_3 (id, test_date) values (3, '2004-01-31');
insert into test_3 (id, test_date) values (4, '2004-02-15');
insert into test_3 (id, test_date) values (5, null);
commit;



select * from test_1;

select * from test_2;

select * from test_3;

select
id, test_date, extract(month from test_date)
from
test_3
where
extract(month from test_date) = 1;
/* Ok just 3 rows returned and no error */



select
id, test_date, extract(month from test_date)
from
test_3
where
extract(month from test_date) is null;
/* Ok just one returned and no error */

select
id, test_date, extract(month from test_date)
from
test_3;
/* OK all rows returned and no error */


select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id);
/* OK all rows returned and no error */


select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date,
Extract(Month from t3.test_date), Extract(Year from t3.test_date)
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id);
/* OK all rows returned and no error */

select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id)
where
t2.test_month = extract(month from t3.test_date);
/* OK all rows returned and no error */

commit;

insert into test_1 (id, foo) values (6, 'zebra');

/* HERE IS THE PROBLEM, AFTER I INSERT THIS ROW, IF I SEND THE SAME QUERIES
I GET THE ABOVE ERROR */

commit;

select * from test_1;

select * from test_2;

select * from test_3;

select
id, test_date, extract(month from test_date)
from
test_3
where
extract(month from test_date) = 1;
/* Ok just 3 rows returned and no error */

select
id, test_date, extract(month from test_date)
from
test_3
where
extract(month from test_date) is null;
/* Ok just 1 row returned and no error */

select
id, test_date, extract(month from test_date)
from
test_3;
/* OK all rows returned and no error */


select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id);
/* Ok all rows returned and no error */

select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date,
Extract(Month from t3.test_date), Extract(Year from t3.test_date)
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id);
/* Here I got an error */

select
t1.id, t1.foo,
t2.id, t2.test_month,
t3.id, t3.test_date
from
test_1 t1 left join
test_2 t2 on (t2.id = t1.id) left join
test_3 t3 on (t3.id= t1.id)
where
t2.test_month = extract(month from t3.test_date);
/* Here I got an error */

commit;


see the results and the error I got....

CON>
CON> select
CON> t1.id, t1.foo,
CON> t2.id, t2.test_month,
CON> t3.id, t3.test_date
CON> from
CON> test_1 t1 left join
CON> test_2 t2 on (t2.id = t1.id) left join
CON> test_3 t3 on (t3.id= t1.id);

ID FOO ID TEST_MONTH ID TEST_DATE
============ ========== ============ ============ ============ ===========

1 dog 1 1 1 1-JAN-2004
2 cat 2 1 2 31-JAN-2004
3 fish 3 1 3 31-JAN-2004
4 monkey <null> <null> 4 15-FEB-2004
5 horse <null> <null> 5 <null>
6 zebra <null> <null> <null> <null>

SQL> /* Ok all rows returned and no error */
CON>
CON> select
CON> t1.id, t1.foo,
CON> t2.id, t2.test_month,
CON> t3.id, t3.test_date,
CON> Extract(Month from t3.test_date), Extract(Year from t3.test_date)
CON> from
CON> test_1 t1 left join
CON> test_2 t2 on (t2.id = t1.id) left join
CON> test_3 t3 on (t3.id= t1.id);

ID FOO ID TEST_MONTH ID TEST_DATE
EXTRA
CT EXTRACT
============ ========== ============ ============ ============ ===========
=====
== =======

1 dog 1 1 1 1-JAN-2004
1 2004
2 cat 2 1 2 31-JAN-2004
1 2004
3 fish 3 1 3 31-JAN-2004
1 2004
4 monkey <null> <null> 4 15-FEB-2004
2 2004
5
horse <null> <null> 5 <null> <nul
l> <null>
Statement failed, SQLCODE = -833

expression evaluation not supported
SQL> /* Here I got an error */
CON>
CON> select
CON> t1.id, t1.foo,
CON> t2.id, t2.test_month,
CON> t3.id, t3.test_date
CON> from
CON> test_1 t1 left join
CON> test_2 t2 on (t2.id = t1.id) left join
CON> test_3 t3 on (t3.id= t1.id)
CON> where
CON> t2.test_month = extract(month from t3.test_date);

ID FOO ID TEST_MONTH ID TEST_DATE
============ ========== ============ ============ ============ ===========

1 dog 1 1 1 1-JAN-2004
2 cat 2 1 2 31-JAN-2004
3 fish 3 1 3 31-JAN-2004
Statement failed, SQLCODE = -833

expression evaluation not supported
SQL> /* Here I got an error */
CON>
CON> commit;
SQL>


Without the record id = 6 on table_1 (wich is the only one that don't have
a reference on table_3) all runs fine...

Any ideas ????

Sorry for the long post...


TIA


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


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


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