As far as I think both queries should give the proper result. But, not getting the correct result with the 2nd. Can anyone please explain the reason for it.
;WITH CTE1 AS (
SELECT 1 AS ID
UNION ALL
SELECT ID + 1 FROM CTE1 WHERE ID<10
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE1
EXCEPT
SELECT ID FROM CTE2
DECLARE @TAB TABLE(ID INT)
INSERT INTO @TAB VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10)
;WITH CTE1 AS (
SELECT * FROM @TAB
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE1
EXCEPT
SELECT ID FROM CTE2
You are getting different result set because of Order by NewID function.
You can check that, every time whenever you run below query
;WITH CTE1 AS (
SELECT 1 AS ID
UNION ALL
SELECT ID + 1 FROM CTE1 WHERE ID<10
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE2
you are getting the diffrent results.
& whenever using with EXCEPT it will produce result based on CTE2 result set. (as you know that newid is giving unique number everytime)
Now, 1st query is always giving 5 random records as expected. Expecting the same with the 2nd query, but not getting correct result
Dear Sandeep,
Due to NewID both queries are giving different results..you can check simply below query, you can easily understand from that.
;WITH CTE1 AS (
SELECT 1 AS ID
UNION ALL
SELECT ID + 1 FROM CTE1 WHERE ID<10
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE2
--second query
DECLARE @TAB TABLE(ID INT)
INSERT INTO @TAB VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10)
;WITH CTE1 AS (
SELECT * FROM @TAB
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE2
Both are independent queries and definitely would give the different results. But, the logic for both query is same and should always give 5 records.
1st query is absoultey fine. Now, forget 1st query. Now, 2nd query should also give 5 records, but its giving
random no. of records
Now, I think i made it more clear.
Now i got the point. I am also cusious to know about this.
Meanwhile I need to show you something wiered with table variable.
Data are coming randomly because of table variable. Its working fine with temp table logic. you can also check..
;WITH CTE1 AS
(
SELECT 1 AS ID
UNION ALL
SELECT ID + 1 FROM CTE1 WHERE ID<10
), CTE2 AS
(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE1
EXCEPT
SELECT ID FROM CTE2
GO
CREATE TABLE #tempTable(ID INT)
INSERT INTO #tempTable VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10)
;WITH CTE1 AS
(
SELECT * FROM #tempTable
), CTE2 AS
(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE1
EXCEPT
SELECT ID FROM CTE2
DROP TABLE #tempTable
I already tried with temp tables and that also worked fine. I already know the problem is arising only when using with Table Variable. My question is regarding to that only.
why so?
I already tried with temp tables and that also worked fine. I already know the problem is arising only when using with Table Variable. My question is regarding to that only.
why so?
This is a known issue with the QO moving the evaluation of certain expressions up or down.
I have already go through with these posts. It is written, Microsoft said that this behavior is due to the non-determinism of the function.
When we are getting the data in CTE with table variable, then only it is causing issue, why NEWID() is working fine in case of temp tables and recursive CTE. Also, even "UNION" and "UNION ALL" working fine in case of Table variable also. It means CTE1 and
CTE2 contains absolutely correct data as per the logic. then why not "EXPECT" producing desired result.
Check this for your reference
DECLARE @TAB TABLE(ID INT)
INSERT INTO @TAB VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10)
;WITH CTE1 AS (
SELECT * FROM @TAB
), CTE2 AS(
SELECT TOP 5 ID
FROM CTE1
ORDER BY NEWID()
)
SELECT ID FROM CTE1
UNION ALL SELECT ID FROM CTE2
Have you found a solution for this issue? I believe the problem requires a more in-depth level of support. If you still need help with it, please visit the below link to see the various paid support options that are available to better meet your needs.
http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
sandeepmitta...
Contributor
6789 Points
1058 Posts
Getting incorrect result with the query
Apr 11, 2012 11:44 AM|LINK
As far as I think both queries should give the proper result. But, not getting the correct result with the 2nd. Can anyone please explain the reason for it.
Sandeep Mittal | My Blog - IT Developer Zone
yrb.yogi
Star
14460 Points
2402 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:03 PM|LINK
You are getting different result set because of Order by NewID function.
You can check that, every time whenever you run below query
;WITH CTE1 AS ( SELECT 1 AS ID UNION ALL SELECT ID + 1 FROM CTE1 WHERE ID<10 ), CTE2 AS( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE2you are getting the diffrent results.
& whenever using with EXCEPT it will produce result based on CTE2 result set. (as you know that newid is giving unique number everytime)
.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:17 PM|LINK
Dear yogi
I know the concept of NEWID() and I have used NEWID() knowingy because i want to remove 5 numbers dynamically out of total 10 numbers.
Now, 1st query is always giving 5 random records as expected. Expecting the same with the 2nd query, but not getting correct result
Sandeep Mittal | My Blog - IT Developer Zone
yrb.yogi
Star
14460 Points
2402 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:32 PM|LINK
Dear Sandeep,
Due to NewID both queries are giving different results..you can check simply below query, you can easily understand from that.
;WITH CTE1 AS ( SELECT 1 AS ID UNION ALL SELECT ID + 1 FROM CTE1 WHERE ID<10 ), CTE2 AS( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE2 --second query DECLARE @TAB TABLE(ID INT) INSERT INTO @TAB VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10) ;WITH CTE1 AS ( SELECT * FROM @TAB ), CTE2 AS( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE2.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:44 PM|LINK
Both are independent queries and definitely would give the different results. But, the logic for both query is same and should always give 5 records.
1st query is absoultey fine. Now, forget 1st query. Now, 2nd query should also give 5 records, but its giving random no. of records
Now, I think i made it more clear.
Sandeep Mittal | My Blog - IT Developer Zone
yrb.yogi
Star
14460 Points
2402 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:48 PM|LINK
Now i got the point. I am also cusious to know about this.
Meanwhile I need to show you something wiered with table variable.
Data are coming randomly because of table variable. Its working fine with temp table logic. you can also check..
;WITH CTE1 AS ( SELECT 1 AS ID UNION ALL SELECT ID + 1 FROM CTE1 WHERE ID<10 ), CTE2 AS ( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE1 EXCEPT SELECT ID FROM CTE2 GO CREATE TABLE #tempTable(ID INT) INSERT INTO #tempTable VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10) ;WITH CTE1 AS ( SELECT * FROM #tempTable ), CTE2 AS ( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE1 EXCEPT SELECT ID FROM CTE2 DROP TABLE #tempTable.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 12:54 PM|LINK
I already tried with temp tables and that also worked fine. I already know the problem is arising only when using with Table Variable. My question is regarding to that only. why so?
Sandeep Mittal | My Blog - IT Developer Zone
yrb.yogi
Star
14460 Points
2402 Posts
Re: Getting incorrect result with the query
Apr 11, 2012 01:10 PM|LINK
This is a known issue with the QO moving the evaluation of certain expressions up or down.
You can check this thread..
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d28f778a-1fbe-48b5-b541-ede505429d9a
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9b1041f7-dc91-4672-94b0-60311b80a121/
.Net All About
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Getting incorrect result with the query
Apr 12, 2012 04:41 AM|LINK
I have already go through with these posts. It is written, Microsoft said that this behavior is due to the non-determinism of the function.
When we are getting the data in CTE with table variable, then only it is causing issue, why NEWID() is working fine in case of temp tables and recursive CTE. Also, even "UNION" and "UNION ALL" working fine in case of Table variable also. It means CTE1 and CTE2 contains absolutely correct data as per the logic. then why not "EXPECT" producing desired result.
Check this for your reference
DECLARE @TAB TABLE(ID INT) INSERT INTO @TAB VALUES (1),(2), (3), (4), (5), (6), (7), (8), (9), (10) ;WITH CTE1 AS ( SELECT * FROM @TAB ), CTE2 AS( SELECT TOP 5 ID FROM CTE1 ORDER BY NEWID() ) SELECT ID FROM CTE1 UNION ALL SELECT ID FROM CTE2Sandeep Mittal | My Blog - IT Developer Zone
Cathy Mi - M...
Member
741 Points
165 Posts
Microsoft
Re: Getting incorrect result with the query
Apr 26, 2012 08:11 PM|LINK
Hi,
Have you found a solution for this issue? I believe the problem requires a more in-depth level of support. If you still need help with it, please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
Thanks,
Cathy Miller