Reported By: Andrew H. Grilk Reported On: 3/9/2005 For: Version 4.17 Build 1
# 1970SELECT COUNT(*) Query Returning Incorrect Results When WHERE Clause Contains Sub-SELECT Doing a select count(*) vs select * yields different # of returned rows when using a subselect in an IN clause.
Suppose 2 tables.
ItemDetails (Reference, description, ...)
Stocks(Reference, Item, ...)
select count(*) from ItemDetails
where reference not in
(select distinct Item from stocks)
Returns: 23,000
select * from ItemDetails
where reference not in
(select distinct Item from stocks)
Returns: 14,000 Rows
There are 23,000 rows in the ItemDetails table
and 9,000 rows in the Stocks table.
ResolutionFixed Problem on 3/9/2005 in version 4.18 build 1