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 301e863 fix: make nulls-order consistent with postgres (#1344)
301e863 is described below
commit 301e8631f444c8a9d5e2ff440cd5c30bf63e518a
Author: Carlos <[email protected]>
AuthorDate: Sun Nov 21 06:06:34 2021 +0800
fix: make nulls-order consistent with postgres (#1344)
---
datafusion/src/sql/planner.rs | 83 +++++++++++-----------
datafusion/tests/sql.rs | 78 +++++++++++++++++++-
integration-tests/create_test_table.sql | 4 +-
.../simple_sort_nulls_order.sql} | 24 ++-----
integration-tests/test_psql_parity.py | 2 +-
5 files changed, 127 insertions(+), 64 deletions(-)
diff --git a/datafusion/src/sql/planner.rs b/datafusion/src/sql/planner.rs
index 575acd0..9f1d243 100644
--- a/datafusion/src/sql/planner.rs
+++ b/datafusion/src/sql/planner.rs
@@ -1026,12 +1026,15 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
/// convert sql OrderByExpr to Expr::Sort
fn order_by_to_sort_expr(&self, e: &OrderByExpr, schema: &DFSchema) ->
Result<Expr> {
- Ok(Expr::Sort {
- expr: Box::new(self.sql_expr_to_logical_expr(&e.expr, schema)?),
- // by default asc
- asc: e.asc.unwrap_or(true),
- // by default nulls first to be consistent with spark
- nulls_first: e.nulls_first.unwrap_or(true),
+ Ok({
+ let asc = e.asc.unwrap_or(true);
+ Expr::Sort {
+ expr: Box::new(self.sql_expr_to_logical_expr(&e.expr,
schema)?),
+ asc,
+ // when asc is true, by default nulls last to be consistent
with postgres
+ // postgres rule:
https://www.postgresql.org/docs/current/queries-order.html
+ nulls_first: e.nulls_first.unwrap_or(!asc),
+ }
})
}
@@ -2962,7 +2965,7 @@ mod tests {
#[test]
fn select_order_by() {
let sql = "SELECT id FROM person ORDER BY id";
- let expected = "Sort: #person.id ASC NULLS FIRST\
+ let expected = "Sort: #person.id ASC NULLS LAST\
\n Projection: #person.id\
\n TableScan: person projection=None";
quick_test(sql, expected);
@@ -3280,9 +3283,9 @@ mod tests {
fn empty_over_dup_with_different_sort() {
let sql = "SELECT order_id oid, MAX(order_id) OVER (), MAX(order_id)
OVER (ORDER BY order_id) from orders";
let expected = "\
- Projection: #orders.order_id AS oid, #MAX(orders.order_id),
#MAX(orders.order_id) ORDER BY [#orders.order_id ASC NULLS FIRST]\
+ Projection: #orders.order_id AS oid, #MAX(orders.order_id),
#MAX(orders.order_id) ORDER BY [#orders.order_id ASC NULLS LAST]\
\n WindowAggr: windowExpr=[[MAX(#orders.order_id)]]\
- \n WindowAggr: windowExpr=[[MAX(#orders.order_id) ORDER BY
[#orders.order_id ASC NULLS FIRST]]]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.order_id) ORDER BY
[#orders.order_id ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3343,8 +3346,8 @@ mod tests {
fn over_order_by() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id),
MIN(qty) OVER (ORDER BY order_id DESC) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST], #MIN(orders.qty) ORDER BY [#orders.order_id
DESC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST], #MIN(orders.qty) ORDER BY [#orders.order_id
DESC NULLS FIRST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST]]]\
\n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id DESC NULLS FIRST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
@@ -3354,8 +3357,8 @@ mod tests {
fn over_order_by_with_window_frame_double_end() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id ROWS
BETWEEN 3 PRECEDING and 3 FOLLOWING), MIN(qty) OVER (ORDER BY order_id DESC)
from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING]]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING]]\
\n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id DESC NULLS FIRST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
@@ -3365,8 +3368,8 @@ mod tests {
fn over_order_by_with_window_frame_single_end() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id ROWS 3
PRECEDING), MIN(qty) OVER (ORDER BY order_id DESC) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW]]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW]]\
\n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id DESC NULLS FIRST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
@@ -3408,8 +3411,8 @@ mod tests {
fn over_order_by_with_window_frame_single_end_groups() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id GROUPS 3
PRECEDING), MIN(qty) OVER (ORDER BY order_id DESC) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST] GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW]]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW,
#MIN(orders.qty) ORDER BY [#orders.order_id DESC NULLS FIRST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST] GROUPS BETWEEN 3 PRECEDING AND CURRENT ROW]]\
\n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id DESC NULLS FIRST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
@@ -3431,9 +3434,9 @@ mod tests {
fn over_order_by_two_sort_keys() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id),
MIN(qty) OVER (ORDER BY (order_id + 1)) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST], #MIN(orders.qty) ORDER BY [#orders.order_id
+ Int64(1) ASC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id + Int64(1) ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST], #MIN(orders.qty) ORDER BY [#orders.order_id
+ Int64(1) ASC NULLS LAST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id + Int64(1) ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3455,10 +3458,10 @@ mod tests {
fn over_order_by_sort_keys_sorting() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY qty, order_id),
SUM(qty) OVER (), MIN(qty) OVER (ORDER BY order_id, qty) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY [#orders.qty
ASC NULLS FIRST, #orders.order_id ASC NULLS FIRST], #SUM(orders.qty),
#MIN(orders.qty) ORDER BY [#orders.order_id ASC NULLS FIRST, #orders.qty ASC
NULLS FIRST]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY [#orders.qty
ASC NULLS LAST, #orders.order_id ASC NULLS LAST], #SUM(orders.qty),
#MIN(orders.qty) ORDER BY [#orders.order_id ASC NULLS LAST, #orders.qty ASC
NULLS LAST]\
\n WindowAggr: windowExpr=[[SUM(#orders.qty)]]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY [#orders.qty
ASC NULLS FIRST, #orders.order_id ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST, #orders.qty ASC NULLS FIRST]]]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY [#orders.qty
ASC NULLS LAST, #orders.order_id ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST, #orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3480,10 +3483,10 @@ mod tests {
fn over_order_by_sort_keys_sorting_prefix_compacting() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id),
SUM(qty) OVER (), MIN(qty) OVER (ORDER BY order_id, qty) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST], #SUM(orders.qty), #MIN(orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST, #orders.qty ASC NULLS FIRST]\
+ Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST], #SUM(orders.qty), #MIN(orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST, #orders.qty ASC NULLS LAST]\
\n WindowAggr: windowExpr=[[SUM(#orders.qty)]]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST, #orders.qty ASC NULLS FIRST]]]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST, #orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3508,11 +3511,11 @@ mod tests {
fn over_order_by_sort_keys_sorting_global_order_compacting() {
let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY qty, order_id),
SUM(qty) OVER (), MIN(qty) OVER (ORDER BY order_id, qty) from orders ORDER BY
order_id";
let expected = "\
- Sort: #orders.order_id ASC NULLS FIRST\
- \n Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.qty ASC NULLS FIRST, #orders.order_id ASC NULLS FIRST],
#SUM(orders.qty), #MIN(orders.qty) ORDER BY [#orders.order_id ASC NULLS FIRST,
#orders.qty ASC NULLS FIRST]\
+ Sort: #orders.order_id ASC NULLS LAST\
+ \n Projection: #orders.order_id, #MAX(orders.qty) ORDER BY
[#orders.qty ASC NULLS LAST, #orders.order_id ASC NULLS LAST],
#SUM(orders.qty), #MIN(orders.qty) ORDER BY [#orders.order_id ASC NULLS LAST,
#orders.qty ASC NULLS LAST]\
\n WindowAggr: windowExpr=[[SUM(#orders.qty)]]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.qty ASC NULLS FIRST, #orders.order_id ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS FIRST, #orders.qty ASC NULLS FIRST]]]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) ORDER BY
[#orders.qty ASC NULLS LAST, #orders.order_id ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) ORDER BY
[#orders.order_id ASC NULLS LAST, #orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3531,8 +3534,8 @@ mod tests {
let sql =
"SELECT order_id, MAX(qty) OVER (PARTITION BY order_id ORDER BY
qty) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS LAST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3551,8 +3554,8 @@ mod tests {
let sql =
"SELECT order_id, MAX(qty) OVER (PARTITION BY order_id, qty ORDER
BY qty) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS LAST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3574,9 +3577,9 @@ mod tests {
let sql =
"SELECT order_id, MAX(qty) OVER (PARTITION BY order_id, qty ORDER
BY qty), MIN(qty) OVER (PARTITION BY qty ORDER BY order_id) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS FIRST],
#MIN(orders.qty) PARTITION BY [#orders.qty] ORDER BY [#orders.order_id ASC
NULLS FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) PARTITION BY
[#orders.qty] ORDER BY [#orders.order_id ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS LAST],
#MIN(orders.qty) PARTITION BY [#orders.qty] ORDER BY [#orders.order_id ASC
NULLS LAST]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) PARTITION BY
[#orders.qty] ORDER BY [#orders.order_id ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.qty ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
@@ -3597,9 +3600,9 @@ mod tests {
let sql =
"SELECT order_id, MAX(qty) OVER (PARTITION BY order_id ORDER BY
qty), MIN(qty) OVER (PARTITION BY order_id, qty ORDER BY price) from orders";
let expected = "\
- Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS FIRST], #MIN(orders.qty)
PARTITION BY [#orders.order_id, #orders.qty] ORDER BY [#orders.price ASC NULLS
FIRST]\
- \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS FIRST]]]\
- \n WindowAggr: windowExpr=[[MIN(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.price ASC NULLS FIRST]]]\
+ Projection: #orders.order_id, #MAX(orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS LAST], #MIN(orders.qty)
PARTITION BY [#orders.order_id, #orders.qty] ORDER BY [#orders.price ASC NULLS
LAST]\
+ \n WindowAggr: windowExpr=[[MAX(#orders.qty) PARTITION BY
[#orders.order_id] ORDER BY [#orders.qty ASC NULLS LAST]]]\
+ \n WindowAggr: windowExpr=[[MIN(#orders.qty) PARTITION BY
[#orders.order_id, #orders.qty] ORDER BY [#orders.price ASC NULLS LAST]]]\
\n TableScan: orders projection=None";
quick_test(sql, expected);
}
diff --git a/datafusion/tests/sql.rs b/datafusion/tests/sql.rs
index c910e55..e761633 100644
--- a/datafusion/tests/sql.rs
+++ b/datafusion/tests/sql.rs
@@ -2412,10 +2412,10 @@ async fn right_join() -> Result<()> {
"+-------+---------+---------+",
"| t1_id | t1_name | t2_name |",
"+-------+---------+---------+",
- "| | | w |",
"| 11 | a | z |",
"| 22 | b | y |",
"| 44 | d | x |",
+ "| | | w |",
"+-------+---------+---------+",
];
for sql in equivalent_sql.iter() {
@@ -2436,11 +2436,11 @@ async fn full_join() -> Result<()> {
"+-------+---------+---------+",
"| t1_id | t1_name | t2_name |",
"+-------+---------+---------+",
- "| | | w |",
"| 11 | a | z |",
"| 22 | b | y |",
"| 33 | c | |",
"| 44 | d | x |",
+ "| | | w |",
"+-------+---------+---------+",
];
for sql in equivalent_sql.iter() {
@@ -3025,7 +3025,7 @@ async fn explain_analyze_baseline_metrics() {
);
assert_metrics!(
&formatted,
- "SortExec: [c1@0 ASC]",
+ "SortExec: [c1@0 ASC NULLS LAST]",
"metrics=[output_rows=5, elapsed_compute="
);
assert_metrics!(
@@ -6204,3 +6204,75 @@ async fn test_expect_distinct() -> Result<()> {
assert_batches_eq!(expected, &actual);
Ok(())
}
+
+#[tokio::test]
+async fn test_nulls_first_asc() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ let sql = "SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (null, 'three'))
AS t (num,letter) ORDER BY num";
+ let actual = execute_to_batches(&mut ctx, sql).await;
+ let expected = vec![
+ "+-----+--------+",
+ "| num | letter |",
+ "+-----+--------+",
+ "| 1 | one |",
+ "| 2 | two |",
+ "| | three |",
+ "+-----+--------+",
+ ];
+ assert_batches_eq!(expected, &actual);
+ Ok(())
+}
+
+#[tokio::test]
+async fn test_nulls_first_desc() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ let sql = "SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (null, 'three'))
AS t (num,letter) ORDER BY num DESC";
+ let actual = execute_to_batches(&mut ctx, sql).await;
+ let expected = vec![
+ "+-----+--------+",
+ "| num | letter |",
+ "+-----+--------+",
+ "| | three |",
+ "| 2 | two |",
+ "| 1 | one |",
+ "+-----+--------+",
+ ];
+ assert_batches_eq!(expected, &actual);
+ Ok(())
+}
+
+#[tokio::test]
+async fn test_specific_nulls_last_desc() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ let sql = "SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (null, 'three'))
AS t (num,letter) ORDER BY num DESC NULLS LAST";
+ let actual = execute_to_batches(&mut ctx, sql).await;
+ let expected = vec![
+ "+-----+--------+",
+ "| num | letter |",
+ "+-----+--------+",
+ "| 2 | two |",
+ "| 1 | one |",
+ "| | three |",
+ "+-----+--------+",
+ ];
+ assert_batches_eq!(expected, &actual);
+ Ok(())
+}
+
+#[tokio::test]
+async fn test_specific_nulls_first_asc() -> Result<()> {
+ let mut ctx = ExecutionContext::new();
+ let sql = "SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (null, 'three'))
AS t (num,letter) ORDER BY num ASC NULLS FIRST";
+ let actual = execute_to_batches(&mut ctx, sql).await;
+ let expected = vec![
+ "+-----+--------+",
+ "| num | letter |",
+ "+-----+--------+",
+ "| | three |",
+ "| 1 | one |",
+ "| 2 | two |",
+ "+-----+--------+",
+ ];
+ assert_batches_eq!(expected, &actual);
+ Ok(())
+}
diff --git a/integration-tests/create_test_table.sql
b/integration-tests/create_test_table.sql
index 89b0861..580ca0a 100644
--- a/integration-tests/create_test_table.sql
+++ b/integration-tests/create_test_table.sql
@@ -18,8 +18,8 @@ CREATE EXTERNAL TABLE test (
c1 VARCHAR NOT NULL,
c2 INT NOT NULL,
c3 SMALLINT NOT NULL,
- c4 SMALLINT NOT NULL,
- c5 INT NOT NULL,
+ c4 SMALLINT,
+ c5 INT,
c6 BIGINT NOT NULL,
c7 SMALLINT NOT NULL,
c8 INT NOT NULL,
diff --git a/integration-tests/create_test_table.sql
b/integration-tests/sqls/simple_sort_nulls_order.sql
similarity index 64%
copy from integration-tests/create_test_table.sql
copy to integration-tests/sqls/simple_sort_nulls_order.sql
index 89b0861..f5c61aa 100644
--- a/integration-tests/create_test_table.sql
+++ b/integration-tests/sqls/simple_sort_nulls_order.sql
@@ -14,21 +14,9 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
-CREATE EXTERNAL TABLE test (
- c1 VARCHAR NOT NULL,
- c2 INT NOT NULL,
- c3 SMALLINT NOT NULL,
- c4 SMALLINT NOT NULL,
- c5 INT NOT NULL,
- c6 BIGINT NOT NULL,
- c7 SMALLINT NOT NULL,
- c8 INT NOT NULL,
- c9 BIGINT NOT NULL,
- c10 VARCHAR NOT NULL,
- c11 FLOAT NOT NULL,
- c12 DOUBLE NOT NULL,
- c13 VARCHAR NOT NULL
-)
-STORED AS CSV
-WITH HEADER ROW
-LOCATION 'testing/data/csv/aggregate_test_100.csv';
+SELECT
+ c5,
+ c4,
+ c10
+FROM test
+ORDER BY c5 ASC, c4 DESC, c10;
\ No newline at end of file
diff --git a/integration-tests/test_psql_parity.py
b/integration-tests/test_psql_parity.py
index 8cd94ba..8a1091c 100644
--- a/integration-tests/test_psql_parity.py
+++ b/integration-tests/test_psql_parity.py
@@ -77,7 +77,7 @@ test_files = set(root.glob("*.sql"))
class TestPsqlParity:
def test_tests_count(self):
- assert len(test_files) == 20, "tests are missed"
+ assert len(test_files) == 21, "tests are missed"
@pytest.mark.parametrize("fname", test_files)
def test_sql_file(self, fname):
