使用SQLFluff检查PostgreSQL时generate_series()列别名解析报错

问题描述 #

使用SQLFluff对PostgreSQL查询进行代码检查时，为generate_series()生成的列设置别名出现解析违规，但该查询在PostgreSQL中可正常运行。

原查询 #

CREATE MATERIALIZED VIEW dummy AS
WITH base_times AS (
    SELECT
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours')::date AS base_start,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '12 weeks' + INTERVAL '1 day')::date AS base_end,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '1 day')::date AS base_next_start
),
bucket AS (
    SELECT (generate_series(
        time_bucket('7 days', base_end, base_start)::date,
        time_bucket('7 days', base_next_start, base_start)::date,
        INTERVAL '7 days'
    ) + INTERVAL '6 days')::date AS end
    FROM base_times
) select * from bucket;

错误信息 #

[L: 14, P:  1]      |                                                            whitespace:  '    '
[L: 14, P:  5]      |                                                            [META] dedent:
[L: 14, P:  5]      |                                                            end_bracket:  ')'
[L: 14, P:  6]      |                                                    whitespace:  ' '
[L: 14, P:  7]      |                                                    binary_operator:  '+'
[L: 14, P:  8]      |                                                    whitespace:  ' '
[L: 14, P:  9]      |                                                    datetime_literal:
[L: 14, P:  9]      |                                                        datetime_type_identifier:
[L: 14, P:  9]      |                                                            keyword:  'INTERVAL'
[L: 14, P: 17]      |                                                        whitespace:  ' '
[L: 14, P: 18]      |                                                        quoted_literal:  "'6 days'"
[L: 14, P: 26]      |                                                [META] dedent:
[L: 14, P: 26]      |                                                end_bracket:  ')'
[L: 14, P: 27]      |                                            casting_operator:  '::'
[L: 14, P: 29]      |                                            data_type:
[L: 14, P: 29]      |                                                datetime_type_identifier:
[L: 14, P: 29]      |                                                    keyword:  'date'
[L: 14, P: 33]      |                                [META] dedent:
[L: 14, P: 33]      |                                whitespace:                   ' '
[L: 14, P: 34]      |                                unparsable:                   !! Expected: 'Nothing here.'
[L: 14, P: 34]      |                                    word:                     'AS'
[L: 14, P: 36]      |                                    whitespace:               ' '
[L: 14, P: 37]      |                                    word:                     'end'
[L: 14, P: 40]      |                            newline:                          '\n'

==== parsing violations ====
L:  14 | P:  34 |  PRS | Line 14, Position 34: Found unparsable section: 'AS end'

解决方案 #

SQLFluff的解析器对多层嵌套表达式的别名识别存在偏差，可通过以下两种方式调整SQL结构来解决：

方法1：为整个表达式添加外层括号 #

给包含generate_series()的完整计算表达式额外套一层括号，让SQLFluff能正确识别这是一个整体表达式，从而正常解析后续的别名：

CREATE MATERIALIZED VIEW dummy AS
WITH base_times AS (
    SELECT
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours')::date AS base_start,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '12 weeks' + INTERVAL '1 day')::date AS base_end,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '1 day')::date AS base_next_start
),
bucket AS (
    SELECT ((generate_series(
        time_bucket('7 days', base_end, base_start)::date,
        time_bucket('7 days', base_next_start, base_start)::date,
        INTERVAL '7 days'
    ) + INTERVAL '6 days')::date) AS end
    FROM base_times
) select * from bucket;

方法2：拆分表达式到独立CTE #

将generate_series()的结果单独放在一个CTE中，先为其设置别名，再进行后续计算，简化SQLFluff的解析逻辑：

CREATE MATERIALIZED VIEW dummy AS
WITH base_times AS (
    SELECT
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours')::date AS base_start,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '12 weeks' + INTERVAL '1 day')::date AS base_end,
        (CURRENT_TIMESTAMP AT TIME ZONE 'america/los_angeles' + INTERVAL '2 hours' - INTERVAL '1 day')::date AS base_next_start
),
series AS (
    SELECT generate_series(
        time_bucket('7 days', base_end, base_start)::date,
        time_bucket('7 days', base_next_start, base_start)::date,
        INTERVAL '7 days'
    ) AS series_val
    FROM base_times
),
bucket AS (
    SELECT (series_val + INTERVAL '6 days')::date AS end
    FROM series
) select * from bucket;

原因说明 #

PostgreSQL原生支持多层嵌套表达式后直接设置别名的写法，但SQLFluff的解析规则暂未完全适配这种结构，通过调整表达式的嵌套层级或拆分逻辑，可让SQLFluff正确识别别名定义，同时不影响SQL的执行效果。

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