[
https://issues.apache.org/jira/browse/METAMODEL-238?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15189335#comment-15189335
]
Kasper Sørensen commented on METAMODEL-238:
-------------------------------------------
Hi Alessandro,
I've been inspecting the issue a bit more, and have actually come to the
conclusion that the query here is invalid in the first place... Let me try and
explain, and then I hope you agree:
You're doing an ORDER BY on something that is not included in the selected
dataset, and could not be determined from it either. I understand that there is
a relationship between country and city (each city exists in a country) but it
is not a guaranteed distinct relationship. For instance the city Copenhagen is
best known as the capital of Denmark, but there also exists a (small) city
called Copenhagen in the US. So when you group by city (and not on country too)
and then order on country - then that leaves no way for the SQL engine to be
able to determine the real order.
Imagine the dataset:
{code}
city | country | total_children
New York | US | 42
Copenhagen | DK | 12
Copenhagen | US | 3
{code}
If you would first do the GROUP BY and COUNT aggregation, then you're left with:
{code}
city | total_children_count
New York | 42
Copenhagen | 12
{code}
And this set is not orderable by country anymore.
> 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)
