doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-04-29 17:33:15 +02:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-2470: Sql Server error in createQuery using ORDER BY and setMaxResults #3099

New Issue
Closed
opened 2026-01-22 14:12:10 +01:00 by admin · 7 comments
admin commented 2026-01-22 14:12:10 +01:00
Owner
Copy Link
Copy Source

Originally created by @doctrinebot on GitHub (May 24, 2013).

Jira issue originally created by user jonsxaero:

Important: This issue only affect to 2.4.* versions

When executing

$query = $em->createQuery('
SELECT m.nombre
     , m.fechainicio
     , m.fechafin 
  FROM Bundle:Medicion m
  JOIN m.estudio e
  JOIN e.cliente c
  JOIN c.usuarios u
 WHERE u.id = :id
 ORDER BY m.fechainicio DESC
')
->setMaxResults(12);

Get the following error:

An exception occurred while executing '
SELECT * 
  FROM (
           SELECT m0_.NOMBRE AS NOMBRE0
                , m0_.FECHAINICIO AS FECHAINICIO1
                , m0_.FECHAFIN AS FECHAFIN2
                , ROW*NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine*rownum 
             FROM MEDICION m0_ WITH (NOLOCK) 
            INNER JOIN ESTUDIO e1_ 
               ON m0*.ESTUDIO_ID = e1*.ID 
            INNER JOIN CLIENTE c2_ 
               ON e1*.CLIENTE_ID = c2*.ID 
            INNER JOIN USUARIO u3_ 
               ON c2*.ID = u3_.CLIENTE_ID WHERE u3*.ID = ?
       ) AS doctrine_tbl 
 WHERE doctrine_rownum BETWEEN 1 AND 12
' with params [2]:

SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]El nombre de columna 'FECHAINICIO1' no es válido.

Attached the BD model

Added extra info!

Engine version: Sql server 2008 R2

When executing this SQL (returned by doctrine error) on Management Studio

SELECT * FROM (SELECT m0*.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2, ROW_NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = 12) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 12

Get the following error:

El nombre de columna 'FECHAINICIO1' no es válido. ('FECHAINICIO1' is invalid)

But if change "FECHAINICIO1"

... OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM...

by this

... OVER (ORDER BY m0*.FECHAINICIO DESC) AS doctrine*rownum FROM...

Don't get error

regards
Jonnatan Oyarzún

Originally created by @doctrinebot on GitHub (May 24, 2013). Jira issue originally created by user jonsxaero: #### Important: This issue only affect to 2.4.* versions When executing ``` $query = $em->createQuery(' SELECT m.nombre , m.fechainicio , m.fechafin FROM Bundle:Medicion m JOIN m.estudio e JOIN e.cliente c JOIN c.usuarios u WHERE u.id = :id ORDER BY m.fechainicio DESC ') ->setMaxResults(12); ``` Get the following error: ``` An exception occurred while executing ' SELECT * FROM ( SELECT m0_.NOMBRE AS NOMBRE0 , m0_.FECHAINICIO AS FECHAINICIO1 , m0_.FECHAFIN AS FECHAFIN2 , ROW*NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine*rownum FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0*.ESTUDIO_ID = e1*.ID INNER JOIN CLIENTE c2_ ON e1*.CLIENTE_ID = c2*.ID INNER JOIN USUARIO u3_ ON c2*.ID = u3_.CLIENTE_ID WHERE u3*.ID = ? ) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 12 ' with params [2]: ``` ``` SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]El nombre de columna 'FECHAINICIO1' no es válido. ``` Attached the BD model Added extra info! Engine version: Sql server 2008 R2 When executing this SQL (returned by doctrine error) on Management Studio ``` SELECT * FROM (SELECT m0*.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2, ROW_NUMBER() OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = 12) AS doctrine_tbl WHERE doctrine*rownum BETWEEN 1 AND 12 ``` Get the following error: ``` El nombre de columna 'FECHAINICIO1' no es válido. ('FECHAINICIO1' is invalid) ``` But if change "FECHAINICIO1" ``` ... OVER (ORDER BY FECHAINICIO1 DESC) AS doctrine_rownum FROM... ``` by this ``` ... OVER (ORDER BY m0*.FECHAINICIO DESC) AS doctrine*rownum FROM... ``` Don't get error regards Jonnatan Oyarzún
admin added the Bug label 2026-01-22 14:12:10 +01:00
admin closed this issue 2026-01-22 14:12:10 +01:00
admin commented 2026-01-22 14:12:11 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 5, 2013):

Comment created by @guilhermeblanco:

  • Which version of SQL Server are you working on?
  • Also, could you please verify 2 queries for us?
    This one should not work:
SELECT m0_.NOMBRE AS NOMBRE0
     , m0_.FECHAINICIO AS FECHAINICIO1
     , m0_.FECHAFIN AS FECHAFIN2 
  FROM MEDICION m0_ 
 ORDER BY FECHAINICIO1 DESC

This one should work:

SELECT m0_.NOMBRE AS NOMBRE0
     , m0_.FECHAINICIO AS FECHAINICIO1
     , m0_.FECHAFIN AS FECHAFIN2 
  FROM MEDICION m0_ 
 ORDER BY m0_.FECHAINICIO DESC

As soon as you get this it may define the approach on how we're gonna fix the issue. =)

Cheers,

Guilherme Blanco

@doctrinebot commented on GitHub (Jun 5, 2013): Comment created by @guilhermeblanco: - Which version of SQL Server are you working on? - Also, could you please verify 2 queries for us? This one should not work: ``` sql SELECT m0_.NOMBRE AS NOMBRE0 , m0_.FECHAINICIO AS FECHAINICIO1 , m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ ORDER BY FECHAINICIO1 DESC ``` This one should work: ``` sql SELECT m0_.NOMBRE AS NOMBRE0 , m0_.FECHAINICIO AS FECHAINICIO1 , m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ ORDER BY m0_.FECHAINICIO DESC ``` As soon as you get this it may define the approach on how we're gonna fix the issue. =) Cheers, Guilherme Blanco
admin commented 2026-01-22 14:12:12 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 5, 2013):

Comment created by jonsxaero:

Add extra info

Cheers,

Jonnatan Oyarzún

@doctrinebot commented on GitHub (Jun 5, 2013): Comment created by jonsxaero: Add extra info Cheers, Jonnatan Oyarzún
admin commented 2026-01-22 14:12:12 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 5, 2013):

Comment created by @FabioBatSilva:

Hi Jonnatan

There is a possible fix in this branch : [DDC-2470](https://github.com/FabioBatSilva/dbal/tree/[DDC-2470]%28http://www.doctrine-project.org/jira/browse/DDC-2470%29),
but i'm not able to test it right now..

Could you please test it in your environment ?

Thanks..

@doctrinebot commented on GitHub (Jun 5, 2013): Comment created by @FabioBatSilva: Hi Jonnatan There is a possible [fix](https://github.com/FabioBatSilva/dbal/commit/72670d263fd35ca30a2d90afb85838a9795bd9b8) in this branch : [[DDC-2470](http://www.doctrine-project.org/jira/browse/DDC-2470)](https://github.com/FabioBatSilva/dbal/tree/[DDC-2470]%28http://www.doctrine-project.org/jira/browse/DDC-2470%29), but i'm not able to test it right now.. Could you please test it in your environment ? Thanks..
admin commented 2026-01-22 14:12:14 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 5, 2013):

Comment created by jonsxaero:

Hi Fabio

Thank you very much for posting this fix.
From your [DDC-2470](https://github.com/FabioBatSilva/dbal/tree/[DDC-2470]%28http://www.doctrine-project.org/jira/browse/DDC-2470%29), I downloaded and pasted files in vendor\doctrine\dbal.
Fix is working for me!.

The question is, when this fix could be merged to dbal/master branch?

Cheers,
Jonnatan Oyarzún

@doctrinebot commented on GitHub (Jun 5, 2013): Comment created by jonsxaero: Hi Fabio Thank you very much for posting this fix. From your [[DDC-2470](http://www.doctrine-project.org/jira/browse/DDC-2470)](https://github.com/FabioBatSilva/dbal/tree/[DDC-2470]%28http://www.doctrine-project.org/jira/browse/DDC-2470%29), I downloaded and pasted files in vendor\doctrine\dbal. Fix is working for me!. The question is, when this fix could be merged to dbal/master branch? Cheers, Jonnatan Oyarzún
admin commented 2026-01-22 14:12:14 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 5, 2013):

Comment created by @FabioBatSilva:

Thanks Jonnatan,

I've created a pull request : https://github.com/doctrine/dbal/pull/332

@doctrinebot commented on GitHub (Jun 5, 2013): Comment created by @FabioBatSilva: Thanks Jonnatan, I've created a pull request : https://github.com/doctrine/dbal/pull/332
admin commented 2026-01-22 14:12:14 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 7, 2013):

Comment created by @FabioBatSilva:

Fixed : https://github.com/doctrine/doctrine2/commit/753d63c2d48facdecba5d84f6ed2450024de2867

@doctrinebot commented on GitHub (Jun 7, 2013): Comment created by @FabioBatSilva: Fixed : https://github.com/doctrine/doctrine2/commit/753d63c2d48facdecba5d84f6ed2450024de2867
admin commented 2026-01-22 14:12:14 +01:00
Author
Owner
Copy Link
Copy Source

@doctrinebot commented on GitHub (Jun 7, 2013):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jun 7, 2013): Issue was closed with resolution "Fixed"
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3099
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?