On 17-4-2019 10:11, Maya Opperman maya@...
[firebird-support] wrote:

> I am (EVENTUALLY), getting around to upgrading from Firebird 2.5 to
> Firebird 3.
>
> I purchased the conversion guide, and so far so good, except for this
> problem, which I cannot find a mention of anywhere, no online, nor in
> conversion the guide:
>
> SELECT C.ENDDATE
> FROM CMN_PERIODS C
> WHERE (C.PERIOD = mod(:IPeriod, 100))
>     AND (C.YR  = (:IPeriod / 100))
>
> The error is:
>
> SQL error state =42000
> Expression evaluation not supported.
> Invalid data type for division in dialect 3

It sounds like the datatype of the parameter can't be inferred. Have you
tried using (C.YR = :IPeriod / 100)?

> Eventually, I figured out that this possibly happens on the Firebird 3
> server when :IPeriod is null, since this now works:
>
> SELECT C.ENDDATE
> FROM CMN_PERIODS C
> WHERE (C.PERIOD = mod(:IPeriod, 100))
>     AND (C.YR  = (coalesce(:IPeriod) / 100))

This will yield a syntax error as coalesce requires two arguments. If
you used coalesce(:IPeriod, 0) instead, then this will work because
passing that 0 as second argument will cause Firebird to infer the
parameter correctly as an integer.

The better way would be to use

cast(:IPeriod as integer) / 100

Because this will be able to preserve null values passed.

> BUT, this also works! (ie. Does not give an error)
>
> SELECT C.ENDDATE
> FROM
>     CMN_PERIODS C
> WHERE
>     (C.PERIOD = mod(:IPeriod, 100))
>  AND
>     (C.YR  = (null / 100))

That is expected: any operation on null will yield null.

> I am doing his via FastReports and FIBPlus datasets, so not sure if that
> is affecting things. The parameter type is set to Integer.

Setting parameter types in the client is - unfortunately - not relevant
here, because Firebird needs to determine the types at statement compile
(prepare) time.

> I am going to go ahead and add coalesce to my 20 queries that have this
> in various reports, but would like to know if this is an intended
> side-effect (and my Google-Fu just not good enough to find the relevant
> documentation), and/or if there is some other better way I should be
> dealing with this problem rather (since I am not 100% understanding what
> has changed and why).

Please report it in the tracker. As far as I can see this is a bug and
regression compared to Firebird 2.5, and otherwise one of the core
developers will be able to tell if this was an intentional change.

Mark
--
Mark Rotteveel