[Firebird-devel] Computed field + index not working in WHERE

[Aivar A. <aiv...@ho...>]

Hi!
I'm using FB 2.1 superserver beta 10th oct. snapshot on WinXP
=20
If i use computed field and an index with the same expression then WHERE cl=
ause doesn't behave as it should..
=20
I'll show a demo about my problem:
=20
------
CREATE TABLE TEST_1 (  ID INTEGER NOT NULL,  LAST_DAY DATE,  COMP_LAST_DAY =
COMPUTED BY (coalesce(last_day, cast('2999-12-31' as date)))
);
=20
insert into test_1 values (1, '2007-10-10');insert into test_1 values (2, n=
ull);
select *from test_1where cast ('2007-09-09' as date) < comp_last_day;
=20
------
until now things work as expected - first row is returned by the select.
But when i add following index then no rows are returned anymore:
--------
=20
create index idx_1 on test_1 computed by(coalesce(last_day, cast('2999-12-3=
1' as date)))
=20
--------
if i remove the index or switch the places of date constant and fieldname i=
n WHERE, like this:
comp_last_day  > cast ('2007-09-09' as date)
then everything works again
=20
=20
Best greetings!
Aivar
_________________________________________________________________
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx=