|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Odd query result in SQL server 2000I am running SQL Server 2000, SP4. I have stored procedure with a query like this: SELECT COUNT(*), -1 FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON PM.PER_ID=PU.PER_ID LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 AND PU.CNT_ID NOT IN (...list of IDs) AND PE.EDU_ID=1 By running the stored procedure this query gives the result 221, -1 This result is wrong. I run EXACTLY the same query using query analyzer and that gives the result 199, -1 Which is correct How is this possible? I am running the both the SP and the query under the same account. I have tried to drop the SP and re-create it. I have tried to re- compile the SP. Nothing seems to help. Any ideas? Thanks. Best regards, Eirik Use the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc? -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "Eiriken" <ei***@oslo.online.no> wrote in message news:d20f3c54-698c-42e8-a5cd-9406623f26e7@i29g2000prf.googlegroups.com... > Hi, > > I am running SQL Server 2000, SP4. > I have stored procedure with a query like this: > > SELECT COUNT(*), -1 > FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON > PM.PER_ID=PU.PER_ID > LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID > WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 > AND PU.CNT_ID NOT IN (...list of IDs) > AND PE.EDU_ID=1 > > By running the stored procedure this query gives the result 221, -1 > This result is wrong. > > I run EXACTLY the same query using query analyzer and that gives the > result 199, -1 > Which is correct > > How is this possible? I am running the both the SP and the query under > the same account. > I have tried to drop the SP and re-create it. I have tried to re- > compile the SP. Nothing seems to help. > > Any ideas? > > Thanks. > > Best regards, > Eirik Thank you for answering
The list of IDs is from a temporary table created in the same SP. Looking at the execution plan shows that indeed there is a difference between running the queries in the SP and in the Query Analyzer. The "messages" tab also shows a difference. By running the queries in query analyzer shows "23 rows affected and 1 rows affected". Running the queries in a SP executed in query analyzer shows "23 rows affected, 5 rows affected and 1 rows affected". Where the 5 rows come from I don't know. I have stripped down the queries and I am pasting the query here. After stripping the result is the still wrong as earlier. CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200), NumberFullTime int, NumberPartTime int, NumberTotal int) INSERT INTO #Tmp_Countries SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190) SELECT COUNT(*), -1 FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON PM.PER_ID=PU.PER_ID LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE CountryId>=0) AND PE.EDU_ID=1 DROP TABLE #Tmp_Countries Show quote On 24 Nov, 17:39, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > Use the Show Actual Query Plan feature to see what both queries are doing. > > Also, is the list of IDs a parameter in the sproc? > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "Eiriken" <ei***@oslo.online.no> wrote in message > > news:d20f3c54-698c-42e8-a5cd-9406623f26e7@i29g2000prf.googlegroups.com... > > > Hi, > > > I am running SQL Server 2000, SP4. > > I have stored procedure with a query like this: > > > SELECT COUNT(*), -1 > > FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON > > PM.PER_ID=PU.PER_ID > > LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID > > WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 > > AND PU.CNT_ID NOT IN (...list of IDs) > > AND PE.EDU_ID=1 > > > By running the stored procedure this query gives the result 221, -1 > > This result is wrong. > > > I run EXACTLY the same query using query analyzer and that gives the > > result 199, -1 > > Which is correct > > > How is this possible? I am running the both the SP and the query under > > the same account. > > I have tried to drop the SP and re-create it. I have tried to re- > > compile the SP. Nothing seems to help. > > > Any ideas? > > > Thanks. > > > Best regards, > > Eirik Eiriken <ei***@oslo.online.no> wrote in
Show quote news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@o42g2000hsc.googlegroups.com: Possibly the problem is with one or more PU/PE columns in the WHERE clause > Thank you for answering > The list of IDs is from a temporary table created in the same SP. > Looking at the execution plan shows that indeed there is a difference > between running the queries in the SP and in the Query Analyzer. The > "messages" tab also shows a difference. By running the queries in > query analyzer shows "23 rows affected and 1 rows affected". Running > the queries in a SP executed in query analyzer shows "23 rows > affected, 5 rows affected and 1 rows affected". Where the 5 rows come > from I don't know. > > I have stripped down the queries and I am pasting the query here. > After stripping the result is the still wrong as earlier. > > CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200), > NumberFullTime int, NumberPartTime int, NumberTotal int) > > INSERT INTO #Tmp_Countries > SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN > (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190) > > SELECT COUNT(*), -1 > FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON > PM.PER_ID=PU.PER_ID > LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID > WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 > AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE > CountryId>=0) > AND PE.EDU_ID=1 > > DROP TABLE #Tmp_Countries being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole of the WHERE clause will evaluate to NULL. Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases. HTH NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings' execution context. Another minor point - ALWAYS prefix EVERY object (even temporary ones) by it's owner/schema. Not only is this more efficient - there are scenarios where it can lead to the wrong results too. -- Show quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@tesco.net> wrote in message news:Xns99F2D9D92765DChrisCheneytesconet@80.5.182.99... > Eiriken <ei***@oslo.online.no> wrote in > news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@o42g2000hsc.googlegroups.com: > >> Thank you for answering >> The list of IDs is from a temporary table created in the same SP. >> Looking at the execution plan shows that indeed there is a difference >> between running the queries in the SP and in the Query Analyzer. The >> "messages" tab also shows a difference. By running the queries in >> query analyzer shows "23 rows affected and 1 rows affected". Running >> the queries in a SP executed in query analyzer shows "23 rows >> affected, 5 rows affected and 1 rows affected". Where the 5 rows come >> from I don't know. >> >> I have stripped down the queries and I am pasting the query here. >> After stripping the result is the still wrong as earlier. >> >> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200), >> NumberFullTime int, NumberPartTime int, NumberTotal int) >> >> INSERT INTO #Tmp_Countries >> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN >> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190) >> >> SELECT COUNT(*), -1 >> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON >> PM.PER_ID=PU.PER_ID >> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID >> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 >> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE >> CountryId>=0) >> AND PE.EDU_ID=1 >> >> DROP TABLE #Tmp_Countries > > Possibly the problem is with one or more PU/PE columns in the WHERE clause > being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the > whole > of the WHERE clause will evaluate to NULL. > > Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases. > > HTH I started investigating the case more carefully and compared the
result to see what was really the difference. The answer was exactly as you proposed. The settings were indeed different and the NULLs were the reason for the different result. Thank you Kevin and Chris. Show quote On 25 Nov, 00:05, "TheSQLGuru" <kgbo***@earthlink.net> wrote: > NULLs are my first guess for this too. They could cause problems because > settings are different between direct execution and the sproc settings' > execution context. > > Another minor point - ALWAYS prefix EVERY object (even temporary ones) by > it's owner/schema. Not only is this more efficient - there are scenarios > where it can lead to the wrong results too. > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "Chris.Cheney" <Chris.CheneyXXNOSPA***@tesco.net> wrote in message > > news:Xns99F2D9D92765DChrisCheneytesconet@80.5.182.99... > > > Eiriken <ei***@oslo.online.no> wrote in > >news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@o42g2000hsc.googlegroups.com: > > >> Thank you for answering > >> The list of IDs is from a temporary table created in the same SP. > >> Looking at the execution plan shows that indeed there is a difference > >> between running the queries in the SP and in the Query Analyzer. The > >> "messages" tab also shows a difference. By running the queries in > >> query analyzer shows "23 rows affected and 1 rows affected". Running > >> the queries in a SP executed in query analyzer shows "23 rows > >> affected, 5 rows affected and 1 rows affected". Where the 5 rows come > >> from I don't know. > > >> I have stripped down the queries and I am pasting the query here. > >> After stripping the result is the still wrong as earlier. > > >> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200), > >> NumberFullTime int, NumberPartTime int, NumberTotal int) > > >> INSERT INTO #Tmp_Countries > >> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN > >> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190) > > >> SELECT COUNT(*), -1 > >> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON > >> PM.PER_ID=PU.PER_ID > >> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID > >> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1 > >> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE > >> CountryId>=0) > >> AND PE.EDU_ID=1 > > >> DROP TABLE #Tmp_Countries > > > Possibly the problem is with one or more PU/PE columns in the WHERE clause > > being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the > > whole > > of the WHERE clause will evaluate to NULL. > > > Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases. > > > HTH  |
|||||||||||||||||||||||
Other interesting topics