[jira] [Commented] (CALCITE-2935) Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions

2019-12-26 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-2935:
-

+Note that BOOL_OR and BOOL_AND will essentially be aliases for MAX and MIN.+ 

Yep. But BOOL_OR/BOOL_AND only support a `boolean` type input which it's 
different with MIN/MAX.

There are duplicated issues:

https://issues.apache.org/jira/browse/CALCITE-3616

https://issues.apache.org/jira/browse/CALCITE-3617

> Support  ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions
> ---
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/
> https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
> A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3617) Add BOOL_OR Aggregate Function

2019-12-26 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3617:
-

Can I work on this issue?

BTW, It's duplicated with 
[CALCITE-2935](https://issues.apache.org/jira/browse/CALCITE-2935).

 

> Add BOOL_OR Aggregate Function
> --
>
> Key: CALCITE-3617
> URL: https://issues.apache.org/jira/browse/CALCITE-3617
> Project: Calcite
>  Issue Type: New Feature
>Reporter: Ritesh
>Priority: Major
>
> [https://docs.aws.amazon.com/redshift/latest/dg/r_BOOL_OR.html]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3628) OOB when using CallCopyingArgHandler to copy sql nodes with hint

2019-12-25 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3628:
-

I think this should be already fixed at 
[CALCITE-3590](https://issues.apache.org/jira/browse/CALCITE-3590)

. Codes at  
[https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/SqlSelect.java#L95]

 

Maybe you can give a simple example to reproduce your exception If I am wrong.

> OOB when using CallCopyingArgHandler to copy sql nodes with hint
> 
>
> Key: CALCITE-3628
> URL: https://issues.apache.org/jira/browse/CALCITE-3628
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.22.0
>Reporter: Axis
>Priority: Critical
> Fix For: 1.22.0
>
>
> Hello, 
>     When we use the CallCopyingArgHandler to copy the sql node tree, we will 
> get OOB.
> {code:java}
> java.lang.ArrayIndexOutOfBoundsException: 10
>   at org.apache.calcite.sql.SqlSelectOperator.createCall
>   ...{code}
>  
> I find calcite has been supported SqlHint in commit 
> (bf40ad33e7ee85ff426ddc493fe6d9a5bfe6a208).
> And the function createCall in SqlSelect has been changed:
> {code:java}
> public class SqlSelectOperator extends SqlOperator {
>   public static final SqlSelectOperator INSTANCE =
>   new SqlSelectOperator();
>   //~ Constructors ---
>   private SqlSelectOperator() {
> super("SELECT", SqlKind.SELECT, 2, true, ReturnTypes.SCOPE, null, null);
>   }
>   //~ Methods 
>   public SqlSyntax getSyntax() {
> return SqlSyntax.SPECIAL;
>   }
>   public SqlCall createCall(
>   SqlLiteral functionQualifier,
>   SqlParserPos pos,
>   SqlNode... operands) {
> assert functionQualifier == null;
> return new SqlSelect(pos,
> (SqlNodeList) operands[0],
> (SqlNodeList) operands[1],
> operands[2],
> operands[3],
> (SqlNodeList) operands[4],
> operands[5],
> (SqlNodeList) operands[6],
> (SqlNodeList) operands[7],
> operands[8],
> operands[9],
> (SqlNodeList) operands[10]);   --> Sql hints array
>   } {code}
> operator[10] might be SqlHints array in SqlSelect. 
> When developer wants to copy the sql node tree using  CallCopyingArgHandler. 
> It will call the follow the code:
> {code:java}
> protected class CallCopyingArgHandler implements ArgHandler {
>   boolean update;
>   SqlNode[] clonedOperands;
>   private final SqlCall call;
>   private final boolean alwaysCopy;
>   public CallCopyingArgHandler(SqlCall call, boolean alwaysCopy) {
> this.call = call;
> this.update = false;
> final List operands = call.getOperandList();---> sqlSelect 
> operators
> this.clonedOperands = operands.toArray(new SqlNode[0]);
> this.alwaysCopy = alwaysCopy;
>   }
>   public SqlNode result() {
> if (update || alwaysCopy) {
>   return call.getOperator().createCall(
>   call.getFunctionQuantifier(),
>   call.getParserPosition(),
>   clonedOperands);   --> SqlSelect operstors
> } else {
>   return call;
> }
>   }
> {code}
>  When the code invoke the "result" method, it will call the 
> SqlSelect::createCall, and pass the call.getOperandList as the dynamic 
> params. But SqlSelect's operator only have 10 operators (not contain hints)
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3349) Add Function DDL into SqlKind DDL enum

2019-09-16 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3349:
-

Just be curious, `SqlCreateFunction` is not implemented of 
`SqlExecutableStatement`, maybe throw exceptions when `executeDdl`.

> Add Function DDL into SqlKind DDL enum
> --
>
> Key: CALCITE-3349
> URL: https://issues.apache.org/jira/browse/CALCITE-3349
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: Zhenqiu Huang
>Priority: Minor
>  Labels: pull-request-available
>  Time Spent: 20m
>  Remaining Estimate: 0h
>
> Currently, Create Function, Drop Function are not added into SqlKind DDL 
> enum. 



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-3334) Refinement for Substitution-Based MV Matching

2019-09-15 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3334:
-

Good Refinements. It makes `SubstitutionVisitor` more powerful and organized. 

Any progress about this?

> Refinement for Substitution-Based MV Matching
> -
>
> Key: CALCITE-3334
> URL: https://issues.apache.org/jira/browse/CALCITE-3334
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Reporter: jin xing
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> The approach of substitution-based MV matching is an effective way for its 
> simplicity and extensibility. 
> This JIRA proposes to refine existing implementation by several points:
>  # Canonicalize before MV matching -- by such canonicalization we can 
> significantly simplify the algebra tree and lower the difficulty for 
> materialization matching.
>  # Separate matching rules into two categories and enumerate common matching 
> patterns which need to be covered by rules.
> Please check the design doc: [Design 
> Doc|https://docs.google.com/document/d/1JpwGNFE3hw3yXb7W3-95-jXKClZC5UFPKbuhgYDuEu4/edit#]



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Comment Edited] (CALCITE-3347) IndexOutOfBoundsException in FixNullabilityShuttle when using FilterIntoJoinRule

2019-09-15 Thread ShuMing Li (Jira) 


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

ShuMing Li edited comment on CALCITE-3347 at 9/16/19 2:55 AM:
--

[~julianhyde]  I add a simple test in `JdbcTest.java` as below(no bind 
variables):
{code:java}
@Test public void testSemiJoin() {
  CalciteAssert.that()
  .with(CalciteAssert.Config.JDBC_FOODMART)
  .query("select *\n"
  + " from \"foodmart\".\"employee\""
  + " where \"employee_id\" = 1 and \"last_name\" in"
  + " (select \"last_name\" from \"foodmart\".\"employee\" where 
\"employee_id\" = 2)")
  .runs();
}
{code}
 

The same exception is thrown, So I doubt this maybe a bug in a RelOptRule.
{code:java}
Caused by: java.lang.IndexOutOfBoundsException: index (18) must be less than 
size (17)Caused by: java.lang.IndexOutOfBoundsException: index (18) must be 
less than size (17) at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:310) 
at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:293) 
at 
com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:67)
 at 
com.google.common.collect.Lists$TransformingRandomAccessList.get(Lists.java:627)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2529)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2518)
 at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112) at 
org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:149) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:101) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:34) at 
org.apache.calcite.rex.RexCall.accept(RexCall.java:191) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:277) at 
org.apache.calcite.rex.RexShuttle.mutate(RexShuttle.java:239) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:257) at 
org.apache.calcite.rex.RexUtil.fixUp(RexUtil.java:1635) at 
org.apache.calcite.rel.rules.FilterJoinRule.perform(FilterJoinRule.java:288) at 
org.apache.calcite.rel.rules.FilterJoinRule$FilterIntoJoinRule.onMatch(FilterJoinRule.java:387)
 at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:208)
 ... 41 more
{code}


was (Author: lishuming):
[~julianhyde]  I add a simple test in `JdbcTest.java` as below(no bind 
variables):
{code:java}
// code placeholder
@Test public void testSemiJoin2() {
  CalciteAssert.that()
  .with(CalciteAssert.Config.JDBC_FOODMART)
  .query("select *\n"
  + " from \"foodmart\".\"employee\""
  + " where \"employee_id\" = 1 and \"last_name\" in"
  + " (select \"last_name\" from \"foodmart\".\"employee\" where 
\"employee_id\" = 2)")
  .runs();
}
{code}
 

The same exception is thrown, So I doubt this maybe a bug in a RelOptRule.
{code:java}
Caused by: java.lang.IndexOutOfBoundsException: index (18) must be less than 
size (17)Caused by: java.lang.IndexOutOfBoundsException: index (18) must be 
less than size (17) at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:310) 
at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:293) 
at 
com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:67)
 at 
com.google.common.collect.Lists$TransformingRandomAccessList.get(Lists.java:627)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2529)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2518)
 at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112) at 
org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:149) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:101) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:34) at 
org.apache.calcite.rex.RexCall.accept(RexCall.java:191) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:277) at 
org.apache.calcite.rex.RexShuttle.mutate(RexShuttle.java:239) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:257) at 
org.apache.calcite.rex.RexUtil.fixUp(RexUtil.java:1635) at 
org.apache.calcite.rel.rules.FilterJoinRule.perform(FilterJoinRule.java:288) at 
org.apache.calcite.rel.rules.FilterJoinRule$FilterIntoJoinRule.onMatch(FilterJoinRule.java:387)
 at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:208)
 ... 41 more
{code}

> IndexOutOfBoundsException in FixNullabilityShuttle when using 
> FilterIntoJoinRule
> 
>
> Key: CALCITE-3347
> URL: https://issues.apache.org/jira/browse/CALCITE-3347
> Project: Calcite
>  Issue Type: Bug
>Affects Versions: 1.21.0
>Reporter: Amit Chavan
>

[jira] [Commented] (CALCITE-3347) IndexOutOfBoundsException in FixNullabilityShuttle when using FilterIntoJoinRule

2019-09-15 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3347:
-

[~julianhyde]  I add a simple test in `JdbcTest.java` as below(no bind 
variables):
{code:java}
// code placeholder
@Test public void testSemiJoin2() {
  CalciteAssert.that()
  .with(CalciteAssert.Config.JDBC_FOODMART)
  .query("select *\n"
  + " from \"foodmart\".\"employee\""
  + " where \"employee_id\" = 1 and \"last_name\" in"
  + " (select \"last_name\" from \"foodmart\".\"employee\" where 
\"employee_id\" = 2)")
  .runs();
}
{code}
 

The same exception is thrown, So I doubt this maybe a bug in a RelOptRule.
{code:java}
Caused by: java.lang.IndexOutOfBoundsException: index (18) must be less than 
size (17)Caused by: java.lang.IndexOutOfBoundsException: index (18) must be 
less than size (17) at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:310) 
at 
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:293) 
at 
com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:67)
 at 
com.google.common.collect.Lists$TransformingRandomAccessList.get(Lists.java:627)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2529)
 at 
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2518)
 at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112) at 
org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:149) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:101) at 
org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:34) at 
org.apache.calcite.rex.RexCall.accept(RexCall.java:191) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:277) at 
org.apache.calcite.rex.RexShuttle.mutate(RexShuttle.java:239) at 
org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:257) at 
org.apache.calcite.rex.RexUtil.fixUp(RexUtil.java:1635) at 
org.apache.calcite.rel.rules.FilterJoinRule.perform(FilterJoinRule.java:288) at 
org.apache.calcite.rel.rules.FilterJoinRule$FilterIntoJoinRule.onMatch(FilterJoinRule.java:387)
 at 
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:208)
 ... 41 more
{code}

> IndexOutOfBoundsException in FixNullabilityShuttle when using 
> FilterIntoJoinRule
> 
>
> Key: CALCITE-3347
> URL: https://issues.apache.org/jira/browse/CALCITE-3347
> Project: Calcite
>  Issue Type: Bug
>Affects Versions: 1.21.0
>Reporter: Amit Chavan
>Priority: Major
> Attachments: TestCalcite.java
>
>
> I am reporting a bug that happens in calcite 1.21 release. I have a query as 
> below 
>  String query = "SELECT * FROM tblspace1.tsql where n1=? and k1 in (SELECT k1 
> FROM tblspace1.tsql where n1=?)";
>   
>  I am also attaching the unit test to reproduce this issue.  
>   
>  The filterJoinRule throws an exception –
>  java.lang.RuntimeException: Error while applying rule 
> FilterJoinRule:FilterJoinRule:filter, args 
> [rel#39:EnumerableFilter.ENUMERABLE.[](input=RelSubset#38,condition==($1, 
> ?0)), 
> rel#176:EnumerableHashJoin.ENUMERABLE.[](left=RelSubset#17,right=RelSubset#73,condition==($0,
>  $3),joinType=semi)]
>  at 
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:235)
>  at 
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:631)
>  at org.apache.calcite.TestCalcite.testQuery(TestCalcite.java:199)
>  at org.apache.calcite.TestCalcite.problem_with_1_21(TestCalcite.java:256)
>  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:497)
>  at 
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
>  at 
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
>  at 
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
>  at 
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
>  at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
>  at 
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
>  at 
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
>  at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
>  at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
>  at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
>  at

[jira] [Commented] (CALCITE-3323) Handle arbitrary/unknown functions that have ordinary syntax

2019-09-04 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3323:
-

It's an interesting idea. I don't know how to handle unparsed functions?

Can you give more docs about the implements/ideas?

> Handle arbitrary/unknown functions that have ordinary syntax
> 
>
> Key: CALCITE-3323
> URL: https://issues.apache.org/jira/browse/CALCITE-3323
> Project: Calcite
>  Issue Type: New Feature
>Reporter: Ryan Fu
>Priority: Major
>
> Add a strategy where if a function has ordinary function syntax and we don't 
> recognize it we assume that it can take any argument types and returns a 
> result of unknown type.
> We will still need to change the parser to handle functions with non-standard 
> syntax (e.g. DATEADD). And it's a good idea to explicitly add commonly used 
> non-standard functions (e.g. MD5, CONCAT).”



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-3280) Cannot parse query REGEXP_REPLACE in Redshift

2019-08-22 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3280:
-

Add a review of other databases:

 

BigQuery : 
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#regexp_replace
Syntax : REGEXP_REPLACE(value, regex, replacement)
Input : STRING
Output : STRING/BYTES


MySQL : 
https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace
Syntax : REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
Input : String
Output : String

Oracle : https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm
Syntax : REGEXP_REPLACE
Input : CHAR, VARCHAR2, NCHAR, NVARCHAR2(CLOB or NCLOB)
Output : VARCHAR2/CLOB

PostgreSQL : https://www.postgresql.org/docs/9.3/functions-matching.html
Syntax : regexp_replace(source, pattern, replacement [, flags ])
Input : string
Output : string

Redshift : https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html
Syntax : REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position 
] ] )
Input : String
Output : VARCHAR

> Cannot parse query REGEXP_REPLACE in Redshift
> -
>
> Key: CALCITE-3280
> URL: https://issues.apache.org/jira/browse/CALCITE-3280
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Ryan Fu
>Priority: Minor
>
> REGEXP_REPLACE error:
> {code:}
> No match found for function signature REGEXP_REPLACE(, 
> , ){code}
>  
> Example query:
> {code:sql}
> SELECT * , MD5(TRIM(BOTH ' ' FROM REGEXP_REPLACE(LOWER(name), 
> '([[:space:]]|,)+([iInNcC]|[lLcC]).*$', ''))) AS company_id FROM 
> public.account {code}



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-3272) TUMBLE Table Value Function

2019-08-21 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3272:
-

There is something wrong with the `beam sql` link?

> TUMBLE Table Value Function
> ---
>
> Key: CALCITE-3272
> URL: https://issues.apache.org/jira/browse/CALCITE-3272
> Project: Calcite
>  Issue Type: Sub-task
>Reporter: Rui Wang
>Priority: Major
>
> Define a builtin TVF: Tumble (data , timecol , dur, [ offset ])
> The return value of Tumble is a relation that includes all columns of data as 
> well as additional event time columns wstart and wend.
> Examples of TUMBLE TVF are (from https://s.apache.org/streaming-beam-sql):
> 8:21> SELECT * FROM Bid;
> --
> | bidtime | price | item |
> --
> | 8:07| $2| A|
> | 8:11| $3| B|
> | 8:05| $4| C|
> | 8:09| $5| D|
> | 8:13| $1| E|
> | 8:17| $6| F|
> --
> 8:21> SELECT *
>   FROM Tumble (
> data=> TABLE Bid ,
> timecol => DESCRIPTOR ( bidtime ) ,
> dur => INTERVAL '10' MINUTES ,
> offset  => INTERVAL '0' MINUTES );
> --
> | wstart | wend | bidtime | price | item |
> --
> | 8:00   | 8:10 | 8:07| $2| A|
> | 8:10   | 8:20 | 8:11| $3| B|
> | 8:00   | 8:10 | 8:05| $4| C|
> | 8:00   | 8:10 | 8:09| $5| D|
> | 8:10   | 8:20 | 8:13| $1| E|
> | 8:10   | 8:20 | 8:17| $6| F|
> --
> 8:21> SELECT MAX ( wstart ) , wend , SUM ( price )
>   FROM Tumble (
> data=> TABLE ( Bid ) ,
> timecol => DESCRIPTOR ( bidtime ) ,
> dur => INTERVAL '10 ' MINUTES )
>   GROUP BY wend;
> -
> | wstart | wend | price |
> -
> | 8:00   | 8:10 | $11   |
> | 8:10   | 8:20 | $10   |
> -



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-3280) Cannot parse query REGEXP_REPLACE in Redshift

2019-08-21 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3280:
-

Is somebody working this? In our product situation, we still need 
`REGEXP_REPLACE` UDF to parse SQL. If nobody is working this, can I help to 
work it?

> Cannot parse query REGEXP_REPLACE in Redshift
> -
>
> Key: CALCITE-3280
> URL: https://issues.apache.org/jira/browse/CALCITE-3280
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Ryan Fu
>Priority: Minor
>
> REGEXP_REPLACE error:
> {code:}
> No match found for function signature REGEXP_REPLACE(, 
> , ){code}
>  
> Example query:
> {code:sql}
> SELECT * , MD5(TRIM(BOTH ' ' FROM REGEXP_REPLACE(LOWER(name), 
> '([[:space:]]|,)+([iInNcC]|[lLcC]).*$', ''))) AS company_id FROM 
> public.account {code}



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-1917) Support column reference in "FOR SYSTEM_TIME AS OF"

2019-08-20 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-1917:
-

Has this issue already resolved?  I find Calcite already support such tests in 
`SqlToRelConverterTest.java`:
{code:java}
@Test public void testJoinTemporalTableOnColumnReference() {
  final String sql = "select stream *\n"
  + "from orders\n"
  + "join products_temporal for system_time as of orders.rowtime\n"
  + "on orders.productid = products_temporal.productid";
  sql(sql).ok();
}
{code}

> Support column reference in "FOR SYSTEM_TIME AS OF"
> ---
>
> Key: CALCITE-1917
> URL: https://issues.apache.org/jira/browse/CALCITE-1917
> Project: Calcite
>  Issue Type: New Feature
>Reporter: Jark Wu
>Priority: Major
>
> As discussed in mailing list[1], the standard says QSTPS can’t contain a 
> column reference. So when joining the Orders to the Products table for the 
> price as of the time the order was placed is impossible using "FOR 
> SYSTEM_TIME AS OF". But can be expressed using a subquery, such as:
> {code}
>  SELECT  *
> FROM Orders AS o
> JOIN LATERAL (SELECT * FROM ProductPrices WHERE sysStart <= O.orderTime 
> AND sysEnd > O.orderTime) AS P
>   ON o.productId = p.productId
> {code}
> But subquery is too complex for users. We know that the standard says it 
> can’t contain a column reference. We initialize this discuss as we would like 
> to "extend" the standard to simplify such query:
> {code}
>  SELECT  *
> FROM Orders AS o
> JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF O.orderTime AS P
>   ON o.productId = p.productId
> {code}
> [1] 
> https://lists.apache.org/thread.html/f877f356a8365bf74ea7d8e4a171224104d653cf73861afb2901a58f@%3Cdev.calcite.apache.org%3E



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Updated] (CALCITE-3263) Add MD5, SHA1 SQL functions

2019-08-19 Thread ShuMing Li (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-3263?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ShuMing Li updated CALCITE-3263:

Description: 
`MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
in SQL just like `from_base64`/`to_base64`. 
h3. A Review of Other Databases
 * BigQuery : 
[https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5]
 ** Function : MD5(String/Bytes)
 ** Input : String/Bytes
 ** Output : Bytes
 * MySQL : [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html]
 ** Function : MD5(String)
 ** Input : String
 ** Output : String
 * Oracle : 
[https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647]
 ** Function : STANDARD_HASH(expr, method)
 ** Input : String
 ** Output : RAW
 * PostgreSQL : [https://www.postgresql.org/docs/current/functions-string.html 
|https://www.postgresql.org/docs/current/functions-string.html]
 ** Function : MD5(String)
 ** Input : String
 ** Output : Text
 * Redshift : [https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html]
 ** Function : MD5(String)
 ** Input : String
 ** Output : String

  was:
`MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
in SQL just like `from_base64`/`to_base64`.

 

BigQuery : 
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5
Function : MD5(String/Bytes)
Input : String/Bytes
Output : Bytes


MySQL : https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
Function : MD5(String)
Input : String
Output : String

Oracle : https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
Function : STANDARD_HASH(expr, method)
Input : String
Output : RAW

PostgreSQL : https://www.postgresql.org/docs/current/functions-string.html
Function : MD5(String)
Input : String
Output : Text

Redshift : https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html
Function : MD5(String)
Input : String
Output : String
 


> Add MD5, SHA1 SQL functions
> ---
>
> Key: CALCITE-3263
> URL: https://issues.apache.org/jira/browse/CALCITE-3263
> Project: Calcite
>  Issue Type: Improvement
>Reporter: ShuMing Li
>Priority: Minor
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> `MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
> in SQL just like `from_base64`/`to_base64`. 
> h3. A Review of Other Databases
>  * BigQuery : 
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5]
>  ** Function : MD5(String/Bytes)
>  ** Input : String/Bytes
>  ** Output : Bytes
>  * MySQL : [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html]
>  ** Function : MD5(String)
>  ** Input : String
>  ** Output : String
>  * Oracle : 
> [https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647]
>  ** Function : STANDARD_HASH(expr, method)
>  ** Input : String
>  ** Output : RAW
>  * PostgreSQL : 
> [https://www.postgresql.org/docs/current/functions-string.html 
> |https://www.postgresql.org/docs/current/functions-string.html]
>  ** Function : MD5(String)
>  ** Input : String
>  ** Output : Text
>  * Redshift : [https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html]
>  ** Function : MD5(String)
>  ** Input : String
>  ** Output : String



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Updated] (CALCITE-3263) Add MD5, SHA1 SQL functions

2019-08-19 Thread ShuMing Li (Jira) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-3263?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ShuMing Li updated CALCITE-3263:

Description: 
`MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
in SQL just like `from_base64`/`to_base64`.

 

BigQuery : 
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5
Function : MD5(String/Bytes)
Input : String/Bytes
Output : Bytes


MySQL : https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
Function : MD5(String)
Input : String
Output : String

Oracle : https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
Function : STANDARD_HASH(expr, method)
Input : String
Output : RAW

PostgreSQL : https://www.postgresql.org/docs/current/functions-string.html
Function : MD5(String)
Input : String
Output : Text

Redshift : https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html
Function : MD5(String)
Input : String
Output : String
 

  was:
`MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
in SQL just like `from_base64`/`to_base64`.

 

- MySQL: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
- PostgreSQL: https://www.postgresql.org/docs/current/functions-string.html


> Add MD5, SHA1 SQL functions
> ---
>
> Key: CALCITE-3263
> URL: https://issues.apache.org/jira/browse/CALCITE-3263
> Project: Calcite
>  Issue Type: Improvement
>Reporter: ShuMing Li
>Priority: Minor
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> `MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
> in SQL just like `from_base64`/`to_base64`.
>  
> BigQuery : 
> https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5
> Function : MD5(String/Bytes)
> Input : String/Bytes
> Output : Bytes
> MySQL : https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
> Function : MD5(String)
> Input : String
> Output : String
> Oracle : 
> https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
> Function : STANDARD_HASH(expr, method)
> Input : String
> Output : RAW
> PostgreSQL : https://www.postgresql.org/docs/current/functions-string.html
> Function : MD5(String)
> Input : String
> Output : Text
> Redshift : https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html
> Function : MD5(String)
> Input : String
> Output : String
>  



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Commented] (CALCITE-3263) Add MD5, SHA1 SQL functions

2019-08-19 Thread ShuMing Li (Jira) 


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

ShuMing Li commented on CALCITE-3263:
-

Thanks for [~julianhyde]'s replies. I will add a review of what other databases 
do later.

> Add MD5, SHA1 SQL functions
> ---
>
> Key: CALCITE-3263
> URL: https://issues.apache.org/jira/browse/CALCITE-3263
> Project: Calcite
>  Issue Type: Improvement
>Reporter: ShuMing Li
>Priority: Minor
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> `MD5/SHA1` functions are common UDFs in many SQL engines. We may support them 
> in SQL just like `from_base64`/`to_base64`.
>  
> - MySQL: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
> - PostgreSQL: https://www.postgresql.org/docs/current/functions-string.html



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Created] (CALCITE-3153) Improve testing in TpcdsTest using assertEqual instead of printing results

2019-06-27 Thread ShuMing Li (JIRA) 
ShuMing Li created CALCITE-3153:
---

 Summary: Improve testing in TpcdsTest using assertEqual instead of 
printing results
 Key: CALCITE-3153
 URL: https://issues.apache.org/jira/browse/CALCITE-3153
 Project: Calcite
  Issue Type: Test
Reporter: ShuMing Li


It's a little improve  to use `assertEqual`  instead of just printing result in 
TpcdsTest#testQuery27Builder.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Updated] (CALCITE-2990) fix the document misspelling in RelInput (Shuming Li)

2019-04-11 Thread ShuMing Li (JIRA) 


 [ 
https://issues.apache.org/jira/browse/CALCITE-2990?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ShuMing Li updated CALCITE-2990:

Issue Type: Bug  (was: Improvement)

> fix the document misspelling in RelInput  (Shuming Li)
> --
>
> Key: CALCITE-2990
> URL: https://issues.apache.org/jira/browse/CALCITE-2990
> Project: Calcite
>  Issue Type: Bug
>Reporter: ShuMing Li
>Priority: Trivial
>




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2990) fix the document misspelling in RelInput (Shuming Li)

2019-04-11 Thread ShuMing Li (JIRA) 
ShuMing Li created CALCITE-2990:
---

 Summary: fix the document misspelling in RelInput  (Shuming Li)
 Key: CALCITE-2990
 URL: https://issues.apache.org/jira/browse/CALCITE-2990
 Project: Calcite
  Issue Type: Improvement
Reporter: ShuMing Li






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)