FLATTEN returns INCORRECT data for a ROW_NUMBER in a subquery when acceleration is used

1

Hi,
I found an interesting case where the FLATTEN does not keep the original ROW_NUMBER() from a subquery when acceleration is used. When acceleration is not used, the query works fine.

I want to keep the rownum of a string, that i split then flatten, but unfortunately in the results,
the rownum column is a sorted list of integer without any duplicates, completly loosing the original rownum

SELECT flatten(sids) AS sids, rownum
FROM (
SELECT REGEXP_SPLIT(sids, ‘,’, ‘ALL’, 10) AS sids, ROW_NUMBER() OVER() as rownum
from (
select sids
FROM accelerated_dataset
group by sids)

When the acceleration is not used, the result is correct.

here is the query profile of the query
FLATTEN_ROWNUM_ISSUE_b10d1896-e7e9-4c6b-b88e-95ca05f73b83.zip (41.5 KB)

1 Like