[
https://issues.apache.org/jira/browse/METAMODEL-238?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15189422#comment-15189422
]
Alessandro Portosa commented on METAMODEL-238:
----------------------------------------------
Yes, I can see your point. But if I have something like this:
{code}
city | total_children
New York | 42
Copenhagen | 15
Copenhagen | 3
Rome | 55
Milan | 7
{code}
I cannot say the order of city... the order cannot be guaranteed (Rome and
Milan before Copenhagen, or viceversa?).
{code}
city | country | total_children
New York | US | 42
Copenhagen | DK | 12
Copenhagen | US | 3
Rome | IT | 55
Milan | IT | 7
{code}
Anyhow, it would interesting to apply this kind of queries in many other cases
when the uniqueness of the city for a country exists (i.e. exists a constraing
that assert something like UNIQUE(city, country)).
I don't know if the SQL engine out there already supports this kind of things...
> Error when using GROUPBY and ORBERBY with different fields
> ----------------------------------------------------------
>
> Key: METAMODEL-238
> URL: https://issues.apache.org/jira/browse/METAMODEL-238
> Project: Apache MetaModel
> Issue Type: Bug
> Affects Versions: 4.5.1
> Reporter: Alessandro Portosa
>
> MetaModel does not act as expected when providing it a different field for
> GROUPING and ORDERING.
> Let's say the we have a table with the following columns: total_children,
> city, country.
> 1) If i use
> {code}SELECT SUM(total_children) AS total_children_SUM, city AS city FROM
> schema.table GROUP BY city{code}
> I get the right results.
> 2) If i use
> {code}SELECT SUM(total_children) AS total_children_SUM , city AS city FROM
> schema.table GROUP BY city ORDER BY city asc{code}
> then I get the right results.
> 3) But if I use
> {code}SELECT SUM(total_children) AS total_children_SUM, city AS city FROM
> schema.table GROUP BY city ORDER BY country asc{code}
> I get the wrong results.
> I do always get results, but these are the same as 1) where no ordering was
> required. The same happen with I use any other field that is not city
> (which is the field that I'm using for the grouping).
> This is how I use it:
> {code}
> Query query = dataContext.parseQuery(sqlQuery);
> CompiledQuery cQuery = dataContext.compileQuery(query);
> DataSet dataSet = dataContext.executeQuery(cQuery);
> {code}
> The used DataContext is a PojoDataContext.
> No error or exception. Just wrong data.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
