Skip to content

identifier double quoting support #44

@billreynolds007

Description

@billreynolds007

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 }
  ;

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions