UpdateBuilder缺失了CTE的表名

[Sora233]

表结构如下：

CREATE TABLE `test`
(
    id int primary key not null,
    a  int default 0   not null
);
insert into `test` (id, a) values (1, 1), (2, 2), (3, 3);

例子

func main() {
	sqlbuilder.DefaultFlavor = sqlbuilder.MySQL
	cte := sqlbuilder.CTETable("cte", "id", "a").As(
		sqlbuilder.UnionAll(
			sqlbuilder.Buildf("SELECT 1, 1"),
			sqlbuilder.Buildf("SELECT 100, 100"),
		),
	)

	sb := sqlbuilder.With(cte).Update("test").
		Set("test.a = cte.a").
		Where("test.id = cte.id")

	sqlStr, args := sb.Build()
	interpolate, err := sqlbuilder.DefaultFlavor.Interpolate(sqlStr, args)
	if err != nil {
		panic(err)
	}
	fmt.Println(interpolate)
}

Output:

WITH cte (id, a) AS ((SELECT 1, 1) UNION ALL (SELECT 100, 100)) UPDATE test SET test.a = cte.a WHERE test.id = cte.id

我在mysql8.4中执行报错[42S22][1054] Unknown column 'cte.id' in 'where clause'

正确的sql是

WITH cte (id, a) AS ((SELECT 1, 1) UNION ALL (SELECT 100, 100)) UPDATE test, cte SET test.a = cte.a WHERE test.id = cte.id;

另外在postgresql中正确的写法是：

WITH cte (id, a) AS ((SELECT 1, 1) UNION ALL (SELECT 100, 100))
UPDATE test
SET a = cte.a
FROM cte
WHERE test.id = cte.id;

[huandu]

当前 CTETable 只会在 SELECT 中自动加上表名，其他情况 UPDATE/DELETE 不会处理。你这里提到的 FROM 的问题就是一个比较不好处理的问题，MySQL 得将 CTE 放到 UPDATE 之后，而其他数据库（符合 SQL 标准的做法）是使用 FROM 来引用 CTE 表。我考虑一下，增加一下这个功能的支持吧。

[huandu]

@Sora233 我实现了一下相关逻辑，请帮忙看看 #179 吧，主要看一下 cte_test.go 里面新加的两个 example 函数，看是否生成的 SQL 符合预期，我怕生成的 PostgreSQL 语句有误。感谢。

[huandu]

@Sora233 我先合并了，如果还有问题，欢迎继续提出来。

[arslanovdi]

@Sora233 我实现了一下相关逻辑，请帮忙看看 #179 吧，主要看一下 cte_test.go 里面新加的两个 example 函数，看是否生成的 SQL 符合预期，我怕生成的 PostgreSQL 语句有误。感谢。

Hello! For PostgreSQL it generates incorrectly
Your example:
// WITH users (user_id) AS (SELECT user_id FROM vip_users) UPDATE orders FROM users SET orders.transport_fee = 0 WHERE users.user_id = orders.user_id

It should be:
// WITH users (user_id) AS (SELECT user_id FROM vip_users) UPDATE orders SET transport_fee = 0 FROM users WHERE users.user_id = orders.user_id

[huandu]

@arslanovdi You're right. It's a bug. Could you create a new issue with your comment?