Chacham's Journal: SQL Server: Ignoring a query part?

We're processing batches of data from external sources that maps to internal buckets. The internal buckets are numeric, but the external ones have a choice. One matches our buckets, the other is whatever they want it to be but they have a map telling us what to do. The batches themselves specify which format they are in (stored in the batch master TABLE).

The process loads data into the staging TABLE and has an SP move the data into the Data TABLE. No problem, CREATE a VIEW to map automatically and we're virtually done.

The VIEW is in the format of:

WITH Data AS (Get the data before mapping)
SELECT (SELECT our-bucket WHERE our-bucket = bucket) WHERE format = 'our buckets' UNION ALL
SELECT WHERE format = 'mapping';

The idea is to get the data the same way for both formats and then run the appropriate query part. This worked at first until we had:

1) non-numeric bucket types
2) The VIEW was run with:
  a) Batch = one number
  b) Bucket IS NOT NULL

This was interesting. The VIEW runs fine with no WHERE clause, or with 2a or 2b alone, just not both together. Further, change 2a to Batch BETWEEN a number AND the same number, and it worked. Very strange.

Basically, under some circumstances, SQL Server was running a sub-query inside a query-part that specifically excluded the case.

Arg! I want Oracle back. *sniff*

Well, for the meanwhile, i changed the sub-query's = to a LIKE. Technically, that can change the INDEX seek to a scan (read: bad for performance), but i didn't see a difference in the query plan. When we go to production, we'll have to see if it's any different there. Though, a little slowdown is not much of a problem, as we're not expecting to do more than a few thousand batches a month.