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 <khellom...@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....@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 <khellom...@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: kw...@aub.edu.lb >>> >> >> >
