pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
12.4 -> 12.5 upgrade, broken CTE query
2+ messages / 2 participants
[nested] [flat]

* 12.4 -> 12.5 upgrade, broken CTE query
@ 2021-03-10 21:04  Jason Ayre <jasonayre@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Jason Ayre @ 2021-03-10 21:04 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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...

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: 12.4 -> 12.5 upgrade, broken CTE query
@ 2021-03-10 21:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jason Ayre <jasonayre@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2021-03-10 21:51 UTC (permalink / raw)
  To: Jason Ayre <jasonayre@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

Jason Ayre <jasonayre@gmail.com> writes:
> 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" -

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

That restriction's been there since v10, so I'm having a hard time
believing that this query worked in 12.4.

It looks to me like the easiest way to get around it is just to put
the uuid() call in the SELECT output list:

WITH "questions_order" AS (
      SELECT uuid(question_id) AS question_id, sort_position
          FROM surveys,
          jsonb_array_elements_text(surveys.custom_fields->'questions_order')
          WITH ORDINALITY AS f(question_id, sort_position)
          ...

In other cases it might be best to stick the extra function into
an additional layer of lateral function, like

          FROM surveys,
          jsonb_array_elements_text(surveys.custom_fields->'questions_order')
          WITH ORDINALITY AS f(question_id, sort_position),
          uuid(f.question_id) AS whatever

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2021-03-10 21:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 21:04 12.4 -> 12.5 upgrade, broken CTE query Jason Ayre <jasonayre@gmail.com>
2021-03-10 21:51 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox