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: | Whole Thread | Raw Message | 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...
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 |