Icon View Incident Report

Serious Serious
Reported By: Andrew H. Grilk
Reported On: 3/9/2005
For: Version 4.17 Build 1
# 1970 SELECT 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.



Resolution Resolution
Fixed Problem on 3/9/2005 in version 4.18 build 1
Image