Strange, isn't it? The index is picked up when using "greater than
zero", but it is NOT when using "different than zero" (<> symbol) or
"not equal to zero" (not field_name=0) or with your suggestion
(field_name+0 <> 0)

I should add this: When using "<>0" and "not field_name=0" the proper
index is NOT picked up, but another index is. In this cases, the
optimizer chooses an index that contains only the first field,
CUSTOMER, because this field is a FK on the table and a RDB$FOREIGN
index exists for it. This is the index picked up by the optimizer. But
of course this is not optimal.

I should also add that at this time the table has a few records only,
it's part of a new project with no user-entered data yet.

I know it sounds strange, but could it be that the problem solves once
a lot of records are added to the table? I'd be surprised.

Thanks,

-Benton

> I'm surprised that the index isn't picked up. Though I normally use
> single field indexes myself, it should be able to pick up the first

half

> of the index in your case.
>
> I suppose you could try
>
> SELECT SUM(INVOICETOTAL) FROM INVOICES
> WHERE CUSTOMER=123 AND INVOICETOTAL+0 <> 0;
>
> just to see if it helps making the optimizer less confused.
>
> If this helps, I'd say Firebird 1.5.3 has a bug, if it doesn't, I'd
> suspect that your index isn't very selective or that the table contains
> very few rows (test whether SELECT SUM(INVOICETOTAL) FROM INVOICES

WHERE

> CUSTOMER=123 uses the index).
>
> Another thing that is known to confuse Firebird, is if you have several
> indexes that are basically identical, do you have other indexes,
> constraints or keys that starts with the CUSTOMER field?
>
> HTH,
> Set
>
> dr_bentonquest wrote:
> > Hi there,
> >
> > I have an INVOICES table like this:
> >
> > create table INVOICES
> > (
> > NUMBER integer,
> > CUSTOMER integer,
> > INVOICETOTAL numeric(10,2),
> > ...more fields...
> > )
> >
> > I set up an index like this:
> >
> > CREATE INDEX INV_TOTAL on INVOICES(CUSTOMER,INVOICETOTAL);
> >
> > Now when executing the following query, the index is picked up
> > correctly in the plan:
> >
> > SELECT SUM(INVOICETOTAL) FROM INVOICES
> > WHERE CUSTOMER=123 AND INVOICETOTAL > 0;
> >
> > However, for the following queries the index is NOT picked up:
> >
> > SELECT SUM(INVOICETOTAL) FROM INVOICES
> > WHERE CUSTOMER=123 AND INVOICETOTAL <> 0;
> >
> > SELECT SUM(INVOICETOTAL) FROM INVOICES
> > WHERE (CUSTOMER=123) AND (NOT INVOICETOTAL = 0);
> >
> > I'm currently using FB 1.5.3. Is there any workaround I could use to
> > solve this? I need the total for invoices where the INVOICETOTAL field
> > is not zero.
> >
> > Thanks in advance,
> >
> > -Benton
>