为何WHERE子句中的括号会导致DVM v1.1.0中UNION后JOIN执行报错？

我目前使用的是DVM v1.1.0版本，需要实现的业务逻辑为：将U_1、U_2两张表UNION后，再与第三张表J_1做LEFT OUTER JOIN，其中U_1和U_2的过滤规则为：FILTER_1VAL取值为'G'，FILTER_2VAL取值为'1'或'2'。

当我在UNION的子查询WHERE语句中使用括号控制AND/OR优先级，再将UNION结果与其他表JOIN时，系统抛出如下错误：

Invalid keyword LEFT found in FROM list

可正常执行的查询示例 #

• 不带JOIN的带括号过滤UNION查询，可返回正确结果：

SELECT * 
FROM U_1 
WHERE FILTER_1VAL = 'G' AND (FILTER_2VAL = '1' OR FILTER_2VAL = '2')

UNION

SELECT * 
FROM U_2 
WHERE FILTER_1VAL = 'G' AND (FILTER_2VAL = '1' OR FILTER_2VAL = '2')

• 不带过滤条件的UNION后JOIN查询，可正常执行：

SELECT * 
FROM
    (SELECT * FROM U_1
     UNION
     SELECT * FROM U_2) full_union
LEFT OUTER JOIN 
    J_1 joined ON full_union.field1 = joined.field1

• 不带括号的WHERE条件UNION后JOIN查询，可正常执行（但逻辑不符合业务要求，AND优先级高于OR会导致过滤规则错误）：

SELECT * 
FROM
    (SELECT * 
     FROM U_1 
     WHERE FILTER_1VAL = 'G' AND FILTER_2VAL = '1' OR FILTER_2VAL = '2'
     UNION
     SELECT * 
     FROM U_2 
     WHERE FILTER_1VAL = 'G' AND FILTER_2VAL = '1' OR FILTER_2VAL = '2') full_union
LEFT OUTER JOIN 
    J_1 joined ON full_union.field1 = joined.field1

报错的查询示例 #

当WHERE子句中添加括号控制优先级保证逻辑正确时，查询执行报错：

SELECT *
FROM (
    SELECT *
    FROM U_1
    WHERE FILTER_1VAL = 'G'
      AND (
           FILTER_2VAL = '1'
        OR FILTER_2VAL = '2'
      )
    
    UNION
    SELECT *
    FROM U_2
    WHERE FILTER_1VAL = 'G'
      AND (
           FILTER_2VAL = '1' 
        OR FILTER_2VAL = '2'
      )
  ) full_union
  LEFT OUTER JOIN J_1 joined ON full_union.field1 = joined.field1

报错详情为：

13:20:33:643, 28 Jul 2021 - Error executing statement.
13:20:33:643, 28 Jul 2021 - [DV][JDBC Driver] Invalid keyword LEFT found in FROM list
13:20:33:643, 28 Jul 2021 - Stop SQL Execution (Iteration Time = 47 ms).

问题原因与解决方案 #

问题原因 #

该问题为DVM v1.1.0版本SQL解析器的缺陷导致：解析器在识别子查询中以括号结尾的WHERE条件时，会错误判断子查询的结束位置，进而无法正确识别后续的LEFT JOIN语法，抛出关键字非法错误。

解决方案 #

两种可行方案可以规避该解析缺陷，同时保证过滤逻辑正确：

• 括号后追加恒真条件：在WHERE子句的括号后追加AND 1=1，即可让解析器正确识别WHERE条件的结束位置，查询可正常返回正确结果：

SELECT *
FROM (
    SELECT *
    FROM U_1
    WHERE FILTER_1VAL = 'G'
      AND (
           FILTER_2VAL = '1'
        OR FILTER_2VAL = '2'
      )
      AND 1=1
    
    UNION
    SELECT *
    FROM U_2
    WHERE FILTER_1VAL = 'G'
      AND (
           FILTER_2VAL = '1' 
        OR FILTER_2VAL = '2'
      )
      AND 1=1
  ) full_union
  LEFT OUTER JOIN J_1 joined ON full_union.field1 = joined.field1

• 无需括号的等价写法：使用IN运算符替换OR逻辑，无需括号即可实现相同的过滤优先级，避免触发解析缺陷：

SELECT *
FROM (
    SELECT *
    FROM U_1
    WHERE FILTER_1VAL = 'G'
      AND FILTER_2VAL IN ('1','2')
    
    UNION
    SELECT *
    FROM U_2
    WHERE FILTER_1VAL = 'G'
      AND FILTER_2VAL IN ('1','2')
  ) full_union
  LEFT OUTER JOIN J_1 joined ON full_union.field1 = joined.field1

内容的提问来源于stack exchange，提问作者Josh D