sqlsnowflake-cloud-data-platformtable-functions

Snowflake table and generator functions does not give expected result


I tried to create a simple SQL to track query_history usage, but got into trouble when creating my timeslots using the table and generator functions (the CTE named x below).

I got no results at all when limiting the query_history using my timeslots, so after a while I hardcoded an SQL to give the same result (the CTE named y below) and this works fine.

Why does not x work? As far as I can see x and y produce identical result?

To test the example first run the code as it is, this produces no result. Then comment the line x as timeslots and un-comment the line y as timeslots, this will give the desired result.

with 
x as (
    select
        dateadd('min',seq4()*10,dateadd('min',-60,current_timestamp())) f,
        dateadd('min',(seq4()+1)*10,dateadd('min',-60,current_timestamp())) t
    from table(generator(rowcount => 6))
), 
y as (
    select
        dateadd('min',n*10,dateadd('min',-60,current_timestamp())) f,
        dateadd('min',(n+1)*10,dateadd('min',-60,current_timestamp())) t
    from (select 0 n union all select 1 n union all select 2 union all select 3 
          union all select 4 union all select 5)
)

--select * from x;
--select * from y;

select distinct 
    user_name,
    timeslots.f
from snowflake.account_usage.query_history, 
   x as timeslots
   --y as timeslots
where start_time >= timeslots.f
and start_time < timeslots.t
order by timeslots.f desc;

(I know the code is not optimal, this is only meant to illustrate the problem)


Solution

  • SEQ:

    Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1, 2, 4, or 8 byte).

    If a fully ordered, gap-free sequence is required, consider using the ROW_NUMBER window function.

    For:

    with x as (
        select
            dateadd('min',seq4()*10,dateadd('min',-60,current_timestamp())) f,
            dateadd('min',(seq4()+1)*10,dateadd('min',-60,current_timestamp())) t
        from table(generator(rowcount => 6))
    )
    SELECT * FROM x;
    

    Should be:

    with x as (
        select
            (ROW_NUMBER() OVER(ORDER BY seq4())) - 1 AS n,
            dateadd('min',n*10,dateadd('min',-60,current_timestamp())) f,
            dateadd('min',(n+1)*10,dateadd('min',-60,current_timestamp())) t
        from table(generator(rowcount => 6))
    )
    SELECT * FROM x;