Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3029

DISTINCT , ORDER BY AND Limit in SQL Server

    Details

    • Type: Bug Bug
    • Status: Awaiting Feedback
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I don't know if I should report it here , becouse error is in SQLServerPlatform.php file.
      basicaly Distinct includes ROWNUM()
      So if you add distinct in DQL it will be translated to "select distinct ...... , Rownum()" - which is always distinct offcource.

      Here is original version

          protected function doModifyLimitQuery($query, $limit, $offset = null)
          {
              if ($limit > 0) {
                  if ($offset == 0) {
                      $query = preg_replace('/^(SELECT\s(DISTINCT\s)?)/i', '\1TOP ' . $limit . ' ', $query);
                  } else {
                      $orderby = stristr($query, 'ORDER BY');
      
                      if ( ! $orderby) {
                          $over = 'ORDER BY (SELECT 0)';
                      } else {
                          $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
                      }
      
                      // Remove ORDER BY clause from $query
                      $query = preg_replace('/\s+ORDER BY(.*)/', '', $query);
                      $query = preg_replace('/^SELECT\s/', '', $query);
      
                      $start = $offset + 1;
                      $end = $offset + $limit;
      
                      $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS doctrine_rownum, $query) AS doctrine_tbl WHERE doctrine_rownum BETWEEN $start AND $end";
                  }
              }
      
              return $query;
          }
      

      In Attachenment there is a fixed version of this file.

        Activity

        Hide
        Marco Pivetta added a comment -

        Can you provide a failing test case and an expected result as well?

        Show
        Marco Pivetta added a comment - Can you provide a failing test case and an expected result as well?
        Hide
        Michał Banaś added a comment -

        Well I'm not working with PHP anymore for now, but i will try:
        it's easy and when you look at the code closer you will see the problem:

        Test Case: use Query Builder and genearate query with DIstinct and Order BY And Liimit ( top , skip or something )
        Expected result would be to get distinct set of data ordered by key and with limit.
        Unfortunately - as the result query will be something:
        select distinct .... top ....
        from .........
        SELECT ROW_NUMBER() OVER ($over) AS doctrine_rownum ... rest of the qurery

        so from inner query you will get

        doctrine_rownum;column1;column2;column3
        1;example;example;example
        2;example;example;example
        3;example;example;example

        I think you can clearly see that each row will be diiffrent. That's why DISTINCT will not work.
        What happens next:
        At some point doctrine will treat identical entities as one object, so for example if you limit Your query to 10 and all objects will be identical You will get only one Row.

        I hope thats clear now

        And we have a solution for that. It's probably in attached file. I will try to attach final version

        My team is woried that after updating Doctrine ( symfony ) we will have to face the problem again.

        Show
        Michał Banaś added a comment - Well I'm not working with PHP anymore for now, but i will try: it's easy and when you look at the code closer you will see the problem: Test Case: use Query Builder and genearate query with DIstinct and Order BY And Liimit ( top , skip or something ) Expected result would be to get distinct set of data ordered by key and with limit. Unfortunately - as the result query will be something: select distinct .... top .... from ......... SELECT ROW_NUMBER() OVER ($over) AS doctrine_rownum ... rest of the qurery so from inner query you will get doctrine_rownum;column1;column2;column3 1;example;example;example 2;example;example;example 3;example;example;example I think you can clearly see that each row will be diiffrent. That's why DISTINCT will not work. What happens next: At some point doctrine will treat identical entities as one object, so for example if you limit Your query to 10 and all objects will be identical You will get only one Row. I hope thats clear now And we have a solution for that. It's probably in attached file. I will try to attach final version My team is woried that after updating Doctrine ( symfony ) we will have to face the problem again.
        Hide
        Michał Banaś added a comment -

        well. For some reason i can't attach final version of the file :/
        I can paste it as a comment or You can contact me directly and i will send it to You.

        Show
        Michał Banaś added a comment - well. For some reason i can't attach final version of the file :/ I can paste it as a comment or You can contact me directly and i will send it to You.
        Hide
        Marco Pivetta added a comment -

        Ah, I see now what the problem is. ROWNUM() will basically always make the row unique, making DISTINCT useless.

        The problem is clear, but it needs an SQL generation test first.

        Michał Banaś can you eventually send the patch as a pull request to https://github.com/doctrine/dbal ? Consider that the code for the SQLServerPlatform changed a lot since 2.4.x

        Show
        Marco Pivetta added a comment - Ah, I see now what the problem is. ROWNUM() will basically always make the row unique, making DISTINCT useless. The problem is clear, but it needs an SQL generation test first. Michał Banaś can you eventually send the patch as a pull request to https://github.com/doctrine/dbal ? Consider that the code for the SQLServerPlatform changed a lot since 2.4.x
        Hide
        Michał Banaś added a comment -

        Well I could do that in private time, but it can take a while since i have no spare time right now and i would have to prepare some basic PPH dev environment with MSSQL.otherwise i would send untested code which is not a good idea.

        Show
        Michał Banaś added a comment - Well I could do that in private time, but it can take a while since i have no spare time right now and i would have to prepare some basic PPH dev environment with MSSQL.otherwise i would send untested code which is not a good idea.
        Hide
        Marco Pivetta added a comment -

        We all do develop these tools in private time, so nobody is forcing anyone to do anything

        Show
        Marco Pivetta added a comment - We all do develop these tools in private time, so nobody is forcing anyone to do anything

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Michał Banaś
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: