vscode-sqltools Formatter breaking comments

Formatter breaking comments

Open acdha opened this issue 3 years ago • 4 comments

Describe the bug

The formatter sometimes - but not always - breaks commented SQL queries.

I noticed this with a SQL file:

-- This is optimizing the query which is used to summarize the download history
-- in 10 second intervals:
--
-- SELECT COUNT(*) AS numrows
-- FROM (downloaddata)
-- WHERE moddate_m > DATE_SUB(NOW(), INTERVAL 10 SECOND);

Expected behavior

The input should be unmodified

Actual behaviour

-- This is optimizing the query which is used to summarize the download history
-- in 10 second intervals:
--
-- SELECT COUNT(*) AS numrows
-- FROM (downloaddata)
- -
WHERE moddate_m > DATE_SUB(NOW(), INTERVAL 10 SECOND);

Desktop (please complete the following information):

SQLTools Version: 0.23.0
VSCode Version: 1.48.2
OS: Mac

Aug 28 '20 00:08 acdha

I am having the same issue.

Oct 07 '20 15:10 yunshiuan

Sounds to be an old one #97, but still there for me too.

Dec 01 '20 09:12 Jule-

@mtxr I took a quick look at your code and it seems that you handle new comment lines correctly outside of query blocks but not inside.

I am not sure but do you think adding || this.getCommentToken(input) here could handle this case? https://github.com/mtxr/vscode-sqltools/blob/7c382e8e02b77e7df6147cbc67d19339aabd3a10/packages/formatter/src/core/Tokenizer.ts#L313-L325

Dec 01 '20 09:12 Jule-

You can try to format this in your playground to reproduce 2 different issues with line comments:

select *
from table
where a >= some_date1
and a <  some_date2  -- to
-- and b >= some_date3  -- and
and a <  some_date2
-- and b >= some_date3  -- and
and b <  some_date4  -- where, select etc.
-- and 1 = 1;

It becomes:

SELECT *
FROM TABLE
WHERE a >= some_date1
AND a < some_date2 -- to
- -
AND b >= some_date3 -- and
AND a < some_date2 - -
AND b >= some_date3 -- and
AND b < some_date4 -- where, select etc.
-- and 1 = 1;

And it sounds like your playground use the class operator for the alone -, don't know if it is just rendering stuff or if it could leads to what is really tokenized by your parser.

Dec 01 '20 09:12 Jule-

Should be fixed by #791