Skip to content
New issue

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

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

Already on GitHub? Sign in to your account

identifier double quoting support #44

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants