Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2470

Sql Server error in createQuery using ORDER BY and setMaxResults

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.4
    • Fix Version/s: 2.4
    • Component/s: ORM
    • Security Level: All
    • Labels:
    • Environment:
      Windows 7, Apache 2 (xampp 1.8.1), PHP 5.4.7, Symfony 2.2.1

      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

        Activity

        Show
        Fabio B. Silva added a comment - Fixed : https://github.com/doctrine/doctrine2/commit/753d63c2d48facdecba5d84f6ed2450024de2867
        Hide
        Fabio B. Silva added a comment -

        Thanks Jonnatan,

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

        Show
        Fabio B. Silva added a comment - Thanks Jonnatan, I've created a pull request : https://github.com/doctrine/dbal/pull/332
        Hide
        Jonnatan Oyarzún added a comment - - edited

        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

        Show
        Jonnatan Oyarzún added a comment - - edited 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
        Hide
        Fabio B. Silva added a comment -

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

        Show
        Fabio B. Silva added a comment - 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..
        Hide
        Jonnatan Oyarzún added a comment -

        Add extra info

        Cheers,

        Jonnatan Oyarzún

        Show
        Jonnatan Oyarzún added a comment - Add extra info Cheers, Jonnatan Oyarzún
        Hide
        Guilherme Blanco added a comment -
        • 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

        Show
        Guilherme Blanco added a comment - 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

          People

          • Assignee:
            Fabio B. Silva
            Reporter:
            Jonnatan Oyarzún
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: