select
 cast((current_date-date'2000-01-01') as integer) issue1,
 cast((current_date-timestamp'2000-01-01 00:00:00') year as integer) issue2
from dummy

Issue1:

EDB executes the ambiguous expression (current_date-date'2000-01-01')
without a complaint and returns a DAY interval. Since the result of
subtracting a datetime value from a datetime value can be any valid
interval, an interval qualifier is required in such expressions.

The correct syntax is:

( <datetime value expression> - <datetime term> ) <interval qualifier>

Valid intervals when casted to integer would be any single field interval:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
MSECOND (EDB extension)

Issue2:

The expression (current_date-timestamp'2000-01-01 00:00:00') is invalid,
since the the two operands are not comparable (they have different datetime
fields). EDB executes the expression without a complaint, but returns the
null value. The engine should raise a proper exception here.

Ole Willy Tuv

Ole,

<< EDB executes the ambiguous expression (current_date-date'2000-01-01')
without a complaint and returns a DAY interval. Since the result of
subtracting a datetime value from a datetime value can be any valid
interval, an interval qualifier is required in such expressions. >>

This is intentional to make it easier for DBISAM migration.

<< The expression (current_date-timestamp'2000-01-01 00:00:00') is invalid,
since the the two operands are not comparable (they have different datetime
fields). EDB executes the expression without a complaint, but returns the
null value. The engine should raise a proper exception here. >>

I'm checking this one out this evening.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I'm checking this one out this evening. >>

I understand, but it doesn't make the expression less ambiguous.

I think you'll be better off in the end by removing this kind of ambiguity
from EDB.

Ole Willy Tuv

Tim,

<<< I'm checking this one out this evening. >>>

<< I understand, but it doesn't make the expression less ambiguous. >>

Sorry, I quoted the wrong part of your post.

I was actually responding to:

<<This is intentional to make it easier for DBISAM migration.>>

Ole Willy Tuv

Ole,

<< Sorry, I quoted the wrong part of your post. >>

I knew what you meant.

--
Tim Young
Elevate Software
www.elevatesoft.com