zhangweiwen opened a new pull request, #11374:
URL: https://github.com/apache/seatunnel/pull/11374
#### **Background**
When using the SQL Server JDBC connector to synchronize data, the job fails
with a "duplicate column" or "duplicate field" error, even when testing with a
single table.
#### **Root Cause**
The issue stems from the SQL query used in `SqlServerCatalog` (or the SQL
Server metadata dialect) to fetch table schema information.
The query performs a `LEFT JOIN` on `sys.extended_properties` to retrieve
column comments (`MS_Description`). However, it only joins on `major_id` and
`minor_id` without specifying `ext.name = 'MS_Description'` in the join
condition:
```sql
LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND
ext.minor_id = col.column_id
```
In SQL Server, a single column can have **multiple extended properties**
(e.g., custom metadata, system tracking, diagrams, or third-party tool configs)
besides just the column description. Because of the missing name constraint,
the `LEFT JOIN` multiplies the result rows for a single column, leading
SeaTunnel to believe the table contains duplicate fields.
#### **Example Symptom**
If a column `ALARM_ID` has multiple extended properties (e.g., `-1`,
`false`, `2370`), the query returns duplicate rows for the same `column_id`:
| table_name | column_name | comment | column_id | type | ... |
| --- | --- | --- | --- | --- | --- |
| DEF_ALARM_MSG | ALARM_ID | -1 | 1 | nvarchar | ... |
| DEF_ALARM_MSG | ALARM_ID | false | 1 | nvarchar | ... |
| DEF_ALARM_MSG | ALARM_ID | 2370 | 1 | nvarchar | ... |
#### **Changes**
* Moved and explicitly applied the `ext.name = 'MS_Description'` constraint
directly into the `LEFT JOIN sys.extended_properties` clause.
* Corrected the subsequent `sys.default_constraints` join logic to ensure it
maps properly based on the column's default object ID rather than leaking the
`ext.name` filter into the wrong join condition.
**Updated SQL Logic:**
```sql
LEFT JOIN sys.extended_properties ext
ON ext.major_id = col.object_id
AND ext.minor_id = col.column_id
AND ext.name = 'MS_Description'
LEFT JOIN sys.default_constraints def
ON col.default_object_id = def.object_id
```
#### **Test Record**
* **Manual Verification:** Verified the updated query against a SQL Server
instance containing tables with multiple extended properties. The query now
correctly returns exactly one row per column.
* **Integration Test:** Executed the SeaTunnel sync job using the modified
`connector-jdbc-sqlserver` JAR; the metadata parsing succeeded without throwing
duplicate field errors.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
