This is an automated email from the ASF dual-hosted git repository.
zihaoxiang pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new 9ce565ecc8 [Fix-17701][SqlTask] handle duplicate column aliases in SQL
result by appending column index suffix (#17702)
9ce565ecc8 is described below
commit 9ce565ecc8d3f752ef4f12be247a8af0d42c20bc
Author: njnu-seafish <[email protected]>
AuthorDate: Thu Dec 4 10:35:35 2025 +0800
[Fix-17701][SqlTask] handle duplicate column aliases in SQL result by
appending column index suffix (#17702)
---
.../dolphinscheduler/plugin/task/sql/SqlTask.java | 16 ++-
.../plugin/task/sql/SqlTaskTest.java | 144 +++++++++++++++++++++
2 files changed, 159 insertions(+), 1 deletion(-)
diff --git
a/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/main/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTask.java
b/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/main/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTask.java
index 5b96c1d004..44296bc331 100644
---
a/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/main/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTask.java
+++
b/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/main/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTask.java
@@ -49,9 +49,11 @@ import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
+import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@@ -246,11 +248,23 @@ public class SqlTask extends AbstractTask {
if (resultSet != null) {
ResultSetMetaData md = resultSet.getMetaData();
int num = md.getColumnCount();
+ String[] columnLabels = new String[num];
+
+ // Check for duplicates in column definitions (across all columns)
+ Set<String> uniqueLabels = new HashSet<>(num);
+ for (int i = 1; i <= num; i++) {
+ String label = md.getColumnLabel(i);
+ columnLabels[i - 1] = label;
+ if (!uniqueLabels.add(label)) {
+ throw new TaskException("SQL column name conflict:
duplicate column name '" + label
+ + "'. Please use aliases to ensure unique column
names.");
+ }
+ }
while (resultSet.next()) {
ObjectNode mapOfColValues = JSONUtils.createObjectNode();
for (int i = 1; i <= num; i++) {
- mapOfColValues.set(md.getColumnLabel(i),
JSONUtils.toJsonNode(resultSet.getObject(i)));
+ mapOfColValues.set(columnLabels[i - 1],
JSONUtils.toJsonNode(resultSet.getObject(i)));
}
resultJSONArray.add(mapOfColValues);
}
diff --git
a/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/test/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTaskTest.java
b/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/test/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTaskTest.java
index 233d19757a..3832d653a5 100644
---
a/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/test/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTaskTest.java
+++
b/dolphinscheduler-task-plugin/dolphinscheduler-task-sql/src/test/java/org/apache/dolphinscheduler/plugin/task/sql/SqlTaskTest.java
@@ -17,8 +17,12 @@
package org.apache.dolphinscheduler.plugin.task.sql;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
import org.apache.dolphinscheduler.common.utils.JSONUtils;
import org.apache.dolphinscheduler.plugin.task.api.TaskConstants;
+import org.apache.dolphinscheduler.plugin.task.api.TaskException;
import org.apache.dolphinscheduler.plugin.task.api.TaskExecutionContext;
import org.apache.dolphinscheduler.plugin.task.api.enums.DataType;
import org.apache.dolphinscheduler.plugin.task.api.enums.Direct;
@@ -30,6 +34,10 @@ import
org.apache.dolphinscheduler.plugin.task.api.parameters.resource.ResourceP
import org.apache.dolphinscheduler.plugin.task.api.utils.ParameterUtils;
import org.apache.dolphinscheduler.spi.enums.DbType;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.Map;
@@ -37,6 +45,8 @@ import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
+import com.fasterxml.jackson.databind.node.ArrayNode;
+import com.fasterxml.jackson.databind.node.ObjectNode;
import com.google.common.collect.Lists;
class SqlTaskTest {
@@ -197,4 +207,138 @@ class SqlTaskTest {
Assertions.assertEquals("1", varPoolParam.getValue());
Assertions.assertEquals(Direct.OUT, varPoolParam.getDirect());
}
+
+ @Test
+ void
testGenerateEmptyRow_WithNonNullResultSet_ReturnsEmptyValuesForAllColumns()
throws Exception {
+ // Arrange
+ ResultSet mockResultSet = mock(ResultSet.class);
+ ResultSetMetaData mockMetaData = mock(ResultSetMetaData.class);
+
+ when(mockResultSet.getMetaData()).thenReturn(mockMetaData);
+ when(mockMetaData.getColumnCount()).thenReturn(2);
+ when(mockMetaData.getColumnLabel(1)).thenReturn("id");
+ when(mockMetaData.getColumnLabel(2)).thenReturn("name");
+
+ Method method = SqlTask.class.getDeclaredMethod("generateEmptyRow",
ResultSet.class);
+ method.setAccessible(true);
+
+ // Act
+ ArrayNode result = (ArrayNode) method.invoke(sqlTask, mockResultSet);
+
+ // Assert
+ Assertions.assertNotNull(result);
+ Assertions.assertEquals(1, result.size());
+
+ ObjectNode row = (ObjectNode) result.get(0);
+ Assertions.assertEquals("", row.get("id").asText());
+ Assertions.assertEquals("", row.get("name").asText());
+ }
+
+ @Test
+ void testGenerateEmptyRow_WithNullResultSet_ReturnsErrorObject() throws
Exception {
+ // Arrange
+ Method method = SqlTask.class.getDeclaredMethod("generateEmptyRow",
ResultSet.class);
+ method.setAccessible(true);
+
+ // Act
+ ArrayNode result = (ArrayNode) method.invoke(sqlTask, (ResultSet)
null);
+
+ // Assert
+ Assertions.assertNotNull(result);
+ Assertions.assertEquals(1, result.size());
+
+ ObjectNode row = (ObjectNode) result.get(0);
+ Assertions.assertTrue(row.has("error"));
+ Assertions.assertEquals("resultSet is null",
row.get("error").asText());
+ }
+
+ @Test
+ void testGenerateEmptyRow_WithDuplicateColumns_DeduplicatesLabels() throws
Exception {
+ ResultSet mockResultSet = mock(ResultSet.class);
+ ResultSetMetaData mockMetaData = mock(ResultSetMetaData.class);
+
+ when(mockResultSet.getMetaData()).thenReturn(mockMetaData);
+ when(mockMetaData.getColumnCount()).thenReturn(3);
+ when(mockMetaData.getColumnLabel(1)).thenReturn("id");
+ when(mockMetaData.getColumnLabel(2)).thenReturn("id"); // duplicate
+ when(mockMetaData.getColumnLabel(3)).thenReturn("name");
+
+ Method method = SqlTask.class.getDeclaredMethod("generateEmptyRow",
ResultSet.class);
+ method.setAccessible(true);
+
+ ArrayNode result = (ArrayNode) method.invoke(sqlTask, mockResultSet);
+
+ Assertions.assertNotNull(result);
+ Assertions.assertEquals(1, result.size());
+
+ ObjectNode row = (ObjectNode) result.get(0);
+ Assertions.assertTrue(row.has("id"));
+ Assertions.assertTrue(row.has("name"));
+ }
+
+ @Test
+ void testResultProcess_NullResultSet_ReturnsEmptyResult() throws Exception
{
+ Method resultProcessMethod =
SqlTask.class.getDeclaredMethod("resultProcess", ResultSet.class);
+ resultProcessMethod.setAccessible(true);
+
+ // Mock a null ResultSet
+ String result = (String) resultProcessMethod.invoke(sqlTask,
(ResultSet) null);
+
+ Assertions.assertNotNull(result);
+ Assertions.assertTrue(result.equalsIgnoreCase("[{\"error\":\"resultSet
is null\"}]"));
+ }
+
+ @Test
+ void testResultProcess_EmptyResultSet_ReturnsEmptyResult() throws
Exception {
+ // Mock a non-null ResultSet that contains no data rows
+ ResultSet mockResultSet = mock(ResultSet.class);
+ ResultSetMetaData mockMetaData = mock(ResultSetMetaData.class);
+
+ when(mockResultSet.getMetaData()).thenReturn(mockMetaData);
+ when(mockMetaData.getColumnCount()).thenReturn(2);
+ when(mockMetaData.getColumnLabel(1)).thenReturn("id");
+ when(mockMetaData.getColumnLabel(2)).thenReturn("name");
+ when(mockResultSet.next()).thenReturn(false); // no rows available
+
+ Method resultProcessMethod =
SqlTask.class.getDeclaredMethod("resultProcess", ResultSet.class);
+ resultProcessMethod.setAccessible(true);
+
+ String result = (String) resultProcessMethod.invoke(sqlTask,
mockResultSet);
+
+ Assertions.assertNotNull(result);
+ // Verify the result contains empty string values for all columns and
is a valid JSON array
+ Assertions.assertTrue(result.contains("\"id\":\"\""));
+ Assertions.assertTrue(result.contains("\"name\":\"\""));
+ Assertions.assertTrue(result.startsWith("[{"));
+ Assertions.assertTrue(result.endsWith("}]"));
+ }
+
+ @Test
+ void testResultProcess_DuplicateColumnLabels_ThrowsTaskException() throws
Exception {
+ ResultSet mockRs = mock(ResultSet.class);
+ ResultSetMetaData mockMd = mock(ResultSetMetaData.class);
+
+ when(mockRs.getMetaData()).thenReturn(mockMd);
+ when(mockMd.getColumnCount()).thenReturn(2);
+ when(mockMd.getColumnLabel(1)).thenReturn("id");
+ when(mockMd.getColumnLabel(2)).thenReturn("id"); // duplicate column
name
+
+ Method method = SqlTask.class.getDeclaredMethod("resultProcess",
ResultSet.class);
+ method.setAccessible(true);
+
+ // Assert that InvocationTargetException is thrown
+ InvocationTargetException thrown = Assertions.assertThrows(
+ InvocationTargetException.class,
+ () -> method.invoke(sqlTask, mockRs));
+
+ // Check the actual cause
+ Throwable cause = thrown.getCause();
+ Assertions.assertNotNull(cause);
+ Assertions.assertInstanceOf(TaskException.class, cause,
+ "Cause should be TaskException, but was: " + cause.getClass());
+ Assertions.assertTrue(
+ cause.getMessage().contains("duplicate column name"),
+ "TaskException message should mention duplicate column name");
+ }
+
}
