SQL: placeholder in identifier

[Appalled]

Hi dear ctags developers, I run into a problem while generating tags for the following SQL script with placeholder:

create table  database.tb_name${dt} as 
select col_a, col_b from database.tb_name

table 'tb_name${dt}' is not parsed as an identifier, and the same situation occurs to column name. Would you consider enhancing the sql parser to allow identifier with placeholder such as '${a_1}' ? Thanks.

[masatake]

Where can I find the definition of SQL syntax for ${dt} ?

When extending a parser, I need information about the syntax.

such as '${a_1}' ?

"such as" is not enough to extend the parser.

[Appalled]

Where can I find the definition of SQL syntax for ${dt} ?

When extending a parser, I need information about the syntax.

such as '${a_1}' ?

"such as" is not enough to extend the parser.

I didn't find the strict definition but a page in the hive language mannual: LanguageManual VariableSubstitution. Hope it could be helpful. Maybe I should search more.

[masatake]

Thank you for the information. Is the notation, ${var} is HiveQL specific? I also want to know the file name extension for HiveQL. .sql?

[masatake]

See #3172. You can try the pull request supporting ${var}. See the instructions written in https://docs.ctags.io/en/latest/contributions.html#testing-a-pr-locally-before-being-merged .

$ cat input.sql 
-- Based on issue #3169 opened by @Appalled

create table  database.tb_name${dt} as 
select col_a, col_b from database.tb_name;

create table  database.tb_name${dt}${dt0} as 
select col_a, col_b from database.tb_name;

create table  database.${dt1}tb_name${dt}${dt0} as 
select col_a, col_b from database.tb_name;

create table  database.${dt1}tb_name${dt}${dt0}Z as 
select col_a, col_b from database.tb_name;

create table  database.tb_${dt2}_name as 
select col_${key0}, col_${key1} from database.tb_name;
$ u-ctags --sort=no -o - input.sql 
tb_name${dt}    input.sql   /^create table  database.tb_name${dt} as $/;"   t
col_a   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}
col_b   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}
tb_name${dt}${dt0}  input.sql   /^create table  database.tb_name${dt}${dt0} as $/;" t
col_a   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}${dt0}
col_b   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}${dt0}
${dt1}tb_name${dt}${dt0}    input.sql   /^create table  database.${dt1}tb_name${dt}${dt0} as $/;"   t
col_a   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}
col_b   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}
${dt1}tb_name${dt}${dt0}Z   input.sql   /^create table  database.${dt1}tb_name${dt}${dt0}Z as $/;"  t
col_a   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}Z
col_b   input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}Z
tb_${dt2}_name  input.sql   /^create table  database.tb_${dt2}_name as $/;" t
col_${key0} input.sql   /^select col_${key0}, col_${key1} from database.tb_name;$/;"    E   table:tb_${dt2}_name
col_${key1} input.sql   /^select col_${key0}, col_${key1} from database.tb_name;$/;"    E   table:tb_${dt2}_name
$ 

[Appalled]

See #3172. You can try the pull request supporting ${var}. See the instructions written in https://docs.ctags.io/en/latest/contributions.html#testing-a-pr-locally-before-being-merged .

$ cat input.sql 
-- Based on issue #3169 opened by @Appalled

create table  database.tb_name${dt} as 
select col_a, col_b from database.tb_name;

create table  database.tb_name${dt}${dt0} as 
select col_a, col_b from database.tb_name;

create table  database.${dt1}tb_name${dt}${dt0} as 
select col_a, col_b from database.tb_name;

create table  database.${dt1}tb_name${dt}${dt0}Z as 
select col_a, col_b from database.tb_name;

create table  database.tb_${dt2}_name as 
select col_${key0}, col_${key1} from database.tb_name;
$ u-ctags --sort=no -o - input.sql 
tb_name${dt}  input.sql   /^create table  database.tb_name${dt} as $/;"   t
col_a input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}
col_b input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}
tb_name${dt}${dt0}    input.sql   /^create table  database.tb_name${dt}${dt0} as $/;" t
col_a input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}${dt0}
col_b input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:tb_name${dt}${dt0}
${dt1}tb_name${dt}${dt0}  input.sql   /^create table  database.${dt1}tb_name${dt}${dt0} as $/;"   t
col_a input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}
col_b input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}
${dt1}tb_name${dt}${dt0}Z input.sql   /^create table  database.${dt1}tb_name${dt}${dt0}Z as $/;"  t
col_a input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}Z
col_b input.sql   /^select col_a, col_b from database.tb_name;$/;"    E   table:${dt1}tb_name${dt}${dt0}Z
tb_${dt2}_name    input.sql   /^create table  database.tb_${dt2}_name as $/;" t
col_${key0}   input.sql   /^select col_${key0}, col_${key1} from database.tb_name;$/;"    E   table:tb_${dt2}_name
col_${key1}   input.sql   /^select col_${key0}, col_${key1} from database.tb_name;$/;"    E   table:tb_${dt2}_name
$ 

Sorry for the late reply. I was on vacation. It works like a charm, and thanks again for your immediate response.

[masatake]

Is the notation, ${var} is HiveQL specific?

I would like to get the answer to this question. I wonder where this enhancement comes from. Do you want this enhancement because you are using HiveQL?

[Appalled]

Is the notation, ${var} is HiveQL specific?

I would like to get the answer to this question. I wonder where this enhancement comes from. Do you want this enhancement because you are using HiveQL?

I believe it's not HiveQL specific. This problem emerges while I am using an in-company ad hoc query tool with spark and presto query engines on hive tables. In my experience, sql dialects of Spark, Presto and Hive all support variable substitution. And I just use '.sql' as file name extension.

[masatake]

Thank you.

[masatake]

I looked into Presto a bit. I run `grep '\${' in the files copied from https://github.com/prestodb/presto/tree/master/presto-docs/src/main/sphinx . I cannot find such a pattern used as part of SQL.

[masatake]

About, spark and presto, we cannot find the syntax definition for ${...}. So I would like to forget it.

I focus on Hive. I wonder whether we can say "ctags supports HiveSQL".

I had some questions.

And I just use '.sql' as file name extension.

I found .q is used. https://github.com/apache/hive/blob/ac4020789149115c71bcee9b85577fff7dd01f46/ql/src/test/queries/clientnegative/materialized_view_create_failure.q

How do you think about this? Do you think ctags should recognize foo.q as a SQL file by default?

Just parsing ${...} is enough for supporting HiveSQL? How about set var ... ?

I think the change in #3172 is too ad-hoc. So I wonder whether I should merge the pull request or not.

[masatake]

After seme researching I guess you write about hplsql. Am I correct?