3 Replies Latest reply on Mar 20, 2019 10:44 PM by Ivan Chan

    SQL Error for time with millisecond

    Ivan Chan Apprentice

      Hi, I have a problem running the following SQL on Teiid:

       

      select Sum("column_int") as "Sum_column_int",

      "column_time"

      from "FoodmartDataSourceJNDI_public"."tablewithalltypes"

      where ("column_time" between '02:09:00' and '02:09:59.999')

      group by "column_time"

      order by "column_time"

      limit 1000

       

      What is the correct format for time with millisecond?

       

      Here is the exception:

       

      Caused by: org.teiid.api.exception.query.QueryResolverException: TEIID31172 Could not resolve expressions being compared to a common type excluding character conversions: FoodmartDataSourceJNDI_public.tablewithalltypes.column_time BETWEEN '02:09:00' AND '02:09:59.999'

              at org.teiid.query.resolver.util.ResolverVisitor.resolveBetweenCriteria(ResolverVisitor.java:922)

              at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:239)

              at org.teiid.query.sql.lang.BetweenCriteria.acceptVisitor(BetweenCriteria.java:132)

              at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:54)

              at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:61)

              at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.postVisitVisitor(SimpleQueryResolver.java:223)

              at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:102)

              at org.teiid.query.sql.lang.BetweenCriteria.acceptVisitor(BetweenCriteria.java:132)

              at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:63)

              at org.teiid.query.resolver.command.SimpleQueryResolver$QueryResolverVisitor.visit(SimpleQueryResolver.java:238)

              at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:70)

              at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:281)

              at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:127)

              at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:283)

              at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:413)

              at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:119)

              at org.teiid.dqp.internal.process.Request.processRequest(Request.java:481)

              at org.teiid.dqp.internal.process.PreparedStatementRequest.processRequest(PreparedStatementRequest.java:294)

              at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:655)

              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:338)

              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)

              at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:313)

              at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:245)