Re: SQL column names - double quoted
SqlWriterConfig#withQuoteAllIdentifiers does this work.
However, there are some rules that the SqlDialect could control by
themselves,
especially the info from the original SqlIdentifier whether it's quoted or
not which is
parsed from the original sql.
If the SqlNode is constructed from RelNode, the SqlParserPosition is always
quoted[1].
That's why SqlWriterConfig#withQuoteAllIdentifiers does not work for most
of the identifiers
in this case.
There is only one place which does not use the quoted SqlParserPosition[2],
I suppose
it's the reason why the table identifier is not quoted in this case.
[1]
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L141
[2]
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L959
Sandeep N 于2022年5月29日周日 01:17写道：
> For the little experience I have with using Calcite I have learnt that the
> SqlPrettyWriter has a method "withQuoteAllIdentifiers" on its config but
> that seemingly only addresses table names and not column names in the where
> clause. The proposed path from Yanjing addresses columns.
>
> --Sandeep
>
> On Fri, May 27, 2022 at 8:47 AM Julian Hyde
> wrote:
>
> > Can someone remind me - is there an option for the writer to omit quotes
> > on identifiers if they are unnecessary? (This would imply that the writer
> > knows how the target dialect treats case sensitivity.)
> >
> > I may have implemented such a feature at some point. (I forget.) If so it
> > should generate nice-looking sql with a little extra effort configuring
> the
> > writer.
> >
> > Julian
> >
> > > On May 26, 2022, at 19:32, Sandeep N wrote:
> > >
> > > Thank you Yanjing, that fix addressed it.
> > >
> > > --Sandeep
> > >
> > >> On Thu, May 26, 2022 at 5:33 PM Yanjing Wang <
> zhuangzixiao...@gmail.com
> > >
> > >> wrote:
> > >>
> > >> Hi Sandeep,
> > >>
> > >> try select.toSqlString(new
> > >>
> > >>
> >
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
> > >>
> > >> Sandeep N 于2022年5月27日周五 06:03写道：
> > >>
> > >>> Hi Stamatis,
> > >>>
> > >>> Thanks for the quick response, I tried your suggestion as follows
> > >>>
> > >>> RelToSqlConverter converter = new RelToSqlConverter(new
> > >>>
> > >>>
> > >>
> >
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
> > >>> SqlSelect select = converter.visitRoot(relNode).asSelect();
> > >>> SqlWriterConfig config = SqlPrettyWriter.config().
> > >>>withCaseClausesOnNewLines(false).
> > >>>withQuoteAllIdentifiers(false);
> > >>> System.out.println(new SqlPrettyWriter(config).format(select));
> > >>>
> > >>> and I still get this
> > >>> SELECT "a"
> > >>> FROM test_table
> > >>> WHERE "b" = 'value';
> > >>>
> > >>> It is not obvious to me as to what I am doing wrong. Is the above
> block
> > >> of
> > >>> code the only pieces that I need to put in play to get that sql
> > >> generated?
> > >>>
> > >>> --Sandeep
> > >>>
> > >>> On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis <
> zabe...@gmail.com
> > >
> > >>> wrote:
> > >>>
> > Hi Sandeep,
> >
> > If you want to turn off quoting (which I am not sure if it is a good
> > >>> idea)
> > when you go from relational algebra to SQL you have to customize the
> > SqlDialect that you are using via Context#withIdentifierQuoteString
> > >> [1].
> > You can find a small example as part of RelToSqlConverterTest here
> > [2].
> >
> > Best,
> > Stamatis
> >
> > [1]
> >
> >
> > >>>
> > >>
> >
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> > [2]
> >
> >
> > >>>
> > >>
> >
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
> >
> > On Thu, May 26, 2022 at 9:18 PM Sandeep N >
> > wrote:
> >
> > > Hi all,
> > >
> > > I am pretty aware that this question may have been asked before as
> I
> > >>> have
> > > found references on stackoverflow but have yet to stumble on a
> > >> response
> > > which lays out the path on solving this.
> > >
> > > Here is the problem I am facing -
> > > I am using relational algebra to generate SQL on a table test_table
> > >>> with
> > > two columns a and b fed into the calcite Framework (see below) via
> a
> > custom
> > > catalog. Both a and b are varchar columns.
> > >
> > > FrameworkConfig config = Frameworks.newConfigBuilder().
> > >parserConfig(SqlParser.Config.DEFAULT).
> > >defaultSchema(addSchemas(rootSchema, schemaName,
> > schema)).
> > > <--- this is where I plug my custom table.
> > >traitDefs((List) null).
> > >operatorTable(sqlOperatorTable).
> > >
> > >>
Re: SQL column names - double quoted
For the little experience I have with using Calcite I have learnt that the
SqlPrettyWriter has a method "withQuoteAllIdentifiers" on its config but
that seemingly only addresses table names and not column names in the where
clause. The proposed path from Yanjing addresses columns.
--Sandeep
On Fri, May 27, 2022 at 8:47 AM Julian Hyde wrote:
> Can someone remind me - is there an option for the writer to omit quotes
> on identifiers if they are unnecessary? (This would imply that the writer
> knows how the target dialect treats case sensitivity.)
>
> I may have implemented such a feature at some point. (I forget.) If so it
> should generate nice-looking sql with a little extra effort configuring the
> writer.
>
> Julian
>
> > On May 26, 2022, at 19:32, Sandeep N wrote:
> >
> > Thank you Yanjing, that fix addressed it.
> >
> > --Sandeep
> >
> >> On Thu, May 26, 2022 at 5:33 PM Yanjing Wang >
> >> wrote:
> >>
> >> Hi Sandeep,
> >>
> >> try select.toSqlString(new
> >>
> >>
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
> >>
> >> Sandeep N 于2022年5月27日周五 06:03写道：
> >>
> >>> Hi Stamatis,
> >>>
> >>> Thanks for the quick response, I tried your suggestion as follows
> >>>
> >>> RelToSqlConverter converter = new RelToSqlConverter(new
> >>>
> >>>
> >>
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
> >>> SqlSelect select = converter.visitRoot(relNode).asSelect();
> >>> SqlWriterConfig config = SqlPrettyWriter.config().
> >>>withCaseClausesOnNewLines(false).
> >>>withQuoteAllIdentifiers(false);
> >>> System.out.println(new SqlPrettyWriter(config).format(select));
> >>>
> >>> and I still get this
> >>> SELECT "a"
> >>> FROM test_table
> >>> WHERE "b" = 'value';
> >>>
> >>> It is not obvious to me as to what I am doing wrong. Is the above block
> >> of
> >>> code the only pieces that I need to put in play to get that sql
> >> generated?
> >>>
> >>> --Sandeep
> >>>
> >>> On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis >
> >>> wrote:
> >>>
> Hi Sandeep,
>
> If you want to turn off quoting (which I am not sure if it is a good
> >>> idea)
> when you go from relational algebra to SQL you have to customize the
> SqlDialect that you are using via Context#withIdentifierQuoteString
> >> [1].
> You can find a small example as part of RelToSqlConverterTest here
> [2].
>
> Best,
> Stamatis
>
> [1]
>
>
> >>>
> >>
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> [2]
>
>
> >>>
> >>
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
>
> On Thu, May 26, 2022 at 9:18 PM Sandeep N
> wrote:
>
> > Hi all,
> >
> > I am pretty aware that this question may have been asked before as I
> >>> have
> > found references on stackoverflow but have yet to stumble on a
> >> response
> > which lays out the path on solving this.
> >
> > Here is the problem I am facing -
> > I am using relational algebra to generate SQL on a table test_table
> >>> with
> > two columns a and b fed into the calcite Framework (see below) via a
> custom
> > catalog. Both a and b are varchar columns.
> >
> > FrameworkConfig config = Frameworks.newConfigBuilder().
> >parserConfig(SqlParser.Config.DEFAULT).
> >defaultSchema(addSchemas(rootSchema, schemaName,
> schema)).
> > <--- this is where I plug my custom table.
> >traitDefs((List) null).
> >operatorTable(sqlOperatorTable).
> >
> >> programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> > true, 2)).build()
> >
> > The relational algebra is then used to generate SQL to the
> >>> AnsiSqlDialect
> > and the resultant output SQL is
> >
> > SELECT "a"
> > FROM test_table
> > WHERE "b" = 'value';
> >
> > I have the SqlWriterConfig setup as such
> >
> > SqlWriterConfig config = SqlPrettyWriter.config().
> >withCaseClausesOnNewLines(false).
> >withQuoteAllIdentifiers(false);
> >
> > I have been trying to figure what needs to change in the sql writer
> >> but
> > after scanning stackoverflow it appears this is controlled by
> > CalciteConnectionConfig and potentially not the SqlWriter. Is this
> > accurate? and if so how do I assemble the right connection config
> >> when
> >>> I
> am
> > building the custom catalog?
> >
> > P.s: I am not parsing SQL but writing directly to the relational
> >>> algebra
> > nodes so SqlParser does not come into play here.
> >
> > Any pointers on how I can go about this is appreciated, say existing
> tests
> > or code which will help me put together a configuration
Re: SQL column names - double quoted
Can someone remind me - is there an option for the writer to omit quotes on
identifiers if they are unnecessary? (This would imply that the writer knows
how the target dialect treats case sensitivity.)
I may have implemented such a feature at some point. (I forget.) If so it
should generate nice-looking sql with a little extra effort configuring the
writer.
Julian
> On May 26, 2022, at 19:32, Sandeep N wrote:
>
> Thank you Yanjing, that fix addressed it.
>
> --Sandeep
>
>> On Thu, May 26, 2022 at 5:33 PM Yanjing Wang
>> wrote:
>>
>> Hi Sandeep,
>>
>> try select.toSqlString(new
>>
>> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
>>
>> Sandeep N 于2022年5月27日周五 06:03写道：
>>
>>> Hi Stamatis,
>>>
>>> Thanks for the quick response, I tried your suggestion as follows
>>>
>>> RelToSqlConverter converter = new RelToSqlConverter(new
>>>
>>>
>> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
>>> SqlSelect select = converter.visitRoot(relNode).asSelect();
>>> SqlWriterConfig config = SqlPrettyWriter.config().
>>>withCaseClausesOnNewLines(false).
>>>withQuoteAllIdentifiers(false);
>>> System.out.println(new SqlPrettyWriter(config).format(select));
>>>
>>> and I still get this
>>> SELECT "a"
>>> FROM test_table
>>> WHERE "b" = 'value';
>>>
>>> It is not obvious to me as to what I am doing wrong. Is the above block
>> of
>>> code the only pieces that I need to put in play to get that sql
>> generated?
>>>
>>> --Sandeep
>>>
>>> On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis
>>> wrote:
>>>
Hi Sandeep,
If you want to turn off quoting (which I am not sure if it is a good
>>> idea)
when you go from relational algebra to SQL you have to customize the
SqlDialect that you are using via Context#withIdentifierQuoteString
>> [1].
You can find a small example as part of RelToSqlConverterTest here [2].
Best,
Stamatis
[1]
>>>
>> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
[2]
>>>
>> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
On Thu, May 26, 2022 at 9:18 PM Sandeep N
wrote:
> Hi all,
>
> I am pretty aware that this question may have been asked before as I
>>> have
> found references on stackoverflow but have yet to stumble on a
>> response
> which lays out the path on solving this.
>
> Here is the problem I am facing -
> I am using relational algebra to generate SQL on a table test_table
>>> with
> two columns a and b fed into the calcite Framework (see below) via a
custom
> catalog. Both a and b are varchar columns.
>
> FrameworkConfig config = Frameworks.newConfigBuilder().
>parserConfig(SqlParser.Config.DEFAULT).
>defaultSchema(addSchemas(rootSchema, schemaName,
schema)).
> <--- this is where I plug my custom table.
>traitDefs((List) null).
>operatorTable(sqlOperatorTable).
>
>> programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> true, 2)).build()
>
> The relational algebra is then used to generate SQL to the
>>> AnsiSqlDialect
> and the resultant output SQL is
>
> SELECT "a"
> FROM test_table
> WHERE "b" = 'value';
>
> I have the SqlWriterConfig setup as such
>
> SqlWriterConfig config = SqlPrettyWriter.config().
>withCaseClausesOnNewLines(false).
>withQuoteAllIdentifiers(false);
>
> I have been trying to figure what needs to change in the sql writer
>> but
> after scanning stackoverflow it appears this is controlled by
> CalciteConnectionConfig and potentially not the SqlWriter. Is this
> accurate? and if so how do I assemble the right connection config
>> when
>>> I
am
> building the custom catalog?
>
> P.s: I am not parsing SQL but writing directly to the relational
>>> algebra
> nodes so SqlParser does not come into play here.
>
> Any pointers on how I can go about this is appreciated, say existing
tests
> or code which will help me put together a configuration which gets me
>>> to
> this sql
>
> SELECT a
> FROM test_table
> WHERE b = 'value';
>
> Thanks in advance.
>
> --Sandeep
>
>>>
>>
Re: SQL column names - double quoted
Thank you Yanjing, that fix addressed it.
--Sandeep
On Thu, May 26, 2022 at 5:33 PM Yanjing Wang
wrote:
> Hi Sandeep,
>
> try select.toSqlString(new
>
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
>
> Sandeep N 于2022年5月27日周五 06:03写道：
>
> > Hi Stamatis,
> >
> > Thanks for the quick response, I tried your suggestion as follows
> >
> > RelToSqlConverter converter = new RelToSqlConverter(new
> >
> >
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
> > SqlSelect select = converter.visitRoot(relNode).asSelect();
> > SqlWriterConfig config = SqlPrettyWriter.config().
> > withCaseClausesOnNewLines(false).
> > withQuoteAllIdentifiers(false);
> > System.out.println(new SqlPrettyWriter(config).format(select));
> >
> > and I still get this
> > SELECT "a"
> > FROM test_table
> > WHERE "b" = 'value';
> >
> > It is not obvious to me as to what I am doing wrong. Is the above block
> of
> > code the only pieces that I need to put in play to get that sql
> generated?
> >
> > --Sandeep
> >
> > On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis
> > wrote:
> >
> > > Hi Sandeep,
> > >
> > > If you want to turn off quoting (which I am not sure if it is a good
> > idea)
> > > when you go from relational algebra to SQL you have to customize the
> > > SqlDialect that you are using via Context#withIdentifierQuoteString
> [1].
> > > You can find a small example as part of RelToSqlConverterTest here [2].
> > >
> > > Best,
> > > Stamatis
> > >
> > > [1]
> > >
> > >
> >
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> > > [2]
> > >
> > >
> >
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
> > >
> > > On Thu, May 26, 2022 at 9:18 PM Sandeep N
> > > wrote:
> > >
> > > > Hi all,
> > > >
> > > > I am pretty aware that this question may have been asked before as I
> > have
> > > > found references on stackoverflow but have yet to stumble on a
> response
> > > > which lays out the path on solving this.
> > > >
> > > > Here is the problem I am facing -
> > > > I am using relational algebra to generate SQL on a table test_table
> > with
> > > > two columns a and b fed into the calcite Framework (see below) via a
> > > custom
> > > > catalog. Both a and b are varchar columns.
> > > >
> > > > FrameworkConfig config = Frameworks.newConfigBuilder().
> > > > parserConfig(SqlParser.Config.DEFAULT).
> > > > defaultSchema(addSchemas(rootSchema, schemaName,
> > > schema)).
> > > > <--- this is where I plug my custom table.
> > > > traitDefs((List) null).
> > > > operatorTable(sqlOperatorTable).
> > > >
> programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> > > > true, 2)).build()
> > > >
> > > > The relational algebra is then used to generate SQL to the
> > AnsiSqlDialect
> > > > and the resultant output SQL is
> > > >
> > > > SELECT "a"
> > > > FROM test_table
> > > > WHERE "b" = 'value';
> > > >
> > > > I have the SqlWriterConfig setup as such
> > > >
> > > > SqlWriterConfig config = SqlPrettyWriter.config().
> > > > withCaseClausesOnNewLines(false).
> > > > withQuoteAllIdentifiers(false);
> > > >
> > > > I have been trying to figure what needs to change in the sql writer
> but
> > > > after scanning stackoverflow it appears this is controlled by
> > > > CalciteConnectionConfig and potentially not the SqlWriter. Is this
> > > > accurate? and if so how do I assemble the right connection config
> when
> > I
> > > am
> > > > building the custom catalog?
> > > >
> > > > P.s: I am not parsing SQL but writing directly to the relational
> > algebra
> > > > nodes so SqlParser does not come into play here.
> > > >
> > > > Any pointers on how I can go about this is appreciated, say existing
> > > tests
> > > > or code which will help me put together a configuration which gets me
> > to
> > > > this sql
> > > >
> > > > SELECT a
> > > > FROM test_table
> > > > WHERE b = 'value';
> > > >
> > > > Thanks in advance.
> > > >
> > > > --Sandeep
> > > >
> > >
> >
>
Re: SQL column names - double quoted
Hi Sandeep,
In my project, I used the same way as what Yanjing proposed and it works
well.
Best,
Chunwei
On Fri, May 27, 2022 at 8:34 AM Yanjing Wang
wrote:
> Hi Sandeep,
>
> try select.toSqlString(new
>
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
>
> Sandeep N 于2022年5月27日周五 06:03写道：
>
> > Hi Stamatis,
> >
> > Thanks for the quick response, I tried your suggestion as follows
> >
> > RelToSqlConverter converter = new RelToSqlConverter(new
> >
> >
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
> > SqlSelect select = converter.visitRoot(relNode).asSelect();
> > SqlWriterConfig config = SqlPrettyWriter.config().
> > withCaseClausesOnNewLines(false).
> > withQuoteAllIdentifiers(false);
> > System.out.println(new SqlPrettyWriter(config).format(select));
> >
> > and I still get this
> > SELECT "a"
> > FROM test_table
> > WHERE "b" = 'value';
> >
> > It is not obvious to me as to what I am doing wrong. Is the above block
> of
> > code the only pieces that I need to put in play to get that sql
> generated?
> >
> > --Sandeep
> >
> > On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis
> > wrote:
> >
> > > Hi Sandeep,
> > >
> > > If you want to turn off quoting (which I am not sure if it is a good
> > idea)
> > > when you go from relational algebra to SQL you have to customize the
> > > SqlDialect that you are using via Context#withIdentifierQuoteString
> [1].
> > > You can find a small example as part of RelToSqlConverterTest here [2].
> > >
> > > Best,
> > > Stamatis
> > >
> > > [1]
> > >
> > >
> >
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> > > [2]
> > >
> > >
> >
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
> > >
> > > On Thu, May 26, 2022 at 9:18 PM Sandeep N
> > > wrote:
> > >
> > > > Hi all,
> > > >
> > > > I am pretty aware that this question may have been asked before as I
> > have
> > > > found references on stackoverflow but have yet to stumble on a
> response
> > > > which lays out the path on solving this.
> > > >
> > > > Here is the problem I am facing -
> > > > I am using relational algebra to generate SQL on a table test_table
> > with
> > > > two columns a and b fed into the calcite Framework (see below) via a
> > > custom
> > > > catalog. Both a and b are varchar columns.
> > > >
> > > > FrameworkConfig config = Frameworks.newConfigBuilder().
> > > > parserConfig(SqlParser.Config.DEFAULT).
> > > > defaultSchema(addSchemas(rootSchema, schemaName,
> > > schema)).
> > > > <--- this is where I plug my custom table.
> > > > traitDefs((List) null).
> > > > operatorTable(sqlOperatorTable).
> > > >
> programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> > > > true, 2)).build()
> > > >
> > > > The relational algebra is then used to generate SQL to the
> > AnsiSqlDialect
> > > > and the resultant output SQL is
> > > >
> > > > SELECT "a"
> > > > FROM test_table
> > > > WHERE "b" = 'value';
> > > >
> > > > I have the SqlWriterConfig setup as such
> > > >
> > > > SqlWriterConfig config = SqlPrettyWriter.config().
> > > > withCaseClausesOnNewLines(false).
> > > > withQuoteAllIdentifiers(false);
> > > >
> > > > I have been trying to figure what needs to change in the sql writer
> but
> > > > after scanning stackoverflow it appears this is controlled by
> > > > CalciteConnectionConfig and potentially not the SqlWriter. Is this
> > > > accurate? and if so how do I assemble the right connection config
> when
> > I
> > > am
> > > > building the custom catalog?
> > > >
> > > > P.s: I am not parsing SQL but writing directly to the relational
> > algebra
> > > > nodes so SqlParser does not come into play here.
> > > >
> > > > Any pointers on how I can go about this is appreciated, say existing
> > > tests
> > > > or code which will help me put together a configuration which gets me
> > to
> > > > this sql
> > > >
> > > > SELECT a
> > > > FROM test_table
> > > > WHERE b = 'value';
> > > >
> > > > Thanks in advance.
> > > >
> > > > --Sandeep
> > > >
> > >
> >
>
Re: SQL column names - double quoted
Hi Sandeep,
try select.toSqlString(new
AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString(""))).getSql().
Sandeep N 于2022年5月27日周五 06:03写道：
> Hi Stamatis,
>
> Thanks for the quick response, I tried your suggestion as follows
>
> RelToSqlConverter converter = new RelToSqlConverter(new
>
> AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
> SqlSelect select = converter.visitRoot(relNode).asSelect();
> SqlWriterConfig config = SqlPrettyWriter.config().
> withCaseClausesOnNewLines(false).
> withQuoteAllIdentifiers(false);
> System.out.println(new SqlPrettyWriter(config).format(select));
>
> and I still get this
> SELECT "a"
> FROM test_table
> WHERE "b" = 'value';
>
> It is not obvious to me as to what I am doing wrong. Is the above block of
> code the only pieces that I need to put in play to get that sql generated?
>
> --Sandeep
>
> On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis
> wrote:
>
> > Hi Sandeep,
> >
> > If you want to turn off quoting (which I am not sure if it is a good
> idea)
> > when you go from relational algebra to SQL you have to customize the
> > SqlDialect that you are using via Context#withIdentifierQuoteString [1].
> > You can find a small example as part of RelToSqlConverterTest here [2].
> >
> > Best,
> > Stamatis
> >
> > [1]
> >
> >
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> > [2]
> >
> >
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
> >
> > On Thu, May 26, 2022 at 9:18 PM Sandeep N
> > wrote:
> >
> > > Hi all,
> > >
> > > I am pretty aware that this question may have been asked before as I
> have
> > > found references on stackoverflow but have yet to stumble on a response
> > > which lays out the path on solving this.
> > >
> > > Here is the problem I am facing -
> > > I am using relational algebra to generate SQL on a table test_table
> with
> > > two columns a and b fed into the calcite Framework (see below) via a
> > custom
> > > catalog. Both a and b are varchar columns.
> > >
> > > FrameworkConfig config = Frameworks.newConfigBuilder().
> > > parserConfig(SqlParser.Config.DEFAULT).
> > > defaultSchema(addSchemas(rootSchema, schemaName,
> > schema)).
> > > <--- this is where I plug my custom table.
> > > traitDefs((List) null).
> > > operatorTable(sqlOperatorTable).
> > > programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> > > true, 2)).build()
> > >
> > > The relational algebra is then used to generate SQL to the
> AnsiSqlDialect
> > > and the resultant output SQL is
> > >
> > > SELECT "a"
> > > FROM test_table
> > > WHERE "b" = 'value';
> > >
> > > I have the SqlWriterConfig setup as such
> > >
> > > SqlWriterConfig config = SqlPrettyWriter.config().
> > > withCaseClausesOnNewLines(false).
> > > withQuoteAllIdentifiers(false);
> > >
> > > I have been trying to figure what needs to change in the sql writer but
> > > after scanning stackoverflow it appears this is controlled by
> > > CalciteConnectionConfig and potentially not the SqlWriter. Is this
> > > accurate? and if so how do I assemble the right connection config when
> I
> > am
> > > building the custom catalog?
> > >
> > > P.s: I am not parsing SQL but writing directly to the relational
> algebra
> > > nodes so SqlParser does not come into play here.
> > >
> > > Any pointers on how I can go about this is appreciated, say existing
> > tests
> > > or code which will help me put together a configuration which gets me
> to
> > > this sql
> > >
> > > SELECT a
> > > FROM test_table
> > > WHERE b = 'value';
> > >
> > > Thanks in advance.
> > >
> > > --Sandeep
> > >
> >
>
Re: SQL column names - double quoted
Hi Stamatis,
Thanks for the quick response, I tried your suggestion as follows
RelToSqlConverter converter = new RelToSqlConverter(new
AnsiSqlDialect(AnsiSqlDialect.DEFAULT_CONTEXT.withIdentifierQuoteString("")));
SqlSelect select = converter.visitRoot(relNode).asSelect();
SqlWriterConfig config = SqlPrettyWriter.config().
withCaseClausesOnNewLines(false).
withQuoteAllIdentifiers(false);
System.out.println(new SqlPrettyWriter(config).format(select));
and I still get this
SELECT "a"
FROM test_table
WHERE "b" = 'value';
It is not obvious to me as to what I am doing wrong. Is the above block of
code the only pieces that I need to put in play to get that sql generated?
--Sandeep
On Thu, May 26, 2022 at 2:22 PM Stamatis Zampetakis
wrote:
> Hi Sandeep,
>
> If you want to turn off quoting (which I am not sure if it is a good idea)
> when you go from relational algebra to SQL you have to customize the
> SqlDialect that you are using via Context#withIdentifierQuoteString [1].
> You can find a small example as part of RelToSqlConverterTest here [2].
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408
> [2]
>
> https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29
>
> On Thu, May 26, 2022 at 9:18 PM Sandeep N
> wrote:
>
> > Hi all,
> >
> > I am pretty aware that this question may have been asked before as I have
> > found references on stackoverflow but have yet to stumble on a response
> > which lays out the path on solving this.
> >
> > Here is the problem I am facing -
> > I am using relational algebra to generate SQL on a table test_table with
> > two columns a and b fed into the calcite Framework (see below) via a
> custom
> > catalog. Both a and b are varchar columns.
> >
> > FrameworkConfig config = Frameworks.newConfigBuilder().
> > parserConfig(SqlParser.Config.DEFAULT).
> > defaultSchema(addSchemas(rootSchema, schemaName,
> schema)).
> > <--- this is where I plug my custom table.
> > traitDefs((List) null).
> > operatorTable(sqlOperatorTable).
> > programs(Programs.heuristicJoinOrder(Programs.RULE_SET,
> > true, 2)).build()
> >
> > The relational algebra is then used to generate SQL to the AnsiSqlDialect
> > and the resultant output SQL is
> >
> > SELECT "a"
> > FROM test_table
> > WHERE "b" = 'value';
> >
> > I have the SqlWriterConfig setup as such
> >
> > SqlWriterConfig config = SqlPrettyWriter.config().
> > withCaseClausesOnNewLines(false).
> > withQuoteAllIdentifiers(false);
> >
> > I have been trying to figure what needs to change in the sql writer but
> > after scanning stackoverflow it appears this is controlled by
> > CalciteConnectionConfig and potentially not the SqlWriter. Is this
> > accurate? and if so how do I assemble the right connection config when I
> am
> > building the custom catalog?
> >
> > P.s: I am not parsing SQL but writing directly to the relational algebra
> > nodes so SqlParser does not come into play here.
> >
> > Any pointers on how I can go about this is appreciated, say existing
> tests
> > or code which will help me put together a configuration which gets me to
> > this sql
> >
> > SELECT a
> > FROM test_table
> > WHERE b = 'value';
> >
> > Thanks in advance.
> >
> > --Sandeep
> >
>
Re: SQL column names - double quoted
Hi Sandeep, If you want to turn off quoting (which I am not sure if it is a good idea) when you go from relational algebra to SQL you have to customize the SqlDialect that you are using via Context#withIdentifierQuoteString [1]. You can find a small example as part of RelToSqlConverterTest here [2]. Best, Stamatis [1] https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L1408 [2] https://github.com/zabetak/calcite/commit/ea18d28f4fb44113c414c393fe28fc94b3eecc29 On Thu, May 26, 2022 at 9:18 PM Sandeep N wrote: > Hi all, > > I am pretty aware that this question may have been asked before as I have > found references on stackoverflow but have yet to stumble on a response > which lays out the path on solving this. > > Here is the problem I am facing - > I am using relational algebra to generate SQL on a table test_table with > two columns a and b fed into the calcite Framework (see below) via a custom > catalog. Both a and b are varchar columns. > > FrameworkConfig config = Frameworks.newConfigBuilder(). > parserConfig(SqlParser.Config.DEFAULT). > defaultSchema(addSchemas(rootSchema, schemaName, schema)). > <--- this is where I plug my custom table. > traitDefs((List) null). > operatorTable(sqlOperatorTable). > programs(Programs.heuristicJoinOrder(Programs.RULE_SET, > true, 2)).build() > > The relational algebra is then used to generate SQL to the AnsiSqlDialect > and the resultant output SQL is > > SELECT "a" > FROM test_table > WHERE "b" = 'value'; > > I have the SqlWriterConfig setup as such > > SqlWriterConfig config = SqlPrettyWriter.config(). > withCaseClausesOnNewLines(false). > withQuoteAllIdentifiers(false); > > I have been trying to figure what needs to change in the sql writer but > after scanning stackoverflow it appears this is controlled by > CalciteConnectionConfig and potentially not the SqlWriter. Is this > accurate? and if so how do I assemble the right connection config when I am > building the custom catalog? > > P.s: I am not parsing SQL but writing directly to the relational algebra > nodes so SqlParser does not come into play here. > > Any pointers on how I can go about this is appreciated, say existing tests > or code which will help me put together a configuration which gets me to > this sql > > SELECT a > FROM test_table > WHERE b = 'value'; > > Thanks in advance. > > --Sandeep >
SQL column names - double quoted
Hi all, I am pretty aware that this question may have been asked before as I have found references on stackoverflow but have yet to stumble on a response which lays out the path on solving this. Here is the problem I am facing - I am using relational algebra to generate SQL on a table test_table with two columns a and b fed into the calcite Framework (see below) via a custom catalog. Both a and b are varchar columns. FrameworkConfig config = Frameworks.newConfigBuilder(). parserConfig(SqlParser.Config.DEFAULT). defaultSchema(addSchemas(rootSchema, schemaName, schema)). <--- this is where I plug my custom table. traitDefs((List) null). operatorTable(sqlOperatorTable). programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2)).build() The relational algebra is then used to generate SQL to the AnsiSqlDialect and the resultant output SQL is SELECT "a" FROM test_table WHERE "b" = 'value'; I have the SqlWriterConfig setup as such SqlWriterConfig config = SqlPrettyWriter.config(). withCaseClausesOnNewLines(false). withQuoteAllIdentifiers(false); I have been trying to figure what needs to change in the sql writer but after scanning stackoverflow it appears this is controlled by CalciteConnectionConfig and potentially not the SqlWriter. Is this accurate? and if so how do I assemble the right connection config when I am building the custom catalog? P.s: I am not parsing SQL but writing directly to the relational algebra nodes so SqlParser does not come into play here. Any pointers on how I can go about this is appreciated, say existing tests or code which will help me put together a configuration which gets me to this sql SELECT a FROM test_table WHERE b = 'value'; Thanks in advance. --Sandeep
