What causes "MATCH_RECOGNIZE requires an ORDER BY clause" in risingwavelabs/risingwave?

Add `ORDER BY ` inside the MATCH_RECOGNIZE clause.

MATCH_RECOGNIZE requires an ORDER BY clause

Error message

MATCH_RECOGNIZE requires an ORDER BY clause

What it means

`to_stream` explicitly bails when the first order key index is absent: `MATCH_RECOGNIZE requires an ORDER BY clause`. The pattern-match executor needs a time-ordered append-only stream, so a `MATCH_RECOGNIZE` without `ORDER BY` cannot be planned for streaming and the user gets a `NotSupported` error at CREATE time.

Source

Thrown at src/frontend/src/optimizer/plan_node/logical_match_recognize.rs:233

            .into());
        }
        if self
            .core
            .partition_key_indices()
            .expect("checked above")
            .is_empty()
        {
            return Err(ErrorCode::NotSupported(
                "MATCH_RECOGNIZE without a PARTITION BY".to_owned(),
                "add PARTITION BY; for a global pattern, partition by a constant column computed \
                 in a view below (all rows then match within one partition)"
                    .to_owned(),
            )
            .into());
        }
        let order_indices = self.core.order_key_indices().expect("checked above");
        let Some(&time_col) = order_indices.first() else {
            bail!("MATCH_RECOGNIZE requires an ORDER BY clause");
        };
        let partition_key_indices = self.core.partition_key_indices().expect("checked above");

        let stream_input = self.input().to_stream(ctx)?;
        // The executor matches over an append-only sequence and emits insert-only results; it has no
        // semantics for retracting or revising an already-emitted match, and the stream plan node
        // declares append-only output. Reject a non-append-only input during planning so the user
        // gets an error at `CREATE`, rather than the executor crashing on the first update/delete.
        if !stream_input.append_only() {
            return Err(ErrorCode::NotSupported(
                "MATCH_RECOGNIZE over a non-append-only input (updates or deletes could revise \
                 an already-emitted match)"
                    .to_owned(),
                "use an append-only source or table (e.g. CREATE TABLE ... APPEND ONLY)".to_owned(),
            )
            .into());
        }
        // Event-time contract: the executor buffers rows and finalises matches as the watermark on

View on GitHub (pinned to 6469eb736d)

Solutions

Add `ORDER BY <event-time-column>` inside the MATCH_RECOGNIZE clause.
Make sure the ORDER BY column is the monotonic event-time column of the source so rows are ordered in the stream.
Re-run `CREATE MATERIALIZED VIEW` after fixing the clause.

Example fix

-- before
MATCH_RECOGNIZE (PARTITION BY symbol PATTERN 'A B' ...)
-- after
MATCH_RECOGNIZE (PARTITION BY symbol ORDER BY ts PATTERN 'A B' ...)

Defensive patterns

Strategy: validation

Validate before calling

-- before creating the MV, verify the MATCH_RECOGNIZE clause has ORDER BY:
-- CREATE MATERIALIZED VIEW mv AS SELECT * FROM t MATCH_RECOGNIZE (
--   PARTITION BY id ORDER BY ts PATTERN 'A B' ...);

Try / catch

match create_result {
    Err(e) if e.to_string().contains("requires an ORDER BY") => {
        eprintln!("MATCH_RECOGNIZE needs ORDER BY <event-time column>");
    }
    r => r?,
}

Prevention

Treat ORDER BY as mandatory when porting MATCH_RECOGNIZE SQL from other engines.
Order by the source's monotonic event-time column so the pattern executor sees ordered rows.

When it happens

Trigger: Running `CREATE MATERIALIZED VIEW ... AS SELECT ... MATCH_RECOGNIZE (PARTITION BY ...)` without an `ORDER BY` clause in the MATCH_RECOGNIZE definition.

Common situations: Users porting MATCH_RECOGNIZE SQL from other engines (e.g. Snowflake/Oracle examples) that omit ORDER BY, assuming it is optional.

Understand the failure class

Background: "missing required argument" and "the following required arguments were not provided": what required-argument errors mean and how to fix them — this error's family across 20 libraries.

Related errors

AI-assisted analysis of risingwavelabs/risingwave@6469eb736d (2026-09-11). Data as JSON: /api/errors/3091a3e06971a2e1. Report an issue: GitHub.