In SQL Server 2005, a filtering condition on a column retrieved from a Key Lookup is applied in an explicit Filter operator after an apply join. In SQL Server 2008 onward, this filter can be pushed down to the lookup during copy out, but cardinality estimates are not updated correctly. The post-filter cardinality estimate is applied to the inner side of the lookup join, rather than the result of the join as a whole.-- SQL Server 2005 AdventureWorksSELECT th.ProductID, th.TransactionID, th.TransactionDateFROM Production.TransactionHistory AS thWHERE th.ProductID = 1 AND th.TransactionDate BETWEEN '20030901' AND '20031231';Estimate from index seek: 45 rowsEstimate from clustered index lookup: 1 row per 45 executionsEstimate from join: 45 rowsEstimate from filter: 16.9951 rowsOn SQL Server 2008+ the filter is pushed to the key lookup after optimization:Estimate from index seek: 45 rowsEstimate from lookup: 16.9951 rows per 45 executions (transferred from the filter)Estimate from join: 45 rows (should be 16.9951)Filter removed from query planAny predicate pushed to a lookup causes this apparent cardinality estimation problem. Filters pushed to regular scans and seeks appear to result in correct cardinality adjustments. (We used trace flag 9130 to explore the copy out rewrite behaviour.)I realise small changes to the optimizer are generally risky, but the current behaviour results in very strange looking plans that are tricky to interpret. The behaviour also seems to be unintentional given the behaviour of pushed predicates with non-lookup seeks and scans. Finally, the issue does not affect plan selection, just the estimates we see when analysing plans, so this might be a low-risk fix?
Operating System Language
Steps to Reproduce