phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: Error when using aggregates with correlated subqueries
Date Thu, 23 Apr 2015 14:49:58 GMT
Hi Khaleel,

Thanks for the explanation! But my question was since this is an EXISTS, I
assume if there ever is a H."timestamp" with an 'approved' status, the
EXISTS will return true regardless of whether you are testing H."timestamp"
or Max(H."timestamp"). Is that correct? or have I missed something?


Thanks,
Maryann

On Thu, Apr 23, 2015 at 8:49 AM, khaleel mershad <khellom007@gmail.com>
wrote:

>
> Hello Maryann,
>
> Thanks very much for your reply. Hopefully this bug gets fixed in the next
> release so that I can continue working with this part in my research
> project. Thanks for keeping me posted.
>
> With respect to your question, I am using the "History" table as a data
> store of all versions of a certain data item. When I say Max(H."timestamp")
> inside the query, I am selecting the latest version (most recent) that is
> "approved" (which is checked using the condition H."status" = 'approved')
>
>
>
> Best Regards,
> Khaleel
>
> On Thu, Apr 23, 2015 at 6:22 AM, Maryann Xue <maryann.xue@gmail.com>
> wrote:
>
>> Hi Khaleel,
>>
>> Thanks a lot for reporting the problem, which looks like a bug. I will
>> file a JIRA and keep you posted.
>>
>> One question though, why would we use MAX(H."timestamp") instead of H."timestamp"?
>> What difference would it make?
>>
>>
>> Thanks,
>> Maryann
>>
>> On Wed, Apr 22, 2015 at 9:45 AM, khaleel mershad <khellom007@gmail.com>
>> wrote:
>>
>>>
>>> Hello,
>>>
>>> I tried executing the following query in Phoenix:
>>> select distinct W."rowId", W."md5_current", W."size_current" from "Wiki"
>>> AS W where EXISTS( select MAX(H."timestamp") from "History" AS H where
>>> (H."rowId" = W."rowId") AND (H."status" = 'approved') );
>>>
>>> I got the following error:
>>> ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY.
>>> (state=42Y27,code=1018)
>>>
>>> If I replace W."rowId" (in the where clause) with a constant value (for
>>> example: '3587'), the query works fine. Also, if I replace the aggregate
>>> MAX(H."timestamp") with H."timestamp", the query also works fine.
>>>
>>> So it seems that Phoenix generates error when using a reference to an
>>> outer query while using an aggregate within the inner query.
>>>
>>> Any solutions?
>>>
>>>
>>>
>>> Regards,
>>>
>>> Khaleel Mershad, Ph.D.
>>> Research Associate
>>>
>>> American University of Beirut
>>> Department of Electrical and Computer Engineering
>>> Bliss Street, Beirut, Lebanon
>>> email: kwm03@aub.edu.lb
>>>
>>
>>
>

Mime
View raw message