Equality on NULL column values

Louis van Alphen wrote:

Hello all, I have a table that contains columns that describe an item. E.g.

ITEM table
  ID bigint not null
  COLOUR bigint  not null  (indexed)
  FINISH bigint (indexed)

Then another table that contains a qty of items that is reserved. I.e. the specification and qty of items reserved. Colour is mandatory and Finish is optional

RESERVATION table
  ID bigint not null
  COLOUR bigint  not null  (indexed)
  FINISH bigint (indexed)
  QTY integer not null

Now to determine available stock (item count - reservations) I issue the following query:

select
  STOCK.COLOUR,
  STOCK.FINISH,
  STOCK. STOCK_QTY,
  R.QTY as RESERVED_QTY,
  STOCK. STOCK_QTY - R.QTY as RESERVED_QTY as AVAILABLE_QTY
  from
   (
    /* Get summary of item stock */
    select COLOUR,FINISH,count(*) as STOCK_QTY
      from ITEM
      group by COLOUR,FINISH
   ) STOCK
left outer join RESERVATION R     /* Get Reservations */
  on R.COLOUR = STOCK.COLOUR  --  Match mandatory field
  and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1)  --  Match optional field

The reason for using the coalesce in the join is to force NULL Finishes to match. The problem is that the query plan does not use the indices on FINISH because of the coalesce. This results in HUGE number of reads on the RESERVATION table. Think it table scans the RESERVATION table for each ITEM row. I tried a computed index, but the query did not use that index. The only (non FB) way to optimise the query is to actually refactor the tables and extract a SPECIFICATION table. In my case this will mean a HUGE amount of work and not feasible at this stage.

So my question is: How to optimise this query given that I use FB 2.5.3…?

Dmitry Yemanov answers:

Use:

and R.FINISH is not distinct from STOCK.FINISH

instead:

and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1)