#1609: Oracle - Execution with custom function results in "not a GROUP BY
expression" error
--------------------------+-------------------------------------------------
Reporter: tchakkapark | Owner: guilhermeblanco
Type: defect | Status: new
Priority: major | Milestone: 1.0.4
Component: Pager | Version: 1.0.3
Resolution: | Keywords: oracle group by
Has_test: 0 | Mystatus: Pending Core Response
Has_patch: 0 |
--------------------------+-------------------------------------------------
Old description:
> Symfony 1.1 w/ Doctrine plugin and pager
>
> Using changeset 5139
>
> Oracle 10g
>
> I need my dates converted to unixtime format, and have done the
> following:
>
> {{{
> $messages = Doctrine_Query::create()
> ->select("el.User_Id, el.To_Email,
> el.To_Name, el.To_Contact_Id, el.From_Email, el.From_Name,
> el.From_Contact_Id, el.Reply_Email, el.Subject, el.Text_Clob_Id,
> el.Html_Clob_Id, el.Attachment_Clob, el.Attachment_Name,
> el.Scheduled_Send, el.Status, el.Status_Text, ((el.created -
> to_date('01-JAN-1970','DD-MON-YYYY')) * (86400)) el.created")
> ->from('Email_Log el')
> ->where("el.to_email = ? AND el.status !=
> 'Deleted'", $this->getUser()->getAttribute('email'))
> ->setHydrationMode(Doctrine::HYDRATE_ARRAY);
>
> if($this->reduced == true){
> $pager = new Doctrine_Pager($messages, 0, 5);
> } else {
> $pager = new Doctrine_Pager($messages,
> $request->getParameter("page", 0), 30);
> }
>
> $this->results = $pager->execute();
>
> }}}
>
> Executing all of this results in a:
>
> {{{
> SQLSTATE[HY000]: General error: 979 OCIStmtExecute: ORA-00979: not a
> GROUP BY expression
> (ext\pdo_oci\oci_statement.c:146)
> }}}
>
> What's going on is:
>
> {{{
>
> (Read from bottom to top, with top being recent)
>
> at Doctrine_Connection->execute('SELECT COUNT(DISTINCT e.email_id) AS
> num_results, ((e.created - to_date('01-JAN-1970', 'DD-MON-YYYY')) *
> (86400)) AS e__0 FROM EMAIL_LOG e WHERE (e.to_email = ? AND e.status !=
> 'Deleted') GROUP BY e.email_id', array([EMAIL PROTECTED]))in
> SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Connection.php
> line 788 ...
>
> at Doctrine_Connection->fetchAll('SELECT COUNT(DISTINCT e.email_id) AS
> num_results, ((e.created - to_date('01-JAN-1970', 'DD-MON-YYYY')) *
> (86400)) AS e__0 FROM EMAIL_LOG e WHERE (e.to_email = ? AND e.status !=
> 'Deleted') GROUP BY e.email_id', array(null))in
> SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Query.php line
> 1940 ...
>
> at Doctrine_Query->count(array()) in
> SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Pager.php line
> 108 ...
> at Doctrine_Pager->_initialize(array())in
> SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Pager.php line
> 570 ...
> }}}
>
> It's including that custom function for the UNIX time when it should not
> be.
New description:
Symfony 1.1 w/ Doctrine plugin and pager
Using changeset 5139 (branch 1.1)
Oracle 10g
I need my dates converted to unixtime format, and have done the following:
{{{
$messages = Doctrine_Query::create()
->select("el.User_Id, el.To_Email, el.To_Name,
el.To_Contact_Id, el.From_Email, el.From_Name, el.From_Contact_Id,
el.Reply_Email, el.Subject, el.Text_Clob_Id, el.Html_Clob_Id,
el.Attachment_Clob, el.Attachment_Name, el.Scheduled_Send, el.Status,
el.Status_Text, ((el.created - to_date('01-JAN-1970','DD-MON-YYYY')) *
(86400)) el.created")
->from('Email_Log el')
->where("el.to_email = ? AND el.status !=
'Deleted'", $this->getUser()->getAttribute('email'))
->setHydrationMode(Doctrine::HYDRATE_ARRAY);
if($this->reduced == true){
$pager = new Doctrine_Pager($messages, 0, 5);
} else {
$pager = new Doctrine_Pager($messages,
$request->getParameter("page", 0), 30);
}
$this->results = $pager->execute();
}}}
Executing all of this results in a:
{{{
SQLSTATE[HY000]: General error: 979 OCIStmtExecute: ORA-00979: not a GROUP
BY expression
(ext\pdo_oci\oci_statement.c:146)
}}}
What's going on is:
{{{
(Read from bottom to top, with top being recent)
at Doctrine_Connection->execute('SELECT COUNT(DISTINCT e.email_id) AS
num_results, ((e.created - to_date('01-JAN-1970', 'DD-MON-YYYY')) *
(86400)) AS e__0 FROM EMAIL_LOG e WHERE (e.to_email = ? AND e.status !=
'Deleted') GROUP BY e.email_id', array([EMAIL PROTECTED]))in
SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Connection.php
line 788 ...
at Doctrine_Connection->fetchAll('SELECT COUNT(DISTINCT e.email_id) AS
num_results, ((e.created - to_date('01-JAN-1970', 'DD-MON-YYYY')) *
(86400)) AS e__0 FROM EMAIL_LOG e WHERE (e.to_email = ? AND e.status !=
'Deleted') GROUP BY e.email_id', array(null))in
SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Query.php line
1940 ...
at Doctrine_Query->count(array()) in
SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Pager.php line
108 ...
at Doctrine_Pager->_initialize(array())in
SF_ROOT_DIR\plugins\sfDoctrinePlugin\lib\doctrine\Doctrine\Pager.php line
570 ...
}}}
It's including that custom function for the UNIX time when it should not
be.
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1609#comment:3>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
