You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@arrow.apache.org by Marc Prud'hommeaux <mp...@apache.org> on 2021/02/20 14:31:23 UTC
[Rust][DataFusion] Inconsistent array ordering with "GROUP BY" SQL
When I group by a column in DataFusion SQL, the order of the results is different every time. For example, "select country from data group by country" against https://github.com/Teradata/kylo/blob/master/samples/sample-data/csv/userdata3.csv might return "Moldova" first one time, and then "Sweden" first the next time I execute it.
It appears that this is known and acknowledged behavior (it is mentioned at https://issues.apache.org/jira/browse/ARROW-5680), but is there good reason for it (e.g., performance; simplicity; random hash seeding)? I understand why it makes sense to not unnecessarily impose a particular ordering, but is there any reason the results are not consistent between two identical SQL statements executed against the same datafusion::execution::context::ExecutionContext?
Re: [Rust][DataFusion] Inconsistent array ordering with "GROUP BY" SQL
Posted by Adam Hooper <ad...@adamhooper.com>.
On Sun, Feb 21, 2021 at 5:27 AM Andrew Lamb <al...@influxdata.com> wrote:
> For what it is worth, my experience with some SQL databases has been the
> opposite -- ordering can and does differ from statement to statement if the
> clause has a GROUP BY but no ORDER BY).
>
Is this a security issue? If the GROUP BY results aren't repeatable, then
the ordering must come from some state internal to the server. Could a
clever caller reverse-engineer that state to find out what's in the
server's RAM?
For instance, if a 12-row result's ordering cycles through 25 permutations
repeatedly, then Alice can memorize the pattern. With that information she
can detect, in a given amount of time, how many times Bob ran the same
query.
Adam
--
Adam Hooper
+1-514-882-9694
http://adamhooper.com
Re: [Rust][DataFusion] Inconsistent array ordering with "GROUP BY" SQL
Posted by Andrew Lamb <al...@influxdata.com>.
For what it is worth, my experience with some SQL databases has been the
opposite -- ordering can and does differ from statement to statement if the
clause has a GROUP BY but no ORDER BY).
As Andy mentioned, the core reason is performance -- as can require
additional computation to ensure the results come out in a specific,
repeatable order, so SQL allows users to choose if they want to pay the
potential cost for that feature or not (by including ORDER BY)
Some databases that use (or might use) merge based grouping (as opposed to
hash based grouping, as DataFusion currently has) will often produce data
in out in a consistent order due to an artifact of that implementation.
Andrew
On Sat, Feb 20, 2021 at 10:30 AM Marc Prud'hommeaux <mp...@apache.org>
wrote:
>
> I understand that GROUP BY ought not imply any particular ordering; it's
> just that working with other SQL databases, I've come to expect that
> ordering will be consistent between multiple runs of the same statement, at
> least within the context of a single transaction on a single connection.
>
> I do note that the random ordering is still present when I reduce the CSV
> to 2 rows, so I doubt it is #2, exclusively at least. But the ability to
> process and return the data in parallel is very compelling, so I don't
> think it matters whether the phenomenon is a result of #1 or something else.
>
> Thanks for indulging my curiosity!
>
> –Marc
>
> On 2021/02/20 14:49:59, Andy Grove <an...@gmail.com> wrote:
> > The SQL standard in general makes no guarantee of the order of resulting
> > data unless there is an explicit ORDER BY clause.
> >
> > I would guess that there are two factors in play here:
> >
> > 1. The use of hash-based data structures, as you mention
> > 2. If you have partitioned data then it is processed on multiple threads
> > and that can affect ordering as well
> >
> > Andy.
> >
> > On Sat, Feb 20, 2021 at 7:31 AM Marc Prud'hommeaux <mp...@apache.org>
> > wrote:
> >
> > > When I group by a column in DataFusion SQL, the order of the results is
> > > different every time. For example, "select country from data group by
> > > country" against
> > >
> https://github.com/Teradata/kylo/blob/master/samples/sample-data/csv/userdata3.csv
> > > might return "Moldova" first one time, and then "Sweden" first the next
> > > time I execute it.
> > >
> > > It appears that this is known and acknowledged behavior (it is
> mentioned
> > > at https://issues.apache.org/jira/browse/ARROW-5680), but is there
> good
> > > reason for it (e.g., performance; simplicity; random hash seeding)? I
> > > understand why it makes sense to not unnecessarily impose a particular
> > > ordering, but is there any reason the results are not consistent
> between
> > > two identical SQL statements executed against the same
> > > datafusion::execution::context::ExecutionContext?
> > >
> > >
> >
>
Re: [Rust][DataFusion] Inconsistent array ordering with "GROUP BY" SQL
Posted by Marc Prud'hommeaux <mp...@apache.org>.
I understand that GROUP BY ought not imply any particular ordering; it's just that working with other SQL databases, I've come to expect that ordering will be consistent between multiple runs of the same statement, at least within the context of a single transaction on a single connection.
I do note that the random ordering is still present when I reduce the CSV to 2 rows, so I doubt it is #2, exclusively at least. But the ability to process and return the data in parallel is very compelling, so I don't think it matters whether the phenomenon is a result of #1 or something else.
Thanks for indulging my curiosity!
–Marc
On 2021/02/20 14:49:59, Andy Grove <an...@gmail.com> wrote:
> The SQL standard in general makes no guarantee of the order of resulting
> data unless there is an explicit ORDER BY clause.
>
> I would guess that there are two factors in play here:
>
> 1. The use of hash-based data structures, as you mention
> 2. If you have partitioned data then it is processed on multiple threads
> and that can affect ordering as well
>
> Andy.
>
> On Sat, Feb 20, 2021 at 7:31 AM Marc Prud'hommeaux <mp...@apache.org>
> wrote:
>
> > When I group by a column in DataFusion SQL, the order of the results is
> > different every time. For example, "select country from data group by
> > country" against
> > https://github.com/Teradata/kylo/blob/master/samples/sample-data/csv/userdata3.csv
> > might return "Moldova" first one time, and then "Sweden" first the next
> > time I execute it.
> >
> > It appears that this is known and acknowledged behavior (it is mentioned
> > at https://issues.apache.org/jira/browse/ARROW-5680), but is there good
> > reason for it (e.g., performance; simplicity; random hash seeding)? I
> > understand why it makes sense to not unnecessarily impose a particular
> > ordering, but is there any reason the results are not consistent between
> > two identical SQL statements executed against the same
> > datafusion::execution::context::ExecutionContext?
> >
> >
>
Re: [Rust][DataFusion] Inconsistent array ordering with "GROUP BY" SQL
Posted by Andy Grove <an...@gmail.com>.
The SQL standard in general makes no guarantee of the order of resulting
data unless there is an explicit ORDER BY clause.
I would guess that there are two factors in play here:
1. The use of hash-based data structures, as you mention
2. If you have partitioned data then it is processed on multiple threads
and that can affect ordering as well
Andy.
On Sat, Feb 20, 2021 at 7:31 AM Marc Prud'hommeaux <mp...@apache.org>
wrote:
> When I group by a column in DataFusion SQL, the order of the results is
> different every time. For example, "select country from data group by
> country" against
> https://github.com/Teradata/kylo/blob/master/samples/sample-data/csv/userdata3.csv
> might return "Moldova" first one time, and then "Sweden" first the next
> time I execute it.
>
> It appears that this is known and acknowledged behavior (it is mentioned
> at https://issues.apache.org/jira/browse/ARROW-5680), but is there good
> reason for it (e.g., performance; simplicity; random hash seeding)? I
> understand why it makes sense to not unnecessarily impose a particular
> ordering, but is there any reason the results are not consistent between
> two identical SQL statements executed against the same
> datafusion::execution::context::ExecutionContext?
>
>