morrySnow commented on code in PR #65539:
URL: https://github.com/apache/doris/pull/65539#discussion_r3575790039
##########
fe/fe-core/src/test/java/org/apache/doris/mtmv/MTMVSchemaChangeTest.java:
##########
@@ -91,4 +110,204 @@ public void testDropBaseTable() throws Exception {
Assertions.assertTrue(mtmv.getSchemaChangeVersion() == 1);
Assertions.assertTrue(mtmv.getStatus().getState().equals(MTMVState.SCHEMA_CHANGE));
}
-}
+
+ // ======================================================
+ // 新增: MTMV ADD COLUMN 测试（按 StarRocks test 风格）
+ // ======================================================
+
+ /**
+ * ✅ 场景 1: 简单 SELECT — 核心链路正常
+ * <pre>
+ * 原始: SELECT k1, v1 FROM t1
+ * 改写: SELECT k1, v1, k1 + 1 AS `new_col` FROM t1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnSimpleSelect() throws Exception {
+ String originSql = "SELECT k1, v1 FROM t1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 +
1", "new_col");
+ Assertions.assertEquals("SELECT k1, v1, k1 + 1 AS `new_col` FROM t1",
rewritten);
+ }
+
+ /**
+ * ✅ 场景 2: WHERE 条件
+ * <pre>
+ * 原始: SELECT k1, v1 FROM t1 WHERE k1 > 1
+ * 改写: SELECT k1, v1, v1 * 2 AS `v1_x2` FROM t1 WHERE k1 > 1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithWhere() throws Exception {
+ String originSql = "SELECT k1, v1 FROM t1 WHERE k1 > 1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "v1 *
2", "v1_x2");
+ Assertions.assertEquals(
+ "SELECT k1, v1, v1 * 2 AS `v1_x2` FROM t1 WHERE k1 > 1",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 3: GROUP BY 聚合 — 多个聚合字段
+ * <pre>
+ * 原始: SELECT k1, SUM(v1) AS total, AVG(v1) AS avg_v FROM t1 GROUP BY k1
+ * 改写: SELECT k1, SUM(v1) AS total, AVG(v1) AS avg_v, k1 * k1 AS
`k1_squared` FROM t1 GROUP BY k1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithGroupBy() throws Exception {
+ String originSql = "SELECT k1, SUM(v1) AS total, AVG(v1) AS avg_v FROM
t1 GROUP BY k1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 *
k1", "k1_squared");
+ Assertions.assertEquals(
+ "SELECT k1, SUM(v1) AS total, AVG(v1) AS avg_v, k1 * k1 AS
`k1_squared` FROM t1 GROUP BY k1",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 4: ORDER BY 降序
+ * <pre>
+ * 原始: SELECT k1, v1 FROM t1 ORDER BY k1 DESC
+ * 改写: SELECT k1, v1, CASE WHEN v1 >= 20 THEN 'big' ELSE 'small' END AS
`v1_label` FROM t1 ORDER BY k1 DESC
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithOrderBy() throws Exception {
+ String originSql = "SELECT k1, v1 FROM t1 ORDER BY k1 DESC";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql,
+ "CASE WHEN v1 >= 20 THEN 'big' ELSE 'small' END", "v1_label");
+ Assertions.assertEquals(
+ "SELECT k1, v1, CASE WHEN v1 >= 20 THEN 'big' ELSE 'small' END
AS `v1_label` "
+ + "FROM t1 ORDER BY k1 DESC",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 5: 标量子查询（多表）
+ * <pre>
+ * 原始: SELECT k1, (SELECT max(v1) FROM t2) AS mv FROM t1 WHERE k1 > 1
+ * 改写: SELECT k1, (SELECT max(v1) FROM t2) AS mv, k1 + 1 AS `new_col` FROM
t1 WHERE k1 > 1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithSubqueryFrom() throws Exception {
+ String originSql = "SELECT k1, (SELECT max(v1) FROM t2) AS mv FROM t1
WHERE k1 > 1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 +
1", "new_col");
+ Assertions.assertEquals(
+ "SELECT k1, (SELECT max(v1) FROM t2) AS mv, k1 + 1 AS
`new_col` FROM t1 WHERE k1 > 1",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 6: CTE（WITH 子句 — SelectColumnClauseIndexFinder 跳过 CTE）
+ * <pre>
+ * 原始: WITH cte AS (SELECT k1 FROM t2) SELECT k1 FROM cte
+ * 改写: WITH cte AS (SELECT k1 FROM t2) SELECT k1, k1 * 2 AS `x``` FROM cte
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithCte() throws Exception {
+ String originSql = "WITH cte AS (SELECT k1 FROM t2) SELECT k1 FROM
cte";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 *
2", "x`");
+ Assertions.assertEquals(
+ "WITH cte AS (SELECT k1 FROM t2) SELECT k1, k1 * 2 AS `x```
FROM cte",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 7: JOIN（多表）
+ * <pre>
+ * 原始: SELECT a.id, a.v1, b.name FROM t3 a JOIN t4 b ON a.id = b.id
+ * 改写: SELECT a.id, a.v1, b.name, v1 + 10 AS `v1_plus` FROM t3 a JOIN t4 b
ON a.id = b.id
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithJoin() throws Exception {
+ String originSql = "SELECT a.id, a.v1, b.name FROM t3 a JOIN t4 b ON
a.id = b.id";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "v1 +
10", "v1_plus");
+ Assertions.assertEquals(
+ "SELECT a.id, a.v1, b.name, v1 + 10 AS `v1_plus` FROM t3 a
JOIN t4 b ON a.id = b.id",
+ rewritten);
+ }
+
+ /**
+ * ✅ 场景 8: SELECT *（星号展开）
+ * <pre>
+ * 原始: SELECT * FROM t1
+ * 改写: SELECT *, k1 * 2 AS `x` FROM t1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnSelectStar() throws Exception {
+ String originSql = "SELECT * FROM t1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 *
2", "x");
+ Assertions.assertEquals("SELECT *, k1 * 2 AS `x` FROM t1", rewritten);
+ }
+
+ /**
+ * ✅ 场景 9: 反引号列名转义（双倍反引号）
+ * <pre>
+ * 原始: SELECT k1, v1 FROM t1
+ * 改写: SELECT k1, v1, k1 * 2 AS `x``` FROM t1（列名 x` 的四个反引号）
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithBacktickColumnName() throws Exception {
+ String originSql = "SELECT k1, v1 FROM t1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "k1 *
2", "x`");
+ Assertions.assertEquals("SELECT k1, v1, k1 * 2 AS `x``` FROM t1",
rewritten);
+ }
+
+ /**
+ * ✅ 场景 10: 多字段非聚合
+ * <pre>
+ * 原始: SELECT k1, v1, v2 FROM t1
+ * 改写: SELECT k1, v1, v2, v1 + v2 AS `sum_v` FROM t1
+ * </pre>
+ */
+ @Test
+ public void testAddColumnWithMultipleColumns() throws Exception {
+ String originSql = "SELECT k1, v1, v2 FROM t1";
+ String rewritten = MTMV.rewriteQuerySqlForAddColumn(originSql, "v1 +
v2", "sum_v");
+ Assertions.assertEquals("SELECT k1, v1, v2, v1 + v2 AS `sum_v` FROM
t1", rewritten);
+ }
+
+ /**
+ * ⚠️ 场景 11: UNION ALL — Doris 只改第一个 SELECT 子句
+ * <pre>
+ * 原始: SELECT k1, v1 FROM t1 WHERE k1 <= 3
+ * UNION ALL
+ * SELECT k1, v1 FROM t1 WHERE k1 > 3
+ * 改写: SELECT k1, v1, IF(v1 >= 30, 'high', 'low') AS `comment` FROM t1
WHERE k1 <= 3
+ * UNION ALL
+ * SELECT k1, v1 FROM t1 WHERE k1 > 3
+ * （第二个 SELECT 没有新列 → 列数不匹配）
+ * </pre>
+ * ⚠️ StarRocks 用 AST2SQLVisitor 遍历所有 SELECT，不存在此问题。
+ */
+ @Test
+ public void testAddColumnUnionAllFirstOnly() throws Exception {
Review Comment:
**UNION ALL silently produces broken SQL**: The
`SelectColumnClauseIndexFinder` only finds the **first** `SELECT` clause. For
UNION ALL queries like `SELECT a FROM t1 UNION ALL SELECT a FROM t2`, only the
first branch gets the new column appended, producing a column-count mismatch
error at refresh time.
The test documents this as a known limitation (⚠️) but ships the code
without any guard. Consider either:
1. **Rejecting** the ALTER at analyze time if the query contains
UNION/INTERSECT/EXCEPT (with a clear error message to the user), OR
2. **Walking ALL** `selectColumnClause` nodes (not just the first), applying
the overlay to each branch — this is how StarRocks handles it with
`AST2SQLVisitor`.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
