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

Closed
doctrinebot opened this Issue May 24, 2013 · 7 comments

1 participant

@doctrinebot

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

@doctrinebot

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

Comment created by jonsxaero:

Add extra info

Cheers,

Jonnatan Oyarzún

@doctrinebot

Comment created by @FabioBatSilva:

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..

@doctrinebot

Comment created by jonsxaero:

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

@doctrinebot

Comment created by @FabioBatSilva:

Thanks Jonnatan,

I've created a pull request : doctrine/dbal#332

@doctrinebot

Comment created by @FabioBatSilva:

Fixed : 753d63c

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment