{"record":{"id":"3091a3e06971a2e1","repo":"risingwavelabs/risingwave","slug":"match-recognize-requires-an-order-by-clause","errorCode":null,"errorMessage":"MATCH_RECOGNIZE requires an ORDER BY clause","messagePattern":"MATCH_RECOGNIZE requires an ORDER BY clause","errorType":"error_code","errorClass":null,"httpStatus":null,"severity":"error","filePath":"src/frontend/src/optimizer/plan_node/logical_match_recognize.rs","lineNumber":233,"sourceCode":"            .into());\n        }\n        if self\n            .core\n            .partition_key_indices()\n            .expect(\"checked above\")\n            .is_empty()\n        {\n            return Err(ErrorCode::NotSupported(\n                \"MATCH_RECOGNIZE without a PARTITION BY\".to_owned(),\n                \"add PARTITION BY; for a global pattern, partition by a constant column computed \\\n                 in a view below (all rows then match within one partition)\"\n                    .to_owned(),\n            )\n            .into());\n        }\n        let order_indices = self.core.order_key_indices().expect(\"checked above\");\n        let Some(&time_col) = order_indices.first() else {\n            bail!(\"MATCH_RECOGNIZE requires an ORDER BY clause\");\n        };\n        let partition_key_indices = self.core.partition_key_indices().expect(\"checked above\");\n\n        let stream_input = self.input().to_stream(ctx)?;\n        // The executor matches over an append-only sequence and emits insert-only results; it has no\n        // semantics for retracting or revising an already-emitted match, and the stream plan node\n        // declares append-only output. Reject a non-append-only input during planning so the user\n        // gets an error at `CREATE`, rather than the executor crashing on the first update/delete.\n        if !stream_input.append_only() {\n            return Err(ErrorCode::NotSupported(\n                \"MATCH_RECOGNIZE over a non-append-only input (updates or deletes could revise \\\n                 an already-emitted match)\"\n                    .to_owned(),\n                \"use an append-only source or table (e.g. CREATE TABLE ... APPEND ONLY)\".to_owned(),\n            )\n            .into());\n        }\n        // Event-time contract: the executor buffers rows and finalises matches as the watermark on","sourceCodeStart":215,"sourceCodeEnd":251,"githubUrl":"https://github.com/risingwavelabs/risingwave/blob/6469eb736d691e8e9b8a419a57edd6429ca77417/src/frontend/src/optimizer/plan_node/logical_match_recognize.rs#L215-L251","documentation":"`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.","triggerScenarios":"Running `CREATE MATERIALIZED VIEW ... AS SELECT ... MATCH_RECOGNIZE (PARTITION BY ...)` without an `ORDER BY` clause in the MATCH_RECOGNIZE definition.","commonSituations":"Users porting MATCH_RECOGNIZE SQL from other engines (e.g. Snowflake/Oracle examples) that omit ORDER BY, assuming it is optional.","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."],"exampleFix":"-- before\nMATCH_RECOGNIZE (PARTITION BY symbol PATTERN 'A B' ...)\n-- after\nMATCH_RECOGNIZE (PARTITION BY symbol ORDER BY ts PATTERN 'A B' ...)","handlingStrategy":"validation","validationCode":"-- before creating the MV, verify the MATCH_RECOGNIZE clause has ORDER BY:\n-- CREATE MATERIALIZED VIEW mv AS SELECT * FROM t MATCH_RECOGNIZE (\n--   PARTITION BY id ORDER BY ts PATTERN 'A B' ...);","typeGuard":null,"tryCatchPattern":"match create_result {\n    Err(e) if e.to_string().contains(\"requires an ORDER BY\") => {\n        eprintln!(\"MATCH_RECOGNIZE needs ORDER BY <event-time column>\");\n    }\n    r => r?,\n}","preventionTips":["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."],"tags":["sql","match-recognize","order-by","unsupported-query"],"backgroundTag":"missing-required-argument","analyzedSha":"6469eb736d691e8e9b8a419a57edd6429ca77417","analyzedAt":"2026-09-11T21:06:21.487Z","contentChangedAt":"2026-09-11T21:06:21.487Z","schemaVersion":2},"datasetVersion":"2026-09-14T16:17:12.679Z"}