SQL query containing non-ascii characters

[Swapnil Gujarathi <gu...@gmail.com>]

Dear Experts,

I hope this is the right channel to reach out to you about my problem.

I am using a calcite-core.jar version as 1.28. I am facing an issue with a
sql query containing non-ascii characters passed to calcite. Example query
can be considered as
select * from table1 where col1 in ('h*é*b', 'sio', 'd*é*p');
Please note the bold letters which are causing the problem.
Calcite encodes these characters as  * col1 in (u&'h\00e9b', 'sio',
u&'d\00e9p') *and this gives me sql error as: ORA-00907: missing right
parenthesis.

I tried providing a system property calcite.default.charset=UTF-8 but it
didn't help. I also tried to debug the code and found that SqlDialect.java
method (red colored) is adding this encoding













*public void quoteStringLiteral(StringBuilder buf, @Nullable String
charsetName,      String val) {    if (containsNonAscii(val) && charsetName
== null) {      quoteStringLiteralUnicode(buf, val);    } else {      if
(charsetName != null) {        buf.append("_");
buf.append(charsetName);      }      buf.append(literalQuoteString);
buf.append(val.replace(literalEndQuoteString, literalEscapedQuote));
buf.append(literalEndQuoteString);    }  }*

Just to be sure, in my local environment I removed this condition and it
seemed to work without knowing its impact at other places.


*  if (containsNonAscii(val) && charsetName == null) {
quoteStringLiteralUnicode(buf, val);   }*

However, I am not sure if this can be the solution or anything else is
needed or is it the bug? In the older version of calcite-core 1.15, the
same query used to work. I have no idea since which version of calcite, it
started failing.

Please provide your inputs and let me know if any other details are required
*.*


*Thanks a lot in advance.*
-- 
Thanks,
Swapnil Gujarathi

Re: SQL query containing non-ascii characters

[Stamatis Zampetakis <za...@gmail.com>]

Hi Swapnil,

It seems that you are trying to execute a query with Calcite over Oracle. I
don't see clearly what is the exact SQL query generated by Calcite so
cannot really tell if it is a bug in Calcite or a configuration/limitation
in Oracle. Please log a JIRA case and post there the initial query, the
generated query for Oracle, Oracle version, and other information that
might be relevant and let's continue the discussion there.

Best,
Stamatis

PS. It's better to avoid special formatting (e.g., bold, highlight, etc)
when sending emails to the dev list since people tend to use different
email clients and the message may be harder to read. Also when you refer to
code here or in JIRA it's better to put links to GitHub instead or in
addition to copy-pasting snippets.



On Fri, Feb 4, 2022 at 9:37 PM Swapnil Gujarathi <gu...@gmail.com>
wrote:

> Dear Experts,
>
> I hope this is the right channel to reach out to you about my problem.
>
> I am using a calcite-core.jar version as 1.28. I am facing an issue with a
> sql query containing non-ascii characters passed to calcite. Example query
> can be considered as
> select * from table1 where col1 in ('h*é*b', 'sio', 'd*é*p');
> Please note the bold letters which are causing the problem.
> Calcite encodes these characters as  * col1 in (u&'h\00e9b', 'sio',
> u&'d\00e9p') *and this gives me sql error as: ORA-00907: missing right
> parenthesis.
>
> I tried providing a system property calcite.default.charset=UTF-8 but it
> didn't help. I also tried to debug the code and found that SqlDialect.java
> method (red colored) is adding this encoding
>
>
>
>
>
>
>
>
>
>
>
>
>
> *public void quoteStringLiteral(StringBuilder buf, @Nullable String
> charsetName,      String val) {    if (containsNonAscii(val) && charsetName
> == null) {      quoteStringLiteralUnicode(buf, val);    } else {      if
> (charsetName != null) {        buf.append("_");
> buf.append(charsetName);      }      buf.append(literalQuoteString);
> buf.append(val.replace(literalEndQuoteString, literalEscapedQuote));
> buf.append(literalEndQuoteString);    }  }*
>
> Just to be sure, in my local environment I removed this condition and it
> seemed to work without knowing its impact at other places.
>
>
> *  if (containsNonAscii(val) && charsetName == null) {
> quoteStringLiteralUnicode(buf, val);   }*
>
> However, I am not sure if this can be the solution or anything else is
> needed or is it the bug? In the older version of calcite-core 1.15, the
> same query used to work. I have no idea since which version of calcite, it
> started failing.
>
> Please provide your inputs and let me know if any other details are
> required
> *.*
>
>
> *Thanks a lot in advance.*
> --
> Thanks,
> Swapnil Gujarathi
>