DDC-2695: Order by clause left in subquery when using MSSQL and Paginator #3376

New Issue

admin · 2026-01-22T14:18:51+01:00

admin commented

2026-01-22 14:18:51 +01:00

Originally created by @doctrinebot on GitHub (Sep 20, 2013).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user manseuk:

When using the Doctrine paginator and Fetch Join Collections = true and when the original query has an "ORDER BY" clause in it - the subquery created is something like "SELECT DISTINCT FROM (.... ORDER BY ....) as dctrn_result" - when using Microsoft SQL server having the "ORDER BY" clause in a subquery throws an error. This is a similar problem to http://doctrine-project.org/jira/browse/DDC-2282 but that was for the Count Walker - this is for the LimitSubqueryOutputWalker. The fix is the same.

Originally created by @doctrinebot on GitHub (Sep 20, 2013). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user manseuk: When using the Doctrine paginator and Fetch Join Collections = true and when the original query has an "ORDER BY" clause in it - the subquery created is something like "SELECT DISTINCT FROM (.... ORDER BY ....) as dctrn_result" - when using Microsoft SQL server having the "ORDER BY" clause in a subquery throws an error. This is a similar problem to http://doctrine-project.org/jira/browse/[DDC-2282](http://www.doctrine-project.org/jira/browse/DDC-2282) but that was for the Count Walker - this is for the LimitSubqueryOutputWalker. The fix is the same.

admin added the Bug label 2026-01-22 14:18:52 +01:00

admin commented

2026-01-22 14:18:52 +01:00

@doctrinebot commented on GitHub (Sep 20, 2013):

Comment created by manseuk:

Problem still occurs in 2.4 - but different error message,

Query :

SELECT * FROM (SELECT DISTINCT id0, ROW*NUMBER() OVER (ORDER BY c1_.iccid ASC) AS doctrine_rownum FROM (SELECT w0_.id AS id0, w0_.batch_id AS batch_id1, c1_.id AS id2, c1_.iccid AS iccid3, c2_.id AS id4, c2_.msisdn AS msisdn5, c3_.id AS id6, c3_.name AS name7, c4_.id AS id8, c5_.id AS id9, c5_.name AS name10, w6_.id AS id11, w6_.created AS created12, w7_.id AS id13, w7_.ident AS ident14, w8_.id AS id15, w8_.ident AS ident16, c9_.id AS id17, c10_.id AS id18, c10_.contract_length AS contract_length19, c10_.rental AS rental20, w11_.id AS id21, w11_.ident AS ident22 FROM workflow_request w0_ WITH (NOLOCK) INNER JOIN core_sim c1_ ON w0_.sim = c1_.id LEFT JOIN core_connection c2_ ON c1_.active_connection_id = c2_.id INNER JOIN core_billing_account c3_ ON c1_.billing_account_id = c3_.id INNER JOIN core_mno_account c4_ ON c1_.mno_account_id = c4_.id INNER JOIN core_mno c5_ ON c4_.mno_id = c5_.id INNER JOIN workflow_request_log w6_ ON w0_.id = w6_.request INNER JOIN workflow_action w7_ ON w0_.action = w7_.id INNER JOIN workflow_request_status w8_ ON w0_.status = w8_.id INNER JOIN core_customer_solution c9_ ON w0_.customer_solution = c9_.id INNER JOIN core_customer_tariff c10_ ON c9_.customer_tariff_id = c10_.id INNER JOIN workflow_request_action w11_ ON w6_.request_action = w11_.id WHERE w7_.ident IN (?) AND w8_.ident = ? AND w11_.ident = ?) dctrn_result) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 25' with params ["activate", "network", "approved"]

Error :
{quote}
SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "c1_.iccid" could not be bound.
{quote}

@doctrinebot commented on GitHub (Sep 20, 2013): Comment created by manseuk: Problem still occurs in 2.4 - but different error message, Query : ``` SELECT * FROM (SELECT DISTINCT id0, ROW*NUMBER() OVER (ORDER BY c1_.iccid ASC) AS doctrine_rownum FROM (SELECT w0_.id AS id0, w0_.batch_id AS batch_id1, c1_.id AS id2, c1_.iccid AS iccid3, c2_.id AS id4, c2_.msisdn AS msisdn5, c3_.id AS id6, c3_.name AS name7, c4_.id AS id8, c5_.id AS id9, c5_.name AS name10, w6_.id AS id11, w6_.created AS created12, w7_.id AS id13, w7_.ident AS ident14, w8_.id AS id15, w8_.ident AS ident16, c9_.id AS id17, c10_.id AS id18, c10_.contract_length AS contract_length19, c10_.rental AS rental20, w11_.id AS id21, w11_.ident AS ident22 FROM workflow_request w0_ WITH (NOLOCK) INNER JOIN core_sim c1_ ON w0_.sim = c1_.id LEFT JOIN core_connection c2_ ON c1_.active_connection_id = c2_.id INNER JOIN core_billing_account c3_ ON c1_.billing_account_id = c3_.id INNER JOIN core_mno_account c4_ ON c1_.mno_account_id = c4_.id INNER JOIN core_mno c5_ ON c4_.mno_id = c5_.id INNER JOIN workflow_request_log w6_ ON w0_.id = w6_.request INNER JOIN workflow_action w7_ ON w0_.action = w7_.id INNER JOIN workflow_request_status w8_ ON w0_.status = w8_.id INNER JOIN core_customer_solution c9_ ON w0_.customer_solution = c9_.id INNER JOIN core_customer_tariff c10_ ON c9_.customer_tariff_id = c10_.id INNER JOIN workflow_request_action w11_ ON w6_.request_action = w11_.id WHERE w7_.ident IN (?) AND w8_.ident = ? AND w11_.ident = ?) dctrn_result) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 25' with params ["activate", "network", "approved"] ``` Error : {quote} SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "c1_.iccid" could not be bound. {quote}

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3376