[jira] [Created] (CALCITE-6146) Target charset should be used when comparing two strings through CONVERT/TRANSLATE function during validation
ZheHu created CALCITE-6146:
--
Summary: Target charset should be used when comparing two strings
through CONVERT/TRANSLATE function during validation
Key: CALCITE-6146
URL: https://issues.apache.org/jira/browse/CALCITE-6146
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Reporter: ZheHu
Assignee: ZheHu
The following test in JdbcTest throws "Cannot apply = to the two different
charsets ISO-8859-1 and GBK"
{code:java}
CalciteAssert.hr()
.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ "where convert('a' using GBK)=_GBK'a' order by \"empid\"")
.returns("name=Eric; empid=200\n"
+ "name=Sebastian; empid=150\n"
+ "name=Theodore; empid=110\n"
+ "name=Bill; empid=100\n");
{code}
Since we try to convert 'a' with GBK, the charset of its result should also be
treated as GBK.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6070) Incorrect field name after RelFieldTrimmer
[
https://issues.apache.org/jira/browse/CALCITE-6070?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17779408#comment-17779408
]
ZheHu commented on CALCITE-6070:
BTW, we can also reproduce this case in RelBuilderTest. The RelNode and its
logical plan is as follows:
{code}
RelNode root =
builder.scan("DEPT")
.project(builder.field(0))
.filter(
builder.equals(builder.field("DEPTNO"),
builder.literal(20)))
.project(builder.alias(builder.field(0), "alia"))
.sortLimit(1, 1)
.build();
LogicalSort(offset=[1], fetch=[1])
LogicalFilter(condition=[=($0, 20)])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[scott, DEPT]])
{code}
> Incorrect field name after RelFieldTrimmer
> --
>
> Key: CALCITE-6070
> URL: https://issues.apache.org/jira/browse/CALCITE-6070
> Project: Calcite
> Issue Type: Wish
> Components: core
>Affects Versions: 1.35.0
>Reporter: ZheHu
>Priority: Minor
>
> Run the following SQL in SqlToRelConverterTest:
> {code}
> select ename as did from emp where ename='a' limit 10
> {code}
> Got planA without RelFieldTrimmer:
> {code}
> LogicalSort(fetch=[10])
> LogicalProject(DID=[$1])
> LogicalFilter(condition=[=($1, 'a')])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> Got planB with RelFieldTrimmer:
> {code}
> LogicalSort(fetch=[10])
> LogicalFilter(condition=[=($0, 'a')])
> LogicalProject(ENAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> However, the column alia DID for ENAME is missing in planB.
> Maybe we can do something about the trimmer when we have other
> purposes(renaming column is the only purpose for this case).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6070) Missing column alia after RelFieldTrimmer
ZheHu created CALCITE-6070:
--
Summary: Missing column alia after RelFieldTrimmer
Key: CALCITE-6070
URL: https://issues.apache.org/jira/browse/CALCITE-6070
Project: Calcite
Issue Type: Wish
Components: core
Affects Versions: 1.35.0
Reporter: ZheHu
Run the following SQL in SqlToRelConverterTest:
{code}
select ename as did from emp where ename='a' limit 10
{code}
Got planA without RelFieldTrimmer:
{code}
LogicalSort(fetch=[10])
LogicalProject(DID=[$1])
LogicalFilter(condition=[=($1, 'a')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Got planB with RelFieldTrimmer:
{code}
LogicalSort(fetch=[10])
LogicalFilter(condition=[=($0, 'a')])
LogicalProject(ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
However, the column alia DID for ENAME is missing in planB.
Maybe we can do something about the trimmer when we have other
purposes(renaming column is the only purpose for this case).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5996) TRANSLATE operator is incorrectly unparsed
[
https://issues.apache.org/jira/browse/CALCITE-5996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17765184#comment-17765184
]
ZheHu commented on CALCITE-5996:
Fixed in
[04fdef0|https://github.com/apache/calcite/commit/04fdef03ed9e61f155f9102ec88cd2a4036038ed].
Thanks for the PR [~mbudiu] !
> TRANSLATE operator is incorrectly unparsed
> --
>
> Key: CALCITE-5996
> URL: https://issues.apache.org/jira/browse/CALCITE-5996
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> This query
> {code}
> select translate(col using utf8)
> from (select 'a' as col
> from (values(true)))
> {code}
> if converted to SqlNode and back produces:
> {code}
> SELECT TRANSLATE("COL", "UTF8")
> FROM (SELECT 'a' AS "COL"
> FROM (VALUES ROW(TRUE)))
> {code}
> which is no longer correct SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5996) TRANSLATE operator is incorrectly unparsed
[
https://issues.apache.org/jira/browse/CALCITE-5996?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu resolved CALCITE-5996.
Resolution: Resolved
> TRANSLATE operator is incorrectly unparsed
> --
>
> Key: CALCITE-5996
> URL: https://issues.apache.org/jira/browse/CALCITE-5996
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> This query
> {code}
> select translate(col using utf8)
> from (select 'a' as col
> from (values(true)))
> {code}
> if converted to SqlNode and back produces:
> {code}
> SELECT TRANSLATE("COL", "UTF8")
> FROM (SELECT 'a' AS "COL"
> FROM (VALUES ROW(TRUE)))
> {code}
> which is no longer correct SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5996) TRANSLATE operator is incorrectly unparsed
[
https://issues.apache.org/jira/browse/CALCITE-5996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17763981#comment-17763981
]
ZheHu commented on CALCITE-5996:
I think the SqlParserTest is the way to go.
> TRANSLATE operator is incorrectly unparsed
> --
>
> Key: CALCITE-5996
> URL: https://issues.apache.org/jira/browse/CALCITE-5996
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> This query
> {code}
> select translate(col using utf8)
> from (select 'a' as col
> from (values(true)))
> {code}
> if converted to SqlNode and back produces:
> {code}
> SELECT TRANSLATE("COL", "UTF8")
> FROM (SELECT 'a' AS "COL"
> FROM (VALUES ROW(TRUE)))
> {code}
> which is no longer correct SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5807) Add SUBSTRING_INDEX function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-5807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17748906#comment-17748906
]
ZheHu commented on CALCITE-5807:
I'm sorry that I didn't noticed that this ticket is created by
[~MasseGuillaume] when I reviewed it.
Are you okay with it [~MasseGuillaume] or do you have any advice on this PR?
> Add SUBSTRING_INDEX function (enabled in Spark library)
> ---
>
> Key: CALCITE-5807
> URL: https://issues.apache.org/jira/browse/CALCITE-5807
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Guillaume Massé
>Assignee: hujianhong
>Priority: Minor
> Labels: pull-request-available
>
> [https://spark.apache.org/docs/2.3.0/api/sql/#substring_index]
>
>
> {code:java}
> substring_index(str, delim, count) - Returns the substring from str before
> count occurrences of the delimiter delim. If count is positive, everything to
> the left of the final delimiter (counting from the left) is returned. If
> count is negative, everything to the right of the final delimiter (counting
> from the right) is returned. The function substring_index performs a
> case-sensitive match when searching for delim.
> Examples:
>
> > SELECT substring_index('www.apache.org', '.', 2); www.apache
> {code}
> {{}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5782) Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
[
https://issues.apache.org/jira/browse/CALCITE-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17741447#comment-17741447
]
ZheHu commented on CALCITE-5782:
Fixed in
[8d33c2e|https://github.com/apache/calcite/commit/8d33c2e620f68094ca039c7a97325b5e4075d447].
Thanks for the PR [~FrankZou], and thanks for the review [~tanclary].
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> ---
>
> Key: CALCITE-5782
> URL: https://issues.apache.org/jira/browse/CALCITE-5782
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> * TO_HEX: Converts a sequence of {{BINARY}} into a hexadecimal
> {{CHARACTER}}.
> * FROM_HEX: Converts a hexadecimal-encoded {{CHARACTER}} into {{BINARY}}
> format
> See more details in BigQuery doc
> [TO_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#to_hex],
>
> [FROM_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#from_hex]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5782) Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
[
https://issues.apache.org/jira/browse/CALCITE-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu resolved CALCITE-5782.
Resolution: Fixed
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> ---
>
> Key: CALCITE-5782
> URL: https://issues.apache.org/jira/browse/CALCITE-5782
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> * TO_HEX: Converts a sequence of {{BINARY}} into a hexadecimal
> {{CHARACTER}}.
> * FROM_HEX: Converts a hexadecimal-encoded {{CHARACTER}} into {{BINARY}}
> format
> See more details in BigQuery doc
> [TO_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#to_hex],
>
> [FROM_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#from_hex]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5782) Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
[
https://issues.apache.org/jira/browse/CALCITE-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5782:
---
Fix Version/s: 1.35.0
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> ---
>
> Key: CALCITE-5782
> URL: https://issues.apache.org/jira/browse/CALCITE-5782
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Add TO_HEX and FROM_HEX functions (enabled in BigQuery library)
> * TO_HEX: Converts a sequence of {{BINARY}} into a hexadecimal
> {{CHARACTER}}.
> * FROM_HEX: Converts a hexadecimal-encoded {{CHARACTER}} into {{BINARY}}
> format
> See more details in BigQuery doc
> [TO_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#to_hex],
>
> [FROM_HEX|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#from_hex]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-1924) Support operator "+" as string concat operator(enabled in MSSQL library)
[
https://issues.apache.org/jira/browse/CALCITE-1924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17740630#comment-17740630
]
ZheHu commented on CALCITE-1924:
I have one small question here. We get a SqlCall with BINARY SqlSyntax after
parsing 'a' + 'b', so this function(let's call it PLUS_TO_CONCAT) should also
has BINARY syntax. However, in SqlUtil.filterRoutinesByParameterTypeAndName()
during overloading, those operators without FUNCTION SqlSyntax won't be checked
for their parameter types.
Is there anything else I can refer to or do I miss something?
> Support operator "+" as string concat operator(enabled in MSSQL library)
>
>
> Key: CALCITE-1924
> URL: https://issues.apache.org/jira/browse/CALCITE-1924
> Project: Calcite
> Issue Type: Improvement
>Reporter: Roger Shi
>Assignee: ZheHu
>Priority: Minor
>
> Currently the following SQL is not supported.
> {code}
> select 'A' + 'B' + 'C';
> {code}
> We get the exception:
> {noformat}
> Error while executing SQL "select 'A' + 'B' + 'C'": From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type ' +
> '. Supported form(s): ' + '
> ' + '
> ' + '
> ' + ' (state=,code=0)
> {noformat}
> In this JIRA, I'll fix this by supporting operator "+" as string concat
> operator.
> Similar to CALCITE-1374 and CALCITE-1897.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5800) NVL function should respect implicit conversion among the arguments
[
https://issues.apache.org/jira/browse/CALCITE-5800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17737422#comment-17737422
]
ZheHu commented on CALCITE-5800:
What if we don't constrain the arguments' types to be the same, like
OperandTypes.sequence(any, any), so we can do the conversion in
StandardConvertletTable.convertNvl(). Take *nvl(1, '2')* as example, it's
equivelant to *nvl(1, cast('2' as integer))*.
Hence, if arg1's type is different from arg2's, we convert the call to
*nvl(arg1, cast(arg2 as arg1Type))*. WDYT [~julianhyde]?
> NVL function should respect implicit conversion among the arguments
> ---
>
> Key: CALCITE-5800
> URL: https://issues.apache.org/jira/browse/CALCITE-5800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> The following SQLs in SqlOperatorTest would fail as the NVL function(enabled
> in Oracle) has "SAME_SAME" operand checker. However, they both result in "1"
> in Oracle.
> {code}
> select nvl(1, '2') as alia from dual;
> {code}
> {code}
> select nvl('1', 2) as alia from dual;
> {code}
> According to
> [Oracle-NVL|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2],
> if arguments' data types are different, then Oracle Database implicitly
> converts one to the other.
> Conversion rules(take *nvl(expr1, expr2)* as example):
> # If expr1 is character data, then Oracle Database converts expr2 to the data
> type of expr1 before comparing them and returns VARCHAR2 in the character set
> of expr1
> # If expr1 is numeric, then Oracle Database determines which argument has the
> highest numeric precedence, implicitly converts the other argument to that
> data type, and returns that data type
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5800) NVL function should respect implicit conversion among the arguments
ZheHu created CALCITE-5800:
--
Summary: NVL function should respect implicit conversion among the
arguments
Key: CALCITE-5800
URL: https://issues.apache.org/jira/browse/CALCITE-5800
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: ZheHu
Assignee: ZheHu
The following SQLs in SqlOperatorTest would fail as the NVL function(enabled in
Oracle) has "SAME_SAME" operand checker. However, they both result in "1" in
Oracle.
{code}
select nvl(1, '2') as alia from dual;
{code}
{code}
select nvl('1', 2) as alia from dual;
{code}
According to
[Oracle-NVL|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2],
if arguments' data types are different, then Oracle Database implicitly
converts one to the other.
Conversion rules(take *nvl(expr1, expr2)* as example):
# If expr1 is character data, then Oracle Database converts expr2 to the data
type of expr1 before comparing them and returns VARCHAR2 in the character set
of expr1
# If expr1 is numeric, then Oracle Database determines which argument has the
highest numeric precedence, implicitly converts the other argument to that data
type, and returns that data type
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17733858#comment-17733858 ] ZheHu commented on CALCITE-5688: Thanks for bring the conversation here [~jiajunbernoulli]. To avoid confusion, I don't mean to extend the core Parser, just add one SqlKind, which doesn't break our core parser rules if I understand correctly. > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5741) Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
[
https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5741:
---
Description:
This function returns a string resulting from the concatenation, or joining, of
two or more string values in an end-to-end manner. It separates those
concatenated string values with the delimiter specified in the first function
argument. It has the following syntax:
{code:java}
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
{code}
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
{code:java}
select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'
{code}
One important thing to be noted is that CONCAT_WS has two *NULL semantics for
the separator argument* in different Database products(So we need two function
in Calcite to represent).
Taking the following SQL as an example:
{code:java}
select concat_ws(null, 'a', 'b', null, null, 'c');
{code}
- {*}MSSQL{*}: never returns null and treats null as empty string, so the
above SQL results in "abc" (For more details, see
[mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16])
- {*}MySQL and Postgresql{*}: returns null when the separator is null, so the
example SQL returns NULL.
was:
CONCAT_WS function in MSSQL has the following syntax:
{code:java}
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
{code}
This function returns a string resulting from the concatenation, or joining, of
two or more string values in an end-to-end manner. It separates those
concatenated string values with the delimiter specified in the first function
argument. Moreover, the amount of arguments varies from 3 to 254.
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
{code:java}
select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'
{code}
{code:java}
select concat_ws(null, 'a', 'b', null, null, 'c');
-- result in 'abc'
{code}
For more details, see
[mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
As for CONCAT_WS function in MySQL and Postgresql, it accepts at least 2
arguments, and also ignore NULL value. But the following query returns null
which is different from MSSQL.
{code:java}
select concat_ws(null, 'a', 'b', null, null, 'c');
{code}
> Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
> --
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
>
> This function returns a string resulting from the concatenation, or joining,
> of two or more string values in an end-to-end manner. It separates those
> concatenated string values with the delimiter specified in the first function
> argument. It has the following syntax:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> One important thing to be noted is that CONCAT_WS has two *NULL semantics for
> the separator argument* in different Database products(So we need two
> function in Calcite to represent).
> Taking the following SQL as an example:
> {code:java}
> select concat_ws(null, 'a', 'b', null, null, 'c');
> {code}
> - {*}MSSQL{*}: never returns null and treats null as empty string, so the
> above SQL results in "abc" (For more details, see
> [mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16])
> - {*}MySQL and Postgresql{*}: returns null when the separator is null, so
> the example SQL returns NULL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5771) Apply two different NULL semantics for CONCAT function(enabled in MySQL, Postgresql, BigQuery and MSSQL)
[ https://issues.apache.org/jira/browse/CALCITE-5771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5771: --- Description: We now have one CONCAT_FUNCTION which is enabled in MySQL, Postgresql, BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments. The following table describes how different database products define CONCAT function: ||DB Product||Argument type in CONCAT||Argument num in CONCAT||Result|| |MySQL|string|at least 1|returns null if any argument is null, otherwise return string| |BigQuery|string|at least 1|returns null if any argument is null, otherwise return string| |Postgresql|any(cast to string)|at least 1|always return string, null is treated as empty string| |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql and MSSQL that treats NULL as empty String. And they both accept at least 1 argument. was: We now have one CONCAT_FUNCTION which is enabled in MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments. The following table describes how different database products define CONCAT function: || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result || |MySQL|string|at least 1|returns null if any argument is null, otherwise return string| |BigQuery|string|at least 1|returns null if any argument is null, otherwise return string| |Postgresql|any(cast to string)|at least 1|always return string, null is treated as empty string| |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql and MSSQL that treats NULL as empty String. And they both accept at least 1 argument. > Apply two different NULL semantics for CONCAT function(enabled in MySQL, > Postgresql, BigQuery and MSSQL) > > > Key: CALCITE-5771 > URL: https://issues.apache.org/jira/browse/CALCITE-5771 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Major > Labels: pull-request-available > > We now have one CONCAT_FUNCTION which is enabled in MySQL, Postgresql, > BigQuery, it always returns NULL when any of the arguments is NULL, and it > accepts at least 2 arguments. > The following table describes how different database products define CONCAT > function: > ||DB Product||Argument type in CONCAT||Argument num in CONCAT||Result|| > |MySQL|string|at least 1|returns null if any argument is null, otherwise > return string| > |BigQuery|string|at least 1|returns null if any argument is null, otherwise > return string| > |Postgresql|any(cast to string)|at least 1|always return string, null is > treated as empty string| > |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| > For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to > change the accepted argument number). We also need another CONCAT function > for Postgresql and MSSQL that treats NULL as empty String. And they both > accept at least 1 argument. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5771) Apply two different NULL semantics for CONCAT function(enabled in MySQL, Postgresql, BigQuery and MSSQL)
[ https://issues.apache.org/jira/browse/CALCITE-5771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5771: --- Summary: Apply two different NULL semantics for CONCAT function(enabled in MySQL, Postgresql, BigQuery and MSSQL) (was: Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql、BigQuery and MSSQL)) > Apply two different NULL semantics for CONCAT function(enabled in MySQL, > Postgresql, BigQuery and MSSQL) > > > Key: CALCITE-5771 > URL: https://issues.apache.org/jira/browse/CALCITE-5771 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Major > Labels: pull-request-available > > We now have one CONCAT_FUNCTION which is enabled in > MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments > is NULL, and it accepts at least 2 arguments. > The following table describes how different database products define CONCAT > function: > || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result > || > |MySQL|string|at least 1|returns null if any argument is null, otherwise > return string| > |BigQuery|string|at least 1|returns null if any argument is null, otherwise > return string| > |Postgresql|any(cast to string)|at least 1|always return string, null is > treated as empty string| > |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| > For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to > change the accepted argument number). We also need another CONCAT function > for Postgresql and MSSQL that treats NULL as empty String. And they both > accept at least 1 argument. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5741) Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
[
https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5741:
---
Description:
CONCAT_WS function in MSSQL has the following syntax:
{code:java}
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
{code}
This function returns a string resulting from the concatenation, or joining, of
two or more string values in an end-to-end manner. It separates those
concatenated string values with the delimiter specified in the first function
argument. Moreover, the amount of arguments varies from 3 to 254.
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
{code:java}
select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'
{code}
{code:java}
select concat_ws(null, 'a', 'b', null, null, 'c');
-- result in 'abc'
{code}
For more details, see
[mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
As for CONCAT_WS function in MySQL and Postgresql, it accepts at least 2
arguments, and also ignore NULL value. But the following query returns null
which is different from MSSQL.
{code:java}
select concat_ws(null, 'a', 'b', null, null, 'c');
{code}
was:
CONCAT_WS function in MSSQL has the following syntax:
{code:java}
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
{code}
This function returns a string resulting from the concatenation, or joining, of
two or more string values in an end-to-end manner. It separates those
concatenated string values with the delimiter specified in the first function
argument. Moreover, the amount of arguments varies from 3 to 254.
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
{code:java}
select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'
{code}
For more details, see
[doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
> Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
> --
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONCAT_WS function in MSSQL has the following syntax:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> This function returns a string resulting from the concatenation, or joining,
> of two or more string values in an end-to-end manner. It separates those
> concatenated string values with the delimiter specified in the first function
> argument. Moreover, the amount of arguments varies from 3 to 254.
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> {code:java}
> select concat_ws(null, 'a', 'b', null, null, 'c');
> -- result in 'abc'
> {code}
> For more details, see
> [mssql-concat_ws|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
> As for CONCAT_WS function in MySQL and Postgresql, it accepts at least 2
> arguments, and also ignore NULL value. But the following query returns null
> which is different from MSSQL.
> {code:java}
> select concat_ws(null, 'a', 'b', null, null, 'c');
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5741) Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
[
https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5741:
---
Summary: Add support for CONCAT_WS function(enabled in MSSQL, MySQL and
Postgresql) (was: Add support for MSSQL CONCAT_WS function)
> Add support for CONCAT_WS function(enabled in MSSQL, MySQL and Postgresql)
> --
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONCAT_WS function in MSSQL has the following syntax:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> This function returns a string resulting from the concatenation, or joining,
> of two or more string values in an end-to-end manner. It separates those
> concatenated string values with the delimiter specified in the first function
> argument. Moreover, the amount of arguments varies from 3 to 254.
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> For more details, see
> [doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5741) Add support for MSSQL CONCAT_WS function
[
https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17731299#comment-17731299
]
ZheHu commented on CALCITE-5741:
I've submitted a patch for CALCITE-5771, maybe we can catch 1.35.0.
> Add support for MSSQL CONCAT_WS function
>
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONCAT_WS function in MSSQL has the following syntax:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> This function returns a string resulting from the concatenation, or joining,
> of two or more string values in an end-to-end manner. It separates those
> concatenated string values with the delimiter specified in the first function
> argument. Moreover, the amount of arguments varies from 3 to 254.
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> For more details, see
> [doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5741) Add support for MSSQL CONCAT_WS function
[
https://issues.apache.org/jira/browse/CALCITE-5741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17731291#comment-17731291
]
ZheHu commented on CALCITE-5741:
How about changing Postgresql's behavior in CALCITE-5771? I'm also gonna to
enable CONCAT_FUNCTION in MSSQL too.
> Add support for MSSQL CONCAT_WS function
>
>
> Key: CALCITE-5741
> URL: https://issues.apache.org/jira/browse/CALCITE-5741
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONCAT_WS function in MSSQL has the following syntax:
> {code:java}
> CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
> {code}
> This function returns a string resulting from the concatenation, or joining,
> of two or more string values in an end-to-end manner. It separates those
> concatenated string values with the delimiter specified in the first function
> argument. Moreover, the amount of arguments varies from 3 to 254.
> Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
> {code:java}
> select concat_ws(',', 'a', 'b', null, null, 'c');
> -- result in 'a,b,c'
> {code}
> For more details, see
> [doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function (enabled in Oracle library) gets wrong result when one of the arguments is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17731284#comment-17731284
]
ZheHu commented on CALCITE-5745:
Do you mean fix it as a part of CALCITE-5741 or CALCITE-5745?
BTW, for Postgresql, CONCAT returns empty string if all the arguments is null,
while Oracle returns null.
> CONCAT2 function (enabled in Oracle library) gets wrong result when one of
> the arguments is NULL
>
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5771) Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql、BigQuery and MSSQL)
[ https://issues.apache.org/jira/browse/CALCITE-5771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5771: --- Description: We now have one CONCAT_FUNCTION which is enabled in MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments. The following table describes how different database products define CONCAT function: || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result || |MySQL|string|at least 1|returns null if any argument is null, otherwise return string| |BigQuery|string|at least 1|returns null if any argument is null, otherwise return string| |Postgresql|any(cast to string)|at least 1|always return string, null is treated as empty string| |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql and MSSQL that treats NULL as empty String. And they both accept at least 1 argument. was: We now have one CONCAT_FUNCTION which is enabled in MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments. The following table describes how different database products define CONCAT function: || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result || |MySQL|string|at least 1|returns null if any argument is null, otherwise return string| |Postgresql|any(cast to string)|at least 1|always return string, null is treated as empty string| |BigQuery|string|at least 1|returns null if any argument is null, otherwise return string| For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql that treats NULL as empty String. > Apply two different NULL semantics for CONCAT function(enabled in > MySQL、Postgresql、BigQuery and MSSQL) > -- > > Key: CALCITE-5771 > URL: https://issues.apache.org/jira/browse/CALCITE-5771 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Major > > We now have one CONCAT_FUNCTION which is enabled in > MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments > is NULL, and it accepts at least 2 arguments. > The following table describes how different database products define CONCAT > function: > || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result > || > |MySQL|string|at least 1|returns null if any argument is null, otherwise > return string| > |BigQuery|string|at least 1|returns null if any argument is null, otherwise > return string| > |Postgresql|any(cast to string)|at least 1|always return string, null is > treated as empty string| > |MSSQL|string|2 ~ 254|always return string, null is treated as empty string| > For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to > change the accepted argument number). We also need another CONCAT function > for Postgresql and MSSQL that treats NULL as empty String. And they both > accept at least 1 argument. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5771) Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql、BigQuery and MSSQL)
[ https://issues.apache.org/jira/browse/CALCITE-5771?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5771: --- Summary: Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql、BigQuery and MSSQL) (was: Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql and BigQuery)) > Apply two different NULL semantics for CONCAT function(enabled in > MySQL、Postgresql、BigQuery and MSSQL) > -- > > Key: CALCITE-5771 > URL: https://issues.apache.org/jira/browse/CALCITE-5771 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Major > > We now have one CONCAT_FUNCTION which is enabled in > MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments > is NULL, and it accepts at least 2 arguments. > The following table describes how different database products define CONCAT > function: > || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result > || > |MySQL|string|at least 1|returns null if any argument is null, otherwise > return string| > |Postgresql|any(cast to string)|at least 1|always return string, null is > treated as empty string| > |BigQuery|string|at least 1|returns null if any argument is null, otherwise > return string| > For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to > change the accepted argument number). We also need another CONCAT function > for Postgresql that treats NULL as empty String. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function (enabled in Oracle library) gets wrong result when one of the arguments is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730448#comment-17730448
]
ZheHu commented on CALCITE-5745:
OK, got your point.
> CONCAT2 function (enabled in Oracle library) gets wrong result when one of
> the arguments is NULL
>
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function (enabled in Oracle library) gets wrong result when one of the arguments is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730420#comment-17730420
]
ZheHu commented on CALCITE-5745:
Per the above discussion, I filed CALCITE-5771 to implement two NULL semantics
for CONCAT.
But for MSSQL, we may still need to add a function(maybe MSSQL_CONCAT) based on
its accepted args. Should I need another JIRA case(since we don't enable CONCAT
in MSSQL currently) or I can implement it in CALCITE-5771? What do you think
[~julianhyde]?
> CONCAT2 function (enabled in Oracle library) gets wrong result when one of
> the arguments is NULL
>
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5771) Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql and BigQuery)
ZheHu created CALCITE-5771: -- Summary: Apply two different NULL semantics for CONCAT function(enabled in MySQL、Postgresql and BigQuery) Key: CALCITE-5771 URL: https://issues.apache.org/jira/browse/CALCITE-5771 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.34.0 Reporter: ZheHu Assignee: ZheHu We now have one CONCAT_FUNCTION which is enabled in MySQL、Postgresql、BigQuery, it always returns NULL when any of the arguments is NULL, and it accepts at least 2 arguments. The following table describes how different database products define CONCAT function: || DB Product || Argument type in CONCAT || Argument num in CONCAT || Result || |MySQL|string|at least 1|returns null if any argument is null, otherwise return string| |Postgresql|any(cast to string)|at least 1|always return string, null is treated as empty string| |BigQuery|string|at least 1|returns null if any argument is null, otherwise return string| For MySQL and BigQuery, we can reuse the current CONCAT_FUNCTION(but need to change the accepted argument number). We also need another CONCAT function for Postgresql that treats NULL as empty String. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function(enabled in Oracle library) gets wrong result when one of the argument is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728935#comment-17728935
]
ZheHu commented on CALCITE-5745:
I agree that we need two different null semantics for CONCAT.
For MSSQL, its CONCAT function requires at least 2 and at most 254 arguments,
hence we need a third function like MSSQL_CONCAT(the way it deals with null
value can be covered by the above semantics) to achieve this.
If we reach an agreement on this, I'll start with CONCAT2 for Oracle.
> CONCAT2 function(enabled in Oracle library) gets wrong result when one of the
> argument is NULL
> --
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function(enabled in Oracle library) gets wrong result when one of the argument is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728910#comment-17728910
]
ZheHu commented on CALCITE-5745:
Hi [~julianhyde], I also checked CONCAT function in MySQL and Postgresql, it
seems that we can't treat them as the same(currently we use CONCAT_FUNCTION to
enable it, for Oracle we use CONCAT2).
||DB Product||Argument type in CONCAT||Argument num in CONCAT||Result||
|Oracle|string|exactly 2|return null only when two operand are null, otherwise
return string(ignore null if there is a null operand)|
|MySQL|string|at least 1|returns null if any argument is null, otherwise return
string|
|MSSQL|string|between 2 and 254|always return string, null is treated as empty
string|
|Postgresql|any(cast to string)|at least 1|always return string, null is
treated as empty string|
I think we may need several tasks to finish this, what do you think?
Here are the docs in case someone needs to refer to:
*
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CONCAT.html#GUID-D8723EA5-C93A-45C3-83FB-1F3D2A4CEAF2]
*
[MySQL|https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat]
*
[MSSQL|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16]
* [Postgresql|https://www.postgresql.org/docs/current/functions-string.html]
> CONCAT2 function(enabled in Oracle library) gets wrong result when one of the
> argument is NULL
> --
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-1924) Support operator "+" as string concat operator(enabled in MSSQL library)
[
https://issues.apache.org/jira/browse/CALCITE-1924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728909#comment-17728909
]
ZheHu commented on CALCITE-1924:
According to
[mssql-concat-doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16],
CONCAT implicitly converts null values to empty strings. If CONCAT receives
arguments with all NULL values, it will return an empty string of type
varchar(1).
It behaves differently in Calcite, maybe we should support MSSQL_CONCAT first,
then consider overloading "+" operator in MSSQL library.
> Support operator "+" as string concat operator(enabled in MSSQL library)
>
>
> Key: CALCITE-1924
> URL: https://issues.apache.org/jira/browse/CALCITE-1924
> Project: Calcite
> Issue Type: Improvement
>Reporter: Roger Shi
>Assignee: ZheHu
>Priority: Minor
>
> Currently the following SQL is not supported.
> {code}
> select 'A' + 'B' + 'C';
> {code}
> We get the exception:
> {noformat}
> Error while executing SQL "select 'A' + 'B' + 'C'": From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type ' +
> '. Supported form(s): ' + '
> ' + '
> ' + '
> ' + ' (state=,code=0)
> {noformat}
> In this JIRA, I'll fix this by supporting operator "+" as string concat
> operator.
> Similar to CALCITE-1374 and CALCITE-1897.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5745) CONCAT2 function(enabled in Oracle library) gets wrong result when one of the argument is NULL
[
https://issues.apache.org/jira/browse/CALCITE-5745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17728900#comment-17728900
]
ZheHu commented on CALCITE-5745:
Per
[Oracle-19-release|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/About-SQL-Functions.html#GUID-D51AB228-518C-4213-8BD4-F919623D105E],
the Oracle Database currently treats a character value with a length of zero
as null(I think this is the compatible mode you mentioned above). However, they
also mention that "Any arithmetic expression containing a null always evaluates
to null. For example, null added to 10 is null. In fact, all operators
({*}except concatenation{*}) return null when given a null operand."
And the concatenation operator is equivalent to CONCAT function in Oracle.
> CONCAT2 function(enabled in Oracle library) gets wrong result when one of the
> argument is NULL
> --
>
> Key: CALCITE-5745
> URL: https://issues.apache.org/jira/browse/CALCITE-5745
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> Tested CONCAT function in Oracle and got the corresponding results.
> {code}
> select concat('a', cast(null as varchar(20))) from DUAL;
> --- results in 'a'
> {code}
> {code}
> select concat('a', null) from DUAL;
> select 'a' || null from DUAL;
> --- both result in 'a'
> {code}
> In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
> gets null when running:
> {code}
> concat('a', cast(null as varchar))
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5745) CONCAT2 function(enabled in Oracle library) gets wrong result when one of the argument is NULL
ZheHu created CALCITE-5745:
--
Summary: CONCAT2 function(enabled in Oracle library) gets wrong
result when one of the argument is NULL
Key: CALCITE-5745
URL: https://issues.apache.org/jira/browse/CALCITE-5745
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.34.0
Reporter: ZheHu
Assignee: ZheHu
Tested CONCAT function in Oracle and got the corresponding results.
{code}
select concat('a', cast(null as varchar(20))) from DUAL;
--- results in 'a'
{code}
{code}
select concat('a', null) from DUAL;
select 'a' || null from DUAL;
--- both result in 'a'
{code}
In Calcite(see checkConcat2Func() in SqlOperatorTest.java), CONCAT2 function
gets null when running:
{code}
concat('a', cast(null as varchar))
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5741) Add support for MSSQL CONCAT_WS function
ZheHu created CALCITE-5741:
--
Summary: Add support for MSSQL CONCAT_WS function
Key: CALCITE-5741
URL: https://issues.apache.org/jira/browse/CALCITE-5741
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.34.0
Reporter: ZheHu
Assignee: ZheHu
CONCAT_WS function in MSSQL has the following syntax:
{code:java}
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
{code}
This function returns a string resulting from the concatenation, or joining, of
two or more string values in an end-to-end manner. It separates those
concatenated string values with the delimiter specified in the first function
argument. Moreover, the amount of arguments varies from 3 to 254.
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
{code:java}
select concat_ws(',', 'a', 'b', null, null, 'c');
-- result in 'a,b,c'
{code}
For more details, see
[doc|https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-1924) Support operator "+" as string concat operator(enabled in MSSQL library)
[
https://issues.apache.org/jira/browse/CALCITE-1924?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-1924:
---
Summary: Support operator "+" as string concat operator(enabled in MSSQL
library) (was: Support operator "+" as string concat operator)
> Support operator "+" as string concat operator(enabled in MSSQL library)
>
>
> Key: CALCITE-1924
> URL: https://issues.apache.org/jira/browse/CALCITE-1924
> Project: Calcite
> Issue Type: Improvement
>Reporter: Roger Shi
>Assignee: ZheHu
>Priority: Minor
>
> Currently the following SQL is not supported.
> {code}
> select 'A' + 'B' + 'C';
> {code}
> We get the exception:
> {noformat}
> Error while executing SQL "select 'A' + 'B' + 'C'": From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type ' +
> '. Supported form(s): ' + '
> ' + '
> ' + '
> ' + ' (state=,code=0)
> {noformat}
> In this JIRA, I'll fix this by supporting operator "+" as string concat
> operator.
> Similar to CALCITE-1374 and CALCITE-1897.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-1924) Support operator "+" as string concat operator
[
https://issues.apache.org/jira/browse/CALCITE-1924?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu reassigned CALCITE-1924:
--
Assignee: ZheHu
> Support operator "+" as string concat operator
> --
>
> Key: CALCITE-1924
> URL: https://issues.apache.org/jira/browse/CALCITE-1924
> Project: Calcite
> Issue Type: Improvement
>Reporter: Roger Shi
>Assignee: ZheHu
>Priority: Minor
>
> Currently the following SQL is not supported.
> {code}
> select 'A' + 'B' + 'C';
> {code}
> We get the exception:
> {noformat}
> Error while executing SQL "select 'A' + 'B' + 'C'": From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type ' +
> '. Supported form(s): ' + '
> ' + '
> ' + '
> ' + ' (state=,code=0)
> {noformat}
> In this JIRA, I'll fix this by supporting operator "+" as string concat
> operator.
> Similar to CALCITE-1374 and CALCITE-1897.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-1924) Support operator "+" as string concat operator
[
https://issues.apache.org/jira/browse/CALCITE-1924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17726892#comment-17726892
]
ZheHu commented on CALCITE-1924:
According to the above discussions, I think we can resolve operator "+" to
CONCAT when both operand are CHARACTER type, and it's only *enabled in MSSQL*.
I've alse run the following SQL in JdbcTest, which means Calcite now is able to
convert strings to numbers.
{code}
SELECT '1' + '2' as alia
-- returns 3
{code}
Hi [~R0ger], do you have any progress yet? I'd like to take a look at this
case to see if I can help.
> Support operator "+" as string concat operator
> --
>
> Key: CALCITE-1924
> URL: https://issues.apache.org/jira/browse/CALCITE-1924
> Project: Calcite
> Issue Type: Improvement
>Reporter: Roger Shi
>Priority: Minor
>
> Currently the following SQL is not supported.
> {code}
> select 'A' + 'B' + 'C';
> {code}
> We get the exception:
> {noformat}
> Error while executing SQL "select 'A' + 'B' + 'C'": From line 1, column 8 to
> line 1, column 16: Cannot apply '+' to arguments of type ' +
> '. Supported form(s): ' + '
> ' + '
> ' + '
> ' + ' (state=,code=0)
> {noformat}
> In this JIRA, I'll fix this by supporting operator "+" as string concat
> operator.
> Similar to CALCITE-1374 and CALCITE-1897.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[
https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17726792#comment-17726792
]
ZheHu commented on CALCITE-5723:
As far as I know, the lower version(<12) Oracle doesn't support either ANSI
FECTH/OFFSET or LIMIT/OFFSET. The LIMIT works through:
{code}
select col from table where rownum<=3
{code}
*rownum* is a keyword in Oracle. The above SQL is equivalent to:
{code}
select col from table limit 3
{code}
> Oracle dialect generates SQL that cannot be recognized by lower version
> Oracle Server(<12) when unparsing OffsetFetch
> -
>
> Key: CALCITE-5723
> URL: https://issues.apache.org/jira/browse/CALCITE-5723
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET
> offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports
> such syntax since 12c.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5723: --- Fix Version/s: 1.35.0 > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu reassigned CALCITE-5723: -- Assignee: ZheHu > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-5723: --- Summary: Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch (was: Oracle dialect generates SQL that cannot be recoginized by lower version Oracle Server(<12) when unparsing OffsetFetch) > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Priority: Minor > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5723) Oracle dialect generates SQL that cannot be recoginized by lower version Oracle Server(<12) when unparsing OffsetFetch
ZheHu created CALCITE-5723: -- Summary: Oracle dialect generates SQL that cannot be recoginized by lower version Oracle Server(<12) when unparsing OffsetFetch Key: CALCITE-5723 URL: https://issues.apache.org/jira/browse/CALCITE-5723 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.34.0 Reporter: ZheHu Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17725169#comment-17725169
]
ZheHu commented on CALCITE-5685:
Thanks for the above advice, in this case I use SqlAlienSystemTypeNameSpec to
represent UNSIGNED, so we don't need add a new SqlTypeName.
And, I defer the validation of whether the expr can be cast to UNSIGNED to
MySQL itself.
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17722102#comment-17722102
]
ZheHu commented on CALCITE-5685:
Before going further, I want to make sure I'm doing it right. I've seen related
work on BigQuery functions, it's clever to defer type check from parsing to
validating, which all based on current SqlType in Calcite. Back to this case,
I've implement UNSIGNED as one type(not sure how to achieve this without
adopting MySQL type system), to make CAST function work as expected. Here is my
work so far
[mysql-convert|https://github.com/ILuffZhe/calcite/commit/9ba88d44ab51f4ff6368a55cd9335c4c63d31aba].
I'll try to find out whether there is one more elegant way to do it.
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17720369#comment-17720369
]
ZheHu commented on CALCITE-5685:
Thanks for the tips. The precision can be handled by MySQL type system.
I still think we may need to parse *SIGNED*/*UNSIGNED*, otherwise we can not
support syntax like convert(12, SIGNED).
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17720284#comment-17720284
]
ZheHu commented on CALCITE-5685:
Here is a tricky point I've encountered. When parsing *SIGNED* and
{*}UNSIGNED{*}, according to
[mysql-doc|https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast],
the former can be treated as {*}BigInt{*}, however, *UNSIGNED* values from 0
to "2^64-1", which we may not match a corresponding RelDataType(need to be
validated in CAST).
During test, I've also find two confusing issues:
# The following SQL in *function.iq* returns 2 instead of 2.00
{code:java}
select cast('2' as decimal(10, 2)) as alia;
{code}
# Testing two SQL(following SQL1 and SQL2) in JdbcTest, both the results are
beyond expectations
{code:java}
SQL1: select cast('2.1' as decimal(10, 2)) as "u";
{code}
I get: *u=2.1*, which should be *2.10*.
{code:java}
SQL2:
final String s = "select cast(\"unit_sales\" as decimal(10, 2)) as \"u\"\n"
+ "from \"sales_fact_1997\" as \"sales_fact_1997\"\n";
CalciteAssert.that()
.with(CalciteAssert.Config.FOODMART_CLONE)
.query(s)
.returnsUnordered("1.00");
{code}
The result has 4 scale, like '1.'.
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17719573#comment-17719573
]
ZheHu commented on CALCITE-5685:
They both work on converting data types, here are some differences I can tell:
# MSSQL CONVERT has a optional third arg called style, MySQL CONVERT only
contains two args
# Order of args is also different. The DataType arg in MSSQL CONVERT is
operand[0], while in MySQL CONVERT, it's operand[1]
# The target data types to convert are not the same, however, it seems that we
don't need to validate.
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5685:
---
Description:
CONVERT function in MySQL has two usage：
# convert(s USING transcodingName): as described in
[CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
# convert(value, type): equivalent to CAST function that converts value to
specific type.
Here are some examples:
* convert(150, CHAR)
* convert(now(), DATE)
* convert('9.5', DECIMAL(10, 2))
* convert(15, SIGNED)
* convert(-2, UNSIGNED)
Noted: for CONVERT or CAST function in MySQL, they only support converting to
some specific data
types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
last two aren't JDBC sql Types).
was:
CONVERT function in MySQL has two usage：
# convert(s USING transcodingName): as described in
[CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
# convert(value, type): equivalent to CAST function that converts value to
specific type.
Here are some examples:
* convert(150, CHAR)
* convert(now(), DATE)
* convert('9.5', DECIMAL(10, 2))
Noted: for CONVERT or CAST function in MySQL, they only support converting to
some specific data
types(binary、char、date、time、datetime、decimal、signed、unsigned).
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> * convert(15, SIGNED)
> * convert(-2, UNSIGNED)
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the
> last two aren't JDBC sql Types).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5685) Support MySQL CONVERT function that works on data types
[
https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5685:
---
Description:
CONVERT function in MySQL has two usage：
# convert(s USING transcodingName): as described in
[CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
# convert(value, type): equivalent to CAST function that converts value to
specific type.
Here are some examples:
* convert(150, CHAR)
* convert(now(), DATE)
* convert('9.5', DECIMAL(10, 2))
Noted: for CONVERT or CAST function in MySQL, they only support converting to
some specific data
types(binary、char、date、time、datetime、decimal、signed、unsigned).
was:
CONVERT function in MySQL has two usage：
# convert(s USING transcodingName): as described in
[CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
# convert(value, type): equivalent to CAST function that converts value to
specific type.
Here are some examples:
* convert(150, CHAR)
* convert(now(), DATE)
* convert('9.5', DECIMAL(10, 2))
Noted: for CONVERT or CAST function in MySQL, they only support converting to
some specific date
types(binary、char、date、time、datetime、decimal、signed、unsigned).
> Support MySQL CONVERT function that works on data types
> ---
>
> Key: CALCITE-5685
> URL: https://issues.apache.org/jira/browse/CALCITE-5685
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
>
> CONVERT function in MySQL has two usage：
> # convert(s USING transcodingName): as described in
> [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
> # convert(value, type): equivalent to CAST function that converts value to
> specific type.
> Here are some examples:
> * convert(150, CHAR)
> * convert(now(), DATE)
> * convert('9.5', DECIMAL(10, 2))
> Noted: for CONVERT or CAST function in MySQL, they only support converting to
> some specific data
> types(binary、char、date、time、datetime、decimal、signed、unsigned).
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5685) Support MySQL CONVERT function that works on data types
ZheHu created CALCITE-5685:
--
Summary: Support MySQL CONVERT function that works on data types
Key: CALCITE-5685
URL: https://issues.apache.org/jira/browse/CALCITE-5685
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.34.0
Reporter: ZheHu
Assignee: ZheHu
CONVERT function in MySQL has two usage：
# convert(s USING transcodingName): as described in
[CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664]
# convert(value, type): equivalent to CAST function that converts value to
specific type.
Here are some examples:
* convert(150, CHAR)
* convert(now(), DATE)
* convert('9.5', DECIMAL(10, 2))
Noted: for CONVERT or CAST function in MySQL, they only support converting to
some specific date
types(binary、char、date、time、datetime、decimal、signed、unsigned).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-317) ClassCastException reading from MySQL TINYINT(4) column
[
https://issues.apache.org/jira/browse/CALCITE-317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17717454#comment-17717454
]
ZheHu commented on CALCITE-317:
---
I re-run those tests above through JDBC-Adapter, it seems the ISSUE has been
resolved in a certain version.
My local Mysql's version is 5.7.30.
> ClassCastException reading from MySQL TINYINT(4) column
> ---
>
> Key: CALCITE-317
> URL: https://issues.apache.org/jira/browse/CALCITE-317
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> Reading from a TINYINT(4) column in a MySQL table, accessed via the JDBC
> adapter, we an exception:
> {code:sql}
> create table t2 (x tinyint(4), y int);
> insert into t2 values (1, 1);
> select "x" from "t2";
> {code}
> {quote}
> Caused by: java.lang.RuntimeException: Error while executing command
> net.hydromatic.optiq.tools.SqlRun$CheckResultCommand@1dde4cb2
> at
> net.hydromatic.optiq.tools.SqlRun$CompositeCommand.execute(SqlRun.java:679)
> at net.hydromatic.optiq.tools.SqlRun.execute(SqlRun.java:103)
> ... 28 more
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.lang.Byte
> at Baz$1$1.current(Unknown Source)
> at
> net.hydromatic.optiq.runtime.ObjectEnumeratorCursor$ObjectEnumeratorGetter.getObject(ObjectEnumeratorCursor.java:60)
> at
> net.hydromatic.optiq.runtime.AbstractCursor$AccessorImpl.getObject(AbstractCursor.java:280)
> at
> net.hydromatic.optiq.runtime.AbstractCursor$AccessorImpl.getString(AbstractCursor.java:223)
> at
> net.hydromatic.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:205)
> {quote}
> A different exception if the table has just one column.
> {code:sql}
> create table t1 (x tinyint(4));
> insert into t1 values (1);
> select "x" from "t1";
> {code}
> {quote}
> > Caused by: java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast
> > to java.lang.Comparable
> > at
> > net.hydromatic.optiq.impl.clone.ColumnLoader.load(ColumnLoader.java:215)
> > at
> > net.hydromatic.optiq.impl.clone.ColumnLoader.(ColumnLoader.java:103)
> {quote}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5664) Support CONVERT function with "USING" keyword
[
https://issues.apache.org/jira/browse/CALCITE-5664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-5664:
---
Description:
After CALCITE-111, we now support CONVERT(charValue, srcCharsetName,
destCharsetName) that works on converting character sets.
CONVERT(charValue USING transcodingName) also fits the SQL standard, and MySQL
has implement it, which is the same as TRANSLATE(charValue USING
transcodingName)
For example:
* select convert('employ_id' using utf16) as alia from employee
* select translate('employ_id' using utf8) as alia from employee
Noted: Calcite already supports this from the parser.
was:
After CALCITE-111, we now support CONVERT(charValue, srcCharsetName,
destCharsetName) that works on converting character sets.
CONVERT(charValue USING transcodingName) also fits the SQL standard, and MySQL
has implement it. For example:
* select convert('employ_id' using utf16) as alia from employee
Noted: Calcite already supports this from the parser.
> Support CONVERT function with "USING" keyword
> -
>
> Key: CALCITE-5664
> URL: https://issues.apache.org/jira/browse/CALCITE-5664
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
>
> After CALCITE-111, we now support CONVERT(charValue, srcCharsetName,
> destCharsetName) that works on converting character sets.
> CONVERT(charValue USING transcodingName) also fits the SQL standard, and
> MySQL has implement it, which is the same as TRANSLATE(charValue USING
> transcodingName)
> For example:
> * select convert('employ_id' using utf16) as alia from employee
> * select translate('employ_id' using utf8) as alia from employee
> Noted: Calcite already supports this from the parser.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5664) Support CONVERT function with "USING" keyword
ZheHu created CALCITE-5664:
--
Summary: Support CONVERT function with "USING" keyword
Key: CALCITE-5664
URL: https://issues.apache.org/jira/browse/CALCITE-5664
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.34.0
Reporter: ZheHu
Assignee: ZheHu
After CALCITE-111, we now support CONVERT(charValue, srcCharsetName,
destCharsetName) that works on converting character sets.
CONVERT(charValue USING transcodingName) also fits the SQL standard, and MySQL
has implement it. For example:
* select convert('employ_id' using utf16) as alia from employee
Noted: Calcite already supports this from the parser.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-111) Support CONVERT function, for changing character sets
[
https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17713881#comment-17713881
]
ZheHu commented on CALCITE-111:
---
[~julianhyde] Thanks for your review. I've already seen your changes, which all
look good to me! You can merge this from your branch if it's OK.
> Support CONVERT function, for changing character sets
> -
>
> Key: CALCITE-111
> URL: https://issues.apache.org/jira/browse/CALCITE-111
> Project: Calcite
> Issue Type: Improvement
>Reporter: GitHub Import
>Assignee: ZheHu
>Priority: Major
> Labels: github-import, pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Support the CONVERT function, to convert strings from one character set to
> another.
> CONVERT is defined in SQL:2011 as a feature called {{ transliteration>}}. Here is an example:
> {code}
> SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd'
> {code}
> The [MySQL
> site|https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_convert]
> has other examples.
> {noformat}
> Imported from GitHub
> Url: https://github.com/julianhyde/optiq/issues/111
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement,
> Created at: Tue Jan 14 03:40:53 CET 2014
> State: open
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5257) NVARCHAR is treated as "UNKNOWN TYPE" when searching Oracle
ZheHu created CALCITE-5257:
--
Summary: NVARCHAR is treated as "UNKNOWN TYPE" when searching
Oracle
Key: CALCITE-5257
URL: https://issues.apache.org/jira/browse/CALCITE-5257
Project: Calcite
Issue Type: Bug
Components: avatica
Affects Versions: avatica-1.22.0
Reporter: ZheHu
Assignee: ZheHu
When I run the following SQL to query Oracle through Calcite:
{code:java}
select NVARCHAR_COL from oracleSchema.t1
{code}
A RuntimeException is thrown by Avatica(in Method
AbstractCursor.createAccessor()):
{code:java}
unknown type -9(which represents NVARCHAR)
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5124) LIMIT won't work when GROUP BY two or more columns in Elasticsearch Adapter
ZheHu created CALCITE-5124:
--
Summary: LIMIT won't work when GROUP BY two or more columns in
Elasticsearch Adapter
Key: CALCITE-5124
URL: https://issues.apache.org/jira/browse/CALCITE-5124
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.30.0
Reporter: ZheHu
Add one doc(like following doc4) in AggregationTest :
{code:java}
String doc4 = "{val1:1, cat4:'2018-01-02'}"
{code}
Then running the following test case:
{code:java}
@Test void dateCat2() {
CalciteAssert.that()
.with(AggregationTest::createConnection)
.query("select val1, cat4 from view group by val1, cat4 limit 2")
.returnsUnordered("val1=1; cat4=151476480",
"val1=1; cat4=151485120",
"val1=null; cat4=157610880");
}
{code}
We can see that *+limit 2+* in SQL doesn't take effect. The generated ES script
is:
{code:java}
{
"_source": false,
"size": 0,
"stored_fields": "_none_",
"aggregations": {
"g_val1": {
"terms": {
"field": "val1",
"missing": -9223372036854775808,
"size": 2
},
"aggregations": {
"g_cat4": {
"terms": {
"field": "cat4",
"missing": 25340221440,
"size": 2
}
}
}
}
}
}
{code}
There are two bucket aggregations in the script, which both have the size 2.
However, the size can only control the doc's num for the current bucket, when
two buckets interact, the total results cannot be assured.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-4954) Group TEXT field failed in Elasticsearch Adapter
[
https://issues.apache.org/jira/browse/CALCITE-4954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17492021#comment-17492021
]
ZheHu commented on CALCITE-4954:
Hi, this [patch|https://github.com/apache/calcite/pull/2695] needs a final
review(already approved by one reviewer), any comments would be much appreciate.
> Group TEXT field failed in Elasticsearch Adapter
>
>
> Key: CALCITE-4954
> URL: https://issues.apache.org/jira/browse/CALCITE-4954
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Adding a text type field "cat6" in AggregationTest.java, I've run the
> following SQL:
> {code:java}
> @Test void testGroupTextField() {
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query("select cat6 from view group by cat6")
> .returns("EXPR$0=1\n");
> }
> {code}
> and I get such exception:
> {code:java}
> {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Text
> fields are not optimised for operations that require per-document field data
> like aggregations and sorting, so these operations are disabled by default.
> Please use a keyword field instead. Alternatively, set fielddata=true on
> [cat6] in order to load field data by uninverting the inverted index. Note
> that this can use significant memory."}],..(useless message),"status":400}
> {code}
> As Elasticsearch does supports grouping text field(memory consumed), it's
> better to show SQL users how to implement it in Calcite by enriching ES's
> test cases.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-111) Support CONVERT function, for changing character sets
[ https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17486185#comment-17486185 ] ZheHu commented on CALCITE-111: --- Hi, [~julianhyde]. I've submit a [patch|https://github.com/apache/calcite/pull/2705] for this, feel free to comment when you have time. > Support CONVERT function, for changing character sets > - > > Key: CALCITE-111 > URL: https://issues.apache.org/jira/browse/CALCITE-111 > Project: Calcite > Issue Type: Improvement >Reporter: GitHub Import >Assignee: ZheHu >Priority: Major > Labels: github-import, pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Support the CONVERT function, to convert strings from one character set to > another. > CONVERT is defined in SQL:2011 as a feature calledtransliteration>. Here is an example: > ```sql > SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd' > ``` > The href="http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html";>MySQL > site has other examples. > Imported from GitHub > Url: https://github.com/julianhyde/optiq/issues/111 > Created by: [julianhyde|https://github.com/julianhyde] > Labels: enhancement, > Created at: Tue Jan 14 03:40:53 CET 2014 > State: open -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-111) Support CONVERT function, for changing character sets
[
https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17482169#comment-17482169
]
ZheHu commented on CALCITE-111:
---
Based on Parser.jj and testConvertAndTranslate() in SqlParserTest.java, I find
CONVERT in Calcite same with the one in Mysql. So my former tests in JdbcTest
are:
{code:java}
1.select convert(cast(employee_id as varchar) using utf8) as alia from employee
limit 3
{code}
{code:java}
2.select convert(cast(null as varchar) using utf16) as alia from employee limit
1
{code}
{code:java}
3.select convert('' using utf16) as alia from employee
{code}
{code:java}
4.select convert('employ_id' using utf16) as alia from employee
{code}
{code:java}
5.select employee_id from employee where convert(cast(employee_id as varchar)
using utf8) <> 1 limit 3
{code}
According to the description of ** in
{*}ISO-SQL-2016{*}(chapter 4.2.3.2) , CONVERT in Postgresql fits the standard
well. So, the existing semantics in Parser.jj need to be modified, so does the
use cases above. But I'm not sure if we need to support *MYSQL_CONVERT* in
Calcite(if yes, we might need to file another JIRA case to record).
As for TRANSLATE(not TRANSLATE3), I haven't found any proof for it yet, I will
spend some time searching afterwards.
> Support CONVERT function, for changing character sets
> -
>
> Key: CALCITE-111
> URL: https://issues.apache.org/jira/browse/CALCITE-111
> Project: Calcite
> Issue Type: Improvement
>Reporter: GitHub Import
>Assignee: ZheHu
>Priority: Major
> Labels: github-import
>
> Support the CONVERT function, to convert strings from one character set to
> another.
> CONVERT is defined in SQL:2011 as a feature called transliteration>. Here is an example:
> ```sql
> SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd'
> ```
> The href="http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html";>MySQL
> site has other examples.
> Imported from GitHub
> Url: https://github.com/julianhyde/optiq/issues/111
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement,
> Created at: Tue Jan 14 03:40:53 CET 2014
> State: open
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Assigned] (CALCITE-111) Support CONVERT function, for changing character sets
[ https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu reassigned CALCITE-111: - Assignee: ZheHu > Support CONVERT function, for changing character sets > - > > Key: CALCITE-111 > URL: https://issues.apache.org/jira/browse/CALCITE-111 > Project: Calcite > Issue Type: Improvement >Reporter: GitHub Import >Assignee: ZheHu >Priority: Major > Labels: github-import > > Support the CONVERT function, to convert strings from one character set to > another. > CONVERT is defined in SQL:2011 as a feature calledtransliteration>. Here is an example: > ```sql > SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd' > ``` > The href="http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html";>MySQL > site has other examples. > Imported from GitHub > Url: https://github.com/julianhyde/optiq/issues/111 > Created by: [julianhyde|https://github.com/julianhyde] > Labels: enhancement, > Created at: Tue Jan 14 03:40:53 CET 2014 > State: open -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-111) Support CONVERT function, for changing character sets
[ https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17481160#comment-17481160 ] ZheHu commented on CALCITE-111: --- Hi, [~julianhyde] . Based on your conversation in CALCITE-4014, I have two questions about this issue: # Does CONVERT function only work between two String value? I tried convert(int_field using utf8) in mysql, which also succeeded. # According to the java_doc, TRANSLATE works the same as CONVERT. Do we need to implement those two funcs in this case(including test cases)? If yes, can we add a new SqlKind like *CONVERT* on behalf of those two, instead of OTHER_FUNCTION. After my local test, I find the following steps might work: * During validation, instead of identifier, treat and validate the param Charset by SqlUtil.getCharset(charsetName), which only support corresponding Standard Charsets in *java.nio.charset.Charset*. * Add a ConvertImplementor in RexImpTable for the codegen of CONVERT/TRANSLATE func. If I understand this correctly, I'm happy to take this issue. > Support CONVERT function, for changing character sets > - > > Key: CALCITE-111 > URL: https://issues.apache.org/jira/browse/CALCITE-111 > Project: Calcite > Issue Type: Improvement >Reporter: GitHub Import >Priority: Major > Labels: github-import > > Support the CONVERT function, to convert strings from one character set to > another. > CONVERT is defined in SQL:2011 as a feature calledtransliteration>. Here is an example: > ```sql > SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd' > ``` > The href="http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html";>MySQL > site has other examples. > Imported from GitHub > Url: https://github.com/julianhyde/optiq/issues/111 > Created by: [julianhyde|https://github.com/julianhyde] > Labels: enhancement, > Created at: Tue Jan 14 03:40:53 CET 2014 > State: open -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4992) RestClient resource leak in ElasticSearch adapter
[ https://issues.apache.org/jira/browse/CALCITE-4992?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17480122#comment-17480122 ] ZheHu commented on CALCITE-4992: Hi, [~dzamo] . Thanks for reporting this. I've left some minor comments on Github. By the way, it'll be very helpful if you simply show us how Drill's ES plugin make use of ES Adapter in Calcite. > RestClient resource leak in ElasticSearch adapter > - > > Key: CALCITE-4992 > URL: https://issues.apache.org/jira/browse/CALCITE-4992 > Project: Calcite > Issue Type: Bug > Components: elasticsearch-adapter >Affects Versions: 1.29.0 >Reporter: James Turton >Priority: Minor > Labels: pull-request-available > Fix For: 1.30.0 > > Time Spent: 20m > Remaining Estimate: 0h > > RestClient instances should receive a `close()`call at the end of their > lifecyle, as documented here. > [https://www.elastic.co/guide/en/elasticsearch/client/java-rest/current/java-rest-low-usage-initialization.html] > We have monitored file descriptor leaks on systems running Apache Drill and > found that they are associated with Drill's ElasticSearch plugin, and that in > turn makes use of Calcite's ElasticSearch adapter. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Resolved] (CALCITE-4965) IS NOT NULL failed in Elasticsearch Adapter
[
https://issues.apache.org/jira/browse/CALCITE-4965?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu resolved CALCITE-4965.
Resolution: Fixed
> IS NOT NULL failed in Elasticsearch Adapter
> ---
>
> Key: CALCITE-4965
> URL: https://issues.apache.org/jira/browse/CALCITE-4965
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.30.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Running the following test in BooleanLogicTest failed:
> {code:java}
> @Test void testIsNotNull() {
> CalciteAssert.that()
> .with(newConnectionFactory())
> // "where num is not null" also failed
> .query("select * from view where num > 42 or num < 42 or num = 42")
> .returns("");
> }
> {code}
> Exception message:
> {code:java}
> PredicateAnalyzer$PredicateAnalyzerException: Unsupported expression:
> [org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$CastExpression@26545ea]
> {code}
> The filter condition in query "num > 42 or num < 42 or num = 42" is optimized
> to "IS NOT NULL(CAST(ITEM($0, 'int')):INTEGER)". However, converting the
> optimized RexNode to Elasticsearch query filter failed.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4965) IS NOT NULL failed in Elasticsearch Adapter
[
https://issues.apache.org/jira/browse/CALCITE-4965?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-4965:
---
Fix Version/s: 1.30.0
> IS NOT NULL failed in Elasticsearch Adapter
> ---
>
> Key: CALCITE-4965
> URL: https://issues.apache.org/jira/browse/CALCITE-4965
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.30.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Running the following test in BooleanLogicTest failed:
> {code:java}
> @Test void testIsNotNull() {
> CalciteAssert.that()
> .with(newConnectionFactory())
> // "where num is not null" also failed
> .query("select * from view where num > 42 or num < 42 or num = 42")
> .returns("");
> }
> {code}
> Exception message:
> {code:java}
> PredicateAnalyzer$PredicateAnalyzerException: Unsupported expression:
> [org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$CastExpression@26545ea]
> {code}
> The filter condition in query "num > 42 or num < 42 or num = 42" is optimized
> to "IS NOT NULL(CAST(ITEM($0, 'int')):INTEGER)". However, converting the
> optimized RexNode to Elasticsearch query filter failed.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement with a bind variable
[
https://issues.apache.org/jira/browse/CALCITE-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17475027#comment-17475027
]
ZheHu commented on CALCITE-4985:
[~nobigo] Sorry for misunderstanding your advice, I've updated the summary.
> Elasticsearch Adapter fails to execute a prepared statement with a bind
> variable
>
>
> Key: CALCITE-4985
> URL: https://issues.apache.org/jira/browse/CALCITE-4985
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When running the following test in AggregationTest:
> {code:java}
> @Test void testPrepare() {
> String sql = "select * from view where cat5=?";
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query(sql)
> // currently, it doesn't work with or without the following
> PrepareStatementConsumer
> .consumesPreparedStatement(p -> p.setInt(1, 1))
> .returns("a");
> }
> {code}
> I got such exception:
> {code:java}
> Suppressed:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
> Unexpected combination of expressions [left:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
> [right: null]
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
> org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> {code}
> Useful reference in JDBC Adapter: see
> https://issues.apache.org/jira/browse/CALCITE-563
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement with a bind variable
[
https://issues.apache.org/jira/browse/CALCITE-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-4985:
---
Summary: Elasticsearch Adapter fails to execute a prepared statement with a
bind variable (was: Elasticsearch Adapter fails to execute a prepared
statement)
> Elasticsearch Adapter fails to execute a prepared statement with a bind
> variable
>
>
> Key: CALCITE-4985
> URL: https://issues.apache.org/jira/browse/CALCITE-4985
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When running the following test in AggregationTest:
> {code:java}
> @Test void testPrepare() {
> String sql = "select * from view where cat5=?";
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query(sql)
> // currently, it doesn't work with or without the following
> PrepareStatementConsumer
> .consumesPreparedStatement(p -> p.setInt(1, 1))
> .returns("a");
> }
> {code}
> I got such exception:
> {code:java}
> Suppressed:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
> Unexpected combination of expressions [left:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
> [right: null]
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
> org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> {code}
> Useful reference in JDBC Adapter: see
> https://issues.apache.org/jira/browse/CALCITE-563
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Comment Edited] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement
[
https://issues.apache.org/jira/browse/CALCITE-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17475024#comment-17475024
]
ZheHu edited comment on CALCITE-4985 at 1/13/22, 12:27 AM:
---
So far, this issue has't got there about dealing with a bind variable.
In ElasticsearchFilter, PredicateAnalyzerTranslator cannot handle
RexDynamicParam during translateMatch(*where cat5=?*), which throws an
exception as the above description mentioned.
I think we should finish the translation for condition, and then I'll figure
whether a bind variable can work or not.
By the way, I've updated the description to make it more clear.
was (Author: vae):
So far, this issue has't got there about dealing with a bind variable.
In ElasticsearchFilter, PredicateAnalyzerTranslator cannot handle
RexDynamicParam during translateMatch(*where cat5=?*), which throws an
exception as the above description mentioned.
I think we should finish the translation for condition, and then I'll figure
whether a bind variable can work or not.
> Elasticsearch Adapter fails to execute a prepared statement
> ---
>
> Key: CALCITE-4985
> URL: https://issues.apache.org/jira/browse/CALCITE-4985
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When running the following test in AggregationTest:
> {code:java}
> @Test void testPrepare() {
> String sql = "select * from view where cat5=?";
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query(sql)
> // currently, it doesn't work with or without the following
> PrepareStatementConsumer
> .consumesPreparedStatement(p -> p.setInt(1, 1))
> .returns("a");
> }
> {code}
> I got such exception:
> {code:java}
> Suppressed:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
> Unexpected combination of expressions [left:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
> [right: null]
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
> org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> {code}
> Useful reference in JDBC Adapter: see
> https://issues.apache.org/jira/browse/CALCITE-563
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement
[
https://issues.apache.org/jira/browse/CALCITE-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-4985:
---
Description:
When running the following test in AggregationTest:
{code:java}
@Test void testPrepare() {
String sql = "select * from view where cat5=?";
CalciteAssert.that()
.with(newConnectionFactory())
.query(sql)
// currently, it doesn't work with or without the following
PrepareStatementConsumer
.consumesPreparedStatement(p -> p.setInt(1, 1))
.returns("a");
}
{code}
I got such exception:
{code:java}
Suppressed:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
Unexpected combination of expressions [left:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
[right: null]
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
{code}
Useful reference in JDBC Adapter: see
https://issues.apache.org/jira/browse/CALCITE-563
was:
When running the following test in AggregationTest:
{code:java}
@Test void testPrepare() {
String sql = "select * from view where cat5=?";
CalciteAssert.that()
.with(newConnectionFactory())
.query(sql)
.consumesPreparedStatement(p -> p.setInt(1, 1))
.returns("a");
}
{code}
I got such exception:
{code:java}
Suppressed:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
Unexpected combination of expressions [left:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
[right: null]
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
{code}
Useful reference in JDBC Adapter: see
https://issues.apache.org/jira/browse/CALCITE-563
> Elasticsearch Adapter fails to execute a prepared statement
> ---
>
> Key: CALCITE-4985
> URL: https://issues.apache.org/jira/browse/CALCITE-4985
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When running the following test in AggregationTest:
> {code:java}
> @Test void testPrepare() {
> String sql = "sel
[jira] [Commented] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement
[
https://issues.apache.org/jira/browse/CALCITE-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17475024#comment-17475024
]
ZheHu commented on CALCITE-4985:
So far, this issue has't got there about dealing with a bind variable.
In ElasticsearchFilter, PredicateAnalyzerTranslator cannot handle
RexDynamicParam during translateMatch(*where cat5=?*), which throws an
exception as the above description mentioned.
I think we should finish the translation for condition, and then I'll figure
whether a bind variable can work or not.
> Elasticsearch Adapter fails to execute a prepared statement
> ---
>
> Key: CALCITE-4985
> URL: https://issues.apache.org/jira/browse/CALCITE-4985
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When running the following test in AggregationTest:
> {code:java}
> @Test void testPrepare() {
> String sql = "select * from view where cat5=?";
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query(sql)
> .consumesPreparedStatement(p -> p.setInt(1, 1))
> .returns("a");
> }
> {code}
> I got such exception:
> {code:java}
> Suppressed:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
> Unexpected combination of expressions [left:
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
> [right: null]
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
> org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
> org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
> {code}
> Useful reference in JDBC Adapter: see
> https://issues.apache.org/jira/browse/CALCITE-563
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4985) Elasticsearch Adapter fails to execute a prepared statement
ZheHu created CALCITE-4985:
--
Summary: Elasticsearch Adapter fails to execute a prepared
statement
Key: CALCITE-4985
URL: https://issues.apache.org/jira/browse/CALCITE-4985
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.29.0
Reporter: ZheHu
Assignee: ZheHu
When running the following test in AggregationTest:
{code:java}
@Test void testPrepare() {
String sql = "select * from view where cat5=?";
CalciteAssert.that()
.with(newConnectionFactory())
.query(sql)
.consumesPreparedStatement(p -> p.setInt(1, 1))
.returns("a");
}
{code}
I got such exception:
{code:java}
Suppressed:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$PredicateAnalyzerException:
Unexpected combination of expressions [left:
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$NamedFieldExpression@4a325e09]
[right: null]
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.swap(PredicateAnalyzer.java:506)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:353)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:243)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.visitCall(PredicateAnalyzer.java:127)
org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer.analyze(PredicateAnalyzer.java:112)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter$PredicateAnalyzerTranslator.translateMatch(ElasticsearchFilter.java:102)
org.apache.calcite.adapter.elasticsearch.ElasticsearchFilter.implement(ElasticsearchFilter.java:67)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:62)
org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:129)
org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:67)
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111)
{code}
Useful reference in JDBC Adapter: see
https://issues.apache.org/jira/browse/CALCITE-563
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4519) INSERT INTO mysql table failed with AUTO_INCREMENT field
[
https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17469918#comment-17469918
]
ZheHu commented on CALCITE-4519:
Additional info (SQL behavior inconsistency):
For the above my_test table in mysql, SQL below is supported, even though
column *id* has NOT NULL property:
{code:java}
insert into my_test(id, name) values(null, "name")
{code}
However, same SQL in PG and HSQLDB is not supported.
> INSERT INTO mysql table failed with AUTO_INCREMENT field
>
>
> Key: CALCITE-4519
> URL: https://issues.apache.org/jira/browse/CALCITE-4519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.26.0
> Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>Reporter: wang_da_ye
>Assignee: ZheHu
>Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem. this function parse row
> name, and transform to RelProtoDataType object.get meta data used
> DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName,
> tableName, null);{code}
>
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but
> this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no
> default value and does not allow NULLs
> the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this
> function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may
> be can skip the validate.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4519) INSERT INTO mysql table failed with AUTO_INCREMENT field
[
https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-4519:
---
Summary: INSERT INTO mysql table failed with AUTO_INCREMENT field (was:
INSERT mysql table operate failure)
> INSERT INTO mysql table failed with AUTO_INCREMENT field
>
>
> Key: CALCITE-4519
> URL: https://issues.apache.org/jira/browse/CALCITE-4519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.26.0
> Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>Reporter: wang_da_ye
>Assignee: ZheHu
>Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem. this function parse row
> name, and transform to RelProtoDataType object.get meta data used
> DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName,
> tableName, null);{code}
>
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but
> this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no
> default value and does not allow NULLs
> the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this
> function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may
> be can skip the validate.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Assigned] (CALCITE-4519) INSERT mysql table operate failure
[
https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu reassigned CALCITE-4519:
--
Assignee: ZheHu
> INSERT mysql table operate failure
> --
>
> Key: CALCITE-4519
> URL: https://issues.apache.org/jira/browse/CALCITE-4519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.26.0
> Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>Reporter: wang_da_ye
>Assignee: ZheHu
>Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem. this function parse row
> name, and transform to RelProtoDataType object.get meta data used
> DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName,
> tableName, null);{code}
>
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but
> this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no
> default value and does not allow NULLs
> the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this
> function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may
> be can skip the validate.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4547) Support Java 16 and 17
[ https://issues.apache.org/jira/browse/CALCITE-4547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-4547: --- Description: Support building and running on [Java 16|https://jdk.java.net/16/] (OpenJDK 16) and [Java 17|https://jdk.java.net/17/](OpenJDK 17). This will require an [upgrade to Gradle 7|https://melix.github.io/blog/2021/03/gradle-java16.html], because Gradle 6 will not support Java 16. was: Support building and running on [Java 16|https://jdk.java.net/16/] (OpenJDK 16). This will require an [upgrade to Gradle 7|https://melix.github.io/blog/2021/03/gradle-java16.html], because Gradle 6 will not support Java 16. > Support Java 16 and 17 > -- > > Key: CALCITE-4547 > URL: https://issues.apache.org/jira/browse/CALCITE-4547 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.29.0 > > Time Spent: 50m > Remaining Estimate: 0h > > Support building and running on [Java 16|https://jdk.java.net/16/] (OpenJDK > 16) and [Java 17|https://jdk.java.net/17/](OpenJDK 17). > This will require an [upgrade to Gradle > 7|https://melix.github.io/blog/2021/03/gradle-java16.html], because Gradle 6 > will not support Java 16. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4965) IS NOT NULL failed in Elasticsearch Adapter
ZheHu created CALCITE-4965:
--
Summary: IS NOT NULL failed in Elasticsearch Adapter
Key: CALCITE-4965
URL: https://issues.apache.org/jira/browse/CALCITE-4965
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.28.0
Reporter: ZheHu
Assignee: ZheHu
Running the following test in BooleanLogicTest failed:
{code:java}
@Test void testIsNotNull() {
CalciteAssert.that()
.with(newConnectionFactory())
// "where num is not null" also failed
.query("select * from view where num > 42 or num < 42 or num = 42")
.returns("");
}
{code}
Exception message:
{code:java}
PredicateAnalyzer$PredicateAnalyzerException: Unsupported expression:
[org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$CastExpression@26545ea]
{code}
The filter condition in query "num > 42 or num < 42 or num = 42" is optimized
to "IS NOT NULL(CAST(ITEM($0, 'int')):INTEGER)". However, converting the
optimized RexNode to Elasticsearch query filter failed.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4960) Enable unit tests in Elasticsearch Adapter
ZheHu created CALCITE-4960: -- Summary: Enable unit tests in Elasticsearch Adapter Key: CALCITE-4960 URL: https://issues.apache.org/jira/browse/CALCITE-4960 Project: Calcite Issue Type: Improvement Components: elasticsearch-adapter Affects Versions: 1.28.0 Reporter: ZheHu Assignee: ZheHu Currently, the following unit test cases are disabled in ES Adapter due to CI failure(see CALCITE-4101): * BooleanLogicTest * ElasticSearchAdapterTest * MatchTest * Projection2Test * ProjectionTest * ScrollingTest As a result, when it comes to modifying ES Adapter(bug fix or new features), like the [discussion|https://github.com/apache/calcite/pull/2657#discussion_r774472022] mentioned, it's unreasonable to add all the test cases in AggregationTest.java or somewhere else not that suitable. So, the RestClient timeout issue during CI should be fixed. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4101) Calcite PR CI offten failed due to elasticsearch:test
[ https://issues.apache.org/jira/browse/CALCITE-4101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17464203#comment-17464203 ] ZheHu commented on CALCITE-4101: Hi, [~Aron.tao]. Forgive me throwing a question under a closed issue. I've seen many UT in ES Adapter are disabled after this issue due to CI failure. I'm wandering if it's better to fix timeout issue rather than disable those UT? Or, do you have any failure message about this(I can't find it here, neither do Github)? That would be very helpful for other contributors. Thanks! > Calcite PR CI offten failed due to elasticsearch:test > - > > Key: CALCITE-4101 > URL: https://issues.apache.org/jira/browse/CALCITE-4101 > Project: Calcite > Issue Type: Improvement > Components: elasticsearch-adapter >Reporter: Jiatao Tao >Assignee: Jiatao Tao >Priority: Major > Fix For: 1.24.0 > > Time Spent: 40m > Remaining Estimate: 0h > > UT uses `EmbeddedElasticsearchPolicy#restClient()` has risk connection > timeout: > AggregationTest > BooleanLogicTest > ElasticSearchAdapterTest > MatchTest > Projection2Test > ProjectionTest > ScrollingTest > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4860) In Elasticsearch adapter, support NULLS FIRST and NULLS LAST query
[
https://issues.apache.org/jira/browse/CALCITE-4860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17463791#comment-17463791
]
ZheHu commented on CALCITE-4860:
Hi, [~nobigo] . I've submitted a PR for this issue, and you can take a look
when you have time.
> In Elasticsearch adapter, support NULLS FIRST and NULLS LAST query
> --
>
> Key: CALCITE-4860
> URL: https://issues.apache.org/jira/browse/CALCITE-4860
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.27.0
>Reporter: duan xiong
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> In Elasticsearch Adapter, The SQL:
> {code:java}
> @Test void integerCat3() {
> CalciteAssert.that()
> .with(newConnectionFactory())
> .query("select cat1 from view order by cat1 nulls first")
> .returns("cat1=a\n" +
> "cat1=b\n" +
> "cat1=null\n");
> }
> {code}
> NULLS FIRST don't handle.
> According
> [es_missing_values|https://www.elastic.co/guide/en/elasticsearch/reference/current/sort-search-results.html#_missing_values]
> . We can add another parameter in the script to implement this.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4547) Support Java 16 and 17
[ https://issues.apache.org/jira/browse/CALCITE-4547?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17463536#comment-17463536 ] ZheHu commented on CALCITE-4547: Hi, [~julianhyde] . I've submit a [patch|https://github.com/apache/calcite/pull/2656] for what you mentioned on dev list. I'll update the release notes of 1.29 RC 0 after it's merged. > Support Java 16 and 17 > -- > > Key: CALCITE-4547 > URL: https://issues.apache.org/jira/browse/CALCITE-4547 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.29.0 > > Time Spent: 20m > Remaining Estimate: 0h > > Support building and running on [Java 16|https://jdk.java.net/16/] (OpenJDK > 16). > This will require an [upgrade to Gradle > 7|https://melix.github.io/blog/2021/03/gradle-java16.html], because Gradle 6 > will not support Java 16. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4547) Support Java 16 and 17
[ https://issues.apache.org/jira/browse/CALCITE-4547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-4547: --- Fix Version/s: 1.29.0 Summary: Support Java 16 and 17 (was: Support Java 16) > Support Java 16 and 17 > -- > > Key: CALCITE-4547 > URL: https://issues.apache.org/jira/browse/CALCITE-4547 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.29.0 > > Time Spent: 10m > Remaining Estimate: 0h > > Support building and running on [Java 16|https://jdk.java.net/16/] (OpenJDK > 16). > This will require an [upgrade to Gradle > 7|https://melix.github.io/blog/2021/03/gradle-java16.html], because Gradle 6 > will not support Java 16. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4954) Group TEXT field failed in Elasticsearch Adapter
ZheHu created CALCITE-4954:
--
Summary: Group TEXT field failed in Elasticsearch Adapter
Key: CALCITE-4954
URL: https://issues.apache.org/jira/browse/CALCITE-4954
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.28.0
Reporter: ZheHu
Assignee: ZheHu
Adding a text type field "cat6" in AggregationTest.java, I've run the following
SQL:
{code:java}
@Test void testGroupTextField() {
CalciteAssert.that()
.with(newConnectionFactory())
.query("select cat6 from view group by cat6")
.returns("EXPR$0=1\n");
}
{code}
and I get such exception:
{code:java}
{"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Text
fields are not optimised for operations that require per-document field data
like aggregations and sorting, so these operations are disabled by default.
Please use a keyword field instead. Alternatively, set fielddata=true on [cat6]
in order to load field data by uninverting the inverted index. Note that this
can use significant memory."}],..(useless message),"status":400}
{code}
As Elasticsearch does supports grouping text field(memory consumed), it's
better to show SQL users how to implement it in Calcite by enriching ES's test
cases.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4949) Evaluate if using "org.codelibs" is appropriate for an Elasticsearch painless dependency
[
https://issues.apache.org/jira/browse/CALCITE-4949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17461779#comment-17461779
]
ZheHu commented on CALCITE-4949:
Hi, [~jnadeau].
I've checked ES discuss(see
[18131|https://github.com/elastic/elasticsearch/issues/18131]) and Maven
Central for "lang-painless" dependency. We still need to stick to the 3rd-party
jar for test purpose.
And I also move some old
[comments|https://github.com/apache/calcite/blob/94e6272d57478a5913cb51e7df5c25e67511229a/elasticsearch/pom.xml#L113]
here, which I find it necessary for other developers.
[PR|https://github.com/apache/calcite/pull/2650] is ready for review.
> Evaluate if using "org.codelibs" is appropriate for an Elasticsearch painless
> dependency
>
>
> Key: CALCITE-4949
> URL: https://issues.apache.org/jira/browse/CALCITE-4949
> Project: Calcite
> Issue Type: Improvement
> Components: elasticsearch-adapter
>Reporter: Jacques Nadeau
>Assignee: ZheHu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> There was an old comment
> [here|https://github.com/apache/calcite/blob/94e6272d57478a5913cb51e7df5c25e67511229a/elasticsearch/pom.xml]
> that said:
> {code}
> Elastic search doesn't export painless script artifact to maven central.
> Using 3rd party version (codelibs)
> This JAR is used only in tests
> {code}
> This was referring to org.codelibs.elasticsearch.module:lang-painless
> dependency. Is this still true or can we update this to a proper first-party
> jar?
> Came up during review of CALCITE-4948
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4949) Evaluate if using "org.codelibs" is appropriate for an Elasticsearch painless dependency
[
https://issues.apache.org/jira/browse/CALCITE-4949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17461719#comment-17461719
]
ZheHu commented on CALCITE-4949:
Sure.
> Evaluate if using "org.codelibs" is appropriate for an Elasticsearch painless
> dependency
>
>
> Key: CALCITE-4949
> URL: https://issues.apache.org/jira/browse/CALCITE-4949
> Project: Calcite
> Issue Type: Improvement
> Components: elasticsearch-adapter
>Reporter: Jacques Nadeau
>Assignee: ZheHu
>Priority: Major
>
> There was an old comment
> [here|https://github.com/apache/calcite/blob/94e6272d57478a5913cb51e7df5c25e67511229a/elasticsearch/pom.xml]
> that said:
> {code}
> Elastic search doesn't export painless script artifact to maven central.
> Using 3rd party version (codelibs)
> This JAR is used only in tests
> {code}
> This was referring to org.codelibs.elasticsearch.module:lang-painless
> dependency. Is this still true or can we update this to a proper first-party
> jar?
> Came up during review of CALCITE-4948
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Assigned] (CALCITE-4896) GROUP BY might get wrong results under certain circumstances in Elasticsearch Adapter
[
https://issues.apache.org/jira/browse/CALCITE-4896?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu reassigned CALCITE-4896:
--
Assignee: ZheHu
> GROUP BY might get wrong results under certain circumstances in Elasticsearch
> Adapter
> -
>
> Key: CALCITE-4896
> URL: https://issues.apache.org/jira/browse/CALCITE-4896
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> In Elasticsearch Adapter, if one field is grouped by along with missing
> value, SQL user might get incorrect results. Take the following case as an
> example:
> {code:java}
> ES mappings: {"int_field1" : integer, "int_field2" : integer}
> doc1 = {"int_field1":1, "int_field2": -2147483648}
> doc1 = {"int_field1":2}
> {code}
> When I try "select count(1) as CNT from view group by int_field2", the result
> is 2.
> For Integer type, the missing value is replaced by Integer.MIN_VALUE, so doc1
> and doc2 will be divided in the same group.
> Any other data types like short、long、float、double, they also have such
> problem.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4898) Upgrading Elasticsearch version from 7.0.1 to 7.15 in Elasticsearch Adapter
[ https://issues.apache.org/jira/browse/CALCITE-4898?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ZheHu updated CALCITE-4898: --- Description: The current Elasticsearch version in Calcite is 7.0.1, while the latest released ES version is 7.15.2. There are many new SQL features added and bugs fixed from 7.0.1 to 7.15. By upgrading the embedded ES, we can make good use of ES's new features, especially about DSL, which enable developers to find one more suitable or effective way to implement features and improve existing functions. Since low level rest client has good compatibility among 7.x versions, RestClient is also upgraded to 7.15.2, which is supported currently. was: The current Elasticsearch version in Calcite is 7.0.1, while the latest released ES version is 7.15.2. There are many new SQL features added and bugs fixed from 7.0.1 to 7.15. By upgrading the embedded ES, we can make good use of ES's new features, especially about DSL, which enable developers to find one more suitable or effective way to implement features and improve existing functions. Since series of 7.x versions have good compatibility, this issue will just upgrade ES server without adjusting RestClient version in Elasticsearch Adapter. > Upgrading Elasticsearch version from 7.0.1 to 7.15 in Elasticsearch Adapter > --- > > Key: CALCITE-4898 > URL: https://issues.apache.org/jira/browse/CALCITE-4898 > Project: Calcite > Issue Type: Improvement > Components: elasticsearch-adapter >Affects Versions: 1.28.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The current Elasticsearch version in Calcite is 7.0.1, while the latest > released ES version is 7.15.2. > There are many new SQL features added and bugs fixed from 7.0.1 to 7.15. By > upgrading the embedded ES, we can make good use of ES's new features, > especially about DSL, which enable developers to find one more suitable or > effective way to implement features and improve existing functions. > Since low level rest client has good compatibility among 7.x versions, > RestClient is also upgraded to 7.15.2, which is supported currently. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Comment Edited] (CALCITE-4519) INSERT mysql table operate failure
[
https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17450049#comment-17450049
]
ZheHu edited comment on CALCITE-4519 at 11/28/21, 2:32 PM:
---
Same problem occurred in Postgresql when I tried to reproduce this issue.
+1 for [~aleigh], in JdbcSchema.java, we can get whether the field is
AUTO_INCREMENT or not through method getRelDataType(). So, through validation,
if ColumnStrategy is NOT_NULLABLE but the targetField is null, we should judge
if the field is AUTO_INCREMENT or not either(validation shall pass if yes).
Hi, [~wang_da_ye], are you working on this? I'v solved this locally, so I'd
like to submit a PR if it's OK for you.
was (Author: vae):
Same problem occurred in Postgresql when I tried to reproduce this issue.
+1 for [~aleigh], in JdbcSchema.java, we can get whether the field is
AUTO_INCREMENT or not through method getRelDataType(). So, in validation, if
ColumnStrategy is NOT_NULLABLE but the targetField is null, we should judge if
the field is AUTO_INCREMENT or not either(validation shall passes if yes).
Hi, [~wang_da_ye], are you working on this? I'v solved this locally, so I'd
like to submit a PR if it's OK for you.
> INSERT mysql table operate failure
> --
>
> Key: CALCITE-4519
> URL: https://issues.apache.org/jira/browse/CALCITE-4519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.26.0
> Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>Reporter: wang_da_ye
>Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem. this function parse row
> name, and transform to RelProtoDataType object.get meta data used
> DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName,
> tableName, null);{code}
>
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but
> this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no
> default value and does not allow NULLs
> the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this
> function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may
> be can skip the validate.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4519) INSERT mysql table operate failure
[
https://issues.apache.org/jira/browse/CALCITE-4519?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17450049#comment-17450049
]
ZheHu commented on CALCITE-4519:
Same problem occurred in Postgresql when I tried to reproduce this issue.
+1 for [~aleigh], in JdbcSchema.java, we can get whether the field is
AUTO_INCREMENT or not through method getRelDataType(). So, in validation, if
ColumnStrategy is NOT_NULLABLE but the targetField is null, we should judge if
the field is AUTO_INCREMENT or not either(validation shall passes if yes).
Hi, [~wang_da_ye], are you working on this? I'v solved this locally, so I'd
like to submit a PR if it's OK for you.
> INSERT mysql table operate failure
> --
>
> Key: CALCITE-4519
> URL: https://issues.apache.org/jira/browse/CALCITE-4519
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.26.0
> Environment: JDK: 15.0.2
> calcite: 1.26.0
> mysql: 5.7.31
>Reporter: wang_da_ye
>Priority: Major
>
> I have a mysql table as follow:
> {code:java}
> | my_test | CREATE TABLE `my_test`(
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`))
> ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
> {code}
>
> my code as follow:
> {code:java}
> Connection connection = Util.getConnect("/model.json");
> Statement statement = connection.createStatement();
> String sql = "INSERT INTO test.my_test(name) VALUES('test...')";
> statement.execute(sql); //a error here
> {code}
>
> debug calcite code, I found:
> JdbcSchema#getRelDataType() maybe some problem. this function parse row
> name, and transform to RelProtoDataType object.get meta data used
> DatabaseMetaData class.
> then,the result set of the metadata is obtained,as follow code:
> {code:java}
> final ResultSet resultSet = metaData.getColumns(catalogName, schemaName,
> tableName, null);{code}
>
> after,in while loop, resultSet get each row meta, like sqlType,nullable.
> my test table "my_test" id is primary key, so this filed is not null, but
> this filed has a "auto_increment" attribute.
> it means that:
> {code:java}
> INSERT INTO test.my_test(name) VALUES('test...'){code}
> this sql run ok, I can omit the id filed.
> in
> {code:java}
> statement.execute(sql);{code}
> this sentence, trigger validate,and throws a exeception:Column 'id' has no
> default value and does not allow NULLs
> the same sql in mysql 5.7.31 run ok, but in calcite get a error,
> JdbcSchema#getRelDataType() there may be a lack of judgment.in this
> function,add a "IS_AUTOINCREMENT" meta info, may be can deal with that,
> in validate logic,if a filed has "nullable" and "autoincrement" attribute may
> be can skip the validate.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4898) Upgrading Elasticsearch version from 7.0.1 to 7.15 in Elasticsearch Adapter
ZheHu created CALCITE-4898: -- Summary: Upgrading Elasticsearch version from 7.0.1 to 7.15 in Elasticsearch Adapter Key: CALCITE-4898 URL: https://issues.apache.org/jira/browse/CALCITE-4898 Project: Calcite Issue Type: Improvement Components: elasticsearch-adapter Affects Versions: 1.28.0 Reporter: ZheHu Assignee: ZheHu The current Elasticsearch version in Calcite is 7.0.1, while the latest released ES version is 7.15.2. There are many new SQL features added and bugs fixed from 7.0.1 to 7.15. By upgrading the embedded ES, we can make good use of ES's new features, especially about DSL, which enable developers to find one more suitable or effective way to implement features and improve existing functions. Since series of 7.x versions have good compatibility, this issue will just upgrade ES server without adjusting RestClient version in Elasticsearch Adapter. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-2689) ES Adapter. Grouping on date / number fields fails
[
https://issues.apache.org/jira/browse/CALCITE-2689?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17446732#comment-17446732
]
ZheHu commented on CALCITE-2689:
OK, I've marked it in
[CALCITE-4896|https://issues.apache.org/jira/browse/CALCITE-4896].
> ES Adapter. Grouping on date / number fields fails
> --
>
> Key: CALCITE-2689
> URL: https://issues.apache.org/jira/browse/CALCITE-2689
> Project: Calcite
> Issue Type: Improvement
> Components: elasticsearch-adapter
>Reporter: Andrei Sereda
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> For [Terms
> Aggregation|https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html]
> missing value has to have same type as group key:
> {code:sql}
> select max(amount), date from orders group by date -- date column is of type
> date (in ES)
> {code}
> Currently single (text) key is used {{__MISSING__}} which fails when grouping
> on non-string fields (eg. dates, numbers or booleans).
> When using {{missing}} (value) query converter should consider field type.
> This logic should be reviewed once we migrate to [composite
> aggregations|https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html]
> (available since
> [6.1|https://www.elastic.co/guide/en/elasticsearch/reference/6.1/release-notes-6.1.0.html]
> see PR[26800|https://github.com/elastic/elasticsearch/pull/26800])
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4896) GROUP BY might get wrong results under certain circumstances in Elasticsearch Adapter
[
https://issues.apache.org/jira/browse/CALCITE-4896?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-4896:
---
External issue URL: (was:
https://issues.apache.org/jira/browse/CALCITE-2689)
> GROUP BY might get wrong results under certain circumstances in Elasticsearch
> Adapter
> -
>
> Key: CALCITE-4896
> URL: https://issues.apache.org/jira/browse/CALCITE-4896
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> In Elasticsearch Adapter, if one field is grouped by along with missing
> value, SQL user might get incorrect results. Take the following case as an
> example:
> {code:java}
> ES mappings: {"int_field1" : integer, "int_field2" : integer}
> doc1 = {"int_field1":1, "int_field2": -2147483648}
> doc1 = {"int_field1":2}
> {code}
> When I try "select count(1) as CNT from view group by int_field2", the result
> is 2.
> For Integer type, the missing value is replaced by Integer.MIN_VALUE, so doc1
> and doc2 will be divided in the same group.
> Any other data types like short、long、float、double, they also have such
> problem.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4896) GROUP BY might get wrong results under certain circumstances in Elasticsearch Adapter
ZheHu created CALCITE-4896:
--
Summary: GROUP BY might get wrong results under certain
circumstances in Elasticsearch Adapter
Key: CALCITE-4896
URL: https://issues.apache.org/jira/browse/CALCITE-4896
Project: Calcite
Issue Type: Bug
Components: elasticsearch-adapter
Affects Versions: 1.28.0
Reporter: ZheHu
In Elasticsearch Adapter, if one field is grouped by along with missing value,
SQL user might get incorrect results. Take the following case as an example:
{code:java}
ES mappings: {"int_field1" : integer, "int_field2" : integer}
doc1 = {"int_field1":1, "int_field2": -2147483648}
doc1 = {"int_field1":2}
{code}
When I try "select count(1) as CNT from view group by int_field2", the result
is 2.
For Integer type, the missing value is replaced by Integer.MIN_VALUE, so doc1
and doc2 will be divided in the same group.
Any other data types like short、long、float、double, they also have such problem.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-2689) ES Adapter. Grouping on date / number fields fails
[
https://issues.apache.org/jira/browse/CALCITE-2689?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17446516#comment-17446516
]
ZheHu commented on CALCITE-2689:
Hi, [~julianhyde][~sereda], recently, when I tried to fix
[CALCITE-4868|https://issues.apache.org/jira/browse/CALCITE-4868?filter=-1], I
found one interesting thing about this issue.
Take Integer type as an example, missing value is replaced by Integer.MIN_VALUE.
{code:java}
doc1 = {"int_field1":1, "int_field2": -2147483648}
doc1 = {"int_field1":2}
{code}
When "int_field2" is grouped by in a query, doc1 and doc2 will be in the same
group. Situation like this is rare, but may confuse others and make them treat
it as a bug.
I did some research on ES scripts about how to aggregate missing value, and
unfortunately, no solution was found.
So, maybe we should add some test cases to tell people about this potential
problem/limitation, what do you think?
> ES Adapter. Grouping on date / number fields fails
> --
>
> Key: CALCITE-2689
> URL: https://issues.apache.org/jira/browse/CALCITE-2689
> Project: Calcite
> Issue Type: Improvement
> Components: elasticsearch-adapter
>Reporter: Andrei Sereda
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.18.0
>
>
> For [Terms
> Aggregation|https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html]
> missing value has to have same type as group key:
> {code:sql}
> select max(amount), date from orders group by date -- date column is of type
> date (in ES)
> {code}
> Currently single (text) key is used {{__MISSING__}} which fails when grouping
> on non-string fields (eg. dates, numbers or booleans).
> When using {{missing}} (value) query converter should consider field type.
> This logic should be reviewed once we migrate to [composite
> aggregations|https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html]
> (available since
> [6.1|https://www.elastic.co/guide/en/elasticsearch/reference/6.1/release-notes-6.1.0.html]
> see PR[26800|https://github.com/elastic/elasticsearch/pull/26800])
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Resolved] (CALCITE-4886) When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator
[
https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu resolved CALCITE-4886.
Resolution: Not A Problem
> When converting SQL to RelNode, SqlOrderBy is missing in sub-query that
> contains SqlSetOperator
> ---
>
> Key: CALCITE-4886
> URL: https://issues.apache.org/jira/browse/CALCITE-4886
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> When I run the following test in SqlToRelConverterTest.java:
> {code:java}
> @Test void testIntersectWithNulls() {
> final String sql = ""
> + "select deptno from\n"
> + "(select deptno from dept\n"
> + "union all\n"
> + "select deptno from emp order by deptno) as alia";
> final RelNode rel = tester.convertSqlToRel(sql).rel;
> System.out.println(RelOptUtil.toString(rel));
> }
> {code}
> The RelNode is:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4886) When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator
[
https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17444569#comment-17444569
]
ZheHu commented on CALCITE-4886:
Yeah, I get the expected result by adding configuration.
Thanks, [~zabetak]
> When converting SQL to RelNode, SqlOrderBy is missing in sub-query that
> contains SqlSetOperator
> ---
>
> Key: CALCITE-4886
> URL: https://issues.apache.org/jira/browse/CALCITE-4886
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> When I run the following test in SqlToRelConverterTest.java:
> {code:java}
> @Test void testIntersectWithNulls() {
> final String sql = ""
> + "select deptno from\n"
> + "(select deptno from dept\n"
> + "union all\n"
> + "select deptno from emp order by deptno) as alia";
> final RelNode rel = tester.convertSqlToRel(sql).rel;
> System.out.println(RelOptUtil.toString(rel));
> }
> {code}
> The RelNode is:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit
[
https://issues.apache.org/jira/browse/CALCITE-4876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17443251#comment-17443251
]
ZheHu commented on CALCITE-4876:
Hi [~julianhyde], I've made some adjustments on SqlSetOperator's precedence,
please review [PR 2609|https://github.com/apache/calcite/pull/2609] if you have
time.
> Converting RelNode to SQL with CalciteSqlDialect gets wrong result while
> EnumerableIntersect is followed by EnumerableLimit
> ---
>
> Key: CALCITE-4876
> URL: https://issues.apache.org/jira/browse/CALCITE-4876
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When I convert the following RelNode to SQL(with CalciteSqlDialect) in
> ToLogicalConverterTest.java:
> {code:java}
> @Test void testIntersect() {
> final RelBuilder builder = builder();
> RelNode rel =
> builder.scan("DEPT")
> .project(builder.field("DEPTNO"))
> .scan("EMP")
> .project(builder.field("DEPTNO"))
> .intersect(true)
> .limit(0, 2)
> .build();
> System.out.println(new
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement());
> {code}
> I get:
> {code:java}
> SELECT *
> FROM SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`
> FETCH NEXT 2 ROWS ONLY
> {code}
> But the expected SQL should be:
> {code:java}
> SELECT *
> FROM (SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`)
> FETCH NEXT 2 ROWS ONLY
> {code}
> Other SetOperators like UNION、MINUS can be converted correctly.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4886) When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator
[
https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17443244#comment-17443244
]
ZheHu commented on CALCITE-4886:
Thanks for your explanation [~sphillips] ，I tried the above SQL in PG, and the
query plan does include SORT node, that's a little confusing.
{code:java}
Sort Key: dept.deptno DESC NULLS LAST
-> Append (cost=0.00..33.80 rows=920 width=4)
-> Seq Scan on dept (cost=0.00..14.10 rows=410 width=4)
-> Seq Scan on emp (cost=0.00..15.10 rows=510 width=4)
{code}
> When converting SQL to RelNode, SqlOrderBy is missing in sub-query that
> contains SqlSetOperator
> ---
>
> Key: CALCITE-4886
> URL: https://issues.apache.org/jira/browse/CALCITE-4886
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> When I run the following test in SqlToRelConverterTest.java:
> {code:java}
> @Test void testIntersectWithNulls() {
> final String sql = ""
> + "select deptno from\n"
> + "(select deptno from dept\n"
> + "union all\n"
> + "select deptno from emp order by deptno) as alia";
> final RelNode rel = tester.convertSqlToRel(sql).rel;
> System.out.println(RelOptUtil.toString(rel));
> }
> {code}
> The RelNode is:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Created] (CALCITE-4886) When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator
ZheHu created CALCITE-4886:
--
Summary: When converting SQL to RelNode, SqlOrderBy is missing in
sub-query that contains SqlSetOperator
Key: CALCITE-4886
URL: https://issues.apache.org/jira/browse/CALCITE-4886
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.28.0
Reporter: ZheHu
When I run the following test in SqlToRelConverterTest.java:
{code:java}
@Test void testIntersectWithNulls() {
final String sql = ""
+ "select deptno from\n"
+ "(select deptno from dept\n"
+ "union all\n"
+ "select deptno from emp order by deptno) as alia";
final RelNode rel = tester.convertSqlToRel(sql).rel;
System.out.println(RelOptUtil.toString(rel));
}
{code}
The RelNode is:
{code:java}
LogicalProject(DEPTNO=[$0])
LogicalUnion(all=[true])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Assigned] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit
[
https://issues.apache.org/jira/browse/CALCITE-4876?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu reassigned CALCITE-4876:
--
Assignee: ZheHu
> Converting RelNode to SQL with CalciteSqlDialect gets wrong result while
> EnumerableIntersect is followed by EnumerableLimit
> ---
>
> Key: CALCITE-4876
> URL: https://issues.apache.org/jira/browse/CALCITE-4876
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Major
>
> When I convert the following RelNode to SQL(with CalciteSqlDialect) in
> ToLogicalConverterTest.java:
> {code:java}
> @Test void testIntersect() {
> final RelBuilder builder = builder();
> RelNode rel =
> builder.scan("DEPT")
> .project(builder.field("DEPTNO"))
> .scan("EMP")
> .project(builder.field("DEPTNO"))
> .intersect(true)
> .limit(0, 2)
> .build();
> System.out.println(new
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement());
> {code}
> I get:
> {code:java}
> SELECT *
> FROM SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`
> FETCH NEXT 2 ROWS ONLY
> {code}
> But the expected SQL should be:
> {code:java}
> SELECT *
> FROM (SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`)
> FETCH NEXT 2 ROWS ONLY
> {code}
> Other SetOperators like UNION、MINUS can be converted correctly.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit
[
https://issues.apache.org/jira/browse/CALCITE-4876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17439941#comment-17439941
]
ZheHu commented on CALCITE-4876:
INTERSECT should have higher precedence to other SetOperators.
Moreover, I find the real problem is SqlJoinOperator having lower precedence
than INTERSECT's. As quoted in SqlSelectOperator, "for FROM clause, use
precedence just below join operator to make sure that an un-joined nested
select will be properly parenthesized", I'm going to make a little adjustments
for some SqlOperators and add necessary tests to ensure the queries are not
broken.
> Converting RelNode to SQL with CalciteSqlDialect gets wrong result while
> EnumerableIntersect is followed by EnumerableLimit
> ---
>
> Key: CALCITE-4876
> URL: https://issues.apache.org/jira/browse/CALCITE-4876
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> When I convert the following RelNode to SQL(with CalciteSqlDialect) in
> ToLogicalConverterTest.java:
> {code:java}
> @Test void testIntersect() {
> final RelBuilder builder = builder();
> RelNode rel =
> builder.scan("DEPT")
> .project(builder.field("DEPTNO"))
> .scan("EMP")
> .project(builder.field("DEPTNO"))
> .intersect(true)
> .limit(0, 2)
> .build();
> System.out.println(new
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement());
> {code}
> I get:
> {code:java}
> SELECT *
> FROM SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`
> FETCH NEXT 2 ROWS ONLY
> {code}
> But the expected SQL should be:
> {code:java}
> SELECT *
> FROM (SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`)
> FETCH NEXT 2 ROWS ONLY
> {code}
> Other SetOperators like UNION、MINUS can be converted correctly.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit
[
https://issues.apache.org/jira/browse/CALCITE-4876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17439678#comment-17439678
]
ZheHu commented on CALCITE-4876:
Hi, [~julianhyde], you are right about this.
When I change the precedence of *INTERSECT_ALL* from {color:#FF}18 to
14{color}(same with UNION and EXCEPT), I get the right SQL expression. Just as
you mentioned above, I also tried the following two testcases:
(1) Converting the below RelNode to SQL with HiveSqlDialect(LIMIT syntax), SQL
expression is correct.
{code:java}
RelNode rel =
builder.scan("DEPT")
.project(builder.field("DEPTNO"))
.scan("EMP")
.project(builder.field("DEPTNO"))
.minus(true)
.limit(0, 2)
.build();
new RelToSqlConverter(SqlDialect.DatabaseProduct.HIVE.getDialect())
.visitRoot(rel)
.asStatement()
.toSqlString(SqlDialect.DatabaseProduct.HIVE.getDialect())
{code}
(2) Running the following SQL in JdbcTest.java, I also get correct results.
{code:java}
CalciteAssert.hr()
.query("select \"deptno\" from \"hr\".\"depts\"\n"
+ "intersect all\n"
+ "select \"deptno\" from \"hr\".\"depts\"\n"
+ "limit 2")
.returns("deptno=40\ndeptno=10\n");
{code}
I might need a bit more time to look further into this precedence issue.
> Converting RelNode to SQL with CalciteSqlDialect gets wrong result while
> EnumerableIntersect is followed by EnumerableLimit
> ---
>
> Key: CALCITE-4876
> URL: https://issues.apache.org/jira/browse/CALCITE-4876
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: ZheHu
>Priority: Major
>
> When I convert the following RelNode to SQL(with CalciteSqlDialect) in
> ToLogicalConverterTest.java:
> {code:java}
> @Test void testIntersect() {
> final RelBuilder builder = builder();
> RelNode rel =
> builder.scan("DEPT")
> .project(builder.field("DEPTNO"))
> .scan("EMP")
> .project(builder.field("DEPTNO"))
> .intersect(true)
> .limit(0, 2)
> .build();
> System.out.println(new
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement());
> {code}
> I get:
> {code:java}
> SELECT *
> FROM SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`
> FETCH NEXT 2 ROWS ONLY
> {code}
> But the expected SQL should be:
> {code:java}
> SELECT *
> FROM (SELECT `DEPTNO`
> FROM `scott`.`DEPT`
> INTERSECT ALL
> SELECT `DEPTNO`
> FROM `scott`.`EMP`)
> FETCH NEXT 2 ROWS ONLY
> {code}
> Other SetOperators like UNION、MINUS can be converted correctly.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit
ZheHu created CALCITE-4876:
--
Summary: Converting RelNode to SQL with CalciteSqlDialect gets
wrong result while EnumerableIntersect is followed by EnumerableLimit
Key: CALCITE-4876
URL: https://issues.apache.org/jira/browse/CALCITE-4876
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.28.0
Reporter: ZheHu
When I convert the following RelNode to SQL(with CalciteSqlDialect) in
ToLogicalConverterTest.java:
{code:java}
@Test void testIntersect() {
final RelBuilder builder = builder();
RelNode rel =
builder.scan("DEPT")
.project(builder.field("DEPTNO"))
.scan("EMP")
.project(builder.field("DEPTNO"))
.intersect(true)
.limit(0, 2)
.build();
System.out.println(new
RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement());
{code}
I get:
{code:java}
SELECT *
FROM SELECT `DEPTNO`
FROM `scott`.`DEPT`
INTERSECT ALL
SELECT `DEPTNO`
FROM `scott`.`EMP`
FETCH NEXT 2 ROWS ONLY
{code}
But the expected SQL should be:
{code:java}
SELECT *
FROM (SELECT `DEPTNO`
FROM `scott`.`DEPT`
INTERSECT ALL
SELECT `DEPTNO`
FROM `scott`.`EMP`)
FETCH NEXT 2 ROWS ONLY
{code}
Other SetOperators like UNION、MINUS can be converted correctly.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
