Skip to content

identifier double quoting support #44

New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Closed
billreynolds007 opened this issue Sep 13, 2020 · 1 comment
Closed

identifier double quoting support #44

billreynolds007 opened this issue Sep 13, 2020 · 1 comment

Comments

@billreynolds007
Copy link

We are scanning the SQL from a PowerBI to Postgres. PowerBI really likes to double quote things.
A query example is select "name" from "q_sample"."airports"

In the code I see references to IDENTIFIER and QUOTED_IDENTIFIER.
There was some discusssion regarding single quoting aliases here
#33

The module's syntax is based on
https://dev.mysql.com/doc/refman/5.7/en/select.html

and I read about double quotes being valid identifier characters at
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

ANSI_QUOTES
Treat " as an identifier quote character (like the quote character) and not as a string quote character. You can still use to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot
use double quotation marks to quote literal strings because they are interpreted as identifiers.

The following changes in sqlParser.jison parsed the query without complaining, but I am not sure if it is correct or the best approach.

  1. Moved the following lines above the STRING declaration line to give them priority
['"][a-zA-Z_\u4e00-\u9fa5][a-zA-Z0-9_\u4e00-\u9fa5]*["']          return 'QUOTED_IDENTIFIER'
[`].+[`]                                                          return 'QUOTED_IDENTIFIER'
  1. Below identifier_list I added
quoted_identifier
  : QUOTED_IDENTIFIER { $$ = { type: 'Identifier', value: $1 } }
  | quoted_identifier DOT QUOTED_IDENTIFIER { $$ = $1; $1.value += '.' + $3 }
  ;
  1. At the bottom of the file, I reference the new quoted_identifier
table_factor
  : quoted_identifier partitionOpt aliasOpt index_hint_list_opt { $$ = { type: 'TableFactor', value: $1, partition: $2, alias: $3.alias, hasAs: $3.hasAs, indexHintOpt: $4 } }
  | '(' selectClause ')' aliasOpt { $$ = { type: 'TableFactor', value: { type: 'SubQuery', value: $2 }, alias: $4.alias, hasAs: $4.hasAs} }
  | '(' table_references ')' { $$ = $2; $$.hasParentheses = true }
  ;
@billreynolds007
Copy link
Author

billreynolds007 commented Sep 14, 2020

Well upon further testing the suggesting fails for simple things.
I'm trying to understand the syntax at
http://dinosaur.compilertools.net/bison/bison_6.html#SEC41

to get double quoted SELECT and FROM operands around compound (aa.bb) identifiers.

@albin3 albin3 mentioned this issue Feb 4, 2021
@albin3 albin3 closed this as completed Feb 4, 2021
# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants