[DDC-2470] Sql Server error in createQuery using ORDER BY and setMaxResults Created: 24/May/13  Updated: 07/Jun/13  Resolved: 07/Jun/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Blocker
Reporter: Jonnatan Oyarzún Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: dql, sqlserver
Environment:

Windows 7, Apache 2 (xampp 1.8.1), PHP 5.4.7, Symfony 2.2.1


Attachments: PNG File BD.png    

 Description   

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



 Comments   
Comment by Guilherme Blanco [ 05/Jun/13 ]
  • 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

Comment by Jonnatan Oyarzún [ 05/Jun/13 ]

Add extra info

Cheers,

Jonnatan Oyarzún

Comment by Fabio B. Silva [ 05/Jun/13 ]

Hi Jonnatan

There is a possible fix in this branch : DDC-2470,
but i'm not able to test it right now..

Could you please test it in your environment ?

Thanks..

Comment by Jonnatan Oyarzún [ 05/Jun/13 ]

Hi Fabio

Thank you very much for posting this fix.
From your DDC-2470, 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

Comment by Fabio B. Silva [ 05/Jun/13 ]

Thanks Jonnatan,

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

Comment by Fabio B. Silva [ 07/Jun/13 ]

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

Generated at Tue Jul 29 17:13:21 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.