This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 4b48cb0899 tests: add SLT test coverage for `MERGE INTO` (#24174)
4b48cb0899 is described below
commit 4b48cb0899ddfdf6d48532e18991df78aadacd62
Author: Andrew Lamb <[email protected]>
AuthorDate: Sat Aug 8 08:41:03 2026 -0400
tests: add SLT test coverage for `MERGE INTO` (#24174)
## Which issue does this PR close?
- Follow on ot https://github.com/apache/datafusion/pull/22988 from
@wirybeaver
## Rationale for this change
In addition to planning for MERGE INTO I think we should have some SQL
level tests . Since the default MemTable doesn't implement MERGE INTO we
can mostly only test the planning, but we should still add the coverage
I think
## What changes are included in this PR?
Add slt tests for `MERGE INTO`, with mostly explain plan coverage
## Are these changes tested?
Only tests
## Are there any user-facing changes?
No
---
datafusion/sqllogictest/test_files/merge_into.slt | 248 ++++++++++++++++++++++
1 file changed, 248 insertions(+)
diff --git a/datafusion/sqllogictest/test_files/merge_into.slt
b/datafusion/sqllogictest/test_files/merge_into.slt
new file mode 100644
index 0000000000..f868bcbdc4
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/merge_into.slt
@@ -0,0 +1,248 @@
+# 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.
+
+##########
+## MERGE INTO Tests
+##
+## Note that MERGE INTO planning is supported, but the built-in MemTable does
not
+## (yet) support execution. These tests verify planning
+##########
+
+statement ok
+create table target(id int, val varchar, qty int);
+
+statement ok
+insert into target values (1, 'foo', 100.0);
+
+statement ok
+insert into target values (2, 'bar', 200.0);
+
+statement ok
+insert into target values (3, 'baz', 300.0);
+
+
+statement ok
+create table source(id int, val varchar, is_active boolean);
+
+statement ok
+insert into source values (2, 'xxxx', true);
+
+statement ok
+insert into source values (4, 'yyyy', false);
+
+
+##########
+# Logical planning
+##########
+
+query TT
+explain merge into target using source on target.id = source.id
+when matched then update set val = source.val
+when not matched then insert (id, val) values (source.id, source.val);
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--TableScan: source projection=[id, val, is_active]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# Simple MATCHED DELETE
+query TT
+explain merge into target using source on target.id = source.id
+when matched then delete;
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--TableScan: source projection=[id, val, is_active]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# Aliased target and source: alias is canonicalized to the table name
+query TT
+explain merge into target as t using source as s on t.id = s.id
+when matched and s.is_active then update set val = s.val
+when not matched by source then delete;
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--SubqueryAlias: s
+03)----TableScan: source projection=[id, val, is_active]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# WHEN NOT MATCHED THEN DELETE is rejected by the parser (no target row
exists);
+query error DELETE is not allowed in a NOT MATCHED merge clause at Line: 2,
Column: 23
+merge into target using source on target.id = source.id
+when not matched then delete;
+
+# NOT MATCHED BY SOURCE
+query TT
+explain merge into target using source on target.id = source.id
+when not matched by source then delete;
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--TableScan: source projection=[id, val, is_active]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# Subquery as the USING source
+query TT
+explain merge into target using (select id, max(val) as val from source group
by id) as s
+on target.id = s.id
+when matched then update set val = s.val;
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--SubqueryAlias: s
+03)----Projection: source.id, max(source.val) AS val
+04)------Aggregate: groupBy=[[source.id]], aggr=[[max(source.val)]]
+05)--------TableScan: source projection=[id, val]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# INSERT without an explicit column list requires values for all target columns
+query TT
+explain merge into target using source on target.id = source.id
+when not matched then insert values (source.id, source.val, 0);
+----
+logical_plan
+01)Dml: op=[MergeInto] table=[target]
+02)--TableScan: source projection=[id, val, is_active]
+physical_plan_error
+01)MERGE INTO operation on table 'target'
+02)caused by
+03)This feature is not implemented: MERGE INTO not supported for Base table
+
+# Execution fails: the default TableProvider does not implement merge_into
+statement error
+merge into target using source on target.id = source.id
+when matched then delete;
+----
+DataFusion error: MERGE INTO operation on table 'target'
+caused by
+This feature is not implemented: MERGE INTO not supported for Base table
+
+
+##########
+# Type coercion of ON / WHEN conditions
+##########
+
+statement error DataFusion error: type_coercion\ncaused by\nError during
planning: MERGE ON condition must be boolean type, but got Int64
+merge into target using source on 1
+when matched then delete;
+
+statement error DataFusion error: type_coercion\ncaused by\nError during
planning: MERGE WHEN condition must be boolean type, but got Utf8
+merge into target using source on target.id = source.id
+when matched and 'yes' then delete;
+
+##########
+# Planning errors: invalid structure
+##########
+
+statement error DataFusion error: Error during planning: MERGE INTO requires
at least one WHEN clause
+merge into target using source on target.id = source.id;
+
+statement error DataFusion error: Error during planning: Duplicate column
'val' in MERGE UPDATE
+merge into target using source on target.id = source.id
+when matched then update set val = source.val, val = 'x';
+
+statement error DataFusion error: Error during planning: Duplicate column 'id'
in MERGE INSERT
+merge into target using source on target.id = source.id
+when not matched then insert (id, ID) values (1, 2);
+
+statement error DataFusion error: Error during planning: MERGE INSERT has 2
column\(s\) but 1 value\(s\)
+merge into target using source on target.id = source.id
+when not matched then insert (id, val) values (source.id);
+
+statement error DataFusion error: Error during planning: MERGE INSERT has 3
column\(s\) but 2 value\(s\)
+merge into target using source on target.id = source.id
+when not matched then insert values (source.id, source.val);
+
+statement error DataFusion error: Error during planning: MERGE INSERT must
have exactly one row of values
+merge into target using source on target.id = source.id
+when not matched then insert (id) values (1), (2);
+
+# Unknown column in UPDATE assignment
+statement error DataFusion error: Schema error: No field named nonexistent.
+merge into target using source on target.id = source.id
+when matched then update set nonexistent = 1;
+
+# Unknown column in INSERT column list
+statement error DataFusion error: Schema error: No field named nonexistent.
+merge into target using source on target.id = source.id
+when not matched then insert (nonexistent) values (1);
+
+# UPDATE assignment must reference the target table
+statement error DataFusion error: Error during planning: MERGE assignment
target 's.val' must reference target table 't'
+merge into target as t using source as s on t.id = s.id
+when matched then update set s.val = 'x';
+
+##########
+# Planning errors: qualifier and alias handling
+##########
+
+# Source alias may not collide with the target table name when the target is
aliased
+statement error DataFusion error: Error during planning: MERGE source may not
use the target table name 'target' as a qualifier while the target is aliased
as 't'; use a different source alias
+merge into target as t using source as target on t.id = target.id
+when matched then delete;
+
+# Subqueries correlated to the target alias are not supported yet
+statement error DataFusion error: This feature is not implemented: MERGE
subqueries correlated to target alias 't' are not supported
+merge into target as t using source as s
+on exists (select 1 from source x where x.id = t.id)
+when matched then delete;
+
+##########
+# Planning errors: unsupported syntax
+##########
+
+statement error DataFusion error: This feature is not implemented: MERGE
target table modifiers are not supported
+merge into target partition (p0) using source on target.id = source.id
+when matched then delete;
+
+statement error DataFusion error: This feature is not implemented: MERGE
target alias column lists are not supported
+merge into target as t(a, b) using source as s on t.a = s.id
+when matched then delete;
+
+statement error DataFusion error: This feature is not implemented: MERGE
UPDATE WHERE predicates are not supported
+merge into target using source on target.id = source.id
+when matched then update set val = source.val where source.is_active;
+
+statement error DataFusion error: This feature is not implemented: MERGE
INSERT WHERE predicates are not supported
+merge into target using source on target.id = source.id
+when not matched then insert (id) values (source.id) where source.is_active;
+
+statement error DataFusion error: This feature is not implemented: MERGE
INSERT ROW is not supported
+merge into target using source on target.id = source.id
+when not matched then insert row;
+
+statement ok
+drop table target;
+
+statement ok
+drop table source;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
