Forum Discussion
Weird count behaviour for table in Warehouse
I have created a procedure to iterate over all the records within a config table and do things according to some logic that is not really imporant in this particular case. I assumed that count(*) should correctly return the number of rows within a table but as it turns out, below code snippet turns into an infitnie loop in Fabric's Warehouse (broken by the 20 limit in the code). Can anyone explain such behaviour within Fabric's Warehouse considering the same code works as expected within SQL Server?
declare @iter int = 0
select
1 x
into
dbo.temp
while (select count(*) from dbo.temp)>@iter
begin
print @iter
set @iter += 1
if @iter=20
break
end
print 'finished'
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
9 Replies
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.tempHi FabricUser25 ,
Thanks for using Fabric Community.
When I tried to execute the below code in Fabric Warehouse, it is working as expected -drop table if exists dbo.temp declare @iter int = 0 select 1 x into dbo.temp while (select count(*) from dbo.temp)>@iter begin print @iter set @iter += 1 if @iter=20 break end print 'finished'
Can you please provide the screenshots of your issue, so I can understand it better?
I hope this is helpful. Do let me know incase of further queries.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.
