[DBAL-1081] Paginator - Query Limit for SQL Server - SqlServerPlatform.php Created: 16/Dec/14  Updated: 29/Dec/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Maciej Grajcarek Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, dql, orderBy, sqlserver
Environment:

Windows



 Description   

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 :

ROW_NUMBER() OVER (ORDER BY value) DESC)

should look like

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC)

In doModifyLimitQuery method I have modified:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', $column['table'], $column['column']);

to:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/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!



 Comments   
Comment by Marco Pivetta [ 17/Dec/14 ]

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

Comment by Maciej Grajcarek [ 17/Dec/14 ]

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(); 
Comment by Marco Pivetta [ 17/Dec/14 ]

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
Comment by Maciej Grajcarek [ 18/Dec/14 ]

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

Comment by Steve Müller [ 24/Dec/14 ]

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

Comment by Steve Müller [ 24/Dec/14 ]

well forget about it I think I am wrong here.

Comment by Maciej Grajcarek [ 29/Dec/14 ]

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.





[DBAL-578] [GH-354] Added hour to DATE_ADD and DATE_SUB Created: 06/Aug/13  Updated: 08/Sep/13  Resolved: 07/Aug/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Improvement Priority: Major
Reporter: Alexey Makhov Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: dql, query


 Description   

Url: https://github.com/doctrine/dbal/pull/354



 Comments   
Comment by Doctrine Bot [ 06/Aug/13 ]

A related Github Pull-Request [GH-354] was closed:
https://github.com/doctrine/dbal/pull/354

Comment by Marco Pivetta [ 07/Aug/13 ]

Merged: https://github.com/doctrine/dbal/commit/b4d80abd371a15fa24f06417973223569d971ef8





[DBAL-402] Fatal error: Uncaught exception Created: 29/Dec/12  Updated: 25/Jun/13  Resolved: 25/Jun/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Blocker
Reporter: Ruslan Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql, oracle


 Description   
Fatal error: Uncaught exception 'Doctrine\DBAL\Driver\OCI8\OCI8Exception' with message 'ORA-00904: "T0"."ID": invalid identifier' in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28 Doctrine\DBAL\Driver\OCI8\OCI8Exception: ORA-00904: "T0"."ID": invalid identifier in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28

Call Stack: 0.0002 665368
1. {main}() /var/www/doctrine/doctrine/tools/sandbox/index.php:0 0.3389 6023864
2. Doctrine\ORM\EntityManager->find() /var/www/doctrine/doctrine/tools/sandbox/index.php:71 0.3514 8264024
3. Doctrine\ORM\Persisters\BasicEntityPersister->load() /var/www/doctrine/doctrine/lib/Doctrine/ORM/EntityManager.php:444 0.3521 8413576 
4. Doctrine\DBAL\Connection->executeQuery() /var/www/doctrine/doctrine/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php:725 0.3532 8625920
5. Doctrine\DBAL\Driver\OCI8\OCI8Statement->execute() /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php:635 Variables in local scope (#5): $hasZeroIndex = *uninitialized* $key = *uninitialized* $params = NULL $ret = FALSE $val = *uninitialized* 


 Comments   
Comment by Benjamin Eberlei [ 06/Jan/13 ]

Format code sample

Comment by Benjamin Eberlei [ 04/Apr/13 ]

What is wrong here? The error alone is not very helpful

Comment by Benjamin Eberlei [ 25/Jun/13 ]

No feedback given.





[DBAL-397] PostgreSQL - getDateTimeTzFormatString() Created: 12/Dec/12  Updated: 17/Dec/12  Resolved: 16/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phill Pafford Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: datetime, dql, format, postgresql
Environment:

PostgreSQL Database



 Description   

In:

  • vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

The function:

getDateTimeTzFormatString()

only returns one datetime format:


    /**
     * {@inheritDoc}
     */
    public function getDateTimeTzFormatString()
    {
        //return 'Y-m-d H:i:sO'; // original format
        return 'Y-m-d H:i:s.uO'; // format also needed
    }


Here is the error I get using the original format:

Could not convert database value "2012-12-07 16:01:52.580789-05" to Doctrine Type datetimetz. Expected format: Y-m-d H:i:s.O

If I use this format: 'Y-m-d H:i:s.uO' it works.



 Comments   
Comment by Phill Pafford [ 12/Dec/12 ]

adding html tags for code to display correctly

Comment by Benjamin Eberlei [ 16/Dec/12 ]

DateTimeTz is created as 'TIMESTAMP(0) WITH TIME ZONE' on DBAL. If you have 'TIMESTAMP(6) WITH TIME ZONE' instead, then you have to create your own datatye.

Comment by Phill Pafford [ 17/Dec/12 ]

Why would this be a new data type? I understand how to fix the problem with creating my own data type to handle the formatting issue and would even suggest just overriding the data type with


        Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');

But it's not a new data type, it's a formatting issue.





Generated at Sun Apr 26 13:14:13 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.