You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by ja...@apache.org on 2023/05/08 12:13:58 UTC
[arrow-datafusion] branch main updated: Port tests in functions.rs to sqllogictest (#6256)
This is an automated email from the ASF dual-hosted git repository.
jakevin pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new dcc5feb070 Port tests in functions.rs to sqllogictest (#6256)
dcc5feb070 is described below
commit dcc5feb0705fcd9128d8cf33436e716853def54a
Author: parkma99 <84...@users.noreply.github.com>
AuthorDate: Mon May 8 20:13:53 2023 +0800
Port tests in functions.rs to sqllogictest (#6256)
---
datafusion/core/tests/sql/functions.rs | 423 ---------------------
.../core/tests/sqllogictests/test_files/scalar.slt | 156 ++++++++
2 files changed, 156 insertions(+), 423 deletions(-)
diff --git a/datafusion/core/tests/sql/functions.rs b/datafusion/core/tests/sql/functions.rs
index ab3cc82b50..6083f8834c 100644
--- a/datafusion/core/tests/sql/functions.rs
+++ b/datafusion/core/tests/sql/functions.rs
@@ -35,305 +35,6 @@ async fn sqrt_f32_vs_f64() -> Result<()> {
Ok(())
}
-#[tokio::test]
-async fn csv_query_cast() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT CAST(c12 AS float) FROM aggregate_test_100 WHERE c12 > 0.376 AND c12 < 0.4";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+------------------------+",
- "| aggregate_test_100.c12 |",
- "+------------------------+",
- "| 0.39144436 |",
- "| 0.3887028 |",
- "+------------------------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_cast_literal() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql =
- "SELECT c12, CAST(1 AS float) FROM aggregate_test_100 WHERE c12 > CAST(0 AS float) LIMIT 2";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+--------------------+----------+",
- "| c12 | Int64(1) |",
- "+--------------------+----------+",
- "| 0.9294097332465232 | 1.0 |",
- "| 0.3114712539863804 | 1.0 |",
- "+--------------------+----------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_concat() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Utf8, false),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(StringArray::from_slice(["", "a", "aa", "aaa"])),
- Arc::new(Int32Array::from(vec![Some(0), Some(1), None, Some(3)])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT concat(c1, '-hi-', cast(c2 as varchar)) FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------------------------------+",
- "| concat(test.c1,Utf8(\"-hi-\"),test.c2) |",
- "+--------------------------------------+",
- "| -hi-0 |",
- "| a-hi-1 |",
- "| aa-hi- |",
- "| aaa-hi-3 |",
- "+--------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_array() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Utf8, false),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(StringArray::from_slice(["", "a", "aa", "aaa"])),
- Arc::new(Int32Array::from(vec![Some(0), Some(1), None, Some(3)])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT make_array(c1, cast(c2 as varchar)) FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----------------------------+",
- "| makearray(test.c1,test.c2) |",
- "+----------------------------+",
- "| [, 0] |",
- "| [a, 1] |",
- "| [aa, ] |",
- "| [aaa, 3] |",
- "+----------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_array_scalar() -> Result<()> {
- let ctx = SessionContext::new();
-
- let sql = "SELECT make_array(1, 2, 3);";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------------------------+",
- "| makearray(Int64(1),Int64(2),Int64(3)) |",
- "+---------------------------------------+",
- "| [1, 2, 3] |",
- "+---------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_static_empty_value() -> Result<()> {
- let ctx = SessionContext::new();
- let sql = "SELECT COALESCE('', 'test')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------------------+",
- "| coalesce(Utf8(\"\"),Utf8(\"test\")) |",
- "+---------------------------------+",
- "| |",
- "+---------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_static_value_with_null() -> Result<()> {
- let ctx = SessionContext::new();
- let sql = "SELECT COALESCE(NULL, 'test')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------------------+",
- "| coalesce(NULL,Utf8(\"test\")) |",
- "+-----------------------------+",
- "| test |",
- "+-----------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_result() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Int32, true),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(Int32Array::from(vec![Some(0), None, Some(1), None, None])),
- Arc::new(Int32Array::from(vec![
- Some(1),
- Some(1),
- Some(0),
- Some(1),
- None,
- ])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT COALESCE(c1, c2) FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------------+",
- "| coalesce(test.c1,test.c2) |",
- "+---------------------------+",
- "| 0 |",
- "| 1 |",
- "| 1 |",
- "| 1 |",
- "| |",
- "+---------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_result_with_default_value() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Int32, true),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(Int32Array::from(vec![Some(0), None, Some(1), None, None])),
- Arc::new(Int32Array::from(vec![
- Some(1),
- Some(1),
- Some(0),
- Some(1),
- None,
- ])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT COALESCE(c1, c2, '-1') FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------------------------------+",
- "| coalesce(test.c1,test.c2,Utf8(\"-1\")) |",
- "+--------------------------------------+",
- "| 0 |",
- "| 1 |",
- "| 1 |",
- "| 1 |",
- "| -1 |",
- "+--------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_sum_with_default_value() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Int32, true),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(Int32Array::from(vec![Some(1), None, Some(1), None])),
- Arc::new(Int32Array::from(vec![Some(2), Some(2), None, None])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT SUM(COALESCE(c1, c2, 0)) FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------------------------------+",
- "| SUM(coalesce(test.c1,test.c2,Int64(0))) |",
- "+-----------------------------------------+",
- "| 4 |",
- "+-----------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn coalesce_mul_with_default_value() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("c1", DataType::Int32, true),
- Field::new("c2", DataType::Int32, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(Int32Array::from(vec![Some(1), None, Some(1), None])),
- Arc::new(Int32Array::from(vec![Some(2), Some(2), None, None])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT COALESCE(c1 * c2, 0) FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------------------------------+",
- "| coalesce(test.c1 * test.c2,Int64(0)) |",
- "+--------------------------------------+",
- "| 2 |",
- "| 0 |",
- "| 0 |",
- "| 0 |",
- "+--------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
#[tokio::test]
async fn case_sensitive_identifiers_functions() {
let ctx = SessionContext::new();
@@ -441,127 +142,3 @@ async fn case_builtin_math_expression() {
assert_batches_sorted_eq!(expected, &results);
}
}
-
-#[tokio::test]
-async fn test_power() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![
- Field::new("i32", DataType::Int16, true),
- Field::new("i64", DataType::Int64, true),
- Field::new("f32", DataType::Float32, true),
- Field::new("f64", DataType::Float64, true),
- ]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![
- Arc::new(Int16Array::from(vec![
- Some(2),
- Some(5),
- Some(0),
- Some(-14),
- None,
- ])),
- Arc::new(Int64Array::from(vec![
- Some(2),
- Some(5),
- Some(0),
- Some(-14),
- None,
- ])),
- Arc::new(Float32Array::from(vec![
- Some(1.0),
- Some(2.5),
- Some(0.0),
- Some(-14.5),
- None,
- ])),
- Arc::new(Float64Array::from(vec![
- Some(1.0),
- Some(2.5),
- Some(0.0),
- Some(-14.5),
- None,
- ])),
- ],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = r"SELECT power(i32, exp_i) as power_i32,
- power(i64, exp_f) as power_i64,
- pow(f32, exp_i) as power_f32,
- power(f64, exp_f) as power_f64,
- pow(2, 3) as power_int_scalar,
- power(2.5, 3.0) as power_float_scalar
- FROM (select test.*, 3 as exp_i, 3.0 as exp_f from test) a";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------+-----------+-----------+-----------+------------------+--------------------+",
- "| power_i32 | power_i64 | power_f32 | power_f64 | power_int_scalar | power_float_scalar |",
- "+-----------+-----------+-----------+-----------+------------------+--------------------+",
- "| 8 | 8.0 | 1.0 | 1.0 | 8 | 15.625 |",
- "| 125 | 125.0 | 15.625 | 15.625 | 8 | 15.625 |",
- "| 0 | 0.0 | 0.0 | 0.0 | 8 | 15.625 |",
- "| -2744 | -2744.0 | -3048.625 | -3048.625 | 8 | 15.625 |",
- "| | | | | 8 | 15.625 |",
- "+-----------+-----------+-----------+-----------+------------------+--------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- //dbg!(actual[0].schema().fields());
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_i32")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Int64
- );
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_i64")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Float64
- );
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_f32")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Float64
- );
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_f64")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Float64
- );
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_int_scalar")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Int64
- );
- assert_eq!(
- actual[0]
- .schema()
- .field_with_name("power_float_scalar")
- .unwrap()
- .data_type()
- .to_owned(),
- DataType::Float64
- );
-
- Ok(())
-}
diff --git a/datafusion/core/tests/sqllogictests/test_files/scalar.slt b/datafusion/core/tests/sqllogictests/test_files/scalar.slt
index bdd28eb07a..8b8ad80d5a 100644
--- a/datafusion/core/tests/sqllogictests/test_files/scalar.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/scalar.slt
@@ -676,6 +676,20 @@ SELECT cbrt(cbrt(c12)) FROM aggregate_test_100 LIMIT 1
----
0.991899036678
+# csv query cast
+query R rowsort
+SELECT CAST(c12 AS float) FROM aggregate_test_100 WHERE c12 > 0.376 AND c12 < 0.4
+----
+0.3887028
+0.39144436
+
+# csv query cast literal
+query RR rowsort
+SELECT c12, CAST(1 AS float) FROM aggregate_test_100 WHERE c12 > CAST(0 AS float) LIMIT 2
+----
+0.311471253986 1
+0.929409733247 1
+
statement ok
drop table aggregate_test_100
@@ -993,3 +1007,145 @@ SELECT column1 not like NULL as col_null, NULL not like column1 as null_col from
NULL NULL
NULL NULL
NULL NULL
+
+statement ok
+CREATE TABLE test(
+ c1 VARCHAR,
+ c2 INT
+) as VALUES
+('',0),
+('a',1),
+('aa',NULL),
+('aaa',3);
+
+# query concat
+query T rowsort
+SELECT concat(c1, '-hi-', cast(c2 as varchar)) FROM test
+----
+-hi-0
+a-hi-1
+aa-hi-
+aaa-hi-3
+
+# query array
+query ? rowsort
+SELECT make_array(c1, cast(c2 as varchar)) FROM test
+----
+[, 0]
+[a, 1]
+[aa, ]
+[aaa, 3]
+
+statement ok
+drop table test
+
+# query array scalar
+query ?
+SELECT make_array(1, 2, 3);
+----
+[1, 2, 3]
+
+# coalesce static empty value
+query T
+SELECT COALESCE('', 'test')
+----
+(empty)
+
+# coalesce static value with null
+query T
+SELECT COALESCE(NULL, 'test')
+----
+test
+
+statement ok
+CREATE TABLE test(
+ c1 INT,
+ c2 INT
+) as VALUES
+(0, 1),
+(NULL, 1),
+(1, 0),
+(NULL, 1),
+(NULL, NULL);
+
+# coalesce result
+query I rowsort
+SELECT COALESCE(c1, c2) FROM test
+----
+0
+1
+1
+1
+NULL
+
+# coalesce result with default value
+query T rowsort
+SELECT COALESCE(c1, c2, '-1') FROM test
+----
+-1
+0
+1
+1
+1
+
+statement ok
+drop table test
+
+statement ok
+CREATE TABLE test(
+ c1 INT,
+ c2 INT
+) as VALUES
+(1, 2),
+(NULL, 2),
+(1, NULL),
+(NULL, NULL);
+
+# coalesce sum with default value
+query I
+SELECT SUM(COALESCE(c1, c2, 0)) FROM test
+----
+4
+
+# coalesce mul with default value
+query I
+SELECT COALESCE(c1 * c2, 0) FROM test
+----
+2
+0
+0
+0
+
+statement ok
+drop table test
+
+statement ok
+CREATE TABLE test(
+ i32 INT,
+ i64 INT,
+ f32 FLOAT,
+ f64 FLOAT
+) as VALUES
+(2, 2, 1.0, 1.0),
+(5, 5, 2.5, 2.5),
+(0, 0, 0.0, 0.0),
+(-14, -14, -14.5, -14.5),
+(NULL, NULL, NULL, NULL);
+
+query RRRRIR rowsort
+SELECT power(i32, exp_i) as power_i32,
+ power(i64, exp_f) as power_i64,
+ pow(f32, exp_i) as power_f32,
+ power(f64, exp_f) as power_f64,
+ pow(2, 3) as power_int_scalar,
+ power(2.5, 3.0) as power_float_scalar
+FROM (select test.*, 3 as exp_i, 3.0 as exp_f from test) a
+----
+-2744 -2744 -3048.625 -3048.625 8 15.625
+0 0 0 0 8 15.625
+125 125 15.625 15.625 8 15.625
+8 8 1 1 8 15.625
+NULL NULL NULL NULL 8 15.625
+
+statement ok
+drop table test