Greenplum CTE не найден - ошибка [XX000] (allpaths.c:904)

Question

Я строю запрос PostgreSQL для БД Greenplum, чтобы "сгладить" все составные типы в таблицах в псевдостолбцы, чтобы убедиться, что устаревшая система, использующая MySQL, сможет понять, что происходит. Я не знаю, разумно ли это, правда.

Для этого я использую CTE и наткнулся на эту ошибку:

Ошибка SQL [XX000]: ОШИБКА: не удалось найти CTE "aggregated_attributes" (allpaths.c:904)

Чем это вызвано?

Кажется, это происходит в конкретном случае. Я думаю, что это может быть что-то, связанное с областью применения CTE, но я не знаю, как они работают под капотом.

Это работает для следующего запроса:

WITH RECURSIVE udt_tree (udt_catalog, udt_name, attribute_name) AS (
        SELECT
            udt_catalog,
            udt_name,
            attribute_name
        FROM information_schema."attributes"
        WHERE data_type = 'USER-DEFINED'
    UNION ALL
        SELECT
            _attributes.udt_catalog,
            _attributes.udt_name,
            _attributes.attribute_name
        FROM
            udt_tree,
            information_schema."attributes" AS _attributes
        WHERE ((_attributes.udt_name, _attributes.udt_catalog)
                = (udt_tree.attribute_name, udt_tree.udt_catalog))
    ),
    aggregated_attributes AS (
        SELECT
            udt_catalog,
            udt_name,
            array_agg(
                CAST(attribute_name AS varchar)
                ORDER BY attribute_name) AS attributes_names
        FROM udt_tree
        GROUP BY
            udt_catalog,
            udt_name
    ),
    aggregated_tree (udt_catalog, udt_name, attributes_tree) AS (
        SELECT
            udt_catalog,
            udt_name,
            attributes_names AS attributes_tree
        FROM aggregated_attributes
    UNION ALL
        SELECT
            at.udt_catalog,
            at.udt_name,
        CASE
            WHEN aa.udt_name IS NULL THEN at.attributes_tree
            ELSE ARRAY(
                SELECT CAST(attribute_name AS text)
                    FROM (SELECT unnest(at.attributes_tree) AS attribute_name) AS deepest_level
                    WHERE attribute_name <> aa.udt_name
                UNION
                SELECT aa.udt_name || '_' || attribute_name
                    FROM (SELECT unnest(aa.attributes_names) AS attribute_name) AS aggregated_level)
        END AS attributes_tree
        FROM
            aggregated_tree AS at,
            aggregated_attributes AS aa
        WHERE aa.udt_name = ANY (at.attributes_tree)
    )
SELECT
    DISTINCT ON (udt_catalog, udt_name)
    *
FROM aggregated_tree
ORDER BY
    udt_catalog,
    udt_name,
attributes_tree DESC;

Но я хочу, чтобы это выглядело так (что не работает). Они очень похожи, за исключением type_attributes КТР:

WITH RECURSIVE udt_tree (udt_catalog, udt_name, attribute_name) AS (
        SELECT
            udt_catalog,
            udt_name,
            attribute_name
        FROM information_schema."attributes"
        WHERE data_type = 'USER-DEFINED'
    UNION ALL
        SELECT
            _attributes.udt_catalog,
            _attributes.udt_name,
            _attributes.attribute_name
        FROM
            udt_tree,
            information_schema."attributes" AS _attributes
        WHERE ((_attributes.udt_name, _attributes.udt_catalog)
                = (udt_tree.attribute_name, udt_tree.udt_catalog))
    ),
    aggregated_attributes AS (
        SELECT
            udt_catalog,
            udt_name,
            array_agg(
                CAST(attribute_name AS varchar)
                ORDER BY attribute_name) AS attributes_names
        FROM udt_tree
        GROUP BY
            udt_catalog,
            udt_name
    ),
    aggregated_tree (udt_catalog, udt_name, attributes_tree) AS (
        SELECT
            udt_catalog,
            udt_name,
            attributes_names AS attributes_tree
        FROM aggregated_attributes
    UNION ALL
        SELECT
            at.udt_catalog,
            at.udt_name,
        CASE
            WHEN aa.udt_name IS NULL THEN at.attributes_tree
            ELSE ARRAY(
                SELECT CAST(attribute_name AS text)
                    FROM (SELECT unnest(at.attributes_tree) AS attribute_name) AS deepest_level
                    WHERE attribute_name <> aa.udt_name
                UNION
                SELECT aa.udt_name || '_' || attribute_name
                    FROM (SELECT unnest(aa.attributes_names) AS attribute_name) AS aggregated_level)
        END AS attributes_tree
        FROM
            aggregated_tree AS at,
            aggregated_attributes AS aa
        WHERE aa.udt_name = ANY (at.attributes_tree)
    ),
    type_attributes AS (
        SELECT
            DISTINCT ON (udt_catalog, udt_name)
            *
        FROM aggregated_tree
        ORDER BY
            udt_catalog,
            udt_name,
            attributes_tree DESC
    )
SELECT * FROM type_attributes;

Для воспроизведения создайте базу данных с именем ericsson_voz и затем создайте таблицы, используя этот ddl:

CREATE TYPE CAMELTDPData AS (
    serviceKey varchar,
    gsmSCFAddress varchar
);
CREATE TYPE UserRate AS ENUM ('urindneg', 'ur600bps', 'ur1200bps', 'ur2400bps', 'ur3600bps', 'ur4800bps', 'ur7200bps', 'ur8000bps', 'ur9600bps', 'ur14400bps', 'ur16000bps', 'ur19200bps', 'ur32000bps', 'ur38400bps', 'ur48000bps', 'ur56000bps', 'ur64000bps', 'ur38400bps1', 'ur57600bps', 'ur28800bps', 'ur134-5bps', 'ur100bps', 'ur75bps-1200bps', 'ur1200bps-75bps', 'ur50bps', 'ur75bps', 'ur110bps', 'ur150bps', 'ur200bps', 'ur300bps', 'ur12000bps');
CREATE TYPE AsyncSyncIndicator AS ENUM ('syncdata', 'asyncdata');
CREATE TYPE UILayer1Protocol AS ENUM ('v110-x30', 'g711mulaw', 'g711alaw', 'g721-32000bps-i460', 'h221-h242', 'h223-h245', 'nonitu-t', 'v120', 'x31', 'vselp-speech');
CREATE TYPE MultimediaInformation AS (
    userRate UserRate,
    asyncSyncIndicator AsyncSyncIndicator,
    uILayer1Protocol UILayer1Protocol
);
CREATE TYPE TriggerDetectionPoint AS ENUM ('originatingcallattemptauthorized', 'collectedinformation', 'analyzedinformation', 'originatingcallattemptrouteselectfailure', 'originatingcallattemptcalledpartybusy', 'originatingcallattemptcalledpartynotanswer', 'originatingcallattemptcalledpartyanswer', 'originatingcallattemptmid-calleventdetected', 'originatingcallattemptcalldisconnecting', 'originatingcallattemptcallabandon', 'terminatingcallattemptauthorized', 'terminatingcallattemptcalledpartybusy', 'terminatingcallattemptnoanswer', 'terminatingcallattemptanswer', 'terminatingcallattemptmid-calleventdetected', 'terminatingcallattemptcalldisconnect', 'terminatingcallattemptcallabandon', 'terminatingcallattemptcallreanswer', 'terminatingcallattemptcallsuspended', 'terminatingcallattemptcalledpartynotreachable', 'terminatingcallattemptalerting', 'terminatingcallattemptrouteselectfailure', 'originatingcallattemptcalledpartyreanswer', 'originatingcallattemptcallsuspended', 'originatingcallattemptcalledpartynotreachable', 'originatingcallattemptalerting');
CREATE TYPE SCPAddress AS (
    pointCodeAndSubSystemNumber varchar,
    globalTitle varchar,
    globalTitleAndSubSystemNumber varchar
);
CREATE TYPE TriggerData AS (
    triggerDetectionPoint TriggerDetectionPoint,
    serviceKey varchar,
    sCPAddress SCPAddress
);
CREATE TABLE MSOriginating (
    bCSMTDPData1 CAMELTDPData
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;
CREATE TABLE Transit (
    multimediaInformation MultimediaInformation
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;
CREATE TABLE INIncomingCall (
    triggerData0 TriggerData
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;

Ожидаемый результат:

udt_catalog udt_name    attributes_tree
ericsson_voz    multimediainformation   {asyncsyncindicator,uilayer1protocol,userrate}
ericsson_voz    scpaddress  {globaltitle,globaltitleandsubsystemnumber,pointcodeandsubsystemnumber}
ericsson_voz    triggerdata {scpaddress_globaltitle,scpaddress_globaltitleandsubsystemnumber,scpaddress_pointcodeandsubsystemnumber,triggerdetectionpoint}