[
https://issues.apache.org/jira/browse/CASSANDRA-10707?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15167462#comment-15167462
]
Sylvain Lebresne commented on CASSANDRA-10707:
----------------------------------------------
bq. we filter the data once they have been sorted, in most of the cases, but
the restrictions appear before the {{ORDER BY}} clause
The difference is that this has no external visibility because the operation
(filtering and ordering) commute. That's not really the case for {{ORDER BY}}
and {{GROUP BY}}. Suppose I take an aggregate that just return the first value
is has seen, you won't get at all the same result if you order before or after
you group. And I do think having the operation in the "wrong" order in the
syntax will be unintuitive in that case, and I'll admit I'm always for
privileging intuitiveness over sticking to familiarity with SQL. Opinion that
may or may not be popular, but I will add that I'm rather strongly against
forcing similarity with SQL if it goes against CQL self-consistency.
bq. If we consider that the pager is not exhausted if groupCount = groupLimit -
1 and rowCount < rowLimit, the user might end up requesting the next page for
nothing.
To hopefully help clarity I'll use "external" paging to describe the pages of
groups, the one sent to the user versus "internal" paging. I'm indeed
suggesting that 1) if replica hit the external page count (groupCount ==
groupLimit) then it doesn't send the first row of the next group and 2) on the
coordinator, we don't consider things exhausted if we hit exactly the external
page count.
Conceptually, this means that if an external page is complete, we assume there
is probably more (which may not be true). Note that this is the same logic that
normal paging use, so I'd argue that it's intuitive.
You approach consists in saying that when we do have a complete external page,
we send an additional row so the coordinator can decide whether or not we're
done or not.
My suggestion means we'll be wrong when the total number of groups for a query
is a multiple of the page size (and there is less groups than the user limit)
and will query one last empty page in the end in that case.
Your suggestion avoid this case but at the cost of
# transferring an extra row from replicas to coordinator for every "complete"
external page
# imo less clean and intuitive code
I suppose we can debate which of "an empty page query when #(groups) %
page_size == 0" and "an additional row transfered for every complete external
page" is worth, and we can definitively build examples where each one is better
than the other. I could argue however that, for a given user query, my
suggestion can only ever be worth than yours by some (small) constant factor
(the cost of an empty page), while if you take lots of groups, a smallish page
size and fat rows, your suggestion can be arbitrarily worth.
In practice, I suspect this doesn't matter much. Online queries almost never
page (if they do, you either don't care about performance or you'd better
configure your page size correctly) so both approach are the same. And
analytical queries are long enough that one additional empty page query don't
matter much, and if anything the "one additional row per complete page" might
matter more (though of course, rows are rarely too fat).
So to sum it up, I don't think any approach performance drawbacks matter for
the common case, but your solution has worst worst-cases and is, I argue, a bit
less clear code wise. I could still be missing something though and I apologize
if that's the case, but if I don't, I suspect you've guess which one I prefer :)
> Add support for Group By to Select statement
> --------------------------------------------
>
> Key: CASSANDRA-10707
> URL: https://issues.apache.org/jira/browse/CASSANDRA-10707
> Project: Cassandra
> Issue Type: Improvement
> Components: CQL
> Reporter: Benjamin Lerer
> Assignee: Benjamin Lerer
>
> Now that Cassandra support aggregate functions, it makes sense to support
> {{GROUP BY}} on the {{SELECT}} statements.
> It should be possible to group either at the partition level or at the
> clustering column level.
> {code}
> SELECT partitionKey, max(value) FROM myTable GROUP BY partitionKey;
> SELECT partitionKey, clustering0, clustering1, max(value) FROM myTable GROUP
> BY partitionKey, clustering0, clustering1;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
