Unwanted columns reordering in SELECT while fixing

See original GitHub issue

Hello,

After executing sqlfluff fix, columns in select statement has been reordered and I need them to be ordered in a specific order.

input

SELECT
	distinct communication_fhir_reference as MAIL_ID,
	CASE
		when (lf_domain = 'domain1'
		AND s_value LIKE '%char2%')
		OR (lf_domain = 'domain2'
		AND s_value LIKE '%char1%') THEN s_value
    end as EXPEDITEUR,
	fcf.medium_display as DESTINATAIRE,
	TO_CHAR(sent_at,'YYYY-MM-dd HH:MM:ss') as DATE,
	0 as TAILLE
FROM
	table1

I executed poetry run sqlfluff fix sql_file.sql --dialect postgres -f

And I got

SELECT DISTINCT
    communication_fhir_reference AS mail_id,
    fcf.medium_display AS destinataire,
    0 AS taille,
    CASE
        WHEN (lf_domain = 'domain1'
               AND s_value LIKE '%char2%')
               OR (lf_domain = 'domain2'
               AND s_value LIKE '%char1%') THEN s_value
    END AS expediteur,
    TO_CHAR(sent_at, 'YYYY-MM-dd HH:MM:ss') AS date
FROM
    table1

As you can see, colums has been reordered, and i need them not to be

Logs

==== finding fixable violations ====
WARNING    One fix for L003 not applied, it would re-cause the same error. 
== [sql_file.sql] FAIL
L:   1 | P:   1 | L034 | Use wildcards then simple targets before calculations
                       | and aggregates in select statements.
L:   1 | P:   1 | L041 | SELECT clause modifiers such as DISTINCT must be on the
                       | same line as SELECT.
L:   2 | P:   2 | L003 | Indentation not consistent with line #1
L:   2 | P:   2 | L010 | Keywords must be consistently upper case.
L:   2 | P:  40 | L010 | Keywords must be consistently upper case.
L:   2 | P:  43 | L014 | Unquoted identifiers must be consistently lower case.
L:   3 | P:   1 | L004 | Incorrect indentation type found in file.
L:   4 | P:   1 | L004 | Incorrect indentation type found in file.
L:   4 | P:   3 | L010 | Keywords must be consistently upper case.
L:   5 | P:   1 | L004 | Incorrect indentation type found in file.
L:   5 | P:   3 | L003 | Indent expected and not found compared to line #4
L:   5 | P:   3 | L039 | Unnecessary whitespace found.
L:   6 | P:   1 | L004 | Incorrect indentation type found in file.
L:   6 | P:   3 | L003 | Indent expected and not found compared to line #4
L:   6 | P:   3 | L039 | Unnecessary whitespace found.
L:   7 | P:   1 | L004 | Incorrect indentation type found in file.
L:   7 | P:   3 | L003 | Indent expected and not found compared to line #4
L:   7 | P:   3 | L039 | Unnecessary whitespace found.
L:   8 | P:   5 | L010 | Keywords must be consistently upper case.
L:   8 | P:   9 | L010 | Keywords must be consistently upper case.
L:   8 | P:  12 | L014 | Unquoted identifiers must be consistently lower case.
L:   9 | P:   1 | L004 | Incorrect indentation type found in file.
L:   9 | P:  21 | L010 | Keywords must be consistently upper case.
L:   9 | P:  24 | L014 | Unquoted identifiers must be consistently lower case.
L:  10 | P:   1 | L004 | Incorrect indentation type found in file.
L:  10 | P:  17 | L008 | Commas should be followed by a single whitespace unless
                       | followed by a comment.
L:  10 | P:  17 | L008 | Commas should be followed by a single whitespace unless
                       | followed by a comment.
L:  10 | P:  41 | L010 | Keywords must be consistently upper case.
L:  10 | P:  44 | L014 | Unquoted identifiers must be consistently lower case.
L:  11 | P:   1 | L004 | Incorrect indentation type found in file.
L:  11 | P:   4 | L010 | Keywords must be consistently upper case.
L:  11 | P:   7 | L014 | Unquoted identifiers must be consistently lower case.
L:  13 | P:   1 | L004 | Incorrect indentation type found in file.
==== fixing violations ====
33 fixable linting violations found
FORCE MODE: Attempting fixes...
Persisting Changes...
== [sql_file.sql] PASS
Done. Please check your files to confirm.
  [3 unfixable linting violations found]

Version

▶ poetry run sqlfluff --version  
sqlfluff, version 0.6.6

Configuration

[sqlfluff]
templater = jinja

Any idea ?

Issue Analytics

  • State:closed
  • Created 2 years ago
  • Comments:8 (6 by maintainers)

github_iconTop GitHub Comments

1reaction
WittierDinosaurcommented, Sep 28, 2021

If you exclude rule L034 this will not happen

0reactions
tunethewebcommented, Sep 28, 2021

Raised #1471 for that issue as a bit noisy to include in this one.

Read more comments on GitHub >

github_iconTop Results From Across the Web

How to Reorder Multiple Columns in Power Query with Drag ...
1. Select the last column in the table, hold the Shift key, and then select the first column in the table. · 2....
Read more >
Column Reordering Disorder - Pitfall #4 in #PowerBI ...
To reorder columns, you can simply select the columns and drag and drop them to their new position. Pitfall 4 - Reorder columns...
Read more >
How To Remove/Delete/Reorder/Move Columns in Power BI ...
How to Remove a Column In Power BI, How to Delete columns in Power BI, How to Reorder Columns in Power BI, How...
Read more >
Why exactly doesn't PostgreSQL allow reordering the columns?
If you explicitly specify the columns in the SELECT list, the order of the columns in the table is irrelevant.
Read more >
`dplyr::select` without reordering columns - Stack Overflow
I am looking for an easy, concise way to use dplyr::select without rearranging columns.
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found