SQLBot Version
1.10.0 专业版
Run Mode
安装包
Describe the bug
当 AI 生成包含WITH CTE多段聚合 SQL 时，系统表权限校验抛出异常：
SQL contains unauthorized tables: xxx_agg. Allowed tables: xxx
其中提示的未授权表均为 SQL 内自定义 CTE 别名，并非数据库真实物理表,
CTE 别名不应参与白名单校验；仅等物理表需校验
下面是示例 SQL
WITH
sales_base AS (
SELECT
o.order_no,
o.order_date,
o.bu_code,
o.dept_code,
o.region_code,
o.customer_code,
o.owner_account,
o.channel,
o.order_status,
o.order_amount,
o.gross_profit,
o.cost_amount,
SUM(i.qty) AS item_qty,
SUM(i.line_amount) AS item_amount,
COUNT(DISTINCT i.product_code) AS sku_cnt
FROM fact_sales_order o
INNER JOIN fact_sales_order_item i
ON o.order_no = i.order_no
WHERE o.order_date >= '2025-01-01'
AND o.order_date < '2026-01-01'
AND o.order_status <> '已取消'
GROUP BY
o.order_no, o.order_date, o.bu_code, o.dept_code, o.region_code,
o.customer_code, o.owner_account, o.channel, o.order_status,
o.order_amount, o.gross_profit, o.cost_amount
),
exp_agg AS (
SELECT
bu_code, dept_code, owner_account,
ROUND(SUM(amount), 2) AS expense_amt,
COUNT() AS expense_cnt
FROM fact_expense
WHERE expense_date >= '2025-01-01'
AND expense_date < '2026-01-01'
AND status = '已报销'
GROUP BY bu_code, dept_code, owner_account
),
visit_agg AS (
SELECT
bu_code, dept_code, customer_code, owner_account,
COUNT() AS visit_cnt,
SUM(CASE WHEN result_level = '成功' THEN 1 ELSE 0 END) AS visit_ok_cnt,
ROUND(SUM(opportunity_amt), 2) AS opportunity_amt
FROM fact_customer_visit
WHERE visit_date >= '2025-01-01'
AND visit_date < '2026-01-01'
GROUP BY bu_code, dept_code, customer_code, owner_account
),
inv_agg AS (
SELECT
i.bu_code,
i.dept_code,
i.product_code,
ROUND(AVG(i.stock_amount), 2) AS avg_stock_amt,
ROUND(MAX(i.qty_onhand), 0) AS max_onhand
FROM fact_inventory_daily i
INNER JOIN dim_warehouse w
ON i.warehouse_code = w.warehouse_code
WHERE i.biz_date >= '2025-01-01'
AND i.biz_date < '2026-01-01'
GROUP BY i.bu_code, i.dept_code, i.product_code
),
att_agg AS (
SELECT
bu_code, dept_code, owner_account,
COUNT(*) AS attend_days,
SUM(CASE WHEN attend_status = '迟到' THEN 1 ELSE 0 END) AS late_cnt,
ROUND(SUM(overtime_hours), 2) AS ot_hours
FROM fact_hr_attendance
WHERE attend_date >= '2025-03-01'
AND attend_date < '2026-07-01'
GROUP BY bu_code, dept_code, owner_account
),
joined AS (
SELECT
bu.bu_name,
d.dept_name,
r.region_name,
c.customer_name,
c.customer_level,
c.industry,
e.emp_name AS owner_name,
e.job_title,
s.channel,
DATE_FORMAT(s.order_date, '%Y-%m') AS order_month,
s.order_no,
s.order_amount,
s.gross_profit,
s.item_qty,
s.sku_cnt,
COALESCE(x.expense_amt, 0) AS expense_amt,
COALESCE(v.visit_cnt, 0) AS visit_cnt,
COALESCE(v.visit_ok_cnt, 0) AS visit_ok_cnt,
COALESCE(v.opportunity_amt, 0) AS opportunity_amt,
COALESCE(a.late_cnt, 0) AS late_cnt,
COALESCE(a.ot_hours, 0) AS ot_hours,
ROUND(s.gross_profit / NULLIF(s.order_amount, 0), 4) AS gross_margin,
ROUND(
COALESCE(v.visit_ok_cnt, 0) / NULLIF(COALESCE(v.visit_cnt, 0), 0),
4
) AS visit_success_rate,
ROW_NUMBER() OVER (
PARTITION BY bu.bu_code
ORDER BY s.order_amount DESC
) AS bu_sales_rank
FROM sales_base s
INNER JOIN dim_bu bu
ON s.bu_code = bu.bu_code
INNER JOIN dim_dept d
ON s.dept_code = d.dept_code
AND s.bu_code = d.bu_code
LEFT JOIN dim_region r
ON s.region_code = r.region_code
INNER JOIN dim_customer c
ON s.customer_code = c.customer_code
INNER JOIN dim_employee e
ON s.owner_account = e.account
LEFT JOIN exp_agg x
ON s.bu_code = x.bu_code
AND s.dept_code = x.dept_code
AND s.owner_account = x.owner_account
LEFT JOIN visit_agg v
ON s.bu_code = v.bu_code
AND s.dept_code = v.dept_code
AND s.customer_code = v.customer_code
AND s.owner_account = v.owner_account
LEFT JOIN att_agg a
ON s.bu_code = a.bu_code
AND s.dept_code = a.dept_code
AND s.owner_account = a.owner_account
)
SELECT
bu_name AS 事业群,
dept_name AS 部门,
region_name AS 区域,
customer_level AS 客户等级,
customer_name AS 客户,
owner_name AS 负责人,
job_title AS 岗位,
channel AS 渠道,
order_month AS 月份,
order_no AS 订单号,
ROUND(order_amount, 2) AS 销售额,
ROUND(gross_profit, 2) AS 毛利,
gross_margin AS 毛利率,
item_qty AS 销量,
sku_cnt AS SKU数,
expense_amt AS 费用金额,
visit_cnt AS 拜访次数,
visit_success_rate AS 拜访成功率,
opportunity_amt AS 商机金额,
late_cnt AS 迟到次数,
ot_hours AS 加班工时,
bu_sales_rank AS 事业群内销售排名
FROM joined
WHERE bu_sales_rank <= 20
ORDER BY 事业群, 事业群内销售排名, 销售额 DESC
To Reproduce
SQLBot Version
1.10.0 专业版
Run Mode
安装包
Describe the bug
当 AI 生成包含WITH CTE多段聚合 SQL 时，系统表权限校验抛出异常：
SQL contains unauthorized tables: xxx_agg. Allowed tables: xxx
其中提示的未授权表均为 SQL 内自定义 CTE 别名，并非数据库真实物理表,
CTE 别名不应参与白名单校验；仅等物理表需校验
下面是示例 SQL
WITH
sales_base AS (
SELECT
o.order_no,
o.order_date,
o.bu_code,
o.dept_code,
o.region_code,
o.customer_code,
o.owner_account,
o.channel,
o.order_status,
o.order_amount,
o.gross_profit,
o.cost_amount,
SUM(i.qty) AS item_qty,
SUM(i.line_amount) AS item_amount,
COUNT(DISTINCT i.product_code) AS sku_cnt
FROM fact_sales_order o
INNER JOIN fact_sales_order_item i
ON o.order_no = i.order_no
WHERE o.order_date >= '2025-01-01'
AND o.order_date < '2026-01-01'
AND o.order_status <> '已取消'
GROUP BY
o.order_no, o.order_date, o.bu_code, o.dept_code, o.region_code,
o.customer_code, o.owner_account, o.channel, o.order_status,
o.order_amount, o.gross_profit, o.cost_amount
),
exp_agg AS (
SELECT
bu_code, dept_code, owner_account,
ROUND(SUM(amount), 2) AS expense_amt,
COUNT() AS expense_cnt
FROM fact_expense
WHERE expense_date >= '2025-01-01'
AND expense_date < '2026-01-01'
AND status = '已报销'
GROUP BY bu_code, dept_code, owner_account
),
visit_agg AS (
SELECT
bu_code, dept_code, customer_code, owner_account,
COUNT() AS visit_cnt,
SUM(CASE WHEN result_level = '成功' THEN 1 ELSE 0 END) AS visit_ok_cnt,
ROUND(SUM(opportunity_amt), 2) AS opportunity_amt
FROM fact_customer_visit
WHERE visit_date >= '2025-01-01'
AND visit_date < '2026-01-01'
GROUP BY bu_code, dept_code, customer_code, owner_account
),
inv_agg AS (
SELECT
i.bu_code,
i.dept_code,
i.product_code,
ROUND(AVG(i.stock_amount), 2) AS avg_stock_amt,
ROUND(MAX(i.qty_onhand), 0) AS max_onhand
FROM fact_inventory_daily i
INNER JOIN dim_warehouse w
ON i.warehouse_code = w.warehouse_code
WHERE i.biz_date >= '2025-01-01'
AND i.biz_date < '2026-01-01'
GROUP BY i.bu_code, i.dept_code, i.product_code
),
att_agg AS (
SELECT
bu_code, dept_code, owner_account,
COUNT(*) AS attend_days,
SUM(CASE WHEN attend_status = '迟到' THEN 1 ELSE 0 END) AS late_cnt,
ROUND(SUM(overtime_hours), 2) AS ot_hours
FROM fact_hr_attendance
WHERE attend_date >= '2025-03-01'
AND attend_date < '2026-07-01'
GROUP BY bu_code, dept_code, owner_account
),
joined AS (
SELECT
bu.bu_name,
d.dept_name,
r.region_name,
c.customer_name,
c.customer_level,
c.industry,
e.emp_name AS owner_name,
e.job_title,
s.channel,
DATE_FORMAT(s.order_date, '%Y-%m') AS order_month,
s.order_no,
s.order_amount,
s.gross_profit,
s.item_qty,
s.sku_cnt,
COALESCE(x.expense_amt, 0) AS expense_amt,
COALESCE(v.visit_cnt, 0) AS visit_cnt,
COALESCE(v.visit_ok_cnt, 0) AS visit_ok_cnt,
COALESCE(v.opportunity_amt, 0) AS opportunity_amt,
COALESCE(a.late_cnt, 0) AS late_cnt,
COALESCE(a.ot_hours, 0) AS ot_hours,
ROUND(s.gross_profit / NULLIF(s.order_amount, 0), 4) AS gross_margin,
ROUND(
COALESCE(v.visit_ok_cnt, 0) / NULLIF(COALESCE(v.visit_cnt, 0), 0),
4
) AS visit_success_rate,
ROW_NUMBER() OVER (
PARTITION BY bu.bu_code
ORDER BY s.order_amount DESC
) AS bu_sales_rank
FROM sales_base s
INNER JOIN dim_bu bu
ON s.bu_code = bu.bu_code
INNER JOIN dim_dept d
ON s.dept_code = d.dept_code
AND s.bu_code = d.bu_code
LEFT JOIN dim_region r
ON s.region_code = r.region_code
INNER JOIN dim_customer c
ON s.customer_code = c.customer_code
INNER JOIN dim_employee e
ON s.owner_account = e.account
LEFT JOIN exp_agg x
ON s.bu_code = x.bu_code
AND s.dept_code = x.dept_code
AND s.owner_account = x.owner_account
LEFT JOIN visit_agg v
ON s.bu_code = v.bu_code
AND s.dept_code = v.dept_code
AND s.customer_code = v.customer_code
AND s.owner_account = v.owner_account
LEFT JOIN att_agg a
ON s.bu_code = a.bu_code
AND s.dept_code = a.dept_code
AND s.owner_account = a.owner_account
)
SELECT
bu_name AS 事业群,
dept_name AS 部门,
region_name AS 区域,
customer_level AS 客户等级,
customer_name AS 客户,
owner_name AS 负责人,
job_title AS 岗位,
channel AS 渠道,
order_month AS 月份,
order_no AS 订单号,
ROUND(order_amount, 2) AS 销售额,
ROUND(gross_profit, 2) AS 毛利,
gross_margin AS 毛利率,
item_qty AS 销量,
sku_cnt AS SKU数,
expense_amt AS 费用金额,
visit_cnt AS 拜访次数,
visit_success_rate AS 拜访成功率,
opportunity_amt AS 商机金额,
late_cnt AS 迟到次数,
ot_hours AS 加班工时,
bu_sales_rank AS 事业群内销售排名
FROM joined
WHERE bu_sales_rank <= 20
ORDER BY 事业群, 事业群内销售排名, 销售额 DESC
To Reproduce