Jooq: Allow to form CTE from plain SQL or from ResultQuery

[lukaseder]

Currently, common table expressions can be formed only from Select types. We should support also generic ResultQuery types, or plain SQL. One use-case is PostgreSQL's ability to generate CTE from INSERT .. RETURNING statements, e.g.

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

---

Right now, CommonTableExpressionImpl relies on the availability of row type information of the query specified in AS(). This isn't necessarily the case when using plain SQL, which might cause issues when the Table's fields are requested.

---

See also: https://github.com/jOOQ/jOOQ/issues/5723

[reardencode]

:+1:

[tomdcc]

Just adding a +1 here - we just hit a case where we'd like to do this.

[lukaseder]

Thank you for the feedback. We'll re-iterate looking at this for version 3.10.

May I ask both of you, @reardencode / @tomdcc, what would be the kind of use-case where you'd profit from such an extension? Could you show an example query (or several) that you're trying to achieve here? The more I know, the better the support for such API might get...

[tomdcc]

Thanks @lukaseder!

The case that we hit was wanting to do an insert related to another insert, so e.g.

WITH x AS (
    INSERT INTO bar (col1, col2)
    SELECT f.col1, f.col2
    FROM   foo f
    WHERE  f.id BETWEEN 12 AND 23 
    RETURNING col1, col2, bar_id 
)
INSERT INTO foo (col1, col2, bar_id)
SELECT col1, col2, bar_id
FROM x;

[lukaseder]

Thanks @tomdcc, so the usual use-case in PostgreSQL, gotcha

[safaci2000]

Another use case I had was something along these lines.

With x As
(
  -- some distinct SQL condition
   select * from bar where bar.active = 'Y' and modified_date > '01-01-2016'

), update_ui as 
(
  insert into ui.foo (col1, col2, col3) select * from x
  returning id
) update backend.foo set migrated='Y' where id in (select id from update_ui); 

--

this isn't exactly the prod use case I ran into but I've used something similar to create fairly complex conditionals and use the various result of each CTE block in various combinations.

[lukaseder]

Oh, I see, thanks @safaci2000. A PostgreSQL-style, esoteric multi-table upsert. Thus far, I thought that existed only in my crazy SQL presentations :) But it would be great if jOOQ could do it!

[safaci2000]

To be fair, I think a better approach would be use join / multiple statement is much cleaner, but CTE are much easier to read to non-DBAs imo. I think they read very similar to Java 8 streams at least the way I usually write them out. Each CTE block filters the data further and you get a pseudo table that you can easily operate on.

For my code, I find I usually avoid CTEs for production though there are some rare exceptions. But we do have a good number of CTEs so figured I'd chime in on the use cases i've seen.

Another example...

with x as 
(
   // some conditional query
), y as 
(
  // some additional query
) z as (
   some condition that ties x and y and gives you a filtered result set.

), update_ui_schema (
   -- insert / update/delete tables using result from z returning id

), update_backend_schema (
  -- insert /update/delete tables using z schema  returning id

) select update_ui_schema.id as ui_ids, update_backend_schema as backend_ids
from update_ui_schema, update_backend_schema

I'm re-using the same logic / filter to drive the inserts, so rather then re-running it twice, I use CTE.

Hope these examples help.

[lukaseder]

Each CTE block filters the data further

Beware that PostgreSQL has a severe legacy with these CTE: http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences. You should use them sparingly.

This is a different story, of course, if DML is involved.

Nice example... Hmm a cross join. Have you considered using UNION ALL to trigger both updates instead (in case you update many rows)...

[safaci2000]

@lukaseder That'd work as well. Personally I tend to use CTE's more when doing DML. One time updates. Other groups use CTEs all over the place. I sent you some code samples age ago with Multiple nested CTEs where we had compile time issues.

At the end of the day there isn't really anything you're doing with any CTE that can't be done with a different syntax, but especially for migration scripts and such I find myself writing a CTE first (again the whole stream thing makes more sense to me.. and CTEs are closer to how I logically thing about the problem) then optimizing it if has to live in prod that's more then a one time operation.

There's definitely alternatives, just sharing some use cases I ran across of.

[idonman]

Hello.
Is there any progress and/or estimate on data-modifying statements in WITH?
It is an essential feature

[lukaseder]

Thank you very much for your feedback @idonman. We have a lot of "essential" features currently in the queue. :)

I personally hope that this one will make it into the next release, too. It would be really fancy for PostgreSQL users. But we have a lot of requests and ideas and need to prioritise carefully.

In any case, your vote counts, so thanks again for your feedback!

[neylorsousa]

Hi @lukaseder, +1 to reiforce! ;)

[michaelmcchen]

Adding another +1 here - this would be extremely helpful!

[knutwannheden]

Regarding that PostgreSQL optimization fence, read here about PostgreSQL 12: https://www.postgresql.org/about/news/1943/

[jklingsporn]

Is there a workaround to overcome this missing gem?

[lukaseder]

@jklingsporn : Yes of course. As always, plain SQL templating should work: https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

[jklingsporn]

So what I tried was this:

WithStep step = DSL.with("myExpression").as(DSL.select().from("update foo set bar=test returning id"));
//do some meaningful stuff with the step

However SELECT * FROM update foo set bar=test returning id is no valid PgSQL.
DSL.with("myExcpression").as(DSL.queryResult("...")) is not compiling either. Is there another workaround for this or do I have to handcraft the whole query?

[lukaseder]

Can you post your SQL query? I'll translate

[jklingsporn]

I figured out I don't really need the returning here, but the update:

with "updatedValue" as (
update
    "wallets"
    set
        "value" = ("value" + 1000)
    where
        ("userId" in (7)
        and "currency" = 'HC' ) )select
    "userId",
    sum("value") as "newValue"
from
    "wallets"
where
    ("userId" in (7)
    and "currency" = 'HC')
group by
    "userId"

[lukaseder]

I figured out I don't really need the returning here

Oh wow, I was not aware that RETURNING is not necessary. The documentation is clear about it, though:

So, your statement could be written as such (the usual static imports are implied):

ctx.resultQuery("with updatedValue as ({0}) {1}", myUpdateQuery, mySelectQuery).fetch();

This currently produces a Result<Record>. From jOOQ 3.12 onwards, you can get the usual type safety via https://github.com/jOOQ/jOOQ/issues/4473 also on plain SQL queries, as follows:

ctx.resultQuery("with updatedValue as ({0}) {1}", myUpdateQuery, mySelectQuery)
   .coerce(newValueField)
   .fetch();

[monosoul]

Just bumping this up

[lukaseder]

Looking into this now. A first prerequisite seems to be the unification of various Fetchable query types to share more API. This has been done in #3185. Soon looking into what's required to allow CTE to reference Fetchable or ResultQuery or some new type.

[knutwannheden]

I also just stumbled across this issue with a query containing two INSERTs and one UPDATE as CTEs. In my case I use this construct to implement application versioning for data I synchronize from an external source. Of course jOOQ's SQL templating mechanism came to the rescue!