Re: Row pattern recognition

Hi Henson,

> Still deferred:
> - B7 Recursive CTE XXX: pending community input on the
> ISO/IEC 19075-5 6.17.5 / 4.18.5 interpretation.

ISO/IEC 19075-5 6.17.5 stats that "Row pattern matching is prohibited
in recursive queries. For example, the following is a syntax error:

CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM Ticker... WINDOW W AS... MEASURES..."

ISO/IEC 19075-5 4.18.5 stats that "Row pattern matching is prohibited
in recursive queries. For example, the following is a syntax error:

CREATE RECURSIVE VIEW Problem... SELECT Kolo, Xoro FROM t... MATCH_REZOGNIZE..."

From these it is apparent that CREATE RECURSIVE VIEW cannot be used
with RPR in both R010 and R020.

Question is, what about CTE queries?

I looked into ISO/IEC 9075-2:2016 (I don't have access to 2023) and
found this in "7.17 <query expression>".

in "Syntax Rule"

3) If <with clause> WC is specified, then:

a) Let n be the number of <with list element>s.
:
:
3) If <with clause> WC is specified, then:
:
:
e) If WC immediately contains RECURSIVE, then WC, its <with list>, and
its <with list element>s are said to be potentially
recursive. Otherwise, they are said to be non-recursive.

f) A potentially recursive <with list element> shall not contain a
<row pattern measures> or <row pattern common syntax>.

So I think at least SQL:2016 explicitly prohibits using RPR within
recursive CTE. I would appreciate if anybody confirms this in
SQL:2023.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp