SQL Error for time with millisecond

[ichanjasper]

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)

[zhurlik]

Chapter 3. Datatypes

[shawkins]

> What is the correct format for time with millisecond?

The time type does not carry fractional seconds.

[ichanjasper]

Is there anyway to filter time with millisecond in where clause?

[shawkins]

Can you just us a comparison that has whole seconds?

"column_time" >= '02:09:00' and "column_time" < '02:10:00'

Is there some source that you are targeting that needs fractional seconds on time?

[ichanjasper]

One of the requirements is looking up exact time field (with millisecond) using equal clause.  It seems like it can't achieve with Teiid engine at this moment.

[shawkins]

> One of the requirements is looking up exact time field (with millisecond) using equal clause.  It seems like it can't achieve with Teiid engine at this moment.

Timestamps carry fractional seconds.  By the JDBC spec time and date do not.  Can you use timestamps instead?

[ichanjasper]

This doesn't seem to be an option.  However, the following sql used to work in Teiid 8.12.3, but no longer in Teiid 9.1.1.

[shawkins]

> However, the following sql used to work in Teiid 8.12.3, but no longer in Teiid 9.1.1.

More than likely what you are seeing is that the rules around implicit type conversion to string were tightened.  So you are likely actually doing:

cast("column_time" as string) between '02:09:00' and '02:09:59.999'

You can add an explicit cast in later versions.  If the conversion is occurring in Teiid the fractional seconds will be dropped.

[ichanjasper]

Oh I see.  Thank you for the explanation.