cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sylvain Lebresne (JIRA)" <>
Subject [jira] [Commented] (CASSANDRA-10707) Add support for Group By to Select statement
Date Thu, 25 Feb 2016 17:13:18 GMT


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:
>             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
> {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

View raw message