Feature Request: Ignore and warn unrecognized syntax and continue formatting the text

[Ebola-Chan-bot]

Describe the Feature

Provides an extension option to ignore unrecognized syntax instead of reporting an error.

Why do you want this feature?

I know that there are many common PROCEDURE syntax that the extension will not support in a short time. But can we ignore and warn these unsupported syntax for a while, instead of just reporting an error and giving up formatting the whole text?

In any case, you are a code formatter, not a syntax checker. As far as I know, the general practice in the field of code formatters is that you should not require the code to be completely well-formed, but to format those well-formed parts as much as possible.

I think you can just skip and warn when you encounter syntax that you don't recognize and start over from the next semicolon. Would this logic be difficult to implement?

[nene]

Unfortunately this would be pretty difficult to implement. It's not so much a problem with the prettier plugin, but rather with the parser. Also, while this might not be common behavior of many formatters, it's how the core Prettier itself works. When you give it syntactically incorrect JavaScript, it will fail to format it.

[Ebola-Chan-bot]

But the right to determine whether the syntax is correct is in your hands, isn't it?

Error: Syntax Error: Unexpected "PROCEDURE"
Was expecting to see: "EXTERNAL", "FOREIGN", "FULLTEXT", "GLOBAL", "INDEX", "LOCAL", "MATERIALIZED", "RECURSIVE", "SEARCH", "SNAPSHOT", "SPATIAL", "TABLE", "TEMP", "TEMPORARY", "TRIGGER", "UNIQUE", "UNLOGGED", "VECTOR", "VIEW", "VIRTUAL", or whitespace
--> c:\Users\vhtmf\Documents\MATLAB\MATLAB-Extension\+MATLAB\+Database\安装存储过程\_MATLAB_反序列化.sql:2:12
  |
2 | OR REPLACE PROCEDURE `_MATLAB_反序列化` (

From the log I can see that you listed some acceptable keywords, which did not include the actual keyword encountered so the error. I wonder if there should be an inner logical structure like CASE-WHEN? Then you can just add an ELSE branch to handle all the cases that are not in the list: no processing, output as-is. Don't let Prettier know there may be syntax errors. An alternative workaround is prettier-ignore. Prettier appears to have provided a way of using special comments to mark pieces of code that should not be processed. However, your plugin doesn't seem to support it yet.

[nene]

But the right to determine whether the syntax is correct is in your hands, isn't it?

Correct. I also maintain the parser.

Regarding the specific error. It would be helpful if you provided the actual code that's causing you problems. Also, please state which SQL dialect you're using - it's hard for me to guess. The CREATE PROCEDURE syntax is supported for PostgreSQL and BigQuery.

An alternative workaround is prettier-ignore. Prettier appears to have provided a way of using special comments to mark pieces of code that should not be processed. However, your plugin doesn't seem to support it yet.

While it's true that this plugin doesn't yet support these ignore comments, I should point out that even in official Prettier, these comments only work when the parser is able to fully parse the code.

[Ebola-Chan-bot]

I want to CREATE PROCEDURE for MariaDB. But as I understand it, from what you're saying, plugins of Prettier family are either fully applicable or not applicable at all, impossible to be in a state of partially applicable for a while… Long code with just one unsupported keyword disables the entire formatter… extremely unrobust…

[nene]

Yes, that's the downside of this architecture. That's why the support of MariaDB is also labeled "Experimental! expect crashes".

There are other SQL formatting tools out there which don't have this limitation.

[joshmossas]

Hello I'm running into this issue when trying to format sql written for sqlc.

With sqlc you write SQL queries that look like this:

-- name: GetAccount :one
SELECT * FROM account
WHERE id = ? LIMIT 1;

-- name: CreateAccount :execresult
INSERT INTO 
    account ( id, first_name, middle_name, last_name, email, bio )
VALUES ( ?, ?, ?, ?, ?, ? );

This will create corresponding go functions GetAccount and CreateAccount respectively, where the ? designates parameters that the function needs to accept.

Essentially it's an SQL templating system that compiles to various languages. However this formatter breaks because it doesn't seeing the ? character as a valid input.

For example that first query I provided gives me the following error:

[error] database/queries.sql: Error: Syntax Error: Unexpected " "
[error] Was expecting to see: [0-9]
[error] --> /my-project/database/queries.sql:3:13
[error]   |
[error] 3 | WHERE id = ? LIMIT 1;
[error]   |             ^
[error]     at parse (/my-project/node_modules/.pnpm/[email protected]/node_modules/sql-parser-cst/lib/main.js:40:19)
[error]     at Object.parse (/my-project/node_modules/.pnpm/[email protected]/node_modules/prettier-plugin-sql-cst/dist/index.js:41:76)
[error]     at parse5 (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:19837:24)
[error]     at async coreFormat (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:20385:25)
[error]     at async formatWithCursor (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:20597:14)
[error]     at async formatFiles (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3235:18)
[error]     at async main (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3887:5)
[error]     at async Module.run (file:///my-project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3833:5)

[joshmossas]

Oh nvm I literally just found the sqlParamsTypes option. So adding the following to my prettier config fixed my issue:

{
    "overrides": [
        {
            "files": "*.sql",
            "options": {
                // this line
                "sqlParamTypes": ["?"],
            }
        }
    ]
}

[nene]

Also, there is now sqlAcceptUnsupportedGrammar option which resolves this whole issue.

Though it should be used with caution, as it will just silently skip any unrecognized SQL. Better to only turn it on after bumping into problems.

[joshmossas]

@nene Actually I tried setting sqlAcceptUnsupportedGrammar to true and it still failed for me.

For example I have the following sql:

CREATE TABLE account (
    id BINARY(16) NOT NULL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    first_name VARCHAR(200) NOT NULL,
    middle_name VARCHAR(200) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    email VARCHAR(400) UNIQUE,
    phone VARCHAR(22),
    bio text
);

And it gives me the following error event with sqlAcceptUnsupportedGrammar enabled

[error] database/schema.sql: Error: Syntax Error: Unexpected "ON"
[error] Was expecting to see: "!=", "%", "&", ")", "*", "+", ",", "-", "->", "->>", "/", "<", "<<", "<=", "<=>", "<>", "=", "==", ">", ">=", ">>", "AND", "AS", "AUTOINCREMENT", "AUTO_INCREMENT", "BETWEEN", "CHECK", "COLLATE", "CONSTRAINT", "DEFAULT", "DEFERRABLE", "DIV", "GENERATED", "GLOB", "IN", "INITIALLY", "IS", "ISNULL", "KEY", "LIKE", "MATCH", "MOD", "NOT", "NOTNULL", "NULL", "OR", "PRIMARY", "REFERENCES", "REGEXP", "RLIKE", "UNIQUE", "|", "||", or whitespace
[error] --> /my_project/database/schema.sql:4:52
[error]   |
[error] 4 |     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
[error]   |                                                    ^
[error]     at parse (/my_project/node_modules/.pnpm/[email protected]/node_modules/sql-parser-cst/lib/main.js:40:19)
[error]     at Object.parse (/my_project/node_modules/.pnpm/[email protected]/node_modules/prettier-plugin-sql-cst/dist/index.js:41:76)
[error]     at parse5 (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:19837:24)
[error]     at async coreFormat (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:20385:25)
[error]     at async formatWithCursor (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/index.mjs:20597:14)
[error]     at async formatFiles (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3235:18)
[error]     at async main (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3887:5)
[error]     at async Module.run (file:///my_project/node_modules/.pnpm/[email protected]/node_modules/prettier/internal/cli.mjs:3833:5)

[nene]

Indeed. You're right.

That option works fine when the type of statement itself isn't recognized (like when you would attempt to parse CREATE OPERATOR ... which isn't supported at all), but if there's a problem within the statement, it fails.

BTW, which dialect of SQL is this?

[joshmossas]

@nene it's MySQL

[nene]

Interesting... because according to MySQL documentation for CREATE TABLE, there is no such syntax. The docs say that one can only use the ON UPDATE as part of REFERENCES clause. But when I test it on an actual database, it does work.

Finally after some looking around I discover that syntax for this is documented in a separate page about date initialization.

What fun... even if I had implemented all the syntax described in CREATE TABLE page, I still would have missed this bit. Who knows how much such additional syntax there is lurking in the hidden corners of the documentation...

[joshmossas]

Should I create a separate issue for supporting ON UPDATE in CREATE TABLE for Mysql?

[joshmossas]

Oh nvm I see you've added it to the MYSQL support issue in sql-parser-cst (https://github.com/nene/sql-parser-cst/issues/55)