This is an automated email from the ASF dual-hosted git repository.
xudong963 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 2c573105c3 minor: port some expr tests to sqllogictests, improve error
message (#5968)
2c573105c3 is described below
commit 2c573105c3d3e4044b4ca74e4faa59d31e57f17b
Author: Andrew Lamb <[email protected]>
AuthorDate: Wed Apr 12 23:06:20 2023 -0400
minor: port some expr tests to sqllogictests, improve error message (#5968)
* minor: port some expr tests to sqllogictests, improve error message
* touchups
---
datafusion/core/tests/sql/expr.rs | 654 ---------------------
datafusion/core/tests/sql/mod.rs | 16 -
datafusion/core/tests/sqllogictests/src/main.rs | 4 +
datafusion/core/tests/sqllogictests/src/setup.rs | 21 +
.../core/tests/sqllogictests/test_files/scalar.slt | 321 +++++++++-
datafusion/physical-expr/src/expressions/not.rs | 4 +-
6 files changed, 346 insertions(+), 674 deletions(-)
diff --git a/datafusion/core/tests/sql/expr.rs
b/datafusion/core/tests/sql/expr.rs
index 3d8ccf8667..1aa0e137b7 100644
--- a/datafusion/core/tests/sql/expr.rs
+++ b/datafusion/core/tests/sql/expr.rs
@@ -19,660 +19,6 @@ use datafusion::datasource::empty::EmptyTable;
use super::*;
-#[tokio::test]
-async fn case_when() -> Result<()> {
- let ctx = create_case_context()?;
- let sql = "SELECT \
- CASE WHEN c1 = 'a' THEN 1 \
- WHEN c1 = 'b' THEN 2 \
- END \
- FROM t1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
-
"+------------------------------------------------------------------------------------+",
- "| CASE WHEN t1.c1 = Utf8(\"a\") THEN Int64(1) WHEN t1.c1 =
Utf8(\"b\") THEN Int64(2) END |",
-
"+------------------------------------------------------------------------------------+",
- "| 1
|",
- "| 2
|",
- "|
|",
- "|
|",
-
"+------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn case_when_else() -> Result<()> {
- let ctx = create_case_context()?;
- let sql = "SELECT \
- CASE WHEN c1 = 'a' THEN 1 \
- WHEN c1 = 'b' THEN 2 \
- ELSE 999 END \
- FROM t1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
-
"+----------------------------------------------------------------------------------------------------+",
- "| CASE WHEN t1.c1 = Utf8(\"a\") THEN Int64(1) WHEN t1.c1 =
Utf8(\"b\") THEN Int64(2) ELSE Int64(999) END |",
-
"+----------------------------------------------------------------------------------------------------+",
- "| 1
|",
- "| 2
|",
- "| 999
|",
- "| 999
|",
-
"+----------------------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn case_when_with_base_expr() -> Result<()> {
- let ctx = create_case_context()?;
- let sql = "SELECT \
- CASE c1 WHEN 'a' THEN 1 \
- WHEN 'b' THEN 2 \
- END \
- FROM t1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
-
"+--------------------------------------------------------------------------+",
- "| CASE t1.c1 WHEN Utf8(\"a\") THEN Int64(1) WHEN Utf8(\"b\") THEN
Int64(2) END |",
-
"+--------------------------------------------------------------------------+",
- "| 1
|",
- "| 2
|",
- "|
|",
- "|
|",
-
"+--------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn case_when_else_with_base_expr() -> Result<()> {
- let ctx = create_case_context()?;
- let sql = "SELECT \
- CASE c1 WHEN 'a' THEN 1 \
- WHEN 'b' THEN 2 \
- ELSE 999 END \
- FROM t1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
-
"+------------------------------------------------------------------------------------------+",
- "| CASE t1.c1 WHEN Utf8(\"a\") THEN Int64(1) WHEN Utf8(\"b\") THEN
Int64(2) ELSE Int64(999) END |",
-
"+------------------------------------------------------------------------------------------+",
- "| 1
|",
- "| 2
|",
- "| 999
|",
- "| 999
|",
-
"+------------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn case_when_else_with_null_contant() -> Result<()> {
- let ctx = create_case_context()?;
- let sql = "SELECT \
- CASE WHEN c1 = 'a' THEN 1 \
- WHEN NULL THEN 2 \
- ELSE 999 END \
- FROM t1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
-
"+---------------------------------------------------------------------------------------+",
- "| CASE WHEN t1.c1 = Utf8(\"a\") THEN Int64(1) WHEN NULL THEN Int64(2)
ELSE Int64(999) END |",
-
"+---------------------------------------------------------------------------------------+",
- "| 1
|",
- "| 999
|",
- "| 999
|",
- "| 999
|",
-
"+---------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT CASE WHEN NULL THEN 'foo' ELSE 'bar' END";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+------------------------------------------------------+",
- "| CASE WHEN NULL THEN Utf8(\"foo\") ELSE Utf8(\"bar\") END |",
- "+------------------------------------------------------+",
- "| bar |",
- "+------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn case_expr_with_null() -> Result<()> {
- let ctx = SessionContext::new();
- let sql = "select case when b is null then null else b end from (select
a,b from (values (1,null),(2,3)) as t (a,b)) a;";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+----------------------------------------------+",
- "| CASE WHEN a.b IS NULL THEN NULL ELSE a.b END |",
- "+----------------------------------------------+",
- "| |",
- "| 3 |",
- "+----------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "select case when b is null then null else b end from (select
a,b from (values (1,1),(2,3)) as t (a,b)) a;";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
- "+----------------------------------------------+",
- "| CASE WHEN a.b IS NULL THEN NULL ELSE a.b END |",
- "+----------------------------------------------+",
- "| 1 |",
- "| 3 |",
- "+----------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- Ok(())
-}
-
-#[tokio::test]
-async fn case_expr_with_nulls() -> Result<()> {
- let ctx = SessionContext::new();
- let sql = "select case when b is null then null when b < 3 then null when
b >=3 then b + 1 else b end from (select a,b from (values (1,null),(1,2),(2,3))
as t (a,b)) a";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
-
"+---------------------------------------------------------------------------------------------------------------------+",
- "| CASE WHEN a.b IS NULL THEN NULL WHEN a.b < Int64(3) THEN NULL WHEN
a.b >= Int64(3) THEN a.b + Int64(1) ELSE a.b END |",
-
"+---------------------------------------------------------------------------------------------------------------------+",
- "|
|",
- "|
|",
- "| 4
|",
-
"+---------------------------------------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "select case b when 1 then null when 2 then null when 3 then b +
1 else b end from (select a,b from (values (1,null),(1,2),(2,3)) as t (a,b))
a;";
- let actual = execute_to_batches(&ctx, sql).await;
-
- let expected = vec![
-
"+---------------------------------------------------------------------------------------------------------+",
- "| CASE a.b WHEN Int64(1) THEN NULL WHEN Int64(2) THEN NULL WHEN
Int64(3) THEN a.b + Int64(1) ELSE a.b END |",
-
"+---------------------------------------------------------------------------------------------------------+",
- "|
|",
- "|
|",
- "| 4
|",
-
"+---------------------------------------------------------------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- Ok(())
-}
-
-#[tokio::test]
-async fn query_not() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(false),
- None,
- Some(true),
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT NOT c1 FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------+",
- "| NOT test.c1 |",
- "+-------------+",
- "| true |",
- "| |",
- "| false |",
- "+-------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_sum_cast() {
- let ctx = SessionContext::new();
- register_aggregate_csv_by_sql(&ctx).await;
- // c8 = i32; c6 = i64
- let sql = "SELECT c8 + c6 FROM aggregate_test_100";
- // check that the physical and logical schemas are equal
- execute(&ctx, sql).await;
-}
-
-#[tokio::test]
-async fn query_is_null() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Float64,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(Float64Array::from(vec![
- Some(1.0),
- None,
- Some(f64::NAN),
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS NULL FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| test.c1 IS NULL |",
- "+-----------------+",
- "| false |",
- "| true |",
- "| false |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_not_null() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Float64,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(Float64Array::from(vec![
- Some(1.0),
- None,
- Some(f64::NAN),
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS NOT NULL FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------+",
- "| test.c1 IS NOT NULL |",
- "+---------------------+",
- "| true |",
- "| false |",
- "| true |",
- "+---------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_true() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS TRUE as t FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| t |",
- "+-------+",
- "| true |",
- "| false |",
- "| false |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_false() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS FALSE as f FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| f |",
- "+-------+",
- "| false |",
- "| true |",
- "| false |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_not_true() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS NOT TRUE as nt FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| nt |",
- "+-------+",
- "| false |",
- "| true |",
- "| true |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_not_false() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS NOT FALSE as nf FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| nf |",
- "+-------+",
- "| true |",
- "| false |",
- "| true |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_unknown() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS UNKNOWN as t FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| t |",
- "+-------+",
- "| false |",
- "| false |",
- "| true |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_is_not_unknown() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Boolean,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(BooleanArray::from(vec![
- Some(true),
- Some(false),
- None,
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT c1 IS NOT UNKNOWN as t FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| t |",
- "+-------+",
- "| true |",
- "| true |",
- "| false |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_without_from() -> Result<()> {
- // Test for SELECT <expression> without FROM.
- // Should evaluate expressions in project position.
- let ctx = SessionContext::new();
-
- let sql = "SELECT 1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----------+",
- "| Int64(1) |",
- "+----------+",
- "| 1 |",
- "+----------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT 1+2, 3/4, cos(0)";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------+---------------------+---------------+",
- "| Int64(1) + Int64(2) | Int64(3) / Int64(4) | cos(Int64(0)) |",
- "+---------------------+---------------------+---------------+",
- "| 3 | 0 | 1.0 |",
- "+---------------------+---------------------+---------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- Ok(())
-}
-
-#[tokio::test]
-async fn query_scalar_minus_array() -> Result<()> {
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Int32,
true)]));
-
- let data = RecordBatch::try_new(
- schema.clone(),
- vec![Arc::new(Int32Array::from(vec![
- Some(0),
- Some(1),
- None,
- Some(3),
- ]))],
- )?;
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", data)?;
- let sql = "SELECT 4 - c1 FROM test";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------------+",
- "| Int64(4) - test.c1 |",
- "+--------------------+",
- "| 4 |",
- "| 3 |",
- "| |",
- "| 1 |",
- "+--------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn test_string_concat_operator() -> Result<()> {
- let ctx = SessionContext::new();
- // concat 2 strings
- let sql = "SELECT 'aa' || 'b'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------------------+",
- "| Utf8(\"aa\") || Utf8(\"b\") |",
- "+-------------------------+",
- "| aab |",
- "+-------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // concat 4 strings as a string concat pipe.
- let sql = "SELECT 'aa' || 'b' || 'cc' || 'd'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----------------------------------------------------+",
- "| Utf8(\"aa\") || Utf8(\"b\") || Utf8(\"cc\") || Utf8(\"d\") |",
- "+----------------------------------------------------+",
- "| aabccd |",
- "+----------------------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // concat 2 strings and NULL, output should be NULL
- let sql = "SELECT 'aa' || NULL || 'd'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------------------------------+",
- "| Utf8(\"aa\") || NULL || Utf8(\"d\") |",
- "+---------------------------------+",
- "| |",
- "+---------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // concat 1 strings and 2 numeric
- let sql = "SELECT 'a' || 42 || 23.3";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------------------------------+",
- "| Utf8(\"a\") || Int64(42) || Float64(23.3) |",
- "+-----------------------------------------+",
- "| a4223.3 |",
- "+-----------------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn test_not_expressions() -> Result<()> {
- let ctx = SessionContext::new();
-
- let sql = "SELECT not(true), not(false)";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------------------+--------------------+",
- "| NOT Boolean(true) | NOT Boolean(false) |",
- "+-------------------+--------------------+",
- "| false | true |",
- "+-------------------+--------------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT not(1), not(0)";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+--------------+--------------+",
- "| NOT Int64(1) | NOT Int64(0) |",
- "+--------------+--------------+",
- "| -2 | -1 |",
- "+--------------+--------------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT null, not(null)";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+------+----------+",
- "| NULL | NOT NULL |",
- "+------+----------+",
- "| | |",
- "+------+----------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT NOT('hi')";
- let result = plan_and_collect(&ctx, sql).await;
- match result {
- Ok(_) => panic!("expected error"),
- Err(e) => {
- assert_contains!(e.to_string(), "Can't NOT or BITWISE_NOT
datatype: 'Utf8'");
- }
- }
- Ok(())
-}
-
-#[tokio::test]
-async fn test_negative_expressions() -> Result<()> {
- let ctx = SessionContext::new();
-
- let sql = "SELECT null, -null";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+------+----------+",
- "| NULL | (- NULL) |",
- "+------+----------+",
- "| | |",
- "+------+----------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
#[tokio::test]
async fn test_boolean_expressions() -> Result<()> {
test_expression!("true", "true");
diff --git a/datafusion/core/tests/sql/mod.rs b/datafusion/core/tests/sql/mod.rs
index 8ccde3ed9e..2a05c918b5 100644
--- a/datafusion/core/tests/sql/mod.rs
+++ b/datafusion/core/tests/sql/mod.rs
@@ -161,22 +161,6 @@ fn custom_sqrt(args: &[ColumnarValue]) ->
Result<ColumnarValue> {
}
}
-fn create_case_context() -> Result<SessionContext> {
- let ctx = SessionContext::new();
- let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Utf8,
true)]));
- let data = RecordBatch::try_new(
- schema,
- vec![Arc::new(StringArray::from(vec![
- Some("a"),
- Some("b"),
- Some("c"),
- None,
- ]))],
- )?;
- ctx.register_batch("t1", data)?;
- Ok(ctx)
-}
-
fn create_join_context(
column_left: &str,
column_right: &str,
diff --git a/datafusion/core/tests/sqllogictests/src/main.rs
b/datafusion/core/tests/sqllogictests/src/main.rs
index 93779621b7..46ea60da06 100644
--- a/datafusion/core/tests/sqllogictests/src/main.rs
+++ b/datafusion/core/tests/sqllogictests/src/main.rs
@@ -172,6 +172,10 @@ async fn context_for_test_file(relative_path: &Path) ->
SessionContext {
info!("Registering aggregate tables");
setup::register_aggregate_tables(&ctx).await;
}
+ "scalar.slt" => {
+ info!("Registering scalar tables");
+ setup::register_scalar_tables(&ctx).await;
+ }
_ => {
info!("Using default SessionContext");
}
diff --git a/datafusion/core/tests/sqllogictests/src/setup.rs
b/datafusion/core/tests/sqllogictests/src/setup.rs
index b52e06f3f7..9e3f154f59 100644
--- a/datafusion/core/tests/sqllogictests/src/setup.rs
+++ b/datafusion/core/tests/sqllogictests/src/setup.rs
@@ -159,3 +159,24 @@ async fn register_aggregate_test_100(ctx: &SessionContext)
{
.await
.unwrap();
}
+
+pub async fn register_scalar_tables(ctx: &SessionContext) {
+ register_nan_table(ctx)
+}
+
+/// Register a table with a NaN value (different than NULL, and can
+/// not be created via SQL)
+fn register_nan_table(ctx: &SessionContext) {
+ let schema = Arc::new(Schema::new(vec![Field::new("c1", DataType::Float64,
true)]));
+
+ let data = RecordBatch::try_new(
+ schema,
+ vec![Arc::new(Float64Array::from(vec![
+ Some(1.0),
+ None,
+ Some(f64::NAN),
+ ]))],
+ )
+ .unwrap();
+ ctx.register_batch("test_float", data).unwrap();
+}
diff --git a/datafusion/core/tests/sqllogictests/test_files/scalar.slt
b/datafusion/core/tests/sqllogictests/test_files/scalar.slt
index 911aeb37fc..c91ee33c25 100644
--- a/datafusion/core/tests/sqllogictests/test_files/scalar.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/scalar.slt
@@ -26,8 +26,8 @@ CREATE TABLE t1(
c INT,
d INT
) as VALUES
- (1, 100, 567, 1024),
- (2, 1000, 123, 256),
+ (1, 100, 567, 1024),
+ (2, 1000, 123, 256),
(3, 10000, 978, 2048)
;
@@ -116,3 +116,320 @@ select d << 2 from t1;
1024
4096
8192
+
+statement ok
+drop table t1
+
+
+statement ok
+create table t1(c1 varchar) as values ('a'), ('b'), ('c'), (null);
+
+# case_when()
+query I
+SELECT
+CASE WHEN c1 = 'a' THEN 1
+ WHEN c1 = 'b' THEN 2
+ END
+FROM t1
+----
+1
+2
+NULL
+NULL
+
+# case_when_else()
+query I
+SELECT
+CASE WHEN c1 = 'a' THEN 1
+ WHEN c1 = 'b' THEN 2
+ ELSE 999 END
+FROM t1
+----
+1
+2
+999
+999
+
+# case_when_with_base_expr()
+query I
+SELECT
+CASE c1 WHEN 'a' THEN 1
+ WHEN 'b' THEN 2
+ END
+FROM t1
+----
+1
+2
+NULL
+NULL
+
+# case_when_else_with_base_expr()
+query I
+SELECT
+CASE c1 WHEN 'a' THEN 1
+ WHEN 'b' THEN 2
+ ELSE 999 END
+FROM t1
+----
+1
+2
+999
+999
+
+# case_when_else_with_null_contant()
+query I
+ SELECT
+CASE WHEN c1 = 'a' THEN 1
+ WHEN NULL THEN 2
+ ELSE 999 END
+FROM t1
+----
+1
+999
+999
+999
+
+statement ok
+drop table t1
+
+
+query T
+SELECT CASE WHEN NULL THEN 'foo' ELSE 'bar' END
+----
+bar
+
+# case_expr_with_null()
+query I
+select case when b is null then null else b end from (select a,b from (values
(1,null),(2,3)) as t (a,b)) a;
+----
+NULL
+3
+
+query I
+select case when b is null then null else b end from (select a,b from (values
(1,1),(2,3)) as t (a,b)) a;
+----
+1
+3
+
+# case_expr_with_nulls()
+query I
+select case when b is null then null when b < 3 then null when b >=3 then b +
1 else b end from (select a,b from (values (1,null),(1,2),(2,3)) as t (a,b)) a
+----
+NULL
+NULL
+4
+
+query I
+select case b when 1 then null when 2 then null when 3 then b + 1 else b end
from (select a,b from (values (1,null),(1,2),(2,3)) as t (a,b)) a;
+----
+NULL
+NULL
+4
+
+# csv_query_sum_cast() {
+
+statement ok
+CREATE EXTERNAL TABLE aggregate_test_100 (
+ c1 VARCHAR NOT NULL,
+ c2 TINYINT NOT NULL,
+ c3 SMALLINT NOT NULL,
+ c4 SMALLINT,
+ c5 INT,
+ c6 BIGINT NOT NULL,
+ c7 SMALLINT NOT NULL,
+ c8 INT NOT NULL,
+ c9 BIGINT UNSIGNED 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'
+
+# c8 = i32; c6 = i64
+query TTT
+SELECT arrow_typeof(c8), arrow_typeof(c6), arrow_typeof(c8 + c6) FROM
aggregate_test_100 limit 1;
+----
+Int32 Int64 Int64
+
+
+statement ok
+drop table aggregate_test_100
+
+statement ok
+create table test_boolean(c1 boolean) as values (false), (null), (true);
+
+statement ok
+create table test_int32(c1 int) as values (0), (1), (null), (3);
+
+## Note that test_float has a NaN (which is not possible to create in SQL) so
it is registered via rust.
+
+# query_not()
+
+query B
+SELECT NOT c1 FROM test_boolean
+----
+true
+NULL
+false
+
+
+# query_is_null()
+
+# Note the last value is NaN (not NULL)
+query R
+SELECT c1 from test_float;
+----
+1
+NULL
+NaN
+
+
+query B
+SELECT c1 IS NULL FROM test_float
+----
+false
+true
+false
+
+# query_is_not_null()
+query B
+SELECT c1 IS NOT NULL FROM test_float
+----
+true
+false
+true
+
+
+# query_is_true()
+query B
+SELECT c1 IS TRUE as t FROM test_boolean
+----
+false
+false
+true
+
+# query_is_false()
+query B
+SELECT c1 IS FALSE as f FROM test_boolean
+----
+true
+false
+false
+
+# query_is_not_true()
+
+query B
+SELECT c1 IS NOT TRUE as nt FROM test_boolean
+----
+true
+true
+false
+
+# query_is_not_false()
+query B
+SELECT c1 IS NOT FALSE as nf FROM test_boolean
+----
+false
+true
+true
+
+
+# query_is_unknown()
+query B
+SELECT c1 IS UNKNOWN as t FROM test_boolean
+----
+false
+true
+false
+
+# query_is_not_unknown()
+query B
+SELECT c1 IS NOT UNKNOWN as t FROM test_boolean
+----
+true
+false
+true
+
+
+# query_without_from()
+
+query I
+SELECT 1
+----
+1
+
+query IIR
+SELECT 1+2, 3/4, cos(0)
+----
+3 0 1
+
+
+# query_scalar_minus_array()
+query I
+SELECT 4 - c1 FROM test_int32
+----
+4
+3
+NULL
+1
+
+# test_string_concat_operator()
+# concat 2 strings
+query T
+SELECT 'aa' || 'b'
+----
+aab
+
+# concat 4 strings as a string concat pipe.
+query T
+SELECT 'aa' || 'b' || 'cc' || 'd'
+----
+aabccd
+
+# concat 2 strings and NULL, output should be NULL
+query T
+SELECT 'aa' || NULL || 'd'
+----
+NULL
+
+# concat 1 strings and 2 numeric
+query T
+SELECT 'a' || 42 || 23.3
+----
+a4223.3
+
+# test_not_expressions()
+
+query BB
+SELECT not(true), not(false)
+----
+false true
+
+query BB
+SELECT not(1), not(0)
+----
+-2 -1
+
+query ?B
+SELECT null, not(null)
+----
+NULL NULL
+
+query error DataFusion error: Error during planning: NOT or BITWISE_NOT not
supported for datatype: 'Utf8'
+SELECT NOT('hi')
+
+# test_negative_expressions()
+
+query ??
+SELECT null, -null
+----
+NULL NULL
+
+statement ok
+drop table test_boolean
+
+statement ok
+drop table test_int32
diff --git a/datafusion/physical-expr/src/expressions/not.rs
b/datafusion/physical-expr/src/expressions/not.rs
index ee44b91c9d..b27dfdfef8 100644
--- a/datafusion/physical-expr/src/expressions/not.rs
+++ b/datafusion/physical-expr/src/expressions/not.rs
@@ -77,8 +77,8 @@ impl PhysicalExpr for NotExpr {
DataType::Int32 => Ok(DataType::Int32),
DataType::Int64 => Ok(DataType::Int64),
DataType::Null => Ok(DataType::Null),
- _ => Err(DataFusionError::Internal(format!(
- "Can't NOT or BITWISE_NOT datatype: '{:?}'",
+ _ => Err(DataFusionError::Plan(format!(
+ "NOT or BITWISE_NOT not supported for datatype: '{:?}'",
data_type
))),
}
