存在WITH语句时WHERE IN子查询导致SQL报错的问题咨询

问题根源 #

你使用的AlaSQL（JS端轻量SQL查询库）存在CTE场景下的嵌套子查询解析bug：当查询中同时存在WITH定义的CTE、以及IN/EXISTS类型的嵌套子查询时，AlaSQL的语法解析器无法正确识别子查询的表上下文，尝试访问不存在的数组索引[0]时就会抛出TypeError: Cannot read property '0' of undefined报错。

可行解决方案 #

方案1：改用普通查询写法 #

你已验证无CTE的原生查询可以正常运行，无需额外修改逻辑即可满足需求。

方案2：用JOIN替代子查询过滤 #

将WHERE IN/EXISTS子查询改为INNER JOIN关联qaers表实现过滤，可兼容CTE写法：

WITH cosmetic AS (
  SELECT z.qaer_name, 
         AVG(Minutes(z.QA_Time)) AS avg_minutes, 
         COUNT(z.Zendesk_URL) AS num_tickets
  FROM zendeskData z
  -- 用JOIN替代子查询过滤
  INNER JOIN qaers q ON z.qaer_name = q.Name
  WHERE z.tags LIKE '%cosmetic%'                    
  AND CAST(z.sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
  GROUP BY z.qaer_name
)
SELECT * FROM cosmetic

方案3：提前提取过滤值替换子查询 #

先单独查询qaers表的Name字段拼接为字面量数组，再代入主查询避免嵌套子查询：

// 提前提取qaer名称列表
const qaerList = alasql('SELECT Name FROM qaers').map(item => `'${item.Name}'`).join(',')
// 代入主查询
let data = Database.alasql(`
  WITH cosmetic AS (
    SELECT qaer_name, 
          AVG(Minutes(QA_Time)) AS avg_minutes, 
          COUNT(Zendesk_URL) AS num_tickets
    FROM zendeskData
    WHERE qaer_name IN (${qaerList})
    AND tags LIKE '%cosmetic%'                    
    AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
    GROUP BY qaer_name
  )
  SELECT * 
  FROM cosmetic
`);

内容的提问来源于stack exchange，提问作者2me