taozhi8833998 / taozhi8833998/node-sql-parser

Preserve the original identifier quoting

Open
#1,607 1 comment 0 reactions 1 assignee View on GitHub

@taozhi8833998 is already working on this.

Since Oct 3, 2023.

enhancement
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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.