This is an automated email from the ASF dual-hosted git repository.
avantgardner 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 05d8a2527 minor: port predicates tests to sqllogictests (#5374)
05d8a2527 is described below
commit 05d8a25278f5b8e29c842a88ee7652c0e887e53a
Author: jakevin <[email protected]>
AuthorDate: Sat Feb 25 03:29:26 2023 +0800
minor: port predicates tests to sqllogictests (#5374)
---
datafusion/core/tests/sql/predicates.rs | 488 ---------------------
.../tests/sqllogictests/test_files/predicates.slt | 279 ++++++++++++
2 files changed, 279 insertions(+), 488 deletions(-)
diff --git a/datafusion/core/tests/sql/predicates.rs
b/datafusion/core/tests/sql/predicates.rs
index 1e8888ce4..cd4e02111 100644
--- a/datafusion/core/tests/sql/predicates.rs
+++ b/datafusion/core/tests/sql/predicates.rs
@@ -17,286 +17,6 @@
use super::*;
-#[tokio::test]
-async fn csv_query_with_predicate() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT c1, c12 FROM aggregate_test_100 WHERE c12 > 0.376 AND
c12 < 0.4";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----+---------------------+",
- "| c1 | c12 |",
- "+----+---------------------+",
- "| e | 0.39144436569161134 |",
- "| d | 0.38870280983958583 |",
- "+----+---------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_with_negative_predicate() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT c1, c4 FROM aggregate_test_100 WHERE c3 < -55 AND -c4 >
30000";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----+--------+",
- "| c1 | c4 |",
- "+----+--------+",
- "| e | -31500 |",
- "| c | -30187 |",
- "+----+--------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_with_negated_predicate() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT COUNT(1) FROM aggregate_test_100 WHERE NOT(c1 != 'a')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| COUNT(Int64(1)) |",
- "+-----------------+",
- "| 21 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_with_is_not_null_predicate() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT COUNT(1) FROM aggregate_test_100 WHERE c1 IS NOT NULL";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| COUNT(Int64(1)) |",
- "+-----------------+",
- "| 100 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_query_with_is_null_predicate() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT COUNT(1) FROM aggregate_test_100 WHERE c1 IS NULL";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| COUNT(Int64(1)) |",
- "+-----------------+",
- "| 0 |",
- "+-----------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn query_where_neg_num() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv_by_sql(&ctx).await;
-
- // Negative numbers do not parse correctly as of Arrow 2.0.0
- let sql = "select c7, c8 from aggregate_test_100 where c7 >= -2 and c7 <
10";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+----+-------+",
- "| c7 | c8 |",
- "+----+-------+",
- "| 7 | 45465 |",
- "| 5 | 40622 |",
- "| 0 | 61069 |",
- "| 2 | 20120 |",
- "| 4 | 39363 |",
- "+----+-------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- // Also check floating point neg numbers
- let sql = "select c7, c8 from aggregate_test_100 where c7 >= -2.9 and c7 <
10";
- let actual = execute_to_batches(&ctx, sql).await;
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn like() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv_by_sql(&ctx).await;
- let sql = "SELECT COUNT(c1) FROM aggregate_test_100 WHERE c13 LIKE '%FB%'";
- // check that the physical and logical schemas are equal
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+------------------------------+",
- "| COUNT(aggregate_test_100.c1) |",
- "+------------------------------+",
- "| 1 |",
- "+------------------------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_between_expr() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT c4 FROM aggregate_test_100 WHERE c12 BETWEEN 0.995 AND
1.0";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c4 |",
- "+-------+",
- "| 10837 |",
- "+-------+",
- ];
- assert_batches_sorted_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_between_expr_negated() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT c4 FROM aggregate_test_100 WHERE c12 NOT BETWEEN 0 AND
0.995";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c4 |",
- "+-------+",
- "| 10837 |",
- "+-------+",
- ];
- assert_batches_sorted_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn like_on_strings() -> Result<()> {
- let input = vec![Some("foo"), Some("bar"), None, Some("fazzz")]
- .into_iter()
- .collect::<StringArray>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 LIKE '%a%'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| bar |",
- "| fazzz |",
- "+-------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn like_on_string_dictionaries() -> Result<()> {
- let input = vec![Some("foo"), Some("bar"), None, Some("fazzz")]
- .into_iter()
- .collect::<DictionaryArray<Int32Type>>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 LIKE '%a%'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| bar |",
- "| fazzz |",
- "+-------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn test_regexp_is_match() -> Result<()> {
- let input = vec![Some("foo"), Some("Barrr"), Some("Bazzz"), Some("ZZZZZ")]
- .into_iter()
- .collect::<StringArray>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 ~ 'z'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| Bazzz |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 ~* 'z'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| Bazzz |",
- "| ZZZZZ |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 !~ 'z'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| foo |",
- "| Barrr |",
- "| ZZZZZ |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 !~* 'z'";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| foo |",
- "| Barrr |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
#[tokio::test]
async fn string_coercion() -> Result<()> {
let vendor_id_utf8: StringArray =
@@ -340,214 +60,6 @@ async fn string_coercion() -> Result<()> {
Ok(())
}
-#[tokio::test]
-async fn except_with_null_not_equal() {
- let sql = "SELECT * FROM (SELECT null AS id1, 1 AS id2) t1
- EXCEPT SELECT * FROM (SELECT null AS id1, 2 AS id2) t2";
-
- let expected = vec![
- "+-----+-----+",
- "| id1 | id2 |",
- "+-----+-----+",
- "| | 1 |",
- "+-----+-----+",
- ];
-
- let ctx = create_join_context_qualified("t1", "t2").unwrap();
- let actual = execute_to_batches(&ctx, sql).await;
-
- assert_batches_eq!(expected, &actual);
-}
-
-#[tokio::test]
-async fn except_with_null_equal() {
- let sql = "SELECT * FROM (SELECT null AS id1, 1 AS id2) t1
- EXCEPT SELECT * FROM (SELECT null AS id1, 1 AS id2) t2";
-
- let expected = vec!["++", "++"];
- let ctx = create_join_context_qualified("t1", "t2").unwrap();
- let actual = execute_to_batches(&ctx, sql).await;
-
- assert_batches_eq!(expected, &actual);
-}
-
-#[tokio::test]
-async fn test_expect_all() -> Result<()> {
- let ctx = SessionContext::new();
- register_alltypes_parquet(&ctx).await;
- // execute the query
- let sql = "SELECT int_col, double_col FROM alltypes_plain where int_col >
0 EXCEPT ALL SELECT int_col, double_col FROM alltypes_plain where int_col < 1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------+------------+",
- "| int_col | double_col |",
- "+---------+------------+",
- "| 1 | 10.1 |",
- "| 1 | 10.1 |",
- "| 1 | 10.1 |",
- "| 1 | 10.1 |",
- "+---------+------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn test_expect_distinct() -> Result<()> {
- let ctx = SessionContext::new();
- register_alltypes_parquet(&ctx).await;
- // execute the query
- let sql = "SELECT int_col, double_col FROM alltypes_plain where int_col >
0 EXCEPT SELECT int_col, double_col FROM alltypes_plain where int_col < 1";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+---------+------------+",
- "| int_col | double_col |",
- "+---------+------------+",
- "| 1 | 10.1 |",
- "+---------+------------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn csv_in_set_test() -> Result<()> {
- let ctx = SessionContext::new();
- register_aggregate_csv(&ctx).await?;
- let sql = "SELECT count(*) FROM aggregate_test_100 WHERE c7 in
('25','155','204','77','208','67','139','191','26','7','202','113','129','197','249','146','129','220','154','163','220','19','71','243','150','231','196','170','99','255');";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----------------+",
- "| COUNT(UInt8(1)) |",
- "+-----------------+",
- "| 36 |",
- "+-----------------+",
- ];
- assert_batches_sorted_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn in_list_string_dictionaries() -> Result<()> {
- // let input = vec![Some("foo"), Some("bar"), None, Some("fazzz")]
- let input = vec![Some("foo"), Some("bar"), Some("fazzz")]
- .into_iter()
- .collect::<DictionaryArray<Int32Type>>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 IN ('Bar')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["++", "++"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["+-----+", "| c1 |", "+-----+", "| foo |", "+-----+"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('bar', 'foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----+", "| c1 |", "+-----+", "| foo |", "| bar |", "+-----+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('Bar', 'foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["+-----+", "| c1 |", "+-----+", "| foo |", "+-----+"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('foo', 'Bar', 'fazzz')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| foo |",
- "| fazzz |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn in_list_string_dictionaries_with_null() -> Result<()> {
- let input = vec![Some("foo"), Some("bar"), None, Some("fazzz")]
- .into_iter()
- .collect::<DictionaryArray<Int32Type>>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 IN ('Bar')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["++", "++"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["+-----+", "| c1 |", "+-----+", "| foo |", "+-----+"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('bar', 'foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-----+", "| c1 |", "+-----+", "| foo |", "| bar |", "+-----+",
- ];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('Bar', 'foo')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec!["+-----+", "| c1 |", "+-----+", "| foo |", "+-----+"];
- assert_batches_eq!(expected, &actual);
-
- let sql = "SELECT * FROM test WHERE c1 IN ('foo', 'Bar', 'fazzz')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| foo |",
- "| fazzz |",
- "+-------+",
- ];
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
-#[tokio::test]
-async fn in_set_string_dictionaries() -> Result<()> {
- let input = vec![Some("foo"), Some("bar"), None, Some("fazzz")]
- .into_iter()
- .collect::<DictionaryArray<Int32Type>>();
-
- let batch = RecordBatch::try_from_iter(vec![("c1", Arc::new(input) as
_)]).unwrap();
-
- let ctx = SessionContext::new();
- ctx.register_batch("test", batch)?;
-
- let sql = "SELECT * FROM test WHERE c1 IN ('foo', 'Bar', 'fazzz')";
- let actual = execute_to_batches(&ctx, sql).await;
- let expected = vec![
- "+-------+",
- "| c1 |",
- "+-------+",
- "| foo |",
- "| fazzz |",
- "+-------+",
- ];
-
- assert_batches_eq!(expected, &actual);
- Ok(())
-}
-
#[tokio::test]
// Test issue: https://github.com/apache/arrow-datafusion/issues/3635
async fn multiple_or_predicates() -> Result<()> {
diff --git a/datafusion/core/tests/sqllogictests/test_files/predicates.slt
b/datafusion/core/tests/sqllogictests/test_files/predicates.slt
new file mode 100644
index 000000000..952a36964
--- /dev/null
+++ b/datafusion/core/tests/sqllogictests/test_files/predicates.slt
@@ -0,0 +1,279 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+##########
+## Limit Tests
+##########
+
+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'
+
+statement ok
+CREATE EXTERNAL TABLE alltypes_plain STORED AS PARQUET LOCATION
'../../parquet-testing/data/alltypes_plain.parquet';
+
+
+# async fn csv_query_with_predicate()
+query TR
+SELECT c1, c12 FROM aggregate_test_100 WHERE c12 > 0.376 AND c12 < 0.4
+----
+e 0.391444365692
+d 0.38870280984
+
+# async fn csv_query_with_negative_predicate
+query TI
+SELECT c1, c4 FROM aggregate_test_100 WHERE c3 < -55 AND -c4 > 30000
+----
+e -31500
+c -30187
+
+# async fn csv_query_with_negated_predicate()
+query I
+SELECT COUNT(1) FROM aggregate_test_100 WHERE NOT(c1 != 'a')
+----
+21
+
+# async fn csv_query_with_is_not_null_predicate
+query I
+SELECT COUNT(1) FROM aggregate_test_100 WHERE c1 IS NOT NULL
+----
+100
+
+# async fn csv_query_with_is_null_predicate
+query I
+SELECT COUNT(1) FROM aggregate_test_100 WHERE c1 IS NULL
+----
+0
+
+# async fn query_where_neg_num
+query II
+select c7, c8 from aggregate_test_100 where c7 >= -2 and c7 < 10
+----
+7 45465
+5 40622
+0 61069
+2 20120
+4 39363
+
+query II
+select c7, c8 from aggregate_test_100 where c7 >= -2.9 and c7 < 10
+----
+7 45465
+5 40622
+0 61069
+2 20120
+4 39363
+
+# async fn like
+query I
+SELECT COUNT(c1) FROM aggregate_test_100 WHERE c13 LIKE '%FB%'
+----
+1
+
+# async fn csv_between_expr
+query I
+SELECT c4 FROM aggregate_test_100 WHERE c12 BETWEEN 0.995 AND 1.0
+----
+10837
+
+# async fn csv_between_expr_negated
+query I
+SELECT c4 FROM aggregate_test_100 WHERE c12 NOT BETWEEN 0 AND 0.995
+----
+10837
+
+# async fn csv_in_set_test
+query I
+SELECT count(*) FROM aggregate_test_100 WHERE c7 in
('25','155','204','77','208','67','139','191','26','7','202','113','129','197','249','146','129','220','154','163','220','19','71','243','150','231','196','170','99','255')
+----
+36
+
+# async fn except_with_null_not_equal
+query ?I
+SELECT * FROM (SELECT null AS id1, 1 AS id2) t1
+EXCEPT
+SELECT * FROM (SELECT null AS id1, 2 AS id2) t2
+----
+NULL 1
+
+# async fn except_with_null_equal
+query ?I
+SELECT * FROM (SELECT null AS id1, 1 AS id2) t1
+EXCEPT
+SELECT * FROM (SELECT null AS id1, 1 AS id2) t2
+----
+
+statement ok
+CREATE TABLE IF NOT EXISTS test AS VALUES('foo'),('bar'),(NULL),('fazzz');
+
+# async fn like_on_strings
+query T
+SELECT * FROM test WHERE column1 LIKE '%a%'
+----
+bar
+fazzz
+
+# async fn like_on_string_dictionaries
+query T
+SELECT * FROM test WHERE column1 LIKE '%a%'
+----
+bar
+fazzz
+
+# async fn in_list_string_dictionaries_with_null
+query T
+SELECT * FROM test WHERE column1 IN ('Bar')
+----
+
+query T
+SELECT * FROM test WHERE column1 IN ('foo')
+----
+foo
+
+query T
+SELECT * FROM test WHERE column1 IN ('bar', 'foo')
+----
+foo
+bar
+
+query T
+SELECT * FROM test WHERE column1 IN ('Bar', 'foo')
+----
+foo
+
+query T
+SELECT * FROM test WHERE column1 IN ('foo', 'Bar', 'fazzz')
+----
+foo
+fazzz
+
+
+# async fn in_set_string_dictionaries
+query T
+SELECT * FROM test WHERE column1 IN ('foo', 'Bar', 'fazzz')
+----
+foo
+fazzz
+
+statement ok
+DROP TABLE test;
+
+statement ok
+CREATE TABLE IF NOT EXISTS test AS VALUES('foo'),('Barrr'),('Bazzz'),('ZZZZZ');
+
+# async fn test_regexp_is_match
+query T
+SELECT * FROM test WHERE column1 ~ 'z'
+----
+Bazzz
+
+query T
+SELECT * FROM test WHERE column1 ~* 'z'
+----
+Bazzz
+ZZZZZ
+
+query T
+SELECT * FROM test WHERE column1 !~ 'z'
+----
+foo
+Barrr
+ZZZZZ
+
+query T
+SELECT * FROM test WHERE column1 !~* 'z'
+----
+foo
+Barrr
+
+statement ok
+DROP TABLE test;
+
+statement ok
+CREATE TABLE IF NOT EXISTS test AS VALUES('foo'),('bar'),('fazzz');
+
+# async fn in_list_string_dictionaries
+query T
+SELECT * FROM test WHERE column1 IN ('Bar')
+----
+
+query T
+SELECT * FROM test WHERE column1 IN ('foo')
+----
+foo
+
+query T
+SELECT * FROM test WHERE column1 IN ('bar', 'foo')
+----
+foo
+bar
+
+query T
+SELECT * FROM test WHERE column1 IN ('Bar', 'foo')
+----
+foo
+
+query T
+SELECT * FROM test WHERE column1 IN ('foo', 'Bar', 'fazzz')
+----
+foo
+fazzz
+
+# async fn test_expect_all
+query IR
+SELECT int_col, double_col FROM alltypes_plain where int_col > 0 EXCEPT ALL
SELECT int_col, double_col FROM alltypes_plain where int_col < 1
+----
+1 10.1
+1 10.1
+1 10.1
+1 10.1
+
+# async fn test_expect_distinct
+query IR
+SELECT int_col, double_col FROM alltypes_plain where int_col > 0 EXCEPT SELECT
int_col, double_col FROM alltypes_plain where int_col < 1
+----
+1 10.1
+
+
+########
+# Clean up after the test
+########
+
+statement ok
+drop table aggregate_test_100;
+
+statement ok
+drop table alltypes_plain;
+
+statement ok
+DROP TABLE test;
