--- In firebird-support@yahoogroups.com, "asakin312" <asakin@w...>
wrote:

> Hi,
> I ran the following query on a regular table:
> SELECT count(id) FROM FILES WHERE 0=1 group by id
> and I got NULL as an answer.
>
> The following query works ok and returns 0:
> SELECT count(id) FROM FILES WHERE 0=1
> (same as the above only without the group by).
>
> Is this the correct behavior?
> How can I get 0 as an answer from the first query?
>
> Thanks,
> Ariel.

Just an aside, and a possible gotcha, the condition you use is not
very efficient in Firebird

WHERE 1=0

will be evaluated for every record in the table (in Firebird 1.5.2,
not sure about later versions). This means if you have several
million items in the Files table, you will be waiting a while for it
to return 0 records.

A better approach / work around is to use a condition that can use an
existing index. How is ID defined? If it is defined as a domain with
a check that the value > 0, then you are in luck

WHERE ID=0

will guarantee to return no records.

Otherwise, you could always use an AND operator to create an
impossible condition,

WHERE ID=0 AND ID=1

to guarantee 0 records returned. This will be only a single index hit
on the primary key, and will not even touch the data pages.

Hopefully some time in the future Firebird will be optimised to
improve this, but till then, this works a treat.

Hope that helps
Adam