Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Sample dbt sql file that fails LT02 and LT08 #5950

Open
2 of 3 tasks
vitorbaptista opened this issue Jun 5, 2024 · 0 comments
Open
2 of 3 tasks

Sample dbt sql file that fails LT02 and LT08 #5950

vitorbaptista opened this issue Jun 5, 2024 · 0 comments
Labels
bug Something isn't working

Comments

@vitorbaptista
Copy link

Search before asking

  • I searched the issues and found no similar issues.

What Happened

sqlfluff fix failed on LT02 and LT08 on my sql file.

Expected Behaviour

sqlfluff is able to fix the errors in the file.

Observed Behaviour

==== finding fixable violations ====
WARNING    Fixes for LT02 not applied, as it would result in an unparsable file. Please report this as a bug with a minimal query which demonstrates this warning.                             
WARNING    Fixes for LT08 not applied, as it would result in an unparsable file. Please report this as a bug with a minimal query which demonstrates this warning.                             
WARNING    Fixes for LT02 not applied, as it would result in an unparsable file. Please report this as a bug with a minimal query which demonstrates this warning.                             
WARNING    Fixes for LT08 not applied, as it would result in an unparsable file. Please report this as a bug with a minimal query which demonstrates this warning.                             
== [models/marts/dates.sql] FAIL                                                                                                                                                               
L:  15 | P:   4 | LT08 | Blank line expected but not found after CTE closing
                       | bracket. [layout.cte_newline]
L:  16 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  17 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  17 | P:  17 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  17 | P:  38 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  18 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  18 | P:  39 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  18 | P:  71 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  19 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  19 | P:  41 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  20 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  20 | P:  14 | AL01 | Implicit/explicit aliasing of table.
                       | [aliasing.table]
L:  23 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  23 | P:  20 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  23 | P:  41 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  24 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  24 | P:  41 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  24 | P:  68 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  25 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  25 | P:  41 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  25 | P:  67 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  26 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  26 | P:   3 | AL09 | Column should not be self-aliased.
                       | [aliasing.self_alias.column]
L:  26 | P:  57 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  27 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  28 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  28 | P:  18 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  29 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  29 | P:  18 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  29 | P:  45 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  30 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  30 | P:   8 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  30 | P:  26 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  31 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  31 | P:  54 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  32 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  32 | P:  59 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  33 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  33 | P:  38 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  33 | P:  57 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  34 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  35 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  36 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  37 | P:   8 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  37 | P:  29 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  38 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  38 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  38 | P:  38 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  38 | P:  56 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  39 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  39 | P:  40 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  39 | P:  63 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  40 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  40 | P:  62 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  40 | P:  83 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  41 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  41 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  41 | P:  39 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  41 | P:  58 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  42 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  43 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  43 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  43 | P:  39 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  43 | P:  49 | LT03 | Found trailing binary operator ' '. Expected only
                       | leading near line breaks. [layout.operators]
L:  44 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  44 | P:  13 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  44 | P:  41 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  45 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  45 | P:  19 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  46 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  46 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  46 | P:  46 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  46 | P:  76 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  47 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  48 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  48 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  48 | P:  46 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  48 | P:  56 | LT03 | Found trailing binary operator ' '. Expected only
                       | leading near line breaks. [layout.operators]
L:  49 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  49 | P:  13 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  49 | P:  48 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  50 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  50 | P:  30 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  51 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  51 | P:  47 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  51 | P:  81 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  52 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  52 | P:  69 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  52 | P: 101 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  53 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  53 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  53 | P:  40 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  53 | P:  60 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  54 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  54 | P:  50 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  55 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  55 | P:  54 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  56 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  56 | P:  60 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  57 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  57 | P:  62 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  58 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  59 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  59 | P:  11 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  59 | P:  39 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  59 | P:  49 | LT03 | Found trailing binary operator ' '. Expected only
                       | leading near line breaks. [layout.operators]
L:  60 | P:   1 | LT02 | Expected indent of 8 spaces. [layout.indent]
L:  60 | P:  13 | CP01 | Keywords must be upper case.
                       | [capitalisation.keywords]
L:  60 | P:  42 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  61 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  61 | P:  20 | LT04 | Found trailing comma ','. Expected only leading near
                       | line breaks. [layout.commas]
L:  62 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  62 | P:  41 | CP05 | Datatypes must be upper case.
                       | [capitalisation.types]
L:  63 | P:   1 | LT02 | Line should not be indented. [layout.indent]
L:  86 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  86 | P:  15 | AL01 | Implicit/explicit aliasing of table.
                       | [aliasing.table]
  [6 templating/parsing errors found]
==== no fixable linting violations found ====
All Finished 📜 🎉!
  [118 unfixable linting violations found]

How to reproduce

Try to run sqlfluff fix on the following SQL file:

{{
  config(
    unique_key = 'calendar_date',
    materialized = 'table'
  )
}}
WITH dates AS (
  {{
    dbt_utils.date_spine(
      datepart="day",
      start_date="TO_DATE('01/01/2018', 'mm/dd/yyyy')",
      end_date="DATE_TRUNC('year', current_date)   INTERVAL '2 year'"
    )
  }}
), prior_dates AS (
  SELECT
    d.date_day::date AS calendar_date,
    (d.date_day - INTERVAL '1 year')::date AS prior_year_calendar_date,
    (d.date_day - INTERVAL '364 days')::date AS prior_year_comp_date
  FROM dates d
)
SELECT
  d.calendar_date::date AS calendar_date,
  (d.calendar_date - INTERVAL '1 day')::date AS prior_calendar_date,
  (d.calendar_date   INTERVAL '1 day')::date AS next_calendar_date,
  d.prior_year_calendar_date AS prior_year_calendar_date,
  CASE
    WHEN EXTRACT(dow FROM d.calendar_date) = 0 THEN 7
    ELSE EXTRACT(dow FROM d.calendar_date)::int
  END::int AS day_of_week,
  TO_CHAR(d.calendar_date, 'Day') AS day_of_week_name,
  TO_CHAR(d.calendar_date, 'Dy') AS day_of_week_name_short,
  EXTRACT(DAY FROM d.calendar_date)::int AS day_of_month,
  ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('quarter', d.calendar_date)
      ORDER BY d.calendar_date
    )::int AS day_of_quarter,
  EXTRACT(doy FROM d.calendar_date)::int AS day_of_year,
  DATE_TRUNC('week', d.calendar_date)::date AS week_start_date,
  (DATE_TRUNC('week', d.calendar_date)   INTERVAL '6 days')::date AS week_end_date,
  EXTRACT(week FROM d.calendar_date)::int AS week_of_year,
  (
		EXTRACT(year FROM d.calendar_date)::int * 100  
    EXTRACT(week FROM d.calendar_date)::int
  ) AS week_number,
  EXTRACT(week FROM d.prior_year_comp_date)::int AS prior_year_week_of_year,
  (
		EXTRACT(year FROM d.prior_year_comp_date)::int * 100  
    EXTRACT(week FROM d.prior_year_comp_date)::int
  ) AS prior_year_week_number,
  DATE_TRUNC('week', d.prior_year_comp_date)::date AS prior_year_week_start_date,
  (DATE_TRUNC('week', d.prior_year_comp_date)   INTERVAL '6 days')::date AS prior_year_week_end_date,
  EXTRACT(month FROM d.calendar_date)::int AS month_of_year,
  TO_CHAR(d.calendar_date, 'Month') AS month_name,
  TO_CHAR(d.calendar_date, 'Mon') AS month_name_short,
  TO_CHAR(d.calendar_date, 'Month YYYY') AS month_year_name,
  TO_CHAR(d.calendar_date, 'Mon YY') AS month_year_name_short,
  (
		EXTRACT(year FROM d.calendar_date)::int * 100  
    EXTRACT(month FROM d.calendar_date)::int
  ) AS month_number,
  DATE_TRUNC('month', d.calendar_date)::date AS month_start_date,
  {{ dbt.last_day('d.calendar_date', 'month') }} AS month_end_date,
  (
		EXTRACT(year FROM d.prior_year_calendar_date)::int * 100  
    EXTRACT(month FROM d.prior_year_calendar_date)::int
  ) AS prior_year_month_number,
  DATE_TRUNC('month', d.prior_year_calendar_date)::date AS prior_year_month_start_date,
  {{ dbt.last_day('d.prior_year_calendar_date', 'month') }} AS prior_year_month_end_date,
  TO_CHAR(d.prior_year_calendar_date, 'Month') AS prior_year_month_year_name,
  TO_CHAR(d.prior_year_calendar_date, 'Mon') AS prior_year_month_year_name_short,
  EXTRACT(quarter FROM d.calendar_date)::int AS quarter_of_year,
  (
		EXTRACT(year FROM d.calendar_date)::int * 10  
    EXTRACT(quarter FROM d.calendar_date)::int
  ) AS quarter_number,
  DATE_TRUNC('quarter', d.calendar_date)::date AS quarter_start_date,
  {{ dbt.last_day('d.calendar_date', 'quarter') }} AS quarter_end_date,
  DATE_TRUNC('quarter', (DATE_TRUNC('quarter', d.calendar_date) - INTERVAL '1 day'))::date AS previous_quarter_start_date,
  (DATE_TRUNC('quarter', d.calendar_date) - INTERVAL '1 day')::date AS previous_quarter_end_date,
  EXTRACT(year FROM d.calendar_date)::int AS year_number,
  DATE_TRUNC('year', d.calendar_date)::date AS year_start_date,
  {{ dbt.last_day('d.calendar_date', 'year') }} AS year_end_date,
  EXTRACT(dow FROM d.calendar_date) IN (0, 6) AS is_weekend
FROM
  prior_dates d
ORDER BY 1 DESC

Dialect

postgres

Version

sqlfluff, version 3.0.7
Python 3.11.2

Configuration

[sqlfluff]
dialect = postgres
templater = jinja
max_line_length = 120
processes = -1

[sqlfluff:templater:dbt]
project_dir = ./

[sqlfluff:indentation]
allow_implicit_indents = True

[sqlfluff:rules:aliasing.length]
min_alias_length = 3

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = upper

[sqlfluff:rules:convention.not_equal]
preferred_not_equal_style = c_style

[sqlfluff:layout:type:comma]
line_position = leading

[sqlfluff:rules:layout.select_targets]
wildcard_policy = multiple

[sqlfluff:rules:structure.join_condition_order]
preferred_first_table_in_join_clause = later

Are you willing to work on and submit a PR to address the issue?

  • Yes I am willing to submit a PR!

Code of Conduct
@vitorbaptista vitorbaptista added the bug Something isn't working label Jun 5, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@vitorbaptista