Does RisingWave plan to support pattern recognition in materialized views similar to Flink's CEP?

[Firstero]

Question: Does RisingWave plan to support pattern recognition in materialized views similar to Flink's CEP?

Context:
Apache Flink's Complex Event Processing (CEP) library enables pattern detection in event streams using declarative patterns (e.g., BEGIN.where(...).followedBy(...).within()). This is particularly useful for scenarios like fraud detection or failure sequence analysis [1].

Current State in RisingWave:
RisingWave's materialized views excel at continuous SQL-based aggregations and window operations, but lack native syntax for temporal pattern matching on event sequences [2] [3].

Ask:

1. Is there a roadmap to support Flink-style CEP expressions (e.g., MATCH_RECOGNIZE or custom pattern operators) in materialized views?
2. If planned, what would be the expected implementation approach (e.g., leveraging existing SQL:2016 standards vs. custom syntax)?
3. For use cases requiring multi-event pattern detection (e.g., "3 failed logins within 5 minutes followed by a success"), what are the current recommended workarounds?

Community Engagement:

• Should this be tracked via a specific GitHub issue? (e.g., similar to Issue #12435 on watermark propagation) [4]
• Are contributions welcome in this area?

[lmatz]

Hi @Firstero

We had internal discussions, but we decided not to support CEP at the moment. We found that most of the CEP use cases brought up by our users can be expressed in vanilla SQL. But we admit that sometimes, CEP can execute it more efficient by having customized optimization for some queries

WITH ordered_logins AS (
  SELECT
    userid,
    status,
    login_time,
    ROW_NUMBER() OVER (PARTITION BY userid ORDER BY login_time) AS rn
  FROM logins
),
failed_logins AS (
  SELECT l1.userid, l1.login_time AS fail1_time, l2.login_time AS fail2_time, l3.login_time AS fail3_time
  FROM ordered_logins l1
  JOIN ordered_logins l2 ON l1.userid = l2.userid AND l2.rn = l1.rn + 1 AND l2.status = 'fail'
  JOIN ordered_logins l3 ON l1.userid = l3.userid AND l3.rn = l1.rn + 2 AND l3.status = 'fail'
  WHERE l1.status = 'fail'
    AND l3.login_time <= l1.login_time + INTERVAL '5' MINUTE
),
success_after_fail AS (
  SELECT f.userid, f.fail1_time, f.fail2_time, f.fail3_time, s.login_time AS success_time
  FROM failed_logins f
  JOIN ordered_logins s ON f.userid = s.userid AND s.rn > (
    SELECT rn FROM ordered_logins WHERE userid = f.userid AND login_time = f.fail3_time
  )
  WHERE s.status = 'success'
)
SELECT * FROM success_after_fail;

Does this workaround work for you?

But feel free to open an issue and provide usage examples on GitHub, if it gains more traction, we are open to changing our minds.

Thank you for posting your questions here! Please note that our primary communication channel is the Slack community: risingwave.com/slack.