Snowflake table and generator functions does not give expected result-CodePudding

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)

CodePudding user response：

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;