error in example SQL ?

22 views
Skip to first unread message

tod...@googlemail.com

unread,
Feb 21, 2022, 5:04:27 PMFeb 21
to jOOQ User Group
Maybe I missed something, but when I try the PostgreSQL example from the otherwise great "No More MultipleBagFetchException Thanks to Multiset Nested Collections" blog post, I get the following error:

[Code: 0, SQL State: 42P01]  ERROR: missing FROM-clause entry for table "a"
  Position: 156  [Script position: 156 - 168]

I've tried with DBVisualizer and DBeaver.

The faulty SQL:

SELECT
  f.title,
  (
    SELECT coalesce(
      jsonb_agg(jsonb_build_array("v0", "v1")),
      jsonb_build_array()
    )
    FROM (
      SELECT
        a.first_name AS "v0",
        a.last_name AS "v1"
      FROM film_actor AS fa
        JOIN actor AS f
          USING (actor_id)
      WHERE fa.film_id = f.film_id
    ) AS "t"
  ) AS actors,
  (
    SELECT coalesce(
      jsonb_agg(jsonb_build_array("v0")),
      jsonb_build_array()
    )
    FROM (
      SELECT c.name AS "v0"
      FROM film_category AS fc
        JOIN category AS c
          USING (category_id)
      WHERE fc.film_id = f.film_id
    ) AS "t"
  ) AS categories
FROM film AS f
ORDER BY f.title

'a' isn't declared somewhere and the actor table doesn't contain column film_id ...

Kind regards
Dominik

Lukas Eder

unread,
Feb 22, 2022, 2:19:45 AMFeb 22
to jOOQ User Group
Thanks for your message. Yes of course, there's a wrong table alias here:

JOIN actor AS f

It should be:

JOIN actor AS a

I added the aliases for readability purposes. The actual query generated by jOOQ doesn't alias the tables

Best Regards,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0fca15d0-b12b-471a-9258-44b9310d7482n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages