Panicked when handling the request: column pruning fails to generate equivalent plan.

[lmatz]

Describe the bug

Trying out the new generate_series with now() supported, but:

dev=> create materialized view time_mv as
dev-> select Extract('year' FROM Date t::date) as year, Extract('month' FROM Date t::date) as month, 1::int as constant
dev-> from
dev-> generate_series(
dev(>     '2024-01-01 00:00:00+00'::timestamptz,
dev(>     now(),
dev(>     interval '1 month'
dev(> ) as s(t);


ERROR:  Panicked when handling the request: column pruning fails to generate equivalent plan.
Input schema: Schema { fields: [] }
Input plan: 
LogicalProject { exprs: [] }
└─LogicalNow
Output schema: Schema { fields: [ts:Timestamptz] }
Output plan: 
LogicalNow


SQL: CREATE MATERIALIZED VIEW time_mv AS SELECT EXTRACT(YEAR FROM CAST(DATE 't' AS DATE)) AS year, EXTRACT(MONTH FROM CAST(DATE 't' AS DATE)) AS month, CAST(1 AS INT) AS constant FROM generate_series(CAST('2024-01-01 00:00:00+00' AS timestamptz), now(), INTERVAL '1 month') AS s (t)
This is a bug. We would appreciate a bug report at:
  https://github.com/risingwavelabs/risingwave/issues/new?labels=type%2Fbug&template=bug_report.yml

But if:

dev=> create materialized view time_mv as
dev-> select t, 1::int as constant
dev-> from
dev-> generate_series(
dev(>     '2024-01-01 00:00:00+00'::timestamptz,
dev(>     now(),
dev(>     interval '1 month'
dev(> ) as s(t);
CREATE_MATERIALIZED_VIEW

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

latest main branch

dev=> select version();
                       version                        
------------------------------------------------------
 PostgreSQL 13.14.0-RisingWave-1.11.0-alpha (unknown)
(1 row)

Additional context

No response

[stdrc]

The panic is the surface problem. The deep problem is that Date t::date will try to parse t as date type, so that the final schema is actually completely not related to LogicalNow.

Should use extract('year' from t) or extract('year' from t::date) instead.

I'm fixing the column pruning bug.

[xiangjinwu]

Besides the fix for:

• column pruning panic
• sqlparser misinterpretation

I also realized a third issue in the example above:

from
generate_series(
     '2024-01-01 00:00:00+00'::timestamptz,
     now(),
     interval '1 month'
) as s(t);

Here the interval '1 month' is actually of variable length (28 to 31 days). @stdrc Could you confirm if such "bad" interval values would be problematic in the intended use case? FYI the following "worse" interval value can cause an infinite loop in PostgreSQL batch query:

select generate_series('2024-01-31T00:00:00Z'::timestamptz, now(), interval '1 month -29 days');

(4th issue linked below 😢 )

[stdrc]

Here the interval '1 month' is actually of variable length (28 to 31 days). @stdrc Could you confirm if such "bad" interval values would be problematic in the intended use case?

The timestamp calculation is based on session timezone, so it should behave as expected.

[stdrc]

FYI the following "worse" interval value can cause an infinite loop in PostgreSQL batch query:

select generate_series('2024-01-31T00:00:00Z'::timestamptz, now(), interval '1 month -29 days');

I just raised another issue #17786 to track this bug, cuz this seems not quite related to the bug described in this issue. Now we can close this issue.