taozhi8833998 / taozhi8833998/node-sql-parser
Preserve the original identifier quoting
@taozhi8833998 is already working on this.
Since Oct 3, 2023.
- Dominant language
- PEG.js
- Stars
- 1k
- Forks
- 244
- PR merge metrics
- No merged PRs in 30d
Description
Describe the bug
Preserve the quoting (or absence of quoting) of identifiers in the original query
Database Engine
PostgreSQL.
Context
In Postgres, unquoted identifiers are case insensitive. So SELECT mYcoL FROM tEsT and SELECT mycol FROM test are equivalent queries. However, quoted identifiers are case sensitive. So SELECT "MyCol" FROM test is semantically different from the two previous queries.
Since the sqlify() function will always quote identifiers, it will produce SQL that is semantically different from the original, when the original used unquoted identifiers.
To Reproduce
const p = new Parser();
const ast = p.astify(`SELECT MyCol FROM "test"`, { database: 'Postgresql' });
const sql = p.sqlify(ast, { database: 'Postgresql' });
console.log(sql);
node-sql-parser version 4.11.0
Observed output
SELECT "MyCol" FROM "test". "MyCol" is rebuilt with quotes, when the original SQL was unquoted.
Expected output
SELECT MyCol FROM "test", the original quoting style is preserved for the two identifiers MyCol (unquoted) and "test" (quoted).
The solution is to track if the original identifier was quoted or not in the AST, and rebuild the SQL based on this additional information. Perhaps an additional quoted property could be added to the AST for a more detailed description of identifiers:
{
"type": "column_ref",
"table": null,
"column": {
"value": "MyCol",
"quoted": true
}
}
Of course this AST modification will break all the code that expects a string value in the column key. It should probably be considered only in a major release, or sooner with a different AST change that will be backward compatible.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.