You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/06/09 12:41:22 UTC
(datafusion) branch gh-readonly-queue/main/pr-22803-ea5d448a68c060367d75166d3915833034a64664 created (now a0e05f65a2)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-22803-ea5d448a68c060367d75166d3915833034a64664
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at a0e05f65a2 Add smj SQL benchmark (#22803)
This branch includes the following new commits:
new a0e05f65a2 Add smj SQL benchmark (#22803)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: Add smj SQL benchmark (#22803)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-22803-ea5d448a68c060367d75166d3915833034a64664
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit a0e05f65a2293aa2f253e0cae67d6cea51a528bd
Author: Bruce Ritchie <br...@gmail.com>
AuthorDate: Tue Jun 9 08:33:50 2026 -0400
Add smj SQL benchmark (#22803)
## Which issue does this PR close?
Part of #21706
## Rationale for this change
Continue work on sql benchmark migration.
## What changes are included in this PR?
smj sql benchmark
## Are these changes tested?
Yes
`BENCH_NAME=smj cargo bench --bench sql`
## Are there any user-facing changes?
No
---
.../sql_benchmarks/smj/benchmarks/q01.benchmark | 21 ++++++++++++++
.../sql_benchmarks/smj/benchmarks/q02.benchmark | 25 +++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q03.benchmark | 25 +++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q04.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q05.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q06.benchmark | 25 +++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q07.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q08.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q09.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q10.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q11.benchmark | 31 +++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q12.benchmark | 31 +++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q13.benchmark | 31 +++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q14.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q15.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q16.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q17.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q18.benchmark | 31 +++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q19.benchmark | 29 ++++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q20.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q21.benchmark | 25 +++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q22.benchmark | 25 +++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q23.benchmark | 26 ++++++++++++++++++
.../sql_benchmarks/smj/benchmarks/q24.benchmark | 32 ++++++++++++++++++++++
24 files changed, 658 insertions(+)
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q01.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q01.benchmark
new file mode 100644
index 0000000000..f1d44a6fb3
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q01.benchmark
@@ -0,0 +1,21 @@
+name Q01
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q1: INNER 1M x 1M | 1:1
+WITH t1_sorted AS (
+ SELECT value as key FROM range(1000000) ORDER BY value
+ ),
+ t2_sorted AS (
+SELECT value as key FROM range(1000000) ORDER BY value
+ )
+SELECT t1_sorted.key as k1, t2_sorted.key as k2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q02.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q02.benchmark
new file mode 100644
index 0000000000..cd30f53256
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q02.benchmark
@@ -0,0 +1,25 @@
+name Q02
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q2: INNER 1M x 10M | 1:10
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q03.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q03.benchmark
new file mode 100644
index 0000000000..02bab2a685
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q03.benchmark
@@ -0,0 +1,25 @@
+name Q03
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q3: INNER 1M x 1M | 1:100
+WITH t1_sorted AS (
+ SELECT value % 10000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 10000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q04.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q04.benchmark
new file mode 100644
index 0000000000..2442906a3f
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q04.benchmark
@@ -0,0 +1,26 @@
+name Q04
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q4: INNER 1M x 10M | 1:10 | 1%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+WHERE t2_sorted.data % 100 = 0
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q05.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q05.benchmark
new file mode 100644
index 0000000000..1735d0e0e6
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q05.benchmark
@@ -0,0 +1,26 @@
+name Q05
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q5: INNER 1M x 1M | 1:100 | 10%
+WITH t1_sorted AS (
+ SELECT value % 10000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 10000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+WHERE t1_sorted.data <> t2_sorted.data AND t2_sorted.data % 10 = 0
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q06.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q06.benchmark
new file mode 100644
index 0000000000..8c18ee164f
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q06.benchmark
@@ -0,0 +1,25 @@
+name Q06
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q6: LEFT 1M x 10M | 1:10
+WITH t1_sorted AS (
+ SELECT value % 105000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted LEFT JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q07.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q07.benchmark
new file mode 100644
index 0000000000..20619b0948
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q07.benchmark
@@ -0,0 +1,26 @@
+name Q07
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q7: LEFT 1M x 10M | 1:10 | 50%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted LEFT JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+WHERE t2_sorted.data IS NULL OR t2_sorted.data % 2 = 0
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q08.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q08.benchmark
new file mode 100644
index 0000000000..7597f2012a
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q08.benchmark
@@ -0,0 +1,26 @@
+name Q08
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q8: FULL 1M x 1M | 1:10
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 125000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key as k1, t1_sorted.data as d1,
+ t2_sorted.key as k2, t2_sorted.data as d2
+FROM t1_sorted FULL JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q09.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q09.benchmark
new file mode 100644
index 0000000000..ca0565c6a6
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q09.benchmark
@@ -0,0 +1,29 @@
+name Q09
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q9: FULL 1M x 10M | 1:10 | 10%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key as k1, t1_sorted.data as d1,
+ t2_sorted.key as k2, t2_sorted.data as d2
+FROM t1_sorted FULL JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+WHERE (t1_sorted.data IS NULL OR t2_sorted.data IS NULL
+ OR t1_sorted.data <> t2_sorted.data)
+ AND (t1_sorted.data IS NULL OR t1_sorted.data % 10 = 0)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q10.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q10.benchmark
new file mode 100644
index 0000000000..cbba0610c5
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q10.benchmark
@@ -0,0 +1,29 @@
+name Q10
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q10: LEFT SEMI 1M x 10M | 1:10
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key
+FROM range(10000000)
+ORDER BY key
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q11.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q11.benchmark
new file mode 100644
index 0000000000..2431b2646e
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q11.benchmark
@@ -0,0 +1,31 @@
+name Q11
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q11: LEFT SEMI 1M x 10M | 1:10 | 1%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+ AND t2_sorted.data <> t1_sorted.data
+ AND t2_sorted.data % 100 = 0
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q12.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q12.benchmark
new file mode 100644
index 0000000000..79e0a8e8a5
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q12.benchmark
@@ -0,0 +1,31 @@
+name Q12
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q12: LEFT SEMI 1M x 10M | 1:10 | 50%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+ AND t2_sorted.data <> t1_sorted.data
+ AND t2_sorted.data % 2 = 0
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q13.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q13.benchmark
new file mode 100644
index 0000000000..7e13e68743
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q13.benchmark
@@ -0,0 +1,31 @@
+name Q13
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q13: LEFT SEMI 1M x 10M | 1:10 | 90%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+ AND t2_sorted.data <> t1_sorted.data
+ AND t2_sorted.data % 10 <> 0
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q14.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q14.benchmark
new file mode 100644
index 0000000000..a56a0d5863
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q14.benchmark
@@ -0,0 +1,29 @@
+name Q14
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q14: LEFT ANTI 1M x 10M | 1:10
+WITH t1_sorted AS (
+ SELECT value % 105000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key
+FROM range(10000000)
+ORDER BY key
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE NOT EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q15.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q15.benchmark
new file mode 100644
index 0000000000..bd64d74422
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q15.benchmark
@@ -0,0 +1,29 @@
+name Q15
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q15: LEFT ANTI 1M x 10M | 1:10 | partial match
+WITH t1_sorted AS (
+ SELECT value % 120000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key
+FROM range(10000000)
+ORDER BY key
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE NOT EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q16.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q16.benchmark
new file mode 100644
index 0000000000..282d6374eb
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q16.benchmark
@@ -0,0 +1,29 @@
+name Q16
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q16: LEFT ANTI 1M x 1M | 1:1 | stress
+WITH t1_sorted AS (
+ SELECT value % 110000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key
+FROM range(1000000)
+ORDER BY key
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE NOT EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q17.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q17.benchmark
new file mode 100644
index 0000000000..7f1c9a0ae2
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q17.benchmark
@@ -0,0 +1,26 @@
+name Q17
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q17: INNER 1M x 50M | 1:50 | 5%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(50000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+WHERE t2_sorted.data <> t1_sorted.data AND t2_sorted.data % 20 = 0
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q18.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q18.benchmark
new file mode 100644
index 0000000000..fac7edd19b
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q18.benchmark
@@ -0,0 +1,31 @@
+name Q18
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q18: LEFT SEMI 1M x 50M | 1:50 | 2%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(50000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+ AND t2_sorted.data <> t1_sorted.data
+ AND t2_sorted.data % 50 = 0
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q19.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q19.benchmark
new file mode 100644
index 0000000000..a867bb6bff
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q19.benchmark
@@ -0,0 +1,29 @@
+name Q19
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q19: LEFT ANTI 1M x 50M | 1:50 | partial match
+WITH t1_sorted AS (
+ SELECT value % 150000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key
+FROM range(50000000)
+ORDER BY key
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE NOT EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q20.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q20.benchmark
new file mode 100644
index 0000000000..317c6290f7
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q20.benchmark
@@ -0,0 +1,26 @@
+name Q20
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q20: INNER 1M x 10M | 1:100 + GROUP BY
+WITH t1_sorted AS (
+ SELECT value % 10000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 10000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, count(*) as cnt
+FROM t1_sorted JOIN t2_sorted ON t1_sorted.key = t2_sorted.key
+GROUP BY t1_sorted.key
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q21.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q21.benchmark
new file mode 100644
index 0000000000..3fe460ea00
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q21.benchmark
@@ -0,0 +1,25 @@
+name Q21
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q21: INNER 10M x 10M | unique keys (1:1) | 50% join filter
+WITH t1_sorted AS (
+ SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ ),
+ t2_sorted AS (
+SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted JOIN t2_sorted
+ ON t1_sorted.key = t2_sorted.key
+ AND t1_sorted.data + t2_sorted.data < 10000000
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q22.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q22.benchmark
new file mode 100644
index 0000000000..fe0063de57
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q22.benchmark
@@ -0,0 +1,25 @@
+name Q22
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q22: LEFT 10M x 10M | unique keys (1:1) | 50% join filter
+WITH t1_sorted AS (
+ SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ ),
+ t2_sorted AS (
+SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ )
+SELECT t1_sorted.key, t1_sorted.data as d1, t2_sorted.data as d2
+FROM t1_sorted LEFT JOIN t2_sorted
+ ON t1_sorted.key = t2_sorted.key
+ AND t1_sorted.data + t2_sorted.data < 10000000
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q23.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q23.benchmark
new file mode 100644
index 0000000000..592effd993
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q23.benchmark
@@ -0,0 +1,26 @@
+name Q23
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q23: FULL 10M x 10M | unique keys (1:1) | 50% join filter
+WITH t1_sorted AS (
+ SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ ),
+ t2_sorted AS (
+SELECT value as key, value as data
+FROM range(10000000) ORDER BY value
+ )
+SELECT t1_sorted.key as k1, t1_sorted.data as d1,
+ t2_sorted.key as k2, t2_sorted.data as d2
+FROM t1_sorted FULL JOIN t2_sorted
+ ON t1_sorted.key = t2_sorted.key
+ AND t1_sorted.data + t2_sorted.data < 10000000
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
diff --git a/benchmarks/sql_benchmarks/smj/benchmarks/q24.benchmark b/benchmarks/sql_benchmarks/smj/benchmarks/q24.benchmark
new file mode 100644
index 0000000000..893eb1fb78
--- /dev/null
+++ b/benchmarks/sql_benchmarks/smj/benchmarks/q24.benchmark
@@ -0,0 +1,32 @@
+name Q24
+group smj
+
+init
+set datafusion.optimizer.prefer_hash_join=false;
+
+expect_plan SortMergeJoinExec
+
+run
+-- Q24: LEFT MARK 1M x 10M | 1:10 | 1%
+WITH t1_sorted AS (
+ SELECT value % 100000 as key, value as data
+FROM range(1000000)
+ORDER BY key, data
+ ),
+ t2_sorted AS (
+SELECT value % 100000 as key, value as data
+FROM range(10000000)
+ORDER BY key, data
+ )
+SELECT t1_sorted.key, t1_sorted.data
+FROM t1_sorted
+WHERE t1_sorted.data < 0
+ OR EXISTS (
+ SELECT 1 FROM t2_sorted
+ WHERE t2_sorted.key = t1_sorted.key
+ AND t2_sorted.data <> t1_sorted.data
+ AND t2_sorted.data % 100 = 0
+)
+
+cleanup
+reset datafusion.optimizer.prefer_hash_join;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org