Difference with PostgreSQL

[eltorio]

Describe the problem

[Please describe the issue you observed, and any steps we can take to reproduce it: ](q: unnest(): generator functions are not allowed in VALUES)

To Reproduce

I'd like to use CockroachDB with Listmonk which was designed for PostgreSQL 9.4+ I succeed to modify the schema for being working with CockroachDB 22.2.3 because the only problem I had was with DROP TYPE CASCADE…

But while preparing queries I get an error pq: unnest(): generator functions are not allowed in VALUES the cause is in this query

WITH sub AS (
    INSERT INTO subscribers (uuid, email, name, status, attribs)
    VALUES($1, $2, $3, $4, $5)
    RETURNING id, status
),
listIDs AS (
    SELECT id as id FROM lists WHERE
        (CASE WHEN CARDINALITY($6::INT[]) > 0 THEN id=ANY($6)
              ELSE uuid=ANY($7::UUID[]) END)
),
subs AS (
    INSERT INTO subscriber_lists (subscriber_id, list_id, status)
    VALUES(
        (SELECT id FROM sub),
        UNNEST(ARRAY(SELECT id FROM listIDs)),
        (CASE WHEN $4='blocklisted' THEN 'unsubscribed'::subscription_status ELSE $8::subscription_status END)
    )
    ON CONFLICT (subscriber_id, list_id) DO UPDATE
        SET updated_at=NOW(),
            status=(
                CASE WHEN $4='blocklisted' OR (SELECT status FROM sub)='blocklisted'
                THEN 'unsubscribed'::subscription_status
                ELSE $8::subscription_status END
            )
)
SELECT id from sub;

If possible, provide steps to reproduce the behavior:

1. Set up CockroachDB cluster ...
2. Send SQL ... / CLI command ...
3. Look at UI / log file / client app ...
4. See error

Expected behavior This is working in PostgreSQL

Environment:

• CockroachDB version 22.2.3 hosted by cockroachlabs

Jira issue: CRDB-24339

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[HonoreDB]

Thanks for the detailed report! I've added some tags to get this triaged by the right team.

[eltorio]

for being complete this is the schema: https://github.com/highcanfly-club/hcfmailer/blob/master/schema.sql