You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by dh...@apache.org on 2021/05/05 10:25:56 UTC
[arrow-datafusion] branch master updated: Implement select distinct
(#262)
This is an automated email from the ASF dual-hosted git repository.
dheres pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/master by this push:
new 9e84f15 Implement select distinct (#262)
9e84f15 is described below
commit 9e84f15191860befab07d4f257ccb0be75f1b206
Author: Daniël Heres <da...@gmail.com>
AuthorDate: Wed May 5 12:25:46 2021 +0200
Implement select distinct (#262)
* Implement select distinct
* Simplify implementation
* Move out of project fn
* Fix call
* Fix docs
* Add to docs
* Add note of ALL support
* Add test for SELECT ALL
* Clippy
* Update datafusion/tests/sql.rs
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
---
datafusion/src/sql/planner.rs | 33 +++++++++--------
datafusion/tests/sql.rs | 75 +++++++++++++++++++++++++++++++++++++++
docs/user-guide/src/sql/select.md | 8 ++++-
3 files changed, 100 insertions(+), 16 deletions(-)
diff --git a/datafusion/src/sql/planner.rs b/datafusion/src/sql/planner.rs
index a40d0be..48900f5 100644
--- a/datafusion/src/sql/planner.rs
+++ b/datafusion/src/sql/planner.rs
@@ -17,9 +17,9 @@
//! SQL Query Planner (produces logical plan from SQL AST)
-use std::convert::TryInto;
use std::str::FromStr;
use std::sync::Arc;
+use std::{convert::TryInto, vec};
use crate::catalog::TableReference;
use crate::datasource::TableProvider;
@@ -621,7 +621,15 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
plan
};
- self.project(&plan, select_exprs_post_aggr, false)
+ let plan = if select.distinct {
+ return LogicalPlanBuilder::from(&plan)
+ .aggregate(select_exprs_post_aggr, vec![])?
+ .build();
+ } else {
+ plan
+ };
+
+ self.project(&plan, select_exprs_post_aggr)
}
/// Returns the `Expr`'s corresponding to a SQL query's SELECT expressions.
@@ -645,24 +653,19 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
/// Wrap a plan in a projection
///
- /// If the `force` argument is `false`, the projection is applied only when
- /// necessary, i.e., when the input fields are different than the
+ /// The projection is applied only when necessary,
+ /// i.e., when the input fields are different than the
/// projection. Note that if the input fields are the same, but out of
/// order, the projection will be applied.
- fn project(
- &self,
- input: &LogicalPlan,
- expr: Vec<Expr>,
- force: bool,
- ) -> Result<LogicalPlan> {
+ fn project(&self, input: &LogicalPlan, expr: Vec<Expr>) -> Result<LogicalPlan> {
self.validate_schema_satisfies_exprs(&input.schema(), &expr)?;
+
let plan = LogicalPlanBuilder::from(input).project(expr)?.build()?;
- let project = force
- || match input {
- LogicalPlan::TableScan { .. } => true,
- _ => plan.schema().fields() != input.schema().fields(),
- };
+ let project = match input {
+ LogicalPlan::TableScan { .. } => true,
+ _ => plan.schema().fields() != input.schema().fields(),
+ };
if project {
Ok(plan)
diff --git a/datafusion/tests/sql.rs b/datafusion/tests/sql.rs
index 7169294..bf28525 100644
--- a/datafusion/tests/sql.rs
+++ b/datafusion/tests/sql.rs
@@ -373,6 +373,81 @@ async fn csv_query_group_by_float32() -> Result<()> {
}
#[tokio::test]
+async fn select_all() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ register_aggregate_simple_csv(&mut ctx)?;
+
+ let sql = "SELECT c1 FROM aggregate_simple order by c1";
+ let actual_no_all = execute(&mut ctx, sql).await;
+
+ let sql_all = "SELECT ALL c1 FROM aggregate_simple order by c1";
+ let actual_all = execute(&mut ctx, sql_all).await;
+
+ assert_eq!(actual_no_all, actual_all);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn select_distinct() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ register_aggregate_simple_csv(&mut ctx)?;
+
+ let sql = "SELECT DISTINCT * FROM aggregate_simple";
+ let mut actual = execute(&mut ctx, sql).await;
+ actual.sort();
+
+ let mut dedup = actual.clone();
+ dedup.dedup();
+
+ assert_eq!(actual, dedup);
+
+ Ok(())
+}
+
+#[tokio::test]
+async fn select_distinct_simple() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ register_aggregate_simple_csv(&mut ctx)?;
+
+ let sql = "SELECT DISTINCT c1 FROM aggregate_simple order by c1";
+ let actual = execute(&mut ctx, sql).await;
+
+ let expected = vec![
+ vec!["0.00001"],
+ vec!["0.00002"],
+ vec!["0.00003"],
+ vec!["0.00004"],
+ vec!["0.00005"],
+ ];
+ assert_eq!(actual, expected);
+
+ let sql = "SELECT DISTINCT c1, c2 FROM aggregate_simple order by c1";
+ let actual = execute(&mut ctx, sql).await;
+
+ let expected = vec![
+ vec!["0.00001", "0.000000000001"],
+ vec!["0.00002", "0.000000000002"],
+ vec!["0.00003", "0.000000000003"],
+ vec!["0.00004", "0.000000000004"],
+ vec!["0.00005", "0.000000000005"],
+ ];
+ assert_eq!(actual, expected);
+
+ let sql = "SELECT distinct c3 FROM aggregate_simple order by c3";
+ let actual = execute(&mut ctx, sql).await;
+
+ let expected = vec![vec!["false"], vec!["true"]];
+ assert_eq!(actual, expected);
+
+ let sql = "SELECT distinct c1+c2 as a FROM aggregate_simple";
+ let actual = execute(&mut ctx, sql).await;
+
+ assert_eq!(actual.len(), 5);
+ Ok(())
+}
+
+#[tokio::test]
async fn csv_query_group_by_float64() -> Result<()> {
let mut ctx = ExecutionContext::new();
register_aggregate_simple_csv(&mut ctx)?;
diff --git a/docs/user-guide/src/sql/select.md b/docs/user-guide/src/sql/select.md
index 777b4ff..78d0cb5 100644
--- a/docs/user-guide/src/sql/select.md
+++ b/docs/user-guide/src/sql/select.md
@@ -26,7 +26,7 @@ DataFusion supports the following syntax for queries:
<code class="language-sql hljs">
[ [WITH](#with-clause) with_query [, ...] ] <br/>
-[SELECT](#select-clause) select_expr [, ...] <br/>
+[SELECT](#select-clause) [ ALL | DISTINCT ] select_expr [, ...] <br/>
[ [FROM](#from-clause) from_item [, ...] ] <br/>
[ [WHERE](#where-clause) condition ] <br/>
[ [GROUP BY](#group-by-clause) grouping_element [, ...] ] <br/>
@@ -55,6 +55,12 @@ Example:
SELECT a, b, a + b FROM table
```
+The `DISTINCT` quantifier can be added to make the query return all distinct rows.
+By default `ALL` will be used, which returns all the rows.
+
+```sql
+SELECT DISTINCT person, age FROM employees
+```
# FROM clause