1. 首页
  2. 问答
  3. [alibaba/druid]format 后sql不满足语法格式

[alibaba/druid]format 后sql不满足语法格式

2025-11-11 848 views
0

druid 版本 1.2.9 原本SQL:select from mc_job a inner join (mc_role b inner join mc_source c on b.id = c.id) as tem2 on tem2.user_id = a.id ; //能正确执行.SQLUtils.format(sql, DbType.postgresql)格式化后的sql: SELECT FROM mc_job a INNER JOIN ((mc_role b INNER JOIN mc_source c ON b.id = c.id) AS tem2) ON tem2.user_id = a.id; //不能执行 格式化时在inner join 的右边加()时把别名也包进去了. 如果我将原本的sql改为:select from mc_job a inner join (select from mc_role b inner join mc_source c on b.id = c.id) as tem2 on tem2.user_id = a.id ; 格式化后为: SELECT FROM mc_job a INNER JOIN ( SELECT FROM mc_role b INNER JOIN mc_source c ON b.id = c.id ) tem2 ON tem2.user_id = a.id; 这样格式化后是没有问题的.我能提供的信息就这些了,大佬看下哪的问题吧.

capitalR

回答

5

今天发现mysql也有一样的问题,类似于select * from t1 inner join (t2 inner join t3) as tt2 on t1.id = tt2.id ...这样的sql也会format成错误的sql语句,错误的现象和我提出的pgsql的一样.不知道大佬你的这次fix能不能覆盖到mysql.@ShenFeng312

capitalR

7

已经修复了 但是没有被merge 如果比较着急的话拉我的分支自己打个包吧

ShenFeng312

5

原始mysql sqlA如下: SELECT Sub7B.ID1 AS ID1, Sub8C.ID1 AS ID1_3 FROM (SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 5 AND 10 ) Sub7B RIGHT JOIN **( (SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 15 ) Sub8__C INNER JOIN (SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 20 ) Sub9D ON Sub8C.ID1 = Sub9D.ID1 )** ON Sub7B.ID1 = Sub8C.ID1

经过druid1.2.16解析之后得到sqlB： SELECT Sub7B.ID1 AS ID1, Sub8C.ID1 AS ID1_3 FROM ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 5 AND 10 ) Sub7B RIGHT JOIN ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 15 ) Sub8__C INNER JOIN ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 20 ) Sub9D ON Sub8C.ID1 = Sub9D.ID1 ON Sub7B.ID1 = Sub8C.ID1 可以看出 RIGHT JOIN之后的括号被删去了 由于#4765改动导致

继续把sqlB交给druid SQLParser解析 得到sqlC: SELECT Sub7B.ID1 AS ID1, Sub8C.ID1 AS ID1_3 FROM ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 5 AND 10 ) Sub7B RIGHT JOIN ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 15 ) Sub8__C INNER JOIN ( SELECT ID AS ID1 FROM test.coffee_chain WHERE id BETWEEN 10 AND 20 ) Sub9D ON Sub8C.ID1 = Sub9D.ID1 AND Sub7B.ID1 = Sub8C.ID1

可以看出sqlC关联条件多了一个and 导致sqlC 语法错误。

kevinSoso