FeatReq: insert "as" for column alias in Parser

[yan-hic]

We have a 3rd party app that issues generic sql statements like so:
select 'mytype' type, 'myname' name from test_table
... which errors out because name and type are keywords to PG.

Instead of hardcoding a replace in a forked repo to make such statements work, can the Parser module be enhanced to detect column (or table) aliases and insert the as keyword if not passed already ?
E.g. select 'mytype' as type, 'myname' as name from test_table is valid for Postgres.

I wouldn't know how to exactly as there is currently no method to parse column names. Maybe some regexps...

[davecramer]

@yiga2 we have avoided writing a complete SQL parser, and even if we did I'm not sure putting 'as' in front of them is going to help you anyway ?

[yan-hic]

Well, it does help as Postgres then detects that these reserved keywords are actually aliases here.

Give it a try:
select 'mytype' type, 'myname' name --> ERROR: syntax error at or near "type"
vs.
select 'mytype' as type, 'myname' as name --> returns resultset with type and name columns

[davecramer]

@yiga2 fair enough, however this would require a full fledged SQL parser and impose a parsing penalty on everyone...

[vlsi]

@yiga2 , I'm not sure if you would like that, but there's http://calcite.apache.org
Calcite has Babel parser. The aim of Babel parser is to parse "whatever SQL you have" (e.g. Oracle, PostgreSQL, Hive), and you can do something with output (e.g. convert to a PG-specific format).

You can setup Apache Calcite as a "proxy" on top of pgjdbc (so you submit your queries to Calcite, and it sends relevant queries back to PG behind the scenes).
Or you can use Calcite as a parsing library to interpret SQL into PG format.

Relevant class in question: https://calcite.apache.org/apidocs/org/apache/calcite/sql/babel/Babel.html

mailing list: dev@calcite.apache.org

[yan-hic]

Thanks @vlsi. Since I had to customize to address #716 already, I rather just add a regexp replace in the mix in our own version of the Parser.

Calcite in the middle would not alleviate the need to change getTypeInfo(), to get the expected result i.e. varchar as first occurence of sqltype 12

[changkejun]

How about this ? To add " to aliases. It can execute.
select "mytype" "type", "myname" "name"

[yan-hic]

Why not. To Dave's point, it would require some bulletproof logic to quote or prefix with 'as'. Not sure the demand or traction is there so we just forked and customized to our needs.

[davecramer]

@changkejun and the poor soul that writes select MyType type, MyName name would be screaming at us.