Forum Discussion
Weird count behaviour for table in Warehouse
One more thing to note, as it turns out saving the result of count(*) query into a variable and comparing it to the iterator turns out to work properly.
drop table if exists dbo.temp declare @iter int = 0 select 1 x into dbo.temp declare @limit int = (select count(*) from dbo.temp) while @limit>@iter begin print @iter set @iter += 1 if @iter=20 break end print 'finished' drop table if exists dbo.temp
Hi Anonymous ,
The issue is, according to my SQL knowledge, the while loop should only print 0, not all the values from 0 to 19. After the initial check for 1>0 evaluating to true, the next iteration should evaluate 1>1 as false and the loop should be finished. Limit of 20 is basically my workaround to not let the loop go forever, it just seems the evaluation of the condition in loop is not working correctly.
Hi FabricUser25 ,
If table is already created and when we try to execute below code this is working fine.
declare @iter int = 0
while (select count(*) from dbo.temp) > @iter
begin
print @iter
set @iter += 1
if @iter=20
break
end
print 'finished'
FW might isolate temporary tables within each loop iteration. So, the count(*) query inside the loop might initially see an empty table, leading to a count of 0 and keeping the loop running.
Hope this is helpful. Do let me know incase of further queries.
Hi Anonymous ,
Surely the count cannot be 0 since 0>0 is false, so the loop would not be executed even once. In this scenario, it looks to be exactly opposite, that the count(*) returns some unknown big number and the loop seems to be infinite. I could maybe understand that during the comparison check, it was 0 due to some table initialization delay but it is not true. To me it seems to be a bug, but would be really glad if anyone had any idea where it might come from.
Hi FabricUser25 ,
Apologize for the issue you are facing. The best course of action is to open a support ticket and have our support team take a closer look at it.Please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link
After creating a Support ticket please provide the ticket number as it would help us to track for more information.
Hope this helps. Please let us know if you have any other queries.
Hi FabricUser25 ,
We haven’t heard from you on the last response and was just checking back to see if you got a chance to open a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.
Thanks