Weird count behaviour for table in Warehouse

[FabricUser25]

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'

[Anonymous]

Hi @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.

[FabricUser25]

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.

[Anonymous]

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.

[FabricUser25]

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.

[Anonymous]

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.

[Anonymous]

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

[FabricUser25]

Hi @Anonymous ,

I have opened a ticket on the issue #2405290040003138.

As we have established, there is a workaround mentioned in my later post but the source of this behaviour is still being analyzed.

[Anonymous]

Hi @FabricUser25 ,

Thanks for sharing the support ticket.

Please allow some time, so team can check and provide a resolution.

In case if you got a resolution please do share with the community as it can be helpful to others .

[FabricUser25 · Accepted solution]

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