DBAL-1081: Paginator - Query Limit for SQL Server - SqlServerPlatform.php #1017

doctrinebot · 2014-12-16T23:23:36Z

Jira issue originally created by user mgrajcarek:

Hi!
I have a problem with Query results limit when ordering by SUM of a field.

My query looks like this:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

First I was catching following error:
message: "[Syntax Error] line 0, col 395: Error: Expected known function, got 'SUM'"
class: Doctrine\ORM\Query\QueryException

It only accourse if SUM is used in ORDER BY clause.

I have registered new class Sum which extends FunctionNode.

Now, query is build and executed but it has an error:

'SELECT * FROM 
     (SELECT y0*.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY value) DESC) AS doctrine_rownum FROM yellowpage_keywords y0_ INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id INNER JOIN listings l2_ ON y1_.listing_id = l2_.id WHERE l2_.customer_id = ? AND y0_.origin_date >= ? AND y0_.origin_date <= ? GROUP BY y0_.name_crc, y0_.name) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10 ORDER BY doctrine*rownum' with params ["111", "2013-01-01 00:00:00.000000", "2014-12-31 23:59:59.000000"]

The line :

should look like

In doModifyLimitQuery method I have modified:

                $pattern = sprintf('/%s\.%s\s<ins>(?:AS\s</ins>)?([<sup>,\s)]<ins>)/i', $column['table'], $column['column']);

to:

                $pattern = sprintf('/%s\.%s\s</ins>(?:AS\s<ins>)?([</sup>,\s)]</ins>)/i', str*replace('(', '\(', $column['table']), str*replace(')', '\)', $column['column']));

Now preg_match founds matching strings and OVER part of query is build correctly.

I checked other issues about this problem (which are marked as already fixed) and I have no idea why it's not working for me.

Thanks in advance!

The text was updated successfully, but these errors were encountered:

doctrinebot · 2014-12-17T12:49:02Z

Comment created by @Ocramius:

Seems like a bit of information is missing: Is this issue related to the paginator API or not?

doctrinebot · 2014-12-17T22:24:23Z

Comment created by mgrajcarek:

First of all - thank you for formatting my issue.
Secondly yes - issue is directly connected with paginator API.

Here is a code which should help you to replicate the problem:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

        $dql = $this->getEntityManager()->createQuery($query);
        $dql
            ->setParameter('customerId', $customerId)
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo);
        $dql->setMaxResults(10);    

        $keywords = $dql->getArrayResult();

doctrinebot · 2014-12-17T23:34:51Z

Comment created by @Ocramius:

That doesn't involve the paginator, just DQL.

SUM() and computed values are not supported in the ORDER BY clause: you have to select them first. Try:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY total DESC

doctrinebot · 2014-12-18T10:56:16Z

Comment created by mgrajcarek:

If I will do it, it will result in a following query:

SELECT *
FROM (
    SELECT y0*.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine*rownum 
    FROM yellowpage*keywords y0*
    INNER JOIN yellowpages y1* ON y0_.yellowpage_id = y1*.id
    INNER JOIN listings l2* ON y1_.listing_id = l2*.id
    WHERE l2*.customer*id = 111
        AND y0*.origin*date >= '2014-01-01'
        AND y0*.origin*date <= '2014-12-01'
    GROUP BY y0*.name_crc, y0*.name
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 10
ORDER BY doctrine_rownum

It's an incorrect query for an SQL Server. Take a look on this part:

ROW*NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine*rownum

SQL Server do not support aliasing in OVER clause.

It should look like this, to work:

ROW*NUMBER() OVER (ORDER BY SUM(y0_.value) DESC) AS doctrine*rownum

It looks like this is the copy of this issue: http://www.doctrine-project.org/jira/browse/[DBAL-788](http://www.doctrine-project.org/jira/browse/DBAL-788)
I'm on doctrine version 2.5 which has patch from that issue included, but I have no idea why it's not working for me.

doctrinebot · 2014-12-24T13:40:53Z

Comment created by @deeky666:

[~mgrajcarek] looks like it is an issue with your SUM function implementation. If you change your DQL to use ORDER BY COUNT(ypk.value) instead of ORDER BY SUM(ypk.value), does it work then? If so, there is something wrong with your SUM function and therefore not an issue with DBAL.

doctrinebot · 2014-12-24T13:47:22Z

Comment created by @deeky666:

well forget about it I think I am wrong here.

doctrinebot · 2014-12-29T09:41:04Z

Comment created by mgrajcarek:

Hi,
I will try to use COUNT as soon as I will be able to do that.

But I already tried other aggregation functions before and the result was exactly the same.

doctrinebot · 2015-06-16T14:28:55Z

Comment created by luca.cerretani:

I get a similar error using this sql:

$sql = "SELECT table*one.id, table_one.number, table*two.name " . 
           "FROM table_one " .
       "LEFT JOIN table*two ON table_two.table_one_id = table*one.id " .
           "ORDER BY table_one.id DESC";

using the doModifyLimitQuery i get the uncorrect sql

SELECT * FROM (SELECT table*one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine*rownum

it should be

SELECT * FROM (SELECT table*one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY table_one.id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine*rownum

doctrinebot · 2015-10-30T04:32:02Z

Comment created by caps_corp:

Hi,

I have a big problem when executing LIMIT query on SQL Server platform.
The code does not construct the query well.

See below:
Doctrine\DBAL\DBALException
File:
D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php:119
Message:
An exception occurred while executing 'SELECT * FROM (SELECT DISTINCT {color:red}alias1 {color}FROM (SELECT table1.column1 AS {color:red}alias1{color}, table1.column2 AS alias2, ROW_NUMBER() OVER (ORDER BY alias1 ASC) AS {color:red}doctrine_rownum{color} FROM tab1 table1 WHERE 1=1) dctrn_result ) AS doctrine_tbl WHERE {color:red}doctrine_rownum {color}BETWEEN 1 AND 10 ':

{color:red}SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'alias1'.
SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'doctrine_rownum'.
SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'doctrine_rownum'.
Stack trace:{color}
#0 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php(836): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\SQLSrv\Driver), Object(Doctrine\DBAL\Driver\SQLSrv\SQLSrvException), 'SELECT * FROM (...', Array)
#1 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Query\Exec\SingleSelectExecutor.php(50): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM (...', Array, Array, NULL)
#2 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Query.php(286): Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(Object(Doctrine\DBAL\Connection), Array, Array)
#3 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php(794): Doctrine\ORM\Query->_doExecute()
#4 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php(598): Doctrine\ORM\AbstractQuery->execute(NULL, 3)
#5 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\Paginator.php(151): Doctrine\ORM\AbstractQuery->getScalarResult()
#6 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\doctrine-orm-module\src\DoctrineORMModule\Paginator\Adapter\DoctrinePaginator.php(77): Doctrine\ORM\Tools\Pagination\Paginator->getIterator()
#7 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(602): DoctrineORMModule\Paginator\Adapter\DoctrinePaginator->getItems(0, 10)
#8 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(438): Zend\Paginator\Paginator->getItemsByPage(1)
#9 D:\Projects\Recette\alliance.mig.ect\module\Admin\view\admin\utilisateurs\select-utilisateur.phtml(22): Zend\Paginator\Paginator->getCurrentItems()
#10 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\Renderer\PhpRenderer.php(501): include('D:\Projects\Rec...')
#11 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(205): Zend\View\Renderer\PhpRenderer->render(Object(Zend\View\Model\ViewModel))
#12 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(233): Zend\View\View->render(Object(Zend\View\Model\ViewModel))
#13 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(198): Zend\View\View->renderChildren(Object(Zend\View\Model\ViewModel))
#14 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\View\Http\DefaultRenderingStrategy.php(103): Zend\View\View->render(Object(Zend\View\Model\ViewModel))
#15 [internal function]: Zend\Mvc\View\Http\DefaultRenderingStrategy->render(Object(Zend\Mvc\MvcEvent))
#16 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(444): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#17 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(205): Zend\EventManager\EventManager->triggerListeners('render', Object(Zend\Mvc\MvcEvent), Array)
#18 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(353): Zend\EventManager\EventManager->trigger('render', Object(Zend\Mvc\MvcEvent))
#19 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(328): Zend\Mvc\Application->completeRequest(Object(Zend\Mvc\MvcEvent))
#20 D:\Projects\Recette\alliance.mig.ect\public\index.php(23): Zend\Mvc\Application->run()
#21 {main}
______________________________________
Previous exceptions:
• Doctrine\DBAL\Driver\SQLSrv\SQLSrvException
File:
D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\SQLSrv\SQLSrvException.php:42
Message:
{color:red}SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'alias1'.
SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'doctrine_rownum'.
SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Nom de colonne non valide�: 'doctrine_rownum'.
Stack trace:{color}
#0 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement.php(202): Doctrine\DBAL\Driver\SQLSrv\SQLSrvException::fromSqlSrvErrors()
#1 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\SQLSrv\SQLSrvConnection.php(96): Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement->execute()
#2 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php(833): Doctrine\DBAL\Driver\SQLSrv\SQLSrvConnection->query('SELECT * FROM (...')
#3 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Query\Exec\SingleSelectExecutor.php(50): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM (...', Array, Array, NULL)
#4 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Query.php(286): Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(Object(Doctrine\DBAL\Connection), Array, Array)
#5 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php(794): Doctrine\ORM\Query->_doExecute()
#6 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php(598): Doctrine\ORM\AbstractQuery->execute(NULL, 3)
#7 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\Paginator.php(151): Doctrine\ORM\AbstractQuery->getScalarResult()
#8 D:\Projects\Recette\alliance.mig.ect\vendor\doctrine\doctrine-orm-module\src\DoctrineORMModule\Paginator\Adapter\DoctrinePaginator.php(77): Doctrine\ORM\Tools\Pagination\Paginator->getIterator()
#9 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(602): DoctrineORMModule\Paginator\Adapter\DoctrinePaginator->getItems(0, 10)
#10 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php(438): Zend\Paginator\Paginator->getItemsByPage(1)
#11 D:\Projects\Recette\alliance.mig.ect\module\Admin\view\admin\utilisateurs\select-utilisateur.phtml(22): Zend\Paginator\Paginator->getCurrentItems()
#12 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\Renderer\PhpRenderer.php(501): include('D:\Projects\Rec...')
#13 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(205): Zend\View\Renderer\PhpRenderer->render(Object(Zend\View\Model\ViewModel))
#14 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(233): Zend\View\View->render(Object(Zend\View\Model\ViewModel))
#15 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\View\View.php(198): Zend\View\View->renderChildren(Object(Zend\View\Model\ViewModel))
#16 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\View\Http\DefaultRenderingStrategy.php(103): Zend\View\View->render(Object(Zend\View\Model\ViewModel))
#17 [internal function]: Zend\Mvc\View\Http\DefaultRenderingStrategy->render(Object(Zend\Mvc\MvcEvent))
#18 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(444): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#19 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\EventManager\EventManager.php(205): Zend\EventManager\EventManager->triggerListeners('render', Object(Zend\Mvc\MvcEvent), Array)
#20 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(353): Zend\EventManager\EventManager->trigger('render', Object(Zend\Mvc\MvcEvent))
#21 D:\Projects\Recette\alliance.mig.ect\vendor\zendframework\zendframework\library\Zend\Mvc\Application.php(328): Zend\Mvc\Application->completeRequest(Object(Zend\Mvc\MvcEvent))
#22 D:\Projects\Recette\alliance.mig.ect\public\index.php(23): Zend\Mvc\Application->run()
#23 {main}

doctrinebot · 2015-11-15T08:31:17Z

Comment created by ilijatovilo:

Any news on issue? Any kind of help would be greatly appreciated.

doctrinebot · 2015-11-15T09:03:06Z

Comment created by ilijatovilo:

Interestingly, using the master branch fixes the issue for me. Obviously this is no solution for production code.
Any idea when this fix is going to be released?

billschaller · 2016-01-05T17:48:03Z

@deeky666 I think this can be closed as fixed, no?

deeky666 · 2016-01-05T18:14:55Z

fixed by #818

github-actions · 2022-08-09T00:26:39Z

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

doctrinebot added the Bug label Dec 6, 2015

doctrinebot assigned beberlei Dec 6, 2015

deeky666 closed this as completed Jan 5, 2016

github-actions bot locked as resolved and limited conversation to collaborators Aug 9, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DBAL-1081: Paginator - Query Limit for SQL Server - SqlServerPlatform.php #1017

DBAL-1081: Paginator - Query Limit for SQL Server - SqlServerPlatform.php #1017

doctrinebot commented Dec 16, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 18, 2014

doctrinebot commented Dec 24, 2014

doctrinebot commented Dec 24, 2014

doctrinebot commented Dec 29, 2014

doctrinebot commented Jun 16, 2015

doctrinebot commented Oct 30, 2015

doctrinebot commented Nov 15, 2015

doctrinebot commented Nov 15, 2015

billschaller commented Jan 5, 2016

deeky666 commented Jan 5, 2016

github-actions bot commented Aug 9, 2022

DBAL-1081: Paginator - Query Limit for SQL Server - SqlServerPlatform.php #1017

DBAL-1081: Paginator - Query Limit for SQL Server - SqlServerPlatform.php #1017

Comments

doctrinebot commented Dec 16, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 17, 2014

doctrinebot commented Dec 18, 2014

doctrinebot commented Dec 24, 2014

doctrinebot commented Dec 24, 2014

doctrinebot commented Dec 29, 2014

doctrinebot commented Jun 16, 2015

doctrinebot commented Oct 30, 2015

doctrinebot commented Nov 15, 2015

doctrinebot commented Nov 15, 2015

billschaller commented Jan 5, 2016

deeky666 commented Jan 5, 2016

github-actions bot commented Aug 9, 2022