Table Name Qualified Differently in Subquery gives Incorrect Result
-
Hi,
I've got 5 versions of a query that we think ought to return 9 rows. But one version, the top one, returns an unexpected result, the count of all records in the table. It's not filtering by the subquery. It seems like the problem is inconsistent qualification of the table name in the main and subquery, but since there is only one tblDocuments in the database, and it's owned by dbo, so I expected it to work even with the inconsistency. Other inconsistent forms work. Anyone know why the top version doesn't work? (I know we could get the count directly from the subquery but the developers pass the subquery around for various uses...)
-- This version returns the entire contents of tblDocuments (42000 rows)
USE DB1
Select count(DocID) as DocCount from tblDocuments where docid in
(Select tbldocuments.DocID FROM dbo.tblDocuments WHERE dbo.tblDocuments.DocID < 10)
-- qualify with the DB name, but not the owner, and it works. (9 rows)
USE DB1
Select count(DocID) as DocCount from DB1..tblDocuments where docid in
(Select tbldocuments.DocID FROM dbo.tblDocuments WHERE dbo.tblDocuments.DocID < 10)
-- qualify table name the same in both main and sub and it works (9 rows)
USE DB1
Select count(DocID) as DocCount from dbo.tblDocuments where docid in
(Select tblDocuments.DocID FROM dbo.tblDocuments WHERE dbo.tblDocuments.DocID < 10)
-- this one works too, 9 rows
USE DB1
Select count(DocID) as DocCount from tblDocuments where docid in
(Select DocID FROM dbo.tblDocuments WHERE dbo.tblDocuments.DocID < 10)
-- also the correct 9 rows
USE DB1
Select count(DocID) as DocCount from tblDocuments where docid in
(Select DocID FROM tblDocuments WHERE DocID < 10)
Thanks for your assistance,
ADS
-
I think you are correct in assuming it is because of the malformed syntax. I think that SQL is probably getting confused by the 4 different ways you are referencing the table
- tblDocuments
- tblDocuments.DocId
- dbo.tblDocuments
- dbo.tblDocuments.DocIdD
Why would you want to qualify the owner.table.field in the sub-query IF there is only 1 table??? I can understand the qualification for the table even DB.owner.table but the multi. different ways of referencing the fields is probably the bite.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks for the response AJ.
Re: Why would you want to qualify the owner.table.field in the sub-query IF there is only 1 table??? I can understand the qualification for the table even DB.owner.table but the multi. different ways of referencing the fields is probably the bite.
I agree, you wouldn't. The table name in the FROM clause ought to be qualified, and that ought to be sufficient. But I wouldn't have thought it would hurt to over-qualify it if all the references are to one object.
It's pretty clear it doesn't work, so the developers are going to clean up their syntax and put the qualification in the from clause and make it consistent. But I'm still curious about why it doesn't work...
ADS
-
Off the path I know, but just curious because I've seen sql written like this before and was wondering why you'd want to write:
Select count(DocID) as DocCount from tblDocuments where docid in
(Select tbldocuments.DocID FROM dbo.tblDocuments WHERE dbo.tblDocuments.DocID < 10)
and not just:
Select count(*) as DocCount from dbo.tblDocuments where docid < 10
ie. why the sub-select, because it would seem to be more effort?
jt
-
The reason for the syntax is that the developers are storing and passing entire subquery already. It's used to pull the entire recordset or with an additional criterion on the where clause to pull a subset of the recordset, for instance to page through large recordsets.
Since they already have the subquery, they like to keep their scripts a bit cleaner by using it to get the recordcount (we show a recordcount at the top of each current results page). The alternatives would be either parsing the subquery to get just the where clause or passing only the where clause, not using what they have is more work for them.
The cost is a little performance and less clean SQL. Since there has been a lot of development on these scripts since the last round of optimization, we'll be revisiting this soon.
ADS
-
cheers - thought there must be a reason for it
jt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply