[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[
https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17837032#comment-17837032
]
EveyWu commented on CALCITE-6363:
--
It's a good idea to add more dynamic filter rules.(y)
> Introduce a rule to derive more filters from inner join condition
> -
>
> Key: CALCITE-6363
> URL: https://issues.apache.org/jira/browse/CALCITE-6363
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: ruanhui
>Priority: Minor
> Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the
> query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 ->
> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or
> inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions
> in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then
> rewrite all predicates involving equivalent inputRefs using inputRef, for
> example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 =
> 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers
> to another inputRef, rewrite the predicate with the literal/constant
> equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2
> = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} =>
> \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825227#comment-17825227 ] EveyWu commented on CALCITE-6278: -- done, CALCITE-6320 has been added to follow up. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
[ https://issues.apache.org/jira/browse/CALCITE-6320?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6320: Description: # Since Spark 2.0, string literals (including regex patterns) are unescaped in the parser phase, details in [AstBuilder|[https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4]] # In Hive, unescape in the node normalization phase(`Dispatcher#dispatch`).[StrExprProcessor|[https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17]] is the processor for processing string unescape. > Parse SQL with Hive-style and Spark-style unescaped character literals > -- > > Key: CALCITE-6320 > URL: https://issues.apache.org/jira/browse/CALCITE-6320 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in the parser phase, details in > [AstBuilder|[https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4]] > # In Hive, unescape in the node normalization > phase(`Dispatcher#dispatch`).[StrExprProcessor|[https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17]] > is the processor for processing string unescape. > > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
EveyWu created CALCITE-6320: Summary: Parse SQL with Hive-style and Spark-style unescaped character literals Key: CALCITE-6320 URL: https://issues.apache.org/jira/browse/CALCITE-6320 Project: Calcite Issue Type: Bug Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
EveyWu created CALCITE-6320: Summary: Parse SQL with Hive-style and Spark-style unescaped character literals Key: CALCITE-6320 URL: https://issues.apache.org/jira/browse/CALCITE-6320 Project: Calcite Issue Type: Bug Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825003#comment-17825003 ] EveyWu commented on CALCITE-6278: -- [~julianhyde] The unescape operation has been deleted. Thank you very much for reviewing the code again when you have time. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825002#comment-17825002
]
EveyWu commented on CALCITE-6278:
--
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find; {code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time. I will roll back the handling
of unescape.
> Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
>
>
> Key: CALCITE-6278
> URL: https://issues.apache.org/jira/browse/CALCITE-6278
> Project: Calcite
> Issue Type: Improvement
>Reporter: EveyWu
>Priority: Minor
> Labels: pull-request-available
> Attachments: image-2024-03-07-09-32-27-002.png,
> image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png,
> image-2024-03-09-11-38-08-797.png
>
>
> Add Spark functions that have been implemented but have different
> OperandTypes/Returns.
> Add Function
> [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp],
> [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like]
> # Since this function has the same implementation as the Spark
> [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike]
> function, the implementation can be directly reused.
> # -Since Spark 2.0, string literals (including regex patterns) are unescaped
> in SQL parser, also fix this bug in calcite.-
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180 ]
EveyWu deleted comment on CALCITE-6180:
--
was (Author: eveywu):
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time. I will roll back the handling
of unescape.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # -Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite.- was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu edited comment on CALCITE-6180 at 3/10/24 4:32 AM:
---
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time. I will roll back the handling
of unescape.
was (Author: eveywu):
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time. I will roll back the handling
of unescaping.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu edited comment on CALCITE-6180 at 3/10/24 4:31 AM:
---
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time. I will roll back the handling
of unescaping.
was (Author: eveywu):
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu edited comment on CALCITE-6180 at 3/10/24 4:30 AM:
---
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
was (Author: eveywu):
Sure, in my opinion, in this case, 'unescaping' means handling backslashes in
character literals.
Indeed, if a query is based on table fields instead of literal strings, there
will be query inconsistencies, as escaping may have been done when inserting
data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu edited comment on CALCITE-6180 at 3/10/24 4:30 AM:
---
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
was (Author: eveywu):
1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes
in character literals.
2. Indeed, if a query is based on table fields instead of literal strings,
there will be query inconsistencies, as escaping may have been done when
inserting data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
3. For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu edited comment on CALCITE-6180 at 3/10/24 4:29 AM:
---
Sure, in my opinion, in this case, 'unescaping' means handling backslashes in
character literals.
Indeed, if a query is based on table fields instead of literal strings, there
will be query inconsistencies, as escaping may have been done when inserting
data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
{code:java}
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
{code}
For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
was (Author: eveywu):
Sure, in my opinion, in this case, 'unescaping' means handling backslashes in
character literals.
Indeed, if a query is based on table fields instead of literal strings, there
will be query inconsistencies, as escaping may have been done when inserting
data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
```
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
```
For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[
https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825000#comment-17825000
]
EveyWu commented on CALCITE-6180:
--
Sure, in my opinion, in this case, 'unescaping' means handling backslashes in
character literals.
Indeed, if a query is based on table fields instead of literal strings, there
will be query inconsistencies, as escaping may have been done when inserting
data.
A possible solution is to failover unescape, which may lead to an expansion of
the range that `rlike` can support.
```
boolean find;
try {
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
} catch (Exception patternException) {
find = false;
}
if (!find) {
s = StringEscapeUtils.unescapeJava(s);
pattern = StringEscapeUtils.unescapeJava(pattern);
find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find();
}
return find;
```
For the `rlike` function, I currently can't think of a better way to ensure
that the behavior of table field queries and literal string queries is
consistent with spark and hive at the same time.
> Append possibility to escape backslash in LIKE expression
> -
>
> Key: CALCITE-6180
> URL: https://issues.apache.org/jira/browse/CALCITE-6180
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
>
> The expression below must be processed correctly and return positive result
> {noformat}
> select 'Dev\ops' like 'Dev#\ops' escape '#';
> {noformat}
> Insted it returns :
> Invalid escape sequence exception.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824900#comment-17824900 ] EveyWu edited comment on CALCITE-6278 at 3/9/24 3:54 AM: -- [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in `AstBuilder`: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. was (Author: eveywu): [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|[https://spark.apache.org/docs/latest/api/sql/index.html#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in AstBuilder: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824900#comment-17824900 ] EveyWu commented on CALCITE-6278: -- [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|[https://spark.apache.org/docs/latest/api/sql/index.html#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in AstBuilder: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-38-08-797.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-37-27-816.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-13-49-064.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: (was: image-2024-03-09-11-12-51-658.png) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-12-51-658.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-12-51-658.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Fix Version/s: (was: 1.37.0) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 2:39 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. # Add more unescape tests in `SqlOperatorTest` !image-2024-03-07-09-32-27-002.png|width=426,height=392! was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. !image-2024-03-07-09-32-27-002.png|width=426,height=392! > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 1:32 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. !image-2024-03-07-09-32-27-002.png|width=426,height=392! was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 1:30 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. ``` 0: jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ 0: jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ ``` > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824226#comment-17824226 ] EveyWu commented on CALCITE-6278: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. ``` 0: jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ 0: jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ ``` > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] Since this function has the same implementation as the Big Query [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] function. the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Summary: Add REGEXP, REGEXP_LIKE function (enabled in Spark library) (was: Add REGEXP、REGEXP_LIKE function (enabled in Spark library)) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP、REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Fix Version/s: 1.37.0 > Add REGEXP、REGEXP_LIKE function (enabled in Spark library) > --- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP、REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Summary: Add REGEXP、REGEXP_LIKE function (enabled in Spark library) (was: Add REGEXP function (enabled in Spark library)) > Add REGEXP、REGEXP_LIKE function (enabled in Spark library) > --- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17822349#comment-17822349
]
EveyWu commented on CALCITE-6285:
--
I agree with you
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17822347#comment-17822347
]
EveyWu commented on CALCITE-6285:
--
Another simpler way, can we keep consistent with the latest implementation of
spark? In practice, calculations based on spark Function are more likely to be
executed on spark engine, calcite only provides an alternative execution.
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17822343#comment-17822343
]
EveyWu commented on CALCITE-6285:
--
I think the Spark version update causes the problem, -1 for which the function
inserts a new element after the current last element.
The original definition [3.4.0,
|https://spark.apache.org/docs/3.4.0/api/sql/index.html#array_insert]Updated
version definition
[3.4.2|https://spark.apache.org/docs/3.4.2/api/sql/index.html#array_insert]
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6285:
Attachment: (was: image-2024-03-01-10-35-11-673.png)
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6285:
Attachment: image-2024-03-01-10-35-11-673.png
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6285:
Attachment: (was: image-2024-03-01-10-31-55-853.png)
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[
https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6285:
Attachment: image-2024-03-01-10-31-55-853.png
> Function ARRAY_INSERT produces an incorrect result for negative indices
> ---
>
> Key: CALCITE-6285
> URL: https://issues.apache.org/jira/browse/CALCITE-6285
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
> Attachments: image-2024-03-01-10-31-55-853.png
>
>
> Here is a test taken from the Spark documentation page:
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert
> {code}
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
> [5,4,3,2,1]
> {code}
> The result produced by Calcite is:
> [4,5,3,2,1]
> The strange thing is that there are tests for negative indices. I wonder if
> the original tests are wrong, or the behavior of this function in Spark was
> changed since the tests were written.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] Since this function has the same implementation as the Big Query [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] function. the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Add REGEXP function (enabled in Spark library) > -- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6260: Attachment: (was: image-2024-02-22-21-09-22-394.png) > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List： > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
EveyWu created CALCITE-6278: Summary: Add REGEXP function (enabled in Spark library) Key: CALCITE-6278 URL: https://issues.apache.org/jira/browse/CALCITE-6278 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
EveyWu created CALCITE-6278: Summary: Add REGEXP function (enabled in Spark library) Key: CALCITE-6278 URL: https://issues.apache.org/jira/browse/CALCITE-6278 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17819645#comment-17819645 ] EveyWu commented on CALCITE-6260: -- [~caicancai] Thank you for your advice, I will split multiple jira. > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-22-21-09-22-394.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List： > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6260: Attachment: image-2024-02-22-21-09-22-394.png > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-22-21-09-22-394.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List： > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6259) The implementation of the Log library operator does not match the actual dialect behavior.
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6259:
Attachment: (was: image-2024-02-12-21-52-45-462.png)
> The implementation of the Log library operator does not match the actual
> dialect behavior.
> --
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png
>
>
> The implementation of the Log library operator does not match the actual
> dialect behavior.
> For example, when log10(0) returns null in mysql and spark, but log10(0)
> returns error in postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6260) Add already implemented functions in Spark library
EveyWu created CALCITE-6260: Summary: Add already implemented functions in Spark library Key: CALCITE-6260 URL: https://issues.apache.org/jira/browse/CALCITE-6260 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html] Add function List： * CHR * CONCAT_WS * REGEXP * REVERSE -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6260) Add already implemented functions in Spark library
EveyWu created CALCITE-6260: Summary: Add already implemented functions in Spark library Key: CALCITE-6260 URL: https://issues.apache.org/jira/browse/CALCITE-6260 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html] Add function List： * CHR * CONCAT_WS * REGEXP * REVERSE -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17816612#comment-17816612
]
EveyWu commented on CALCITE-6259:
--
log10 is defined in the SqlStdOperatorTable, In my opinion, there is no
guarantee that the execution results of different engines will be consistent.
Calcite defines its criteria for execution results, which can be similar to
common engine results, but exact consistency is not guaranteed.
> Return the result with the log10 and log functions of argument 0
>
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png,
> image-2024-02-12-21-52-45-462.png
>
>
> When log10(0) returns null in mysql and spark, but log10(0) returns error in
> postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6259:
Attachment: image-2024-02-12-21-52-45-462.png
> Return the result with the log10 and log functions of argument 0
>
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png,
> image-2024-02-12-21-52-45-462.png
>
>
> When log10(0) returns null in mysql and spark, but log10(0) returns error in
> postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17816489#comment-17816489
]
EveyWu edited comment on CALCITE-6259 at 2/12/24 5:25 AM:
---
hi [~caicancai]
I think we can optimize the log function calculation.
According to the
[[document]|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log],
the current Caclite calculation for critical conditions does not conform to
the behavior of bigquery.
Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is
-Infinity； LOG10(0) for bigquery is Error, but for Calcite is -Infinity.
I also think that the background of this issue should be confirmed with
[~tanclary]
was (Author: eveywu):
hi [~caicancai]
I think we can optimize the log function calculation.
According to the
[[document|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]|#log]],
the current Caclite calculation for critical conditions does not conform to
the behavior of bigquery.
Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is
-Infinity； LOG10(0) for bigquery is Error, but for Calcite is -Infinity.
I also think that the background of this issue should be confirmed with
[~tanclary]
> Return the result with the log10 and log functions of argument 0
>
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png
>
>
> When log10(0) returns null in mysql and spark, but log10(0) returns error in
> postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17816489#comment-17816489
]
EveyWu commented on CALCITE-6259:
--
hi [~caicancai]
I think we can optimize the log function calculation.
According to the
[document|[https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]],
the current Caclite calculation for critical conditions does not conform to
the behavior of bigquery.
Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is
-Infinity； LOG10(0) for bigquery is Error, but for Calcite is -Infinity.
I also think that the background of this issue should be confirmed with
[~tanclary]
> Return the result with the log10 and log functions of argument 0
>
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png
>
>
> When log10(0) returns null in mysql and spark, but log10(0) returns error in
> postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[
https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17816489#comment-17816489
]
EveyWu edited comment on CALCITE-6259 at 2/12/24 5:24 AM:
---
hi [~caicancai]
I think we can optimize the log function calculation.
According to the
[[document|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]|#log]],
the current Caclite calculation for critical conditions does not conform to
the behavior of bigquery.
Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is
-Infinity； LOG10(0) for bigquery is Error, but for Calcite is -Infinity.
I also think that the background of this issue should be confirmed with
[~tanclary]
was (Author: eveywu):
hi [~caicancai]
I think we can optimize the log function calculation.
According to the
[document|[https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]],
the current Caclite calculation for critical conditions does not conform to
the behavior of bigquery.
Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is
-Infinity； LOG10(0) for bigquery is Error, but for Calcite is -Infinity.
I also think that the background of this issue should be confirmed with
[~tanclary]
> Return the result with the log10 and log functions of argument 0
>
>
> Key: CALCITE-6259
> URL: https://issues.apache.org/jira/browse/CALCITE-6259
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
> Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png,
> 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png
>
>
> When log10(0) returns null in mysql and spark, but log10(0) returns error in
> postgres, neither is calcite's -Intity
> {code:java}
> postgres=# select log10(0);
> ERROR: cannot take logarithm of zero
> postgres=# select log(2,0);
> ERROR: cannot take logarithm of zero
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6257) StarRocks dialect implementation
[ https://issues.apache.org/jira/browse/CALCITE-6257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17816467#comment-17816467 ] EveyWu commented on CALCITE-6257: -- hi [~caicancai] Similar to Presto and Trino, I don't think it's necessary to implement two sets of dialects at the moment. Since Starrocks is more widely used currently, I chose to implement the StarRocks Dialect. > StarRocks dialect implementation > > > Key: CALCITE-6257 > URL: https://issues.apache.org/jira/browse/CALCITE-6257 > Project: Calcite > Issue Type: New Feature >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > StarRocks has an MPP architecture and is equipped with a fully vectorized > execution engine, a columnar storage engine that supports real-time updates. > [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] > At present, StarRocks has a wide range of applications. Implementing dialect > in Calcite will be valuable for many bi-services. > It closely follows MySQL but has enough differences to warrant a separate > dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6258) Map value constructor is unparsed incorrectly for PrestoSqlDialect
EveyWu created CALCITE-6258:
Summary: Map value constructor is unparsed incorrectly for
PrestoSqlDialect
Key: CALCITE-6258
URL: https://issues.apache.org/jira/browse/CALCITE-6258
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
Presto Map function：{{{}map{}}}({_}array{_}, {_}array{_})
https://teradata.github.io/presto/docs/current/functions/map.html
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6258) Map value constructor is unparsed incorrectly for PrestoSqlDialect
EveyWu created CALCITE-6258:
Summary: Map value constructor is unparsed incorrectly for
PrestoSqlDialect
Key: CALCITE-6258
URL: https://issues.apache.org/jira/browse/CALCITE-6258
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
Presto Map function：{{{}map{}}}({_}array{_}, {_}array{_})
https://teradata.github.io/presto/docs/current/functions/map.html
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6257) StarRocks dialect implementation
EveyWu created CALCITE-6257: Summary: StarRocks dialect implementation Key: CALCITE-6257 URL: https://issues.apache.org/jira/browse/CALCITE-6257 Project: Calcite Issue Type: New Feature Reporter: EveyWu StarRocks has an MPP architecture and is equipped with a fully vectorized execution engine, a columnar storage engine that supports real-time updates. [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] At present, StarRocks has a wide range of applications. Implementing dialect in Calcite will be valuable for many bi-services. It closely follows MySQL but has enough differences to warrant a separate dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6257) StarRocks dialect implementation
EveyWu created CALCITE-6257: Summary: StarRocks dialect implementation Key: CALCITE-6257 URL: https://issues.apache.org/jira/browse/CALCITE-6257 Project: Calcite Issue Type: New Feature Reporter: EveyWu StarRocks has an MPP architecture and is equipped with a fully vectorized execution engine, a columnar storage engine that supports real-time updates. [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] At present, StarRocks has a wide range of applications. Implementing dialect in Calcite will be valuable for many bi-services. It closely follows MySQL but has enough differences to warrant a separate dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[
https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17815107#comment-17815107
]
EveyWu edited comment on CALCITE-6246 at 2/7/24 5:51 AM:
--
hi [~hackergin] , I can't reproduce this case.
In my opinion, function validation can obtain the correct parameter type.
Details can be viewed: SqlFunction#deriveType
{code:java}
final List argTypes = constructArgTypeList(validator, scope,call,
args, convertRowArgToColumnList);
//..
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames); {code}
was (Author: eveywu):
I can't reproduce this case.
In my opinion, function validation can obtain the correct parameter type.
Details can be viewed: SqlFunction#deriveType
{code:java}
final List argTypes = constructArgTypeList(validator, scope,call,
args, convertRowArgToColumnList);
//..
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames); {code}
> Unable to find the correct function signature when using named parameters
> with omitting arguments
> -
>
> Key: CALCITE-6246
> URL: https://issues.apache.org/jira/browse/CALCITE-6246
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Feng Jin
>Assignee: Feng Jin
>Priority: Major
>
> When we use named parameters, if a non-mandatory parameter is omitted, it
> will be automatically filled with a DEFAULT node. However, the default return
> type of the DEFAULT node is ANY. This will cause some issues when inferring
> the function signature.
> Take the unit test below as an example.
> org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional
> {code:java}
> // Some comments here
> @Test void testScalarFunctionWithParameterWithOptional() {
> final String sql = "select \"adhoc\".my_det_plus() as p\n"
> + "from \"adhoc\".EMPLOYEES";
> final AtomicInteger c =
> Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get();
> final int before = c.get();
> withUdf()
> .with("caseSensitive", false)
> .query(sql).returnsUnordered("P=110",
> "P=120",
> "P=110",
> "P=110");
> final int after = c.get();
> assertThat(after, is(before + 1));
> }
> {code}
> We will get the following error:
> {code:shell}
> java.sql.SQLException: Error while executing SQL "select
> "adhoc".my_det_plus() as p
> from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No
> match found for function signature MY_DET_PLUS()
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465)
> at
> org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
>
[jira] [Comment Edited] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[
https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17815107#comment-17815107
]
EveyWu edited comment on CALCITE-6246 at 2/7/24 5:50 AM:
--
I can't reproduce this case.
In my opinion, function validation can obtain the correct parameter type.
Details can be viewed: SqlFunction#deriveType
{code:java}
final List argTypes = constructArgTypeList(validator, scope,call,
args, convertRowArgToColumnList);
//..
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames); {code}
was (Author: eveywu):
I can't reproduce this case.
In my opinion, function validation can obtain the correct parameter type.
Details can be viewed: SqlFunction#deriveType
```
final List argTypes = constructArgTypeList(validator, scope,call,
args, convertRowArgToColumnList);
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames);
```
> Unable to find the correct function signature when using named parameters
> with omitting arguments
> -
>
> Key: CALCITE-6246
> URL: https://issues.apache.org/jira/browse/CALCITE-6246
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Feng Jin
>Assignee: Feng Jin
>Priority: Major
>
> When we use named parameters, if a non-mandatory parameter is omitted, it
> will be automatically filled with a DEFAULT node. However, the default return
> type of the DEFAULT node is ANY. This will cause some issues when inferring
> the function signature.
> Take the unit test below as an example.
> org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional
> {code:java}
> // Some comments here
> @Test void testScalarFunctionWithParameterWithOptional() {
> final String sql = "select \"adhoc\".my_det_plus() as p\n"
> + "from \"adhoc\".EMPLOYEES";
> final AtomicInteger c =
> Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get();
> final int before = c.get();
> withUdf()
> .with("caseSensitive", false)
> .query(sql).returnsUnordered("P=110",
> "P=120",
> "P=110",
> "P=110");
> final int after = c.get();
> assertThat(after, is(before + 1));
> }
> {code}
> We will get the following error:
> {code:shell}
> java.sql.SQLException: Error while executing SQL "select
> "adhoc".my_det_plus() as p
> from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No
> match found for function signature MY_DET_PLUS()
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465)
> at
> org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
>
[jira] [Commented] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[
https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17815107#comment-17815107
]
EveyWu commented on CALCITE-6246:
--
I can't reproduce this case.
In my opinion, function validation can obtain the correct parameter type.
Details can be viewed: SqlFunction#deriveType
```
final List argTypes = constructArgTypeList(validator, scope,call,
args, convertRowArgToColumnList);
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames);
```
> Unable to find the correct function signature when using named parameters
> with omitting arguments
> -
>
> Key: CALCITE-6246
> URL: https://issues.apache.org/jira/browse/CALCITE-6246
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Feng Jin
>Assignee: Feng Jin
>Priority: Major
>
> When we use named parameters, if a non-mandatory parameter is omitted, it
> will be automatically filled with a DEFAULT node. However, the default return
> type of the DEFAULT node is ANY. This will cause some issues when inferring
> the function signature.
> Take the unit test below as an example.
> org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional
> {code:java}
> // Some comments here
> @Test void testScalarFunctionWithParameterWithOptional() {
> final String sql = "select \"adhoc\".my_det_plus() as p\n"
> + "from \"adhoc\".EMPLOYEES";
> final AtomicInteger c =
> Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get();
> final int before = c.get();
> withUdf()
> .with("caseSensitive", false)
> .query(sql).returnsUnordered("P=110",
> "P=120",
> "P=110",
> "P=110");
> final int after = c.get();
> assertThat(after, is(before + 1));
> }
> {code}
> We will get the following error:
> {code:shell}
> java.sql.SQLException: Error while executing SQL "select
> "adhoc".my_det_plus() as p
> from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No
> match found for function signature MY_DET_PLUS()
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465)
> at
> org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
>
[jira] [Commented] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17815032#comment-17815032 ] EveyWu commented on CALCITE-6241: -- Hi [~shenlang] Since many functions cannot be placed in the summary, they have been listed in the commit message and Jira Description. > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-07-07-08-59-868.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: image-2024-02-07-07-08-59-868.png > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-07-07-08-59-868.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: (was: image-2024-02-06-17-11-31-144.png) > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6241) Enable a few existing functions to Spark library
[
https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17814696#comment-17814696
]
EveyWu commented on CALCITE-6241:
--
[~hongyuguo]
thanks for your advice，Comparing the duplicated PR,
the following additional functions are added
* DATE_FROM_UNIX_DATE
* TIMESTAMP_MICROS
* TIMESTAMP_MILLIS
* TIMESTAMP_SECONDS
And other functions are not included. The main differences are as follows:
* {*}CONVERT_TIMEZONE{*}: OperandType not matched, Spark
CONVERT_TIMEZONE([sourceTz, ]targetTz, sourceTs), PostgreSQL
CONVERT_TIMEZONE(tz1, tz2, datetime)
* {*}SPLIT{*}: OperandType not matched, Spark SPLIT(str, regex, limit),
BigQuery SPLIT(string [, delimiter]);
* {*}REGEXP_REPLACE{*}: OperandType not matched, Spark REGEXP_REPLACE(str,
regexp, rep[, position]), BigQuery REGEXP_REPLACE(value, regexp, rep [, pos [,
occurrence [, matchType]]]);
* {*}CONCAT_WS{*}：OperandType not matched, Spark CONCAT_WS(sep[, str |
array(str)]+), MySQL CONCAT_WS(separator, str1 [, string ]*);
* {*}TO_DATE{*}: OperandType not matched, Spark TO_DATE(date_str[, fmt]),
PostgreSQL TO_DATE(string1, string2);
* {*}TO_TIMESTAMP{*}: OperandType not matched;
* {*}CHR{*}: Spark returns ASCII code, same as CHAR, BigQuery returns UTF-8
code;
* {*}LOG{*}: OperandType not matched, Spark log(base, expr), BigQuery
LOG(value [, value2]);
> Enable a few existing functions to Spark library
>
>
> Key: CALCITE-6241
> URL: https://issues.apache.org/jira/browse/CALCITE-6241
> Project: Calcite
> Issue Type: Improvement
>Reporter: EveyWu
>Priority: Minor
> Labels: pull-request-available
> Attachments: image-2024-02-06-17-11-31-144.png
>
>
> Add Spark as a supported library for functions that have already been
> implemented for other libraries.
> Spark Functions
> Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim]
> Enable function List：
> * DECODE
> * NVL
> * IFNULL
> * LEN
> * LENGTH
> * LPAD
> * RPAD
> * LTRIM
> * RTRIM
> * ENDSWITH
> * STARTSWITH
> * GREATEST
> * LEAST
> * TRANSLATE
> * BOOL_AND
> * BOOL_OR
> * DATE_FROM_UNIX_DATE
> * UNIX_DATE
> * LEFT
> * REPEAT
> * RIGHT
> * SPACE
> * TIMESTAMP_SECONDS
> * TIMESTAMP_MILLIS
> * TIMESTAMP_MICROS
> * UNIX_SECONDS
> * UNIX_MILLIS
> * UNIX_MICROS
> * MD5
> * SHA1
> * POW
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: image-2024-02-06-17-11-31-144.png > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-06-17-11-31-144.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Enable function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5809) Enable already available functions in Apache Spark Library
[ https://issues.apache.org/jira/browse/CALCITE-5809?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-5809: Summary: Enable already available functions in Apache Spark Library (was: enable already available functions in Apache Spark Library) > Enable already available functions in Apache Spark Library > -- > > Key: CALCITE-5809 > URL: https://issues.apache.org/jira/browse/CALCITE-5809 > Project: Calcite > Issue Type: Improvement >Reporter: Guillaume Massé >Priority: Minor > Labels: pull-request-available > > Those functions are available in Calcite but via different library, we simply > need to add them to the libraries list. > > https://spark.apache.org/docs/3.4.1/api/sql/#to_date > [https://github.com/apache/calcite/blob/50f0e185896a4afb7c77bb6fb4efe41c1531dcfe/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java#L1262-L1267] > > https://spark.apache.org/docs/3.4.1/api/sql/#to_timestamp > [https://github.com/apache/calcite/blob/50f0e185896a4afb7c77bb6fb4efe41c1531dcfe/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java#L1271-L1276] > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Summary: Enable a few existing functions to Spark library (was: Add a few existing functions to Spark library) > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Priority: Minor (was: Major) > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6241) Add a few existing functions to Spark library
EveyWu created CALCITE-6241: Summary: Add a few existing functions to Spark library Key: CALCITE-6241 URL: https://issues.apache.org/jira/browse/CALCITE-6241 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：https://spark.apache.org/docs/latest/api/sql/index.html#rtrim -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6241) Add a few existing functions to Spark library
EveyWu created CALCITE-6241: Summary: Add a few existing functions to Spark library Key: CALCITE-6241 URL: https://issues.apache.org/jira/browse/CALCITE-6241 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：https://spark.apache.org/docs/latest/api/sql/index.html#rtrim -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：https://spark.apache.org/docs/latest/api/sql/index.html#rtrim > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Major > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6231) JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17812268#comment-17812268
]
EveyWu commented on CALCITE-6231:
--
[~julianhyde] thanks for the modification，I have changed the pr commit message
to the latest summary
> JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH
> ORDINALITY"
>
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[
https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17811644#comment-17811644
]
EveyWu commented on CALCITE-6218:
--
[~MasseGuillaume] I submitted a PR to solve the problem of "xs0" not exists。
I also think there can be more improvements here，correlated lateral join can be
handled directly instead of a lot of rewrites.
> RelToSqlConverter fails to convert correlated lateral joins
> ---
>
> Key: CALCITE-6218
> URL: https://issues.apache.org/jira/browse/CALCITE-6218
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Guillaume Massé
>Priority: Major
> Labels: pull-request-available
>
>
> input query:
> {code:sql}
> SELECT
> "a", "x"
> FROM
> "myDb"."myTable",
> unnest("xs") as "x"; {code}
> schema:
> [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
> {code:java}
> myTable(
> a: BIGINT,
> // ...
> xs: ARRAY,
> // ...
> )
> {code}
> logical plan:
> {code:java}
> LogicalProject(a=[$0], x=[$6])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{4}])
> LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
> e=[$4])
> LogicalTableScan(table=[[myDb, myTable]])
> Uncollect
> LogicalProject(xs=[$cor0.xs])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
> obtained sql:
>
> {code:sql}
> SELECT
> "$cor0"."a",
> "$cor0"."xs0" AS "x" -- <-- xs0 ?
> FROM
> (
> SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
> "myDb"."myTable"
> ) AS "$cor0",
> LATERAL UNNEST (
> SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
> ) AS "t1" ("xs") AS "t10"
> {code}
> I would expect the query to be converted to something close to the original
> query. Here "xs0" does not exists.
>
> [https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6231) RelToSqlConverter incorrectly convert UNNEST() WITH ORDINALITY
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17811624#comment-17811624
]
EveyWu commented on CALCITE-6231:
--
[~julianhyde] thanks for the suggestion，I have updated the summary.
> RelToSqlConverter incorrectly convert UNNEST() WITH ORDINALITY
> --
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6231) RelToSqlConverter incorrectly convert UNNEST() WITH ORDINALITY
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6231:
Summary: RelToSqlConverter incorrectly convert UNNEST() WITH ORDINALITY
(was: ORDINALITY is omitted in RelToSqlConverter)
> RelToSqlConverter incorrectly convert UNNEST() WITH ORDINALITY
> --
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6231) ORDINALITY is omitted in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu updated CALCITE-6231:
Summary: ORDINALITY is omitted in RelToSqlConverter (was: ORDINALITY is
omitted during the RelToSql stage)
> ORDINALITY is omitted in RelToSqlConverter
> --
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6231) ORDINALITY is omitted during the RelToSql stage
EveyWu created CALCITE-6231:
Summary: ORDINALITY is omitted during the RelToSql stage
Key: CALCITE-6231
URL: https://issues.apache.org/jira/browse/CALCITE-6231
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
For example sql：
{code:java}
select did + 1 from
unnest(select collect("department_id") as deptid from "department")
with ordinality as t(did, pos){code}
current planned sql：
{code:java}
SELECT DEPTID + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
AS "t0" ("DEPTID", "ORDINALITY") {code}
fixed planned sql：
{code:java}
SELECT "DEPTID" + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6231) ORDINALITY is omitted during the RelToSql stage
[
https://issues.apache.org/jira/browse/CALCITE-6231?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
EveyWu reassigned CALCITE-6231:
Assignee: EveyWu
> ORDINALITY is omitted during the RelToSql stage
> ---
>
> Key: CALCITE-6231
> URL: https://issues.apache.org/jira/browse/CALCITE-6231
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
>
> The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
> For example sql：
> {code:java}
> select did + 1 from
> unnest(select collect("department_id") as deptid from "department")
> with ordinality as t(did, pos){code}
>
> current planned sql：
> {code:java}
> SELECT DEPTID + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> AS "t0" ("DEPTID", "ORDINALITY") {code}
>
> fixed planned sql：
> {code:java}
> SELECT "DEPTID" + 1 FROM UNNEST (
> SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
> WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6231) ORDINALITY is omitted during the RelToSql stage
EveyWu created CALCITE-6231:
Summary: ORDINALITY is omitted during the RelToSql stage
Key: CALCITE-6231
URL: https://issues.apache.org/jira/browse/CALCITE-6231
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.
For example sql：
{code:java}
select did + 1 from
unnest(select collect("department_id") as deptid from "department")
with ordinality as t(did, pos){code}
current planned sql：
{code:java}
SELECT DEPTID + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
AS "t0" ("DEPTID", "ORDINALITY") {code}
fixed planned sql：
{code:java}
SELECT "DEPTID" + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department")
WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6213) The default behavior of NullCollation in Presto is LAST
[
https://issues.apache.org/jira/browse/CALCITE-6213?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17808736#comment-17808736
]
EveyWu commented on CALCITE-6213:
-
[~tanclary]
I have submitted the pull request，thank you for helping me review the code
> The default behavior of NullCollation in Presto is LAST
> ---
>
> Key: CALCITE-6213
> URL: https://issues.apache.org/jira/browse/CALCITE-6213
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Priority: Major
> Labels: pull-request-available
> Attachments: image-2024-01-19-23-54-26-383.png,
> image-2024-01-19-23-57-00-252.png
>
>
> [https://prestodb.io/docs/0.285/sql/select.html]
> Presto default null ordering is NULLS LAST
> !image-2024-01-19-23-54-26-383.png|width=511,height=168!
>
> Demo：select by presto with nulls last
> {code:java}
> select * from product order by brand_name; {code}
> !image-2024-01-19-23-57-00-252.png|width=517,height=83!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6213) The default behavior of NullCollation in Presto is LAST
EveyWu created CALCITE-6213:
---
Summary: The default behavior of NullCollation in Presto is LAST
Key: CALCITE-6213
URL: https://issues.apache.org/jira/browse/CALCITE-6213
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
Attachments: image-2024-01-19-23-54-26-383.png,
image-2024-01-19-23-57-00-252.png
[https://prestodb.io/docs/0.285/sql/select.html]
Presto default null ordering is NULLS LAST
!image-2024-01-19-23-54-26-383.png|width=511,height=168!
Demo：select by presto with nulls last
{code:java}
select * from product order by brand_name; {code}
!image-2024-01-19-23-57-00-252.png|width=517,height=83!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6213) The default behavior of NullCollation in Presto is LAST
EveyWu created CALCITE-6213:
---
Summary: The default behavior of NullCollation in Presto is LAST
Key: CALCITE-6213
URL: https://issues.apache.org/jira/browse/CALCITE-6213
Project: Calcite
Issue Type: Bug
Reporter: EveyWu
Attachments: image-2024-01-19-23-54-26-383.png,
image-2024-01-19-23-57-00-252.png
[https://prestodb.io/docs/0.285/sql/select.html]
Presto default null ordering is NULLS LAST
!image-2024-01-19-23-54-26-383.png|width=511,height=168!
Demo：select by presto with nulls last
{code:java}
select * from product order by brand_name; {code}
!image-2024-01-19-23-57-00-252.png|width=517,height=83!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
