[jira] [Commented] (CALCITE-2935) Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions
[ https://issues.apache.org/jira/browse/CALCITE-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3617?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3628?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3334?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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] [Commented] (CALCITE-3347) IndexOutOfBoundsException in FixNullabilityShuttle when using FilterIntoJoinRule
[ https://issues.apache.org/jira/browse/CALCITE-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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 org.junit.runners.Parent
[jira] [Comment Edited] (CALCITE-3347) IndexOutOfBoundsException in FixNullabilityShuttle when using FilterIntoJoinRule
[ https://issues.apache.org/jira/browse/CALCITE-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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-3323) Handle arbitrary/unknown functions that have ordinary syntax
[ https://issues.apache.org/jira/browse/CALCITE-3323?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3272?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ https://issues.apache.org/jira/browse/CALCITE-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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"
[ https://issues.apache.org/jira/browse/CALCITE-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[ 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
[ 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
[ https://issues.apache.org/jira/browse/CALCITE-3263?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
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)
[ 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)
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)