SQL Server 2016 weird behavior - OR condition gives 0 rows But AND condition gives some rows

Question

I have the following SQL query:

SELECT T.tnum,
       T.secId,           
FROM   TradeCore T
       INNER JOIN Sec S
               ON S.secId = T.secId
       INNER JOIN TradeTransfer TT
               ON t.tnum = TT.tnum
WHERE  ( T.td >= '2019-01-01' )
       AND ( T.td <= '2019-02-25' )
       AND ( T.fundId = 3 OR TT.fundId = 3 )
       AND ( T.stratId = 7 OR TT.stratId = 7 ) --Line 1
    -- AND ( T.stratId = 7 AND TT.stratId = 7 ) --Line 2

When I keep last line commented I get 0 results, But when I un-comment it and comment the line before it, I get some result.

How is this possible?

Answer 1

Any row meeting (T.stratId = 7 AND TT.stratId = 7) must certainly meet (T.stratId = 7 OR TT.stratId = 7) so it is not logically possible that the less restrictive predicate returns less results.

The issue is a corrupt non clustered index.

And Case

• 154 rows in TradeCore matching the date condition and stratId = 7 are emitted.
• Join on TradeTransfer with the stratId and fundId conditions applied ouputs 68 rows (estimated 34 rows)
• These all successfully join onto a row in Sec (using index IX_Sec_secId_sectype_Ccy_valpoint) and 68 rows are returned as the final result.

Or case

• 1173 rows in TradeCore matching the date condition are emitted
• Join on TradeTransfer with a residual predicate on 3 in (T.fundId, TT.fundId) AND 7 in (T.stratId, TT.stratId) brings this down to 73 (estimated 297 rows)
• Then all rows are eliminated by the join on Sec - despite the fact that we know from above that at least 68 of them have a match.

The table cardinality of Sec is 2399 rows. In the plan where all rows are removed by the join SQL Server does a full scan on IX_Sec_idu as input to the probe side of the hash join but the full scan on that index only returns 589 rows.

The rows that appear in the other execution plan are pulled from a different index that contains these 1,810 missing rows.

You have confirmed in the comments that the following return differing results

select count(*) from Sec with(index = IX_Sec_idul); --589 
select count(*) from Sec with(index = IX_Sec_secId_sectype_Ccy_valpoint); --2399
select count(*) from Sec with(index = PK_Sec)  --2399

This should never be the case that rowcounts from different indexes on the same table don't match (except if an index is filtered and that does not apply here).

Reason for different indexes

Because the row estimates going in to the join on Sec in the AND case are only 34 it chooses a plan with nested loops and therfore needs an index with leading column secId to perform a seek. For the OR case it estimates 297 rows and instead of doing an estimated 297 seeks it chooses a hash join instead so selects the smallest index available containing the secId column.

Fix

As all rows exist in the clustered index you can drop IX_Sec_idul and create it again to hopefully resolve this issue (take a backup first).

You should also run dbcc checkdb to see if any other issues are lurking.