Ah, I like a nice gnarly SQL statement! Please log a JIRA case. If you are able to rewrite either query in terms of one of the standard schemas (e.g. the "SCOTT" schema in hsqldb-model.json) and still reproduce the same error, that would be awesome.
On Sat, May 21, 2016 at 8:34 AM, Sanjay Prasad <[email protected]> wrote: > Hi guys, > Correlated scalar selects fails with assert error for an SQL > statement like the one given below. Sorry for the noise in the SQL, it is > generated by our generic SQL processor for json that takes a much simpler > SQL. > > SELECT > (SELECT "input"."type" AS "type" FROM "input" WHERE "ip"."name" = > 'test') AS "uid_1" > FROM > (SELECT "input"."name" AS "name", "input"."type" AS "type", > "input"."enabled" AS "enabled", > "input"."speed" AS "speed", "input"."temperature" AS > "temperature", "input"."time" AS "time", > "input"."water.temperature" AS "water.temperature", > "input"."water.pressure" AS "water.pressure" > FROM "input") "ip" > > java.lang.AssertionError: Correlation variable $cor0 should be defined > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.getCorrelVariableGetter(EnumerableRelImplementor.java:450) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor$1.apply(EnumerableRelImplementor.java:79) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor$1.apply(EnumerableRelImplementor.java:77) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:502) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:655) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:645) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics0(RexImpTable.java:737) > at > org.apache.calcite.adapter.enumerable.RexImpTable.access$900(RexImpTable.java:180) > at > org.apache.calcite.adapter.enumerable.RexImpTable$3.implement(RexImpTable.java:409) > at > org.apache.calcite.adapter.enumerable.RexImpTable$CastOptimizedImplementor.implement(RexImpTable.java:1738) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics(RexImpTable.java:800) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics0(RexImpTable.java:754) > at > org.apache.calcite.adapter.enumerable.RexImpTable.access$900(RexImpTable.java:180) > at > org.apache.calcite.adapter.enumerable.RexImpTable$3.implement(RexImpTable.java:409) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexImpTable$IsXxxImplementor.implement(RexImpTable.java:1887) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCondition(RexToLixTranslator.java:727) > at > org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:161) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:102) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableJoin.implement(EnumerableJoin.java:190) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:119) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102) > at > org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92) > at > org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1172) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:294) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:720) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:587) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:557) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:194) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:184) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:85) > at > org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:153) > at > com.kalkitech.k11.core.sql.calcite.CalciteQueryExecutor.execute(CalciteQueryExecutor.java:43) > at > com.kalkitech.k11.core.calcite.CalciteSqlTest.executeTest(CalciteSqlTest.java:143) > at > com.kalkitech.k11.core.calcite.CalciteSqlTest.basicTest(CalciteSqlTest.java:84) > at > com.kalkitech.k11.core.calcite.CalciteSqlTest.selectCorrelatedScalar(CalciteSqlTest.java:170) > 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.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80) > at org.testng.internal.Invoker.invokeMethod(Invoker.java:673) > at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:842) > at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1166) > at > org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125) > at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109) > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > at java.lang.Thread.run(Thread.java:745) > > Another one fails with a NullPointerException. > > SELECT > (SELECT DISTINCT "rs"."time" AS "time" FROM "input" WHERE "input"."time" > = "rs"."time") AS "x", > (SELECT "input"."value" AS "value" FROM "input" WHERE "input"."time" = > "rs"."time" AND "input"."pointId" = 5) AS "y1" > FROM (SELECT DISTINCT "input"."time" AS "time" FROM "input") "rs" > > java.sql.SQLException: Error while preparing statement [SELECT (SELECT > DISTINCT "rs"."time" AS "time" FROM "input" WHERE "input"."time" = > "rs"."time") AS "x", (SELECT "input"."value" AS "value" FROM "input" WHERE > "input"."time" = "rs"."time" AND "input"."pointId" = 5) AS "y1" FROM (SELECT > DISTINCT "input"."time" AS "time" FROM "input") "rs"] > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:184) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:85) > at > org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:153) > at > com.kalkitech.k11.core.sql.calcite.CalciteQueryExecutor.execute(CalciteQueryExecutor.java:43) > at > com.kalkitech.k11.core.ds.DataSourceQueryManager.executeSql(DataSourceQueryManager.java:88) > at > com.kalkitech.k11.core.ds.DataSourceServlet.sendDataSourceData(DataSourceServlet.java:367) > at > com.kalkitech.k11.core.ds.DataSourceServlet.doGet(DataSourceServlet.java:152) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) > at > org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) > at > org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61) > at > org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) > at > org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) > at > org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) > at > org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) > at > org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) > at > org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) > at > org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) > at > org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) > at > org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) > at > org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) > at > org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) > at > org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) > at > org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) > at > org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) > at > org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) > at > com.kalkitech.k11.core.servlet.CorsFilter.handleNonCORS(CorsFilter.java:391) > at > com.kalkitech.k11.core.servlet.CorsFilter.doFilter(CorsFilter.java:126) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) > at > org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) > at > org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) > at org.apache.tomee.catalina.OpenEJBValve.invoke(OpenEJBValve.java:44) > at > org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) > at > org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) > at > org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) > at > org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:957) > at > org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) > at > org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423) > at > org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079) > at > org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620) > at > org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > at > org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) > at java.lang.Thread.run(Thread.java:745) > Caused by: java.lang.NullPointerException > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:504) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:655) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:645) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics0(RexImpTable.java:737) > at > org.apache.calcite.adapter.enumerable.RexImpTable.access$900(RexImpTable.java:180) > at > org.apache.calcite.adapter.enumerable.RexImpTable$3.implement(RexImpTable.java:409) > at > org.apache.calcite.adapter.enumerable.RexImpTable$CastOptimizedImplementor.implement(RexImpTable.java:1738) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics(RexImpTable.java:800) > at > org.apache.calcite.adapter.enumerable.RexImpTable.implementNullSemantics0(RexImpTable.java:754) > at > org.apache.calcite.adapter.enumerable.RexImpTable.access$900(RexImpTable.java:180) > at > org.apache.calcite.adapter.enumerable.RexImpTable$3.implement(RexImpTable.java:409) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexImpTable$IsXxxImplementor.implement(RexImpTable.java:1887) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:535) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:208) > at > org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCondition(RexToLixTranslator.java:727) > at > org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:161) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:102) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableAggregate.implement(EnumerableAggregate.java:102) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableJoin.implement(EnumerableJoin.java:190) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableJoin.implement(EnumerableJoin.java:185) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) > at > org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:119) > at > org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102) > at > org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92) > at > org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1172) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:294) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:720) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:587) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:557) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:194) > ... 52 more > > The both of them work fine in calcite 1.5 and does not work in calcite > 1.6 or 1.7. Is this a know issue or am I doing something wrong? > > -Regards > -SP > > "Disclaimer: This message is being sent from Kalki Communication > Technologies > Pvt Ltd (KALKITECH). and may contain information which is confidential or > privileged. If you are not the intended recipient, please advise the sender > immediately by reply e-mail and delete this message and any attachments > without retaining a copy. Any unauthorized use of the content of this > message > can expose the responsible party to civil and/or criminal penalties, > and may constitute a more serious offense. Further the company does not > accept > liability for any errors, omissions, viruses or computer problems > experienced as a result of this transmission. If you have received this > message in error, notice is hereby given that no representation, contract or > other binding obligation shall be created by this e-mail."
