12.4 -> 12.5 upgrade, broken CTE query

From: Jason Ayre <jasonayre(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: 12.4 -> 12.5 upgrade, broken CTE query
Date: 2021-03-10 21:04:21
Message-ID: CAJ7aF3ytf3q0YxGvw+t7yG_JUbFEL5uvdkV8AC3=3Sr-cXaOng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

RDS Auto updated my instanced from 12.4 to 12.5 last night, and the
following query (and several others like it) which were previously working
throws a "set-returning functions must appear at the top level of FROM" -
Query is used to order "questions" from a jsonb field on "survey"
containing "custom_fields.questions_order"

https://gist.github.com/jasonayre/54177560de0e77912bdefab965f6868d

WITH "questions_order" AS ( SELECT question_id, sort_position
FROM surveys,
uuid(jsonb_array_elements_text(surveys.custom_fields->'questions_order'))
WITH ORDINALITY AS f(question_id, sort_position)
WHERE "surveys"."id" IN ('1c003de0-a6b0-42e5-9679-938af99e75a2')
) SELECT questions.*, questions_order.sort_position FROM "questions" JOIN
questions_order ON questions.id = questions_order.question_id WHERE
"questions"."survey_id" = '1c003de0-a6b0-42e5-9679-938af99e75a2' ORDER BY
survey_id, questions_order.sort_position

-- Data structure is
survey: {
custom_fields: {
questions_order: [uuid_1, uuid_2, etc]
},
id
}

custom fields is a jsonb field

question: {
id
}

After upgrade, query yields:

ERROR: set-returning functions must appear at top level of FROM
LINE 2: FROM surveys, uuid(jsonb_array_elements_tex...

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2021-03-10 21:51:31 Re: 12.4 -> 12.5 upgrade, broken CTE query
Previous Message PG Bug reporting form 2021-03-10 20:41:32 BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations