QueryBuilder cannot combine union parts with CTE

[Bilge]

Bug Report

Q	A
Version	4.4.1
Previous Version if the bug is a regression	n/a

Summary

A query with union parts cannot also have a CTE (with) expression.

Current behavior

$query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL();

This returns a union with no CTE.

Expected behavior

The CTE should be present in the output.

How to reproduce

As above.

[Bilge]

Having dug into the code a little bit, I can see CTEs are not actually supported for anything other than SELECT, which is just wrong. CTEs are valid for any query type (INSERT, DELETE, etc.).

[derrabus]

cc @sbuerk

[derrabus]

Since you've dug into the code already, do you want to work on a patch, @Bilge?

[sbuerk]

Need to recheck, but as far as I know the INSERT/DELETE CTE support is even smaler and more different accross platforms supporting it. That was the reason I went with the SELECT part
first for the internal integration of the TYPO3 core.

The implementation in doctrine/dbal contributed/started by someone else (not remembering the name) also started with the select part for good.

The current solution still misses the "recursive" part. TBH not rechecked if I missed the followup - or need to contribute that so we can drop the internal implementation in TYPO3.

@Bilge

$query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL();

Correct.

1. ->with() sets the CTE mode internally (SELECT) and setting cte" part removing previos set CTE parts (non-recursive).
2. ->union() sets the UNION mode internally (SELECT) and setting the union part (removing previos set parts).

That's working like expected, designed and it should be.

Can you please explain what query you exactly try to build ? For me it looks like you are trying to create a recursive part which is not possible in that way. That must be defined on the union part level and not on the outer CTE part level.

From a builder POB the outer QueryBuilder instance is used to declare the outer/lower SELECT part (currently).

WITH cte (SELECT field1, field2 FROM some_table)

/* unclear as code does not provide something or it is missing */
SELECT
  * FROM cte

In my eyes the reported issue is no bug and stems from not using the API in the way it should and I hope that I have answered that with the examples and explanations above.

From one of the comments:

Having dug into the code a little bit, I can see CTEs are not actually supported for anything other than SELECT, which is just wrong. CTEs are valid for any query type (INSERT, DELETE, etc.).

Correct. That should be a additional followup and needs more and dedicates investigaten in to continue on that. In my eyes the recursive support would be a good thing first for the select to complete it (TYPO3 needs and uses recursive and was the main goal to implement it there).

We had already possible places where at a least a DELETE CTE would be nice but did not investigated the forth and backs for that, only knowing that it comes with lesser support or more dfferent things to take care accross DBMS than the SELECT part (which already has some quirks and different behavious across the DBMS many not really know).

If the goal was to use a CTE as union part you need to create the CTE QueryBuilder and use it as subquery for the union part on a dedicated QueryBuilder instance.

$cte = $connection->createQueryBuilder();
// ... declaring cte querybuilder

$union = $connection->createQueryBuilder();
$union->union($cte)->addUnion($secondUnionPart)->getSQL():

which should generate something like

      (WITH cte ( SELECT field1, field2 FROM some_table) SELECT * from CTE)
UNION 
      (SELECT fieldX as field1, 'static value' as field2 FROM other_table)

ORDER BY field1 ASC

A query with union parts cannot also have a CTE (with) expression.

Reading that "now" it's exactly the last thing above - to use a CTE as UNION part.

• UNION parts a kind of SUBQUERIES and from interface pov needs to be provided
  with a dedicated QueryBuilder instance and handed over as the UNION part.

• WITH parts are technically also SUBQUERIES and recieves a sub QueryBuilder to define them.

What your code snippet indicates is that you are trying to handle that on a single querybuilder level, which is not possible.

->with() makes a CTE query out of the instance on which the method is used
->union() transforms it to a UNION query.

$query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL();

The passed $cte should be already a WITH QueryBuilder using ->with() on that instance or a similar SQL string.

So you need to rearrange your code (example):

// 1. create all required querybuilder instances
$ctePart1 = $connection->createQueryBuilder();
$ctePart2 = $connection->createQueryBuilder();
$cte = $connection->createQueryBuilder();
$unionPart = $connection->createQueryBuilder();
// $union is the main querybuilder using the above in a subquery chain
$union = $connection->createQueryBuilder();

// define the CTE query used below as subquery for an union part
$ctePart1
    ->select('id')
    ->from('table_a')
    ->where('id = :cteid'); // we need to set that parameter on
                            // the union querybuilder instance.
$ctePart2
    ->select('id')
    ->from('table_b');
$cte
    ->with('cte_a', $ctePart1) // first CTE part
    ->with('cte_b', $ctePart2) // second CTE part
    // now defining the CTE select part based on the parts
    ->select('id')
        ->from('cte_b', 'b')
        ->join('b', 'cte_a', 'a', 'a.id = b.id');

// 2. create the second UNIOn query part
$unionPart
  ->select('field1', 'field2'),
  ->from('other_table);

// 3. create the UNION query (MAIN/OUTER instance)
$union
  // use the CTE query as first union part
  ->union($cte)
  // add another QueryBuilder as second union part
  ->addUnion($unionPart)
  // We need to set the parameter used within the CTE (CTE part)
  ->setParameter('cteid', 1) // => $ctePart1 `:cteid`

// 4. get the complete SQL (with placeholder/parameters not replaced)
$sql = $union->getSQL();

or as pseudo for the $union level.

-- $union intance 
      ( /* $cte->getSQL() */ )
UNION ( /* $unionPart->getSQL() */ )

@derrabus In my eyes this bu report is not a bug report and should be closed (after some time and confirmation from @Bilge ). Regarding the INSERT/DELETE and RECURSIVE CTE .. that are different topics and not part of this report (bug).

[Bilge]

This has nothing to do with recursion.

…

On Tue, 24 Feb 2026, 09:14 Stefan Bürk, ***@***.***> wrote: *sbuerk* left a comment (doctrine/dbal#7318) <#7318 (comment)> Need to recheck, but as far as I know the INSERT/DELETE CTE support is even smaler and more different accross platforms supporting it. That was the reason I went with the SELECT part first for the internal integration of the TYPO3 core. The implementation in doctrine/dbal contributed/started by someone else (not remembering the name) also started with the select part for good. The current solution still misses the "recursive" part. TBH not rechecked if I missed the followup - or need to contribute that so we can drop the internal implementation in TYPO3. @Bilge <https://github.com/Bilge> $query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL(); Correct. 1. ->with() sets the CTE mode internally (SELECT) and setting cte" part removing previos set CTE parts (non-recursive). 2. ->union() sets the UNION mode internally (SELECT) and setting the union part (removing previos set parts). That's working like expected, designed and it should be. Can you please explain what query you exactly try to build ? For me it looks like you are trying to create a recursive part which is not possible in that way. That must be defined on the union part level and not on the outer CTE part level. From a builder POB the outer QueryBuilder instance is used to declare the outer/lower SELECT part (currently). WITH cte (SELECT field1, field2 FROM some_table) /* unclear as code does not provide something or it is missing */SELECT * FROM cte In my eyes the reported issue is no bug and stems from not using the API in the way it should and I hope that I have answered that with the examples and explanations above. From one of the comments: Having dug into the code a little bit, I can see CTEs are not actually supported for anything other than SELECT, which is just wrong. CTEs are valid for any query type (INSERT, DELETE, etc.). Correct. That should be a additional followup and needs more and dedicates investigaten in to continue on that. In my eyes the recursive support would be a good thing first for the select to complete it (TYPO3 needs and uses recursive and was the main goal to implement it there). We had already possible places where at a least a DELETE CTE would be nice but did not investigated the forth and backs for that, only knowing that it comes with lesser support or more dfferent things to take care accross DBMS than the SELECT part (which already has some quirks and different behavious across the DBMS many not really know). If the goal was to use a CTE as union part you need to create the CTE QueryBuilder and use it as subquery for the union part on a dedicated QueryBuilder instance. $cte = $connection->createQueryBuilder(); // ... declaring cte querybuilder $union = $connection->createQueryBuilder(); $union->union($cte)->addUnion($secondUnionPart)->getSQL(): which should generate something like (WITH cte ( SELECT field1, field2 FROM some_table) SELECT * from CTE)UNION (SELECT fieldX as field1, 'static value' as field2 FROM other_table) ORDER BY field1 ASC A query with union parts cannot also have a CTE (with) expression. Reading that "now" it's exactly the last thing above - to use a CTE as UNION part. - UNION parts a kind of SUBQUERIES and from interface pov needs to be provided with a dedicated QueryBuilder instance and handed over as the UNION part. - WITH parts are technically also SUBQUERIES and recieves a sub QueryBuilder to define them. What your code snippet indicates is that you are trying to handle that on a single querybuilder level, which is not possible. ->with() makes a CTE query out of the instance on which the method is used ->union() transforms it to a UNION query. $query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL(); The passed $cte should be already a WITH QueryBuilder using ->with() on that instance or a similar SQL string. So you need to rearrange your code (example): // 1. create all required querybuilder instances$ctePart1 = $connection->createQueryBuilder();$ctePart2 = $connection->createQueryBuilder();$cte = $connection->createQueryBuilder();$unionPart = $connection->createQueryBuilder();// $union is the main querybuilder using the above in a subquery chain$union = $connection->createQueryBuilder(); // define the CTE query used below as subquery for an union part$ctePart1 ->select('id') ->from('table_a') ->where('id = :cteid'); // we need to set that parameter on // the union querybuilder instance.$ctePart2 ->select('id') ->from('table_b');$cte ->with('cte_a', $ctePart1) // first CTE part ->with('cte_b', $ctePart2) // second CTE part // now defining the CTE select part based on the parts ->select('id') ->from('cte_b', 'b') ->join('b', 'cte_a', 'a', 'a.id = b.id'); // 2. create the second UNIOn query part$unionPart ->select('field1', 'field2'), ->from('other_table); // 3. create the UNION query (MAIN/OUTER instance)$union // use the CTE query as first union part ->union($cte) // add another QueryBuilder as second union part ->addUnion($unionPart) // We need to set the parameter used within the CTE (CTE part) ->setParameter('cteid', 1) // => $ctePart1 `:cteid` // 4. get the complete SQL (with placeholder/parameters not replaced)$sql = $union->getSQL(); @derrabus <https://github.com/derrabus> In my eyes this bu report is not a bug report and should be closed (after some time and confirmation from @Bilge <https://github.com/Bilge> ). Regarding the INSERT/DELETE and RECURSIVE CTE .. that are different topics and not part of this report (bug). — Reply to this email directly, view it on GitHub <#7318 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADS4YVJWNIUO3PSGZ2JMWL4NQI7DAVCNFSM6AAAAACV32757KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTSNJQGI2DINRZGA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[sbuerk]

This has nothing to do with recursion.
…

@Bilge See the rest and how to use the api correctly. You missed proper subinstance usage.

[Bilge]

I missed nothing. You're making false assertions about valid query structure.

…

On Tue, 24 Feb 2026, 09:29 Stefan Bürk, ***@***.***> wrote: *sbuerk* left a comment (doctrine/dbal#7318) <#7318 (comment)> This has nothing to do with recursion. … <#m_-6453660161913098207_> @Bilge <https://github.com/Bilge> See the rest and how to use the api correctly. You missed proper subinstance usage. — Reply to this email directly, view it on GitHub <#7318 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADS4YUGDQFB22VTDGDW27D4NQKXJAVCNFSM6AAAAACV32757KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTSNJQGMYTQNBZGI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[Bilge]

For the avoidance of doubt, here is the structure of the (valid) query I was building.

WITH base AS (
    ...
)
(
    SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10
)
UNION ALL
(
    SELECT *, 'dn' subgroup FROM base WHERE ... LIMIT 10
)
UNION ALL
(
    SELECT *, 'nu' subgroup FROM base WHERE ... LIMIT 10
)
UNION ALL
(
    SELECT *, 'no' subgroup FROM base WHERE ... LIMIT 10
)

[sbuerk]

I missed nothing. You're making false assertions about valid query
structure.

Seeing now the SQL from #7318 (comment)
it's clearer and concrete what your use case is and what you are trying to do.

Based on the original BUG report and the the code:

$query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL();

and

The CTE should be present in the output.

it is not clear enough what you tried to archieve and I assumped that you tried to use the CTE as part of a UNION query. Which is possible when adding the CTE as union part and not mixing the calls and producting a UNION using the WITH as one part.

NOW it's clear that my assumption was wrong and I'm sorry for that. I'd try to assume things and help out instead of asking first for more clarity and what you are trying to get working. My fault.

Tip

Making reports more concreate and providing clear context
what is tried to archieve, for example a PLAIN SQL code
either real or PSEUDO to visualise it helps to assume
things in the wrong direction.

Is following pseudo SQL with added nesting to increase visibilty what is tried to build correct ?

WITH base AS (
    ...
)
( -- START CTE SELECT PART
    (
        SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10
    )
  UNION ALL
    (
        SELECT *, 'dn' subgroup FROM base WHERE ... LIMIT 10
    )
  UNION ALL
    (
        SELECT *, 'nu' subgroup FROM base WHERE ... LIMIT 10
    )
  UNION ALL
    (
        SELECT *, 'no' subgroup FROM base WHERE ... LIMIT 10
    )
) -- END OF CTE SELECT PART

You are right that this is not possible to build with the current API and implementation because the UNION building completly ignores the CTE parts (with's) and only creating the select part.

Personally never had that on the table and I don't know if that is really possible and working. Have you tried that with a PLAIN query against the database if it is really working ?

• If not we or better to say doctrine/dbal does not have to melting the brain to get this code wise working without messing around with the API.
• If yes we or better to say doctrine/dbal needs to deal with it and try to add it without messing around with the API or breaking things.

Not sure if this still counts as BUG or more a FEATURE request to improve the API and support like missing CTE support would be also a FEATURE and not a bugfix.

Howeverl, I'm now on travel to a on-location sprint for a project and do not have time to look into the code now to see if there is a simple way to get this together as possible guidance for a PR to create by someone else.

I'm trying to have a look on this on the weekend and if possible try to come up with a pull-request for this (after verifiny that this really works with plain SQL).

In case it works with PLAIN SQL this is a missing FEATURE in my eyes.

@derrabus Would this be than a feature or stays a bug from your side ?

Note

DISCLAIMER I'm not part of the doctrine/dbal and my comments
here are soly my personally view unconnected to any project done
alone as a casual and external contributor.

Not tried it if it works, but maybe you could create the UNION thing as last CTE part .. maybe that could work:

$cte->select()->from();
$union->union(...);
$query
  ->with('base', $cte)
  ->with('aggregate', $union)
  ->select('*')->from('aggregate')

which should create something like

WITH
  base AS (SELECT * FROM table_one),
  aggregate AS (
          (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10)
    UNION ALL
          (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10)
    UNION ALL
          (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10)
  )
-- WITH select part using the aggregated table as source
SELECT * FROM aggregate

Not sure if that does work or if that crashes because it assums that
as recursive than (recursinge requires a two part UNION) and a WITH RECURSIBE in case at least ONE part is recursive .. which this is not the case.

In case it works at least a first solution for you.

[Bilge]

The `WITH` clause is the CTE. The remainder is not a CTE.

…

On Tue, 24 Feb 2026, 10:56 Stefan Bürk, ***@***.***> wrote: *sbuerk* left a comment (doctrine/dbal#7318) <#7318 (comment)> I missed nothing. You're making false assertions about valid query structure. Seeing now the SQL from #7318 (comment) <#7318 (comment)> it's clearer and concrete what your use case is and what you are trying to do. Based on the original BUG report and the the code: $query->with('cte', $cte)->union($alfa)->addUnion($bravo)->getSQL(); and The CTE should be present in the output. it is not clear enough what you tried to archieve and I assumped that you tried to use the CTE as part of a UNION query. Which is possible when adding the CTE as union part and not mixing the calls and producting a UNION using the WITH as one part. NOW it's clear that my assumption was wrong and I'm sorry for that. I'd try to assume things and help out *instead* of asking first for more clarity and what you are trying to get working. *My fault*. Tip Making reports more concreate and providing clear context what is tried to archieve, for example a PLAIN SQL code either real or PSEUDO to visualise it helps to assume things in the wrong direction. Is following pseudo SQL with added nesting to increase visibilty what is tried to build correct ? WITH base AS ( ... ) ( -- START CTE SELECT PART ( SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10 ) UNION ALL ( SELECT *, 'dn' subgroup FROM base WHERE ... LIMIT 10 ) UNION ALL ( SELECT *, 'nu' subgroup FROM base WHERE ... LIMIT 10 ) UNION ALL ( SELECT *, 'no' subgroup FROM base WHERE ... LIMIT 10 ) ) -- END OF CTE SELECT PART You are right that this is not possible to build with the current API and implementation because the UNION building completly ignores the CTE parts (with's) and only creating the select part. Personally never had that on the table and I don't know if that is really possible and working. Have you tried that with a PLAIN query against the database if it is really working ? - If not we or better to say doctrine/dbal does not have to melting the brain to get this code wise working without messing around with the API. - If yes we or better to say doctrine/dbal needs to deal with it and try to add it without messing around with the API or breaking things. Not sure if this still counts as BUG or more a FEATURE request to improve the API and support like missing CTE support would be also a FEATURE and not a bugfix. Howeverl, I'm now on travel to a on-location sprint for a project and do not have time to look into the code now to see if there is a simple way to get this together as possible guidance for a PR to create by someone else. I'm trying to have a look on this on the weekend and if possible try to come up with a pull-request for this (after verifiny that this really works with plain SQL). In case it works with PLAIN SQL this is a missing FEATURE in my eyes. @derrabus <https://github.com/derrabus> Would this be than a feature or stays a bug from your side ? Note *DISCLAIMER* I'm not part of the doctrine/dbal and my comments here are soly my personally view unconnected to any project done alone as a casual and external contributor. Not tried it if it works, but maybe you could create the UNION thing as last CTE part .. maybe that could work: $cte->select()->from();$union->union(...);$query ->with('base', $cte) ->with('aggregate', $union) ->select('*')->from('aggregate') which should create something like WITH base AS (SELECT * FROM table_one), aggregate AS ( (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10) UNION ALL (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10) UNION ALL (SELECT *, 'up' subgroup FROM base WHERE ... LIMIT 10) )-- WITH select part using the aggregated table as sourceSELECT * FROM aggregate Not sure if that does work or if that crashes because it assums that as recursive than (recursinge requires a two part UNION) and a WITH RECURSIBE in case at least ONE part is recursive .. which this is not the case. In case it works at least a first solution for you. — Reply to this email directly, view it on GitHub <#7318 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADS4YW2BY2J72GYXH6VC4D4NQU4ZAVCNFSM6AAAAACV32757KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTSNJQHA2DKMBZG4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[sbuerk]

The WITH clause is the CTE. The remainder is not a CTE.
…

Sorry, that's absolutly not correct. base is the name of ONE CTE part, but the CTE is your whole example. The CTE starts with the WITH key word and ends with the SELECT (or in general a INSERT/DELETE with one or more WITH parts building a chain.

There is no NON-CTE remainder in your SQL Example #7318 (comment)
as all union parts are refering the base part of the CTE. See below, I'm not sure if a UNION is in gerenal supported as SELECT part to aggregate multiple CTE parts together. Need to test this on my own on the weekend as now being on the way.

If you take the UNION parts as NOT part of the CTE then see above and you need to wrap the CTE as UNION part. The issue is that you cannot refere to the BASE part in union the subsequential UNION parts. That is not supported by the DBMS.

WITH [RECURSIVE]
  <WITH-PART>
  <WITH-PART>
  <WITH-part>
  ...
<SELECT PART>

For a WITH-PART:

<with-alias> AS (<SUBQUERY>)

making it

WITH [RECURSIVE]
  <with-alias> AS (<SUBQUERY>),
  <with-alias> AS (<SUBQUERY>),
  <with-alias> AS (<SUBQUERY>)[,]
  ...
<SELECT PART>

A recursive part would be in general

  <with-alias> AS (
       <start subquery>
     UNION [ALL]
       <limit/recursive subquery>
  )

A cte must have at least one part, but can have multiple parts which can use the result of a previous part (ORDER IS KEY HERE).

See for MySQL -> https://dev.mysql.com/doc/refman/8.4/en/with.html.
Sadly, there is not a single example using a UNION for the CTE SELET part so needs to be tested with PLAIN if that works or not.

I know that WITH is a cte. That's not the point.

I don't know if CTE allows using a UNION for the SELECT part. I really dead simple don't know that and don't have the time to test this.

To create a recursive CTE at least one WITH-PART needs to have a two part UNION refereinc it on (start and nesting parts) and some DBMS requires that the WITH RECURSIVE needs to be used instead of WITH.

So far as a side note. The recursive thing is the reason why I don't know if a UNION select with two or more parts can be used as WITH-PART in case they are not self referencing to the own part. If that works you couly move the union from the SELECT part to the PARTS as the LAST part creating the union aggregate there and than simply selecting it outside. If that works from the DBMS side.

If not and UNION is really supported on the SELECT part we need to change the API.

Try ot move it as WITH part and if it works you have a solution for now, other wise we need to see.

As already told I'm now out until the weekend and on tour now.

[sbuerk]

I verified that on the weekend and it's really possible to use a UNION query as CTE main part, albeit I still could not find any documentation for any database vendor on that.

Taking this report now as bug report for a missing oversight to complete the select flavour of a CTE I'll push a bugfix pull-request for it afterwards.

@Bilge You may want to verify #7326

@derrabus Okay for you to handle this as bugfix for 4.4.x ?