topic Re: Weird count behaviour for table in Warehouse in Data Warehouse

Weird count behaviour for table in Warehouse

FabricUser25 — Tue, 28 May 2024 09:30:31 GMT

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'

Re: Weird count behaviour for table in Warehouse

FabricUser25 — Tue, 28 May 2024 09:33:26 GMT

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

Re: Weird count behaviour for table in Warehouse

Anonymous — Wed, 29 May 2024 07:40:01 GMT

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.

Re: Weird count behaviour for table in Warehouse

FabricUser25 — Wed, 29 May 2024 07:46:59 GMT

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.

Re: Weird count behaviour for table in Warehouse

Anonymous — Wed, 29 May 2024 08:53:53 GMT

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.

Re: Weird count behaviour for table in Warehouse

FabricUser25 — Wed, 29 May 2024 09:08:20 GMT

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.

Re: Weird count behaviour for table in Warehouse

Anonymous — Wed, 29 May 2024 09:12:52 GMT

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.

Re: Weird count behaviour for table in Warehouse

Anonymous — Thu, 30 May 2024 11:25:46 GMT

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

Re: Weird count behaviour for table in Warehouse

FabricUser25 — Fri, 31 May 2024 06:38:31 GMT

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.

Re: Weird count behaviour for table in Warehouse

Anonymous — Fri, 31 May 2024 07:33:46 GMT

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 .