[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-02 Thread Ran Tao (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750525#comment-17750525
 ] 

Ran Tao commented on CALCITE-5830:
--

anyone can help me to review this?

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-02 Thread xiong duan (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750540#comment-17750540
 ] 

xiong duan commented on CALCITE-5830:
-

Hi, [~taoran]  I will review and welcome other developers to participate in 
this.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-07 Thread Ran Tao (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17751488#comment-17751488
 ] 

Ran Tao commented on CALCITE-5830:
--

[~nobigo] hi, I have solved some comments. If you have time, pls help to take a 
look. thanks.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-07 Thread Ran Tao (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17751872#comment-17751872
 ] 

Ran Tao commented on CALCITE-5830:
--

[~nobigo] hi, xiong. If you have time, PTAL. I have solved all your comments. 
thanks.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-13 Thread Jiajun Xie (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753673#comment-17753673
 ] 

Jiajun Xie commented on CALCITE-5830:
-

[~taoran] , ARRAY_INSERT is a function only available in the latest version of 
Spark. Is it really stable?

 

I found that it is not the same in Databricks: 
https://docs.databricks.com/en/sql/language-manual/functions/array_insert.html
{code:java}
> SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
 ["a","b","c","z"]

> SELECT array_insert(array('a', 'b', 'c'), -5, 'z');
 ["z",NULL,"a","b","c"] {code}
I think inserting negative numbers is more reasonable in Databricks.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-13 Thread Ran Tao (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753726#comment-17753726
 ] 

Ran Tao commented on CALCITE-5830:
--

[~jiajunbernoulli]  ARRAY_INSERT is from spark 3.4.0 it has been covered 2 
released versions. i think it's stable. and i get your point. however i think 
spark is right. because it defines "Index above array size appends the array, 
or prepends the array if index is negative, with `NULL` elements." 

and spark index begins -1 and 1, so 'z' is -1, the original 'c' should move to 
the next.
// actual spark result
SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
["a","b","z","c"]

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-13 Thread Ran Tao (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753883#comment-17753883
 ] 

Ran Tao commented on CALCITE-5830:
--

[~jiajunbernoulli] hi, jiajun. I get your point. however, the result and 
behavior of yours is wrong. IMHO, firstly, i think in calcite we should respect 
Apache Spark not Databricks Spark. Of cause, in your case I have got some 
details: 

apache-spark-3.4.0:
{code:java}
spark-sql (default)> SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
["a","b","z","c"]
Time taken: 4.478 seconds, Fetched 1 row(s) {code}

apache-spark-3.4.1
{code:java}
spark-sql (default)> SELECT array_insert(array('a', 'b', 'c'), -5, 'z');
["z",null,null,"a","b","c"]
Time taken: 3.587 seconds, Fetched 1 row(s)
 {code}
 

1.the docs in databricks's is a old version, this old version show your copied 
result. I opened a discussion in spark devs: 
[https://lists.apache.org/thread/1p5hkql96k5qc5ww6wkd7mq6qdbgyz1n] 


2.here is the some latest databricks's spark behavior, it's same as Open Source 
Apache Spark behavior, and also same as the implementation in this PR. And this 
is correct result.

!image-2023-08-14-11-20-46-189.png|width=583,height=506!

Finally, spark has this behavior because it distinguish the prepends(negative 
index) and appends(positive index). I have replied in the PR for you.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
> Attachments: image-2023-08-14-11-17-33-205.png, 
> image-2023-08-14-11-20-46-189.png
>
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-14 Thread Jiajun Xie (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754363#comment-17754363
 ] 

Jiajun Xie commented on CALCITE-5830:
-

[~taoran] , I got it.

Thank you for your detailed explanation.

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
> Attachments: image-2023-08-14-11-17-33-205.png, 
> image-2023-08-14-11-20-46-189.png
>
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-5830) Add ARRAY_INSERT function(enabled in Spark library)

2023-08-18 Thread xiong duan (Jira) 


[ 
https://issues.apache.org/jira/browse/CALCITE-5830?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17755918#comment-17755918
 ] 

xiong duan commented on CALCITE-5830:
-

Fixed in 
[955fd9c1cf76|https://github.com/apache/calcite/commit/955fd9c1cf7601d88305c3206890480e2dc076e8].
 Thanks for the contribution [~taoran]. Thanks for the review 
[~jiajunbernoulli] [~jackylau] [~mbudiu] .

> Add ARRAY_INSERT function(enabled in Spark library)
> ---
>
> Key: CALCITE-5830
> URL: https://issues.apache.org/jira/browse/CALCITE-5830
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Affects Versions: 1.34.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-14-11-17-33-205.png, 
> image-2023-08-14-11-20-46-189.png
>
>
> array_insert(x, pos, val) - Places val into index pos of array x. Array 
> indices start at 1, or start from the end if index is negative. Index above 
> array size appends the array, or prepends the array if index is negative, 
> with 'null' elements
> *Examples:*
> > SELECT array_insert(array(1, 2, 3, 4), 5, 5); [1,2,3,4,5]
> > SELECT array_insert(array(5, 3, 2, 1), -3, 4); [5,4,3,2,1] 
> https://spark.apache.org/docs/latest/api/sql/index.html#array_insert



--
This message was sent by Atlassian Jira
(v8.20.10#820010)