[jira] [Created] (CALCITE-2744) throws RuntimeException in RelDecorrelator when optimizing a Semi-Join query with a multi-param aggregate function in subquery
Wei Zhong created CALCITE-2744:
--
Summary: throws RuntimeException in RelDecorrelator when
optimizing a Semi-Join query with a multi-param aggregate function in subquery
Key: CALCITE-2744
URL: https://issues.apache.org/jira/browse/CALCITE-2744
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.17.0
Reporter: Wei Zhong
Assignee: Julian Hyde
Fix For: 1.18.0
when running the following test in org.apache.calcite.test.RelOptRulesTest, we
will get a RuntimeException:
{code:java}
@Test public void testDecorrelateWith2ParamsAgg() {
final HepProgram preProgram =
HepProgram.builder()
.addRuleInstance(FilterProjectTransposeRule.INSTANCE)
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
.addRuleInstance(ProjectMergeRule.INSTANCE)
.build();
final HepProgram program =
HepProgram.builder()
.addRuleInstance(SemiJoinRule.PROJECT)
.build();
final String sql = "select * from dept where exists (\n"
+ "select UDF_AVG(sal, sal + 1) from sales.emp\n"
+ "where emp.deptno = dept.deptno\n"
+ "and emp.sal > 100)";
Sql sqlTest = sql(sql)
.withDecorrelation(true)
.withTrim(true)
.withPre(preProgram)
.with(program);
sqlTest.check();
}
{code}
UDF_AVG code in MockSqlOperatorTable:
{code:java}
public static void addRamp(MockSqlOperatorTable opTab) {
// Don't use anonymous inner classes. They can't be instantiated
// using reflection when we are deserializing from JSON.
opTab.addOperator(new RampFunction());
opTab.addOperator(new DedupFunction());
opTab.addOperator(UDF_AVG);
}
public static final SqlFunction UDF_AVG = new SqlAggFunction(
"UDF_AVG",
null,
SqlKind.OTHER_FUNCTION,
ReturnTypes.AVG_AGG_FUNCTION,
null,
OperandTypes.family(SqlTypeFamily.NUMERIC, SqlTypeFamily.NUMERIC),
SqlFunctionCategory.NUMERIC,
false,
false,
Optionality.FORBIDDEN) {
@Override public boolean isDeterministic() {
return false;
}
};
{code}
The RuntimeExcpetion detail:
{code:java}
java.lang.RuntimeException: While invoking method 'public
org.apache.calcite.sql2rel.RelDecorrelator$Frame
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalProject)'
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:527)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:613)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:254)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:214)
at
org.apache.calcite.sql2rel.SqlToRelConverter.decorrelateQuery(SqlToRelConverter.java:3037)
at
org.apache.calcite.sql2rel.SqlToRelConverter.decorrelate(SqlToRelConverter.java:481)
at
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:615)
at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:177)
at org.apache.calcite.test.RelOptTestBase$Sql.check(RelOptTestBase.java:334)
at org.apache.calcite.test.RelOptTestBase$Sql.check(RelOptTestBase.java:317)
at
org.apache.calcite.test.RelOptRulesTest.testDecorrelateWith2ParamsAgg(RelOptRulesTest.java:4286)
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.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.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at
Re: Can’t seem to parse DDL staements
Hi Dilip, DDL related code including statement parse and data structures are located in calcite-server sub-project. The unit test 'ServerParserTest' may be a good starting point to look into. You can find it here https://github.com/apache/calcite/blob/master/server/src/test/java/org/apache/calcite/test/ServerParserTest.java . On Mon, Dec 17, 2018 at 1:34 AM Dilip Raj Baral wrote: > Hi, team. > > I have been using Apache Calcite for about two weeks now for one of the > projects at my company. I have found it fun and pretty useful so far. I > have been able to parse DMLs very smoothly. However, when I tried to parse > DDLs like CREATE TABLE, ALTER TABLE, etc., the SqlParser.parseStmt() or > SqlParser.parseQuery() failed. > > I have detailed the specifics in the following SO question. > > > https://stackoverflow.com/questions/53801005/apache-calcite-cant-seem-to-parse-ddl-statements > > Looking forward to a suggestion. > > Best regards, > Dilip Raj Baral > https://diliprajbaral.com > > >
Re: Problems about subsets clause order for MATCH_RECOGNIZE
Hi Dawid, The elements’s order in SUBSET clause is consistent with the order in PATTERN, is this what you mean? Let me take an example: SUBSET X=(A,B) I’m not very sure about the implementations of Oracle, but I’m curious that why we use sortedSet to store the elements(A,B) of SUBSET if we don’t need the “sorted”. Best, Jiayi Liao Original Message Sender:Dawid wysakowiczdwysakow...@apache.org Recipient:dev...@calcite.apache.org; Julian hydejh...@apache.org; bupt_ljybupt_...@163.com Date:Sunday, Dec 16, 2018 20:08 Subject:Re: Problems about subsets clause order for MATCH_RECOGNIZE Hi Jiayi, I don't think it should any difference what is the order in the SUBSET clause. The PATTERN clause specifies the definitive order of pattern variables. SUBSET clause just groups them as a single entity you can reference. Therefore LAST/FIRST/PREV/NEXT/AFTER MATCH etc. will take into account only order in the PATTERN clause. Best, Dawid On 16/12/2018 08:07, Julian Hyde wrote: I don’t understand MATCH_RECOGNIZE well enough to give an opinion. Is there a query that gives different results on Oracle if you change the order of items in SUBSET? It seems that the parser preserves the order of items in the subset, but the SqlToRelConverter does not, hence the line "subsets=[[[DOWN, STRT]]” in SqlToRelConverterTest.xml. I would be concerned if the parser re-ordered things, but I am not too concerned about SqlToRelConverter unless the semantics are wrong. On Dec 14, 2018, at 12:37 AM, bupt_ljy bupt_...@163.com wrote: Hi all, It’s my first time to send emails to Calcite developers. It’s a really good project and many projects benefit from it. Now I’ve encountered a problem about the subsets for MATCH_RECOGNIZE in thetestMatchRecognizeSubset1() testing. From the results, I can tell that"subset stdn = (strt, down)"will be explained to "SUBSET \"STDN\" = (\"DOWN\", \"STRT\”)”, which confuses me. IMO, It’ll affect the result of functions like“FIRST” and“LAST”, which may not be what I want, although it works fine with“AVG” function. I’m not sure if this is a bug, or anyone can tell me how we arrive here? Best, Jiayi Liao
[jira] [Created] (CALCITE-2743) TimeStamp confused in avatica jdbc
shining created CALCITE-2743:
Summary: TimeStamp confused in avatica jdbc
Key: CALCITE-2743
URL: https://issues.apache.org/jira/browse/CALCITE-2743
Project: Calcite
Issue Type: Bug
Components: avatica
Affects Versions: 1.10.0
Reporter: shining
I use Phoenix Query Server through avatica, Operation is as follows：
1. create table with sqlline-thin
CREATE TABLE test_timezone(log_time TIMESTAMP NOT NULL PRIMARY KEY, id
VARCHAR(40));
2. Upset data
upsert into test_timezone values('2018-11-27 11:01:59.000','1’);
3. Query
0: jdbc:phoenix:thin:url=http://localhost:876> select * from test_timezone;
+--+-+
| LOG_TIME | ID |
+-+--+
| 2018-11-27 03:01:59 | 1 |
+--+——+
My local timeZone is GMT+8, and configured Phoenix
“phoenix.query.dateFormatTimeZone=GMT+8”
I also view code of avatica， when the timezone is GMT+8， getTimeStamp method
will lose 8 hours：
public Timestamp getTimestamp(Calendar calendar) throws SQLException {
java.util.Date date = (java.util.Date) getObject();
if (date == null) {
return null;
}
long v = date.getTime();
if (calendar != null) {
v -= calendar.getTimeZone().getOffset(v);
}
return new Timestamp(v);
}
sqlline-thin use getString() method get the timestamp，it pass a null timezone
to timestampAsString()
So I have two doubtful places here：
1）I get correct time from phoenixResultSet， why reduce 8 hours ？
2）Can getString method be returned by getTimeStamp().toString():
public String getString() throws SQLException {
final long v = getLong();
if (v == 0 && wasNull()) {
return null;
}
return getTimeStamp(v, null).toString();
}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
??????Can??t seem to parse DDL staements
Can use calcite-server parse DDLs, code : SqlParser.Config sqlParserConfig = SqlParser.configBuilder() .setConformance(SqlConformanceEnum.MYSQL_5) .setLex(Lex.MYSQL) .build(); InputStream inputStream = new ByteArrayInputStream(query.getBytes()); SqlDdlParserImpl parser = new SqlDdlParserImpl(inputStream); parser.setConformance(parserConfig.conformance()); parser.setIdentifierMaxLength(parserConfig.identifierMaxLength()); parser.setQuotedCasing(parserConfig.quotedCasing()); parser.setUnquotedCasing(parserConfig.unquotedCasing()); SqlNode sqlNode = parser.parseSqlStmtEof(); -- -- ??: "Dilip Raj Baral"; : 2018??12??16??(??) 6:19 ??: "dev"; : "Dilip Raj Baral"; : Can??t seem to parse DDL staements Hi, team. I have been using Apache Calcite for about two weeks now for one of the projects at my company. I have found it fun and pretty useful so far. I have been able to parse DMLs very smoothly. However, when I tried to parse DDLs like CREATE TABLE, ALTER TABLE, etc., the SqlParser.parseStmt() or SqlParser.parseQuery() failed. I have detailed the specifics in the following SO question. https://stackoverflow.com/questions/53801005/apache-calcite-cant-seem-to-parse-ddl-statements Looking forward to a suggestion. Best regards, Dilip Raj Baral https://diliprajbaral.com
Relational algebra and signal processing
Hi Calcite-devs, I just had a very interesting mail exchange with Julian (Hyde) on the incubator list [1]. It was about our project CRUNCH (which is mostly about time series analyses and signal processing) and its relation to relational algebra and I wanted to bring the discussion to this list to continue here. We already had some discussion about how time series would work in calcite [2] and it’s closely related to MATCH_RECOGNIZE. But, I have a more general question in mind, to ask the experts here on the list. I ask myself if we can see the signal processing and analysis tasks as proper application of relational algebra. Disclaimer, I’m mathematician, so I know the formals of (relational) algebra pretty well but I’m lacking a lot of experience and knowledge in the database theory. Most of my knowledge there comes from Calcites source code and the book from Garcia-Molina and Ullman). So if we take, for example, a stream of signals from a sensor, then we can of course do filtering or smoothing on it and this can be seen as a mapping between the input relation and the output relation. But as we usually need more than just one tuple at a time we lose many of the advantages of the relational theory. And then, if we analyze the signal, we can again model it as a mapping between relations, but the input relation is a “time series” and the output relation consists of “events”, so these are in some way different dimensions. In this situation it becomes mostly obvious where the main differences between time series and relational algebra are. Think of something simple, an event should be registered, whenever the signal switches from FALSE to TRUE (so not for every TRUE). This could also be modelled with MATCH_RECOGNIZE pretty easily. But, for me it feels “unnatural” because we cannot use any indices (we don’t care about the ratio of TRUE and FALSE in the DB, except for probably some very rough outer bounds). And we are lacking the “right” information for the optimizer like estimations on the number of analysis results. It gets even more complicated when moving to continuous valued signals (INT, DOUBLE, …), e.g., temperature readings or something. If we want to analyze the number of times where we have a temperature change of more than 5 degrees in under 4 hours, this should also be doable with MATCH_RECOGNIZE but again, there is no index to help us and we have no information for the optimizer, so it feels very “black box” for the relational algebra. I’m not sure if you get my point, but for me, the elegance of relational algebra was always this optimization stuff, which comes from declarative and ends in an “optimal” physical plan. And I do not see how we can use much of this for the examples given above. Perhaps, one solution would be to do the same as for spatial queries (or the JSON / JSONB support in postgres, [3]) to add specialized indices, statistics and optimizer rules. Then, this would make it more “relational algebra”-esque in the sense that there really is a possibility to apply transformations to a given query. What do you think? Do I see things to complicated or am I missing something? Julian [1] https://lists.apache.org/thread.html/1d5a5aae1d4f5f5a966438a2850860420b674f98b0db7353e7b476f2@%3Cgeneral.incubator.apache.org%3E [2] https://lists.apache.org/thread.html/250575a56165851ab55351b90a26eaa30e84d5bbe2b31203daaaefb9@%3Cdev.calcite.apache.org%3E [3] https://www.postgresql.org/docs/9.4/datatype-json.html
Re: [DISCUSS] Towards Calcite 1.18
An update on release 1.18. We have had two release candidates RC0 and RC1, and both votes have been cancelled due to faults found by Hive's test suite. I will roll RC2 when I hear from Zoltan that the Hive suite has passed against the latest commit. See https://issues.apache.org/jira/browse/CALCITE-2731. Jacques just pushed a fix to master. I backed it out because (a) our policy is to release from master branch, which means that we have to lock master during releases, and (b) his commit generated warnings. It will go into 1.19. See https://issues.apache.org/jira/browse/CALCITE-2736. I let Jesus commit a minor addendum to https://issues.apache.org/jira/browse/CALCITE-2673 on Friday. Julian On Sun, Dec 2, 2018 at 6:04 PM Francis Chuang wrote: > > Julian, > > Avatica 1.13.0 has been tagged and pushed. I also uploaded the release > artifacts and I am just waiting for the artifacts to propagate across > the mirrors before I make a formal announcement and push the website. > > Francis > > On 3/12/2018 10:08 am, Julian Hyde wrote: > > Francis, > > > > Much appreciated! The nexus push was successful, and I was able to use > > avatica-1.13 in builds. I'll make sure that we do not announce the > > Calcite release 1.18, or push the > > https://issues.apache.org/jira/browse/CALCITE-2716 fix to master, > > until avatica-1.13 is officially announced. > > > > Julian > > > > On Sun, Dec 2, 2018 at 12:23 AM Francis Chuang > > wrote: > >> Thanks for taking this on, Julian! > >> > >> The vote for Avatica 1.13.0 has closed and I plan to release the 1.13.0 > >> soon (I need to wait for the docker hub repository in INFRA-17333 [1] to > >> be created, otherwise the docker hub build will fail once I push the tag > >> for rel/avatica-1.13.0. > >> > >> I've promoted the 1.13.0 repository in nexus, so this should not block > >> Calcite 1.18.0-rc0. > >> > >> Francis > >> > >> [1] https://issues.apache.org/jira/browse/INFRA-17333 > >> > >> On 1/12/2018 11:11 pm, Michael Mior wrote: > >>> Thanks Julian! Unfortunately with family visiting this weekend, my > >>> bandwidth is currently fairly low but I'll try to churn through some PRs > >>> early this coming week. > >>> > >>> On Fri, Nov 30, 2018, 23:00 Julian Hyde >>> > It looks as if Avatica will be released soon, and as that was our main > dependency for Calcite 1.18, we can move ahead with the release. > > I volunteer to be release manager for Calcite 1.18, and propose that we > make an RC on Monday. > > There are a lot of PRs (107 open currently)[1]. I can’t get through them > all myself, so I will need help reviewing & merging them. (If you are > committer, look in the JIRA case to see whether someone has begun > reviewing > the PR. I am reviewing several right now.) > > Even with help, we are not going to get to all PRs. That will have to be > OK; it has been too long since we had a release, and we just don’t have > enough active committers to deal with the number of incoming > contributions. > > Julian > > [1] https://github.com/apache/calcite/pulls > > > > On Nov 6, 2018, at 4:24 AM, Michael Mior wrote: > > > > Not strictly necessary I believe, but I know there's a desire to upgrade > > Jetty in Calcite that can't happen until the Avatica release, so it > > would > > be nice to see that happen first. > > > > > > > > Le lun. 5 nov. 2018 à 16:31, Francis Chuang a > > écrit : > > > >> I think we should consider releasing new versions of Calcite and > >> Avatica > >> soon, especially with the Thanksgiving holiday in North America and > >> Christmas + New Year later in December/January coming up. > >> > >> Am I correct to assume that we need to release Avatica 1.13.0 before > >> Calcite 1.18.0? > >> > >> If so, I am able to be release manager for Avatica if no one else is > >> interested. > >> > >> On 21/10/2018 6:13 PM, Michael Mior wrote: > >>> Thanks for continuing to push releases forward! Unfortunately I won't > be > >>> able to volunteer to be release manager this time around, but I'll try > to > >>> set aside some time to go through some PRs. > >>> > >>> On Sat, Oct 20, 2018, 02:21 Julian Hyde wrote: > >>> > OK, it's now exactly 3 months since 1.17. I think it's time. I think > we should aim for a first RC a week from today (Friday 26th October). > > Can we have a volunteer to be release manager? > > Also, there are lots of PRs to review and merge. Please help out with > that task, committers. > > Julian > > > > On Thu, Sep 20, 2018 at 11:00 AM Julian Hyde > wrote: > > We’ll be sure to get PRs into the release. If you like, you can make > >> the > JIRA case depend on those 3 cases -
Calcite-Master - Build # 987 - Still Failing
The Apache Jenkins build system has built Calcite-Master (build #987) Status: Still Failing Check console output at https://builds.apache.org/job/Calcite-Master/987/ to view the results.
Can’t seem to parse DDL staements
Hi, team. I have been using Apache Calcite for about two weeks now for one of the projects at my company. I have found it fun and pretty useful so far. I have been able to parse DMLs very smoothly. However, when I tried to parse DDLs like CREATE TABLE, ALTER TABLE, etc., the SqlParser.parseStmt() or SqlParser.parseQuery() failed. I have detailed the specifics in the following SO question. https://stackoverflow.com/questions/53801005/apache-calcite-cant-seem-to-parse-ddl-statements Looking forward to a suggestion. Best regards, Dilip Raj Baral https://diliprajbaral.com
Re: Problems about subsets clause order for MATCH_RECOGNIZE
Hi Jiayi, I don't think it should any difference what is the order in the SUBSET clause. The PATTERN clause specifies the definitive order of pattern variables. SUBSET clause just groups them as a single entity you can reference. Therefore LAST/FIRST/PREV/NEXT/AFTER MATCH etc. will take into account only order in the PATTERN clause. Best, Dawid On 16/12/2018 08:07, Julian Hyde wrote: > I don’t understand MATCH_RECOGNIZE well enough to give an opinion. Is there a > query that gives different results on Oracle if you change the order of items > in SUBSET? > > It seems that the parser preserves the order of items in the subset, but the > SqlToRelConverter does not, hence the line "subsets=[[[DOWN, STRT]]” in > SqlToRelConverterTest.xml. I would be concerned if the parser re-ordered > things, but I am not too concerned about SqlToRelConverter unless the > semantics are wrong. > >> On Dec 14, 2018, at 12:37 AM, bupt_ljy wrote: >> >> Hi all, >> It’s my first time to send emails to Calcite developers. It’s a really good >> project and many projects benefit from it. >> Now I’ve encountered a problem about the subsets for MATCH_RECOGNIZE in >> thetestMatchRecognizeSubset1() testing. From the results, I can tell >> that"subset stdn = (strt, down)"will be explained to "SUBSET \"STDN\" = >> (\"DOWN\", \"STRT\”)”, which confuses me. IMO, It’ll affect the result of >> functions like“FIRST” and“LAST”, which may not be what I want, although it >> works fine with“AVG” function. >> I’m not sure if this is a bug, or anyone can tell me how we arrive here? >> >> >> >> >> Best, >> Jiayi Liao signature.asc Description: OpenPGP digital signature
