I've got a query like:

SELECT ... FROM TBL WHERE COALESCE( FIELD1, FIELD2 ) = 12345

where FIELD2 is the primary key and FIELD1 also has an index (in fact
it's a foreign key back to FIELD2). This results in a table scan.

However expanding the COALESCE by hand:

SELECT ... FROM TBL WHERE ( FIELD1 = 12345 ) OR ( FIELD1 IS NULL AND
FIELD2 = 12345)

results in use of both indices and no table scan. (In the vast majority
of cases FIELD1 is null so I just want a lookup of a single record by
primary key; just occasionally FIELD1 is not null and I might want two
or three records in the result set.)

So, my questions:

(1) Have I got this right? Is the second query a correct expansion of
COALESCE that produces the same results as the first query?

(2) If so, is it indeed the case that the optimiser doesn't understand
how to expand COALESCE?

--
Tim Ward