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/09/18 13:24:07 UTC
(datafusion) branch gh-readonly-queue/main/pr-25346-0e292dcbfdabc41a1297c57df9a1e0368b53e64d created (now 5a2000a2ff)
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-25346-0e292dcbfdabc41a1297c57df9a1e0368b53e64d
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 5a2000a2ff bench: add projection_subquery SQL benchmark suite (#25346)
This branch includes the following new commits:
new 5a2000a2ff bench: add projection_subquery SQL benchmark suite (#25346)
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: bench: add projection_subquery SQL benchmark suite (#25346)
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-25346-0e292dcbfdabc41a1297c57df9a1e0368b53e64d
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 5a2000a2ffecfad3e9f270c0840746a38f634ea6
Author: Adrian Garcia Badaracco <17...@users.noreply.github.com>
AuthorDate: Fri Sep 18 13:23:40 2026 +0000
bench: add projection_subquery SQL benchmark suite (#25346)
## Which issue does this PR close?
- This PR closes no issue. It is related to
https://github.com/apache/datafusion/issues/25341 and it prepares the
A/B measurement for https://github.com/apache/datafusion/pull/25338.
## Rationale for this change
An `IN`, `NOT IN` or `EXISTS` subquery in the `SELECT` list is much
slower than the same subquery in a `WHERE` clause.
A projected `IN` must return `NULL`, and not `false`, when there is no
match and the inner side holds a `NULL`.
The decorrelation therefore turns one projected `IN` into more than one
mark join.
A mark join with no hashable join predicate runs as a nested-loop join,
so the cost grows with the outer row count times the inner row count.
There is no benchmark suite that measures this shape today.
The benchmark bot compares a pull request against its merge base.
It can only report a change if the suite is present on both sides.
The suite must therefore land on `main` first, before the fix is
measured with it.
## What changes are included in this PR?
- A new declarative SQL benchmark suite, `projection_subquery`, with
seven queries. Each query is one shape:
- `q01_in_bare`: bare uncorrelated `IN`.
- `q02_in_coalesce`: the same `IN` wrapped in `COALESCE`, which is the
common way to use the result.
- `q03_in_correlated_eq`: `IN` correlated on an equality.
- `q04_in_two_columns`: two independent `IN` subqueries in one `SELECT`
list.
- `q05_not_in_bare`: `NOT IN`.
- `q06_exists_correlated`: correlated `EXISTS`, which has no `NULL`
result and needs only one mark join.
- `q07_in_correlated_residual`: `IN` correlated on `<`. There is no
equality to hash on, so this query keeps the nested-loop plan. It is the
control: its time must not change when the hashable shapes get faster.
- Two small integer tables built inline by the suite load SQL, so the
suite needs no data step. Every 97th inner key is `NULL`, which keeps
three-valued logic in play. Set `PSQ_ROWS` to change the row count in
each table. The default is 30000.
- An aggregate over the projected boolean in every query, so the result
is two numbers and the measured cost is the plan and not the size of the
output.
- Checked-in result files, so `--result-mode validate` also proves that
a change to the decorrelation still returns the same rows. The counts
hold for the default `PSQ_ROWS`.
- `bench.sh` wiring: `./benchmarks/bench.sh data projection_subquery`
reports that there is no external data, and `./benchmarks/bench.sh run
projection_subquery` runs the suite.
- Documentation in `benchmarks/README.md` and in the suite table of
`benchmarks/sql_benchmarks/README.md`.
## What is the testing strategy for this PR?
This PR adds no product code, so there are no unit tests. It was
verified by running the suite.
`./benchmarks/bench.sh data projection_subquery` prints the no-data
message.
`./benchmarks/bench.sh run projection_subquery` builds `cargo bench
--bench sql` and completes with a time for each of the seven queries.
`--result-mode validate` passes on `main` and on the branch of
https://github.com/apache/datafusion/pull/25338, which shows that both
sides return the same rows.
Median of five iterations at the default `PSQ_ROWS` of 30000, on one
machine:
| Query | main | With https://github.com/apache/datafusion/pull/25338 |
| --- | --- | --- |
| `q01_in_bare` | 341 ms | 0.8 ms |
| `q02_in_coalesce` | 690 ms | 1.1 ms |
| `q03_in_correlated_eq` | 2.6 ms | 0.9 ms |
| `q04_in_two_columns` | 527 ms | 1.2 ms |
| `q05_not_in_bare` | 349 ms | 0.6 ms |
| `q06_exists_correlated` | 0.6 ms | 0.4 ms |
| `q07_in_correlated_residual` (control) | 66 ms | 63 ms |
The control query is unchanged, as expected, because it has no equality
to hash on.
## Are there any user-facing changes?
No. This PR adds benchmarks only.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-authored-by: Claude Fable 5.1 <no...@anthropic.com>
---
benchmarks/README.md | 26 +++++++++++++
benchmarks/bench.sh | 29 ++++++++++++++
benchmarks/sql_benchmarks/README.md | 2 +
.../projection_subquery/benchmarks/q01.benchmark | 3 ++
.../projection_subquery/benchmarks/q02.benchmark | 3 ++
.../projection_subquery/benchmarks/q03.benchmark | 3 ++
.../projection_subquery/benchmarks/q04.benchmark | 3 ++
.../projection_subquery/benchmarks/q05.benchmark | 3 ++
.../projection_subquery/benchmarks/q06.benchmark | 3 ++
.../projection_subquery/benchmarks/q07.benchmark | 3 ++
.../projection_subquery/init/cleanup.sql | 2 +
.../projection_subquery/load/tables.sql | 25 ++++++++++++
.../projection_subquery.benchmark.template | 45 ++++++++++++++++++++++
.../projection_subquery/projection_subquery.suite | 23 +++++++++++
.../projection_subquery/queries/q01.sql | 7 ++++
.../projection_subquery/queries/q02.sql | 6 +++
.../projection_subquery/queries/q03.sql | 6 +++
.../projection_subquery/queries/q04.sql | 12 ++++++
.../projection_subquery/queries/q05.sql | 6 +++
.../projection_subquery/queries/q06.sql | 6 +++
.../projection_subquery/queries/q07.sql | 7 ++++
.../projection_subquery/results/q01_in_bare.csv | 2 +
.../results/q02_in_coalesce.csv | 2 +
.../results/q03_in_correlated_eq.csv | 2 +
.../results/q04_in_two_columns.csv | 2 +
.../results/q05_not_in_bare.csv | 2 +
.../results/q06_exists_correlated.csv | 2 +
.../results/q07_in_correlated_residual.csv | 2 +
28 files changed, 237 insertions(+)
diff --git a/benchmarks/README.md b/benchmarks/README.md
index 5a555e15ab..151f1e7df5 100644
--- a/benchmarks/README.md
+++ b/benchmarks/README.md
@@ -1009,6 +1009,32 @@ Several queries are included to test sort merge joins under various workloads.
./bench.sh run smj
```
+
+## Projection Subquery
+
+This benchmark measures `IN`, `NOT IN` and `EXISTS` subqueries that sit in the `SELECT` list instead of a filter
+(see <https://github.com/apache/datafusion/issues/25341>).
+
+A projected `IN` must return `NULL`, and not `false`, when there is no match and the inner side holds a `NULL`.
+The decorrelation therefore turns one projected `IN` into more than one mark join.
+If a mark join has no hashable join predicate, it runs as a nested-loop join, and the cost grows with the outer row count times the inner row count.
+
+Every query projects the boolean subquery result and aggregates it, so the measured cost is the plan and not the size of the output.
+Query `q07` correlates on `<` instead of `=`, so it stays on the nested-loop path.
+It is the control: its time must not change when the hashable shapes get faster.
+
+The two tables are built inline by the suite's load SQL, so there is no data step.
+Set `PSQ_ROWS` to change the row count in each table.
+The default is 30,000, which keeps the nested-loop plans at a few hundred milliseconds.
+The checked-in result files hold the counts for that default, so `--result-mode validate` needs it.
+
+### Example Run
+
+```bash
+# No need to generate data: the suite's load SQL builds the two tables inline
+
+./bench.sh run projection_subquery
+```
## Cancellation
Test performance of cancelling queries.
diff --git a/benchmarks/bench.sh b/benchmarks/bench.sh
index 05ede43178..523e566c3d 100755
--- a/benchmarks/bench.sh
+++ b/benchmarks/bench.sh
@@ -115,6 +115,10 @@ parquet_row_filter_skip: Per-RG fully-matched RowFilter skip on Parquet (apache/
null_aware_join: Null-aware (NOT IN) hash join micro-benchmarks: uncorrelated, non-equality-correlated and equality-correlated
NOT IN across NULL fractions, to measure the per-pair join-filter work the correlated cases do
(data generated inline by the suite's load SQL from range(); knobs: NAJ_ROWS, NAJ_LARGE_ROWS)
+projection_subquery: IN / NOT IN / EXISTS subqueries in the SELECT list (see https://github.com/apache/datafusion/issues/25341); each query projects the
+ boolean subquery result and aggregates it, so the cost is the decorrelation plan and not the output size
+ (q07 correlates on '<' instead of '=', so it keeps the nested-loop plan and acts as the control)
+ (data generated inline by the suite's load SQL; knob: PSQ_ROWS)
# ClickBench Benchmarks
clickbench_1: ClickBench queries against a single parquet file
@@ -279,6 +283,10 @@ main() {
# Data is generated inline by the suite's load SQL from range().
echo "null_aware_join: no external data to generate"
;;
+ projection_subquery)
+ # Data is generated inline by the suite's load SQL.
+ echo "projection_subquery: no external data to generate"
+ ;;
asof_join)
data_asof_join
;;
@@ -530,6 +538,9 @@ main() {
null_aware_join)
run_null_aware_join
;;
+ projection_subquery)
+ run_projection_subquery
+ ;;
asof_join)
run_asof_join
;;
@@ -969,6 +980,24 @@ run_null_aware_join() {
bash -c "$SQL_CARGO_COMMAND"
}
+# Runs the projection_subquery suite: IN / NOT IN / EXISTS subqueries that sit
+# in the SELECT list instead of a filter (see
+# https://github.com/apache/datafusion/issues/25341). The load SQL builds the
+# two tables inline, so there is no data step. Each query projects the boolean
+# subquery result and aggregates it, so the measured cost is the decorrelation
+# plan and not the size of the output. Query 07 correlates on '<' instead of
+# '=', so it keeps the nested-loop plan and acts as the control.
+# Knob (string-substituted into the load SQL, not engine config):
+# PSQ_ROWS rows in each of the two tables (default 30_000; the checked-in
+# result files hold the counts for that value)
+run_projection_subquery() {
+ echo "Running projection_subquery benchmark (rows=${PSQ_ROWS:-30000})..."
+ debug_run env BENCH_NAME=projection_subquery \
+ PSQ_ROWS="${PSQ_ROWS:-30000}" \
+ ${QUERY:+BENCH_QUERY="${QUERY}"} \
+ bash -c "$SQL_CARGO_COMMAND"
+}
+
# Runs the tpch in memory (needs tpch parquet data)
run_tpch_mem() {
SCALE_FACTOR=$1
diff --git a/benchmarks/sql_benchmarks/README.md b/benchmarks/sql_benchmarks/README.md
index fb09c95daa..0bf3dbd862 100644
--- a/benchmarks/sql_benchmarks/README.md
+++ b/benchmarks/sql_benchmarks/README.md
@@ -47,6 +47,7 @@ in the community:
| `wide_schema` | Small-projection queries on a wide (1024-col, 256-file) synthetic dataset; runs `wide` + `narrow` subgroups for comparison |
| `predicate_eval` | Conjunctive (AND) filter-evaluation micro-benchmarks; each subgroup is a different predicate pattern, to test how an adaptive predicate-ordering system behaves across them ([#11262](https://github.com/apache/datafusion/issues/11262)). Subgroups (`--subgroup`): `costsel`, `cost`, `selectivity`, `cardinality`, `width`, `scale`, `neutral`, `correlation`, `drift`, `nulls`. The suite sets no engine config of its own, so by default it measures DataFusion's built-in le [...]
| `parquet_row_filter_skip` | Micro-benchmark for the per-row-group fully-matched RowFilter skip on Parquet scans ([#23696](https://github.com/apache/datafusion/issues/23696)). Subgroups (`--subgroup`): `skip` (clustered key, most row groups fully matched by statistics so the per-row filter is skipped), `control` (scrambled key, no row group is ever fully matched). Size the data with `PRED_ROWS` and the row-group size with `RG_SIZE`. |
+| `projection_subquery` | `IN`, `NOT IN` and `EXISTS` subqueries that sit in the `SELECT` list instead of a filter ([#25341](https://github.com/apache/datafusion/issues/25341)). Every query projects the boolean subquery result and aggregates it, so the measured cost is the decorrelation plan and not the size of the output. `q07` correlates on `<` instead of `=`, so it keeps the nested-loop plan and is the control that must not change. Size the two inline tables with `PSQ_ROWS` (default ` [...]
# Running Benchmarks
@@ -174,6 +175,7 @@ Some benchmarks use custom environment variables as outlined below:
| PRED_ROWS | Used in the predicate_eval benchmark to size the synthetic table (the `scale` subgroup overrides this per query), and in the parquet_row_filter_skip benchmark to size the generated Parquet datasets (default `10000000` there). | `1000000` |
| PRED_FILL | Used in the predicate_eval benchmark as the string-column width knob (filler chars per marker). | `30` |
| RG_SIZE | Used in the parquet_row_filter_skip benchmark as the Parquet `max_row_group_size` for the generated datasets. | `1000000` |
+| PSQ_ROWS | Used in the projection_subquery benchmark to size the two inline tables. | `30000` |
## How it works
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q01.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q01.benchmark
new file mode 100644
index 0000000000..587150c4bf
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q01.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=01
+NAME=q01_in_bare
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q02.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q02.benchmark
new file mode 100644
index 0000000000..0b855f3fea
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q02.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=02
+NAME=q02_in_coalesce
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q03.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q03.benchmark
new file mode 100644
index 0000000000..de302e8d6d
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q03.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=03
+NAME=q03_in_correlated_eq
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q04.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q04.benchmark
new file mode 100644
index 0000000000..d82b3ecad9
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q04.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=04
+NAME=q04_in_two_columns
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q05.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q05.benchmark
new file mode 100644
index 0000000000..3c47bad916
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q05.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=05
+NAME=q05_not_in_bare
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q06.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q06.benchmark
new file mode 100644
index 0000000000..76979e1ac3
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q06.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=06
+NAME=q06_exists_correlated
diff --git a/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q07.benchmark b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q07.benchmark
new file mode 100644
index 0000000000..541b77db3a
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/benchmarks/q07.benchmark
@@ -0,0 +1,3 @@
+template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
+QPAD=07
+NAME=q07_in_correlated_residual
diff --git a/benchmarks/sql_benchmarks/projection_subquery/init/cleanup.sql b/benchmarks/sql_benchmarks/projection_subquery/init/cleanup.sql
new file mode 100644
index 0000000000..29a35be6b4
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/init/cleanup.sql
@@ -0,0 +1,2 @@
+DROP TABLE IF EXISTS outer_t;
+DROP TABLE IF EXISTS inner_t;
diff --git a/benchmarks/sql_benchmarks/projection_subquery/load/tables.sql b/benchmarks/sql_benchmarks/projection_subquery/load/tables.sql
new file mode 100644
index 0000000000..5e57de826c
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/load/tables.sql
@@ -0,0 +1,25 @@
+-- Two small integer tables for the projected IN / EXISTS subquery benchmarks.
+--
+-- `outer_t.id` is dense on 1..PSQ_ROWS and `inner_t.id` is the even numbers,
+-- so about half of the outer keys have a match. Every 97th inner key is NULL,
+-- which keeps three-valued logic in play: a projected `IN` must return NULL,
+-- not false, when there is no match and the inner side holds a NULL. That is
+-- the reason the decorrelation needs more than one mark join.
+--
+-- `z` is a low-cardinality column (1000 groups) used by the correlated
+-- queries, once with an equality correlation and once with a `<` correlation.
+--
+-- The tables are deliberately small. The plans under test are quadratic in
+-- outer rows times inner rows, so a larger PSQ_ROWS makes the suite take
+-- minutes per query instead of seconds.
+CREATE TABLE outer_t AS
+SELECT
+ CAST(value AS INT) AS id,
+ CAST(value % 1000 AS INT) AS z
+FROM generate_series(1, ${PSQ_ROWS:-30000});
+
+CREATE TABLE inner_t AS
+SELECT
+ CASE WHEN value % 97 = 0 THEN NULL ELSE CAST(value * 2 AS INT) END AS id,
+ CAST(value % 1000 AS INT) AS z
+FROM generate_series(1, ${PSQ_ROWS:-30000});
diff --git a/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.benchmark.template b/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
new file mode 100644
index 0000000000..f91c61dfc5
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
@@ -0,0 +1,45 @@
+# Shared template for every projection_subquery benchmark. Each qNN.benchmark
+# includes this template with parameters:
+# QPAD zero-padded query id / query file stem (e.g. 01)
+# NAME criterion display name (e.g. q01_in_bare)
+# Optional, read by the load script as ${...:-default}:
+# PSQ_ROWS rows in each of the two tables (default 30000)
+#
+# The tables are always named `outer_t` and `inner_t`, so the load, the asserts
+# and the cleanup are the same for every query.
+#
+# Every query is an aggregate over the projected subquery result, so the output
+# is two numbers and the measurement is the plan and not the result transfer.
+# The counts are checked in under results/, so --result-mode validate also
+# proves that a change to the decorrelation still returns the same rows. The
+# checked-in counts hold for the default PSQ_ROWS only.
+
+load sql_benchmarks/projection_subquery/load/tables.sql
+
+name ${NAME}
+group projection_subquery
+
+# Both tables have exactly PSQ_ROWS rows.
+assert I
+SELECT count(*) = ${PSQ_ROWS:-30000} FROM outer_t;
+----
+true
+
+assert I
+SELECT count(*) = ${PSQ_ROWS:-30000} FROM inner_t;
+----
+true
+
+# The inner key must keep its NULLs, because three-valued logic is what forces
+# the extra mark joins. A load that loses them makes the suite measure the
+# wrong plan.
+assert I
+SELECT count(*) > 0 FROM inner_t WHERE id IS NULL;
+----
+true
+
+run sql_benchmarks/projection_subquery/queries/q${QPAD}.sql
+
+result sql_benchmarks/projection_subquery/results/${NAME}.csv
+
+cleanup sql_benchmarks/projection_subquery/init/cleanup.sql
diff --git a/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.suite b/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.suite
new file mode 100644
index 0000000000..f5f419b50a
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/projection_subquery.suite
@@ -0,0 +1,23 @@
+description = "IN / NOT IN / EXISTS subqueries that appear in the SELECT list instead of a filter (apache/datafusion#25341). Each query projects the boolean result of a subquery and aggregates it, so the measured work is the decorrelation plan and not the output size. The decorrelation turns one projected IN into mark joins; when the join predicate is not hashable, or the correlation is not an equality, the plan keeps a nested-loop mark join and the cost grows with outer rows times inner [...]
+
+query_pattern = "q{QUERY_ID_PADDED}.benchmark"
+
+[[options]]
+name = "rows"
+short = "r"
+env = "PSQ_ROWS"
+default = "30000"
+values = ["30000", "..."]
+help = "Sets the number of rows in each of the two generated tables. The checked-in result files hold the counts for the default value, so --result-mode validate needs the default."
+
+[[examples]]
+command = "cargo run --release --bin benchmark_runner -- projection_subquery"
+description = "Run every query with the default 30,000 rows per table."
+
+[[examples]]
+command = "cargo run --release --bin benchmark_runner -- projection_subquery --query 1 --result-mode validate"
+description = "Run query 01 and compare the counts with the checked-in result file."
+
+[[examples]]
+command = "cargo run --release --bin benchmark_runner -- projection_subquery -r 5000"
+description = "Run every query on smaller tables."
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q01.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q01.sql
new file mode 100644
index 0000000000..70cd5ee473
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q01.sql
@@ -0,0 +1,7 @@
+-- Shape: bare uncorrelated IN in the SELECT list. The subquery has no
+-- correlation, so the only join key is the outer key against the inner key.
+-- This is the plain form of the quadratic plan.
+SELECT
+ count(*) FILTER (WHERE m) AS true_count,
+ count(*) FILTER (WHERE m IS NULL) AS null_count
+FROM (SELECT id, id IN (SELECT id FROM inner_t) AS m FROM outer_t);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q02.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q02.sql
new file mode 100644
index 0000000000..11b934fe23
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q02.sql
@@ -0,0 +1,6 @@
+-- Shape: the same bare IN, wrapped in COALESCE so the NULL result becomes
+-- false. The wrapper is the common way to use a projected IN, and it must not
+-- stop the decorrelation.
+SELECT
+ count(*) FILTER (WHERE m) AS true_count
+FROM (SELECT id, COALESCE((id IN (SELECT id FROM inner_t))::boolean, false) AS m FROM outer_t);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q03.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q03.sql
new file mode 100644
index 0000000000..e21953305f
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q03.sql
@@ -0,0 +1,6 @@
+-- Shape: IN correlated on an equality. The correlation adds a second join key,
+-- so the whole join condition stays hashable.
+SELECT
+ count(*) FILTER (WHERE m) AS true_count,
+ count(*) FILTER (WHERE m IS NULL) AS null_count
+FROM (SELECT id, id IN (SELECT i.id FROM inner_t i WHERE i.z = o.z) AS m FROM outer_t o);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q04.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q04.sql
new file mode 100644
index 0000000000..838c36d7e6
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q04.sql
@@ -0,0 +1,12 @@
+-- Shape: two independent projected IN subqueries in one SELECT list. Each one
+-- is decorrelated on its own, so this measures whether the cost of the first
+-- one simply doubles.
+SELECT
+ count(*) FILTER (WHERE a) AS a_count,
+ count(*) FILTER (WHERE b) AS b_count
+FROM (
+ SELECT
+ id IN (SELECT id FROM inner_t) AS a,
+ id IN (SELECT id FROM inner_t WHERE z < 500) AS b
+ FROM outer_t
+);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q05.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q05.sql
new file mode 100644
index 0000000000..e429d50853
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q05.sql
@@ -0,0 +1,6 @@
+-- Shape: NOT IN in the SELECT list. It is the negation of q01 and it has the
+-- same three-valued logic, so it must plan the same way.
+SELECT
+ count(*) FILTER (WHERE m) AS true_count,
+ count(*) FILTER (WHERE m IS NULL) AS null_count
+FROM (SELECT id, id NOT IN (SELECT id FROM inner_t) AS m FROM outer_t);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q06.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q06.sql
new file mode 100644
index 0000000000..89414bd282
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q06.sql
@@ -0,0 +1,6 @@
+-- Shape: correlated EXISTS in the SELECT list. EXISTS has no NULL result, so
+-- one mark join is enough. It is the reference for what the IN queries should
+-- cost.
+SELECT
+ count(*) FILTER (WHERE e) AS true_count
+FROM (SELECT id, EXISTS (SELECT 1 FROM inner_t i WHERE i.id = o.id) AS e FROM outer_t o);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/queries/q07.sql b/benchmarks/sql_benchmarks/projection_subquery/queries/q07.sql
new file mode 100644
index 0000000000..54299f0cbd
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/queries/q07.sql
@@ -0,0 +1,7 @@
+-- Shape: IN correlated on `<` instead of `=`. There is no equality to hash on,
+-- so this query keeps the nested-loop mark joins. It is the control: its cost
+-- must stay the same when the hashable cases get faster.
+SELECT
+ count(*) FILTER (WHERE m) AS true_count,
+ count(*) FILTER (WHERE m IS NULL) AS null_count
+FROM (SELECT id, id IN (SELECT i.id FROM inner_t i WHERE i.z < o.z) AS m FROM outer_t o);
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q01_in_bare.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q01_in_bare.csv
new file mode 100644
index 0000000000..fc5b81602d
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q01_in_bare.csv
@@ -0,0 +1,2 @@
+true_count|null_count
+14846|15154
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q02_in_coalesce.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q02_in_coalesce.csv
new file mode 100644
index 0000000000..c0af10b384
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q02_in_coalesce.csv
@@ -0,0 +1,2 @@
+true_count
+14846
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q03_in_correlated_eq.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q03_in_correlated_eq.csv
new file mode 100644
index 0000000000..f966c83888
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q03_in_correlated_eq.csv
@@ -0,0 +1,2 @@
+true_count|null_count
+15|9270
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q04_in_two_columns.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q04_in_two_columns.csv
new file mode 100644
index 0000000000..2b9f129316
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q04_in_two_columns.csv
@@ -0,0 +1,2 @@
+a_count|b_count
+14846|7423
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q05_not_in_bare.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q05_not_in_bare.csv
new file mode 100644
index 0000000000..9e3764aaa3
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q05_not_in_bare.csv
@@ -0,0 +1,2 @@
+true_count|null_count
+0|15154
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q06_exists_correlated.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q06_exists_correlated.csv
new file mode 100644
index 0000000000..c0af10b384
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q06_exists_correlated.csv
@@ -0,0 +1,2 @@
+true_count
+14846
diff --git a/benchmarks/sql_benchmarks/projection_subquery/results/q07_in_correlated_residual.csv b/benchmarks/sql_benchmarks/projection_subquery/results/q07_in_correlated_residual.csv
new file mode 100644
index 0000000000..f9cf5f10be
--- /dev/null
+++ b/benchmarks/sql_benchmarks/projection_subquery/results/q07_in_correlated_residual.csv
@@ -0,0 +1,2 @@
+true_count|null_count
+7408|22487
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org