Prevent to_char implicit conversion for TimeStamp field in database

[bpiepers]

I'm currently toying with the behavior of the translator for a specific TimeStamp field in my model in a Teiid 8.4/JDV 6.0 setup. The database stores this field as a TimeStamp type but very often, clients that connect to the VDB query that field with a string literal like:

SELECT (...) FROM [table_name] WHERE [date_field] > '2015-09-01' AND [date_field] < '2015-09-10';

Unfortunately, when you issue a query like this it is in the end translated like:

SELECT (...) FROM [table_name] WHERE to_char(g_2."[date_field]", 'YYYY-MM-DD HH24:MI:SS.MS') > '2015-03-01' AND to_char(g_2."date_field", 'YYYY-MM-DD HH24:MI:SS.MS') < '2015-04-01'

This is a Netezza database with the corresponding Netezza translator set on the data source. Converting a timestamp to a Varchar like this is very expensive (takes minutes to complete) so I would rather have it pushed down to the database as is (which would in our scenario decrease the processing time to seconds), so without the to_char call. Only when I add time to the above statement like so:

SELECT (...) FROM [table_name] WHERE [date_field] > '2015-09-01 00:00:00' AND [date_field] < '2015-09-10 00:00:00';

The engine translates it to an acceptable form:

SELECT (...) FROM [table_name] WHERE [date_field] > {ts '2015-09-01 00:00:00.0'} AND [date_field] < {ts '2015-09-10 00:00:00.0'};

But unfortunately, clients just don't issue statements like that. They often omit the timestamp (but should be able to provide it if needed).

My question is: how can I prevent JDV from trying to do implicit conversion? As the Reference Material states (paragraph 4.3) JDV automatically converts string literals to a date type but in my case it is trying to convert it to a Varchar in cases where no time is added to the literal. This is not what we want.

[shawkins]

We automatically convert string literals to timestamp, but only in the narrow case of when the string matches the expected timestamp format - yyyy-mm-dd hh:mm:ss.fffffffff

Otherwise we'll assume that a string comparison is needed.  We don't allow full flexibility here (for example something like Joda time string literal to date conversion) as we need to have a consistent notion of what the string to timestamp conversion is for pushdown support.

Expanding to valid date string literals may be possible as long we validate existing pushdown scenarios would not be altered.

[bpiepers]

Okay but most databases are perfectly capable of understanding formats like the one from the example i.e. 'yyyy-mm-dd' and is much faster than trying to first convert the date field to a varchar with a to_char function. In this case it's also just false, as the field in question is a TimeStamp, not a Varchar. It should not intervene here. There is no way around this other than letting clients format the dates differently?

[shawkins]

> Okay but most databases are perfectly capable of understanding formats like the one from the example i.e. 'yyyy-mm-dd'

It's a matter of what is defined as the implicit conversion rules. Just like with the usage of the ANSI DATE / TIME / TIMESTAMP keyword or the JDBC escape syntax {d ' '}, Teiid expects that the string literal follows a specific format. 

We're also trying to ensure that the notion of a conversion from string to timestamp is consistent, which is key for virtualization.  That is for example if you pushdown "timestamp_col = cast(string_val as timestamp)" - that it should expect string_val to be in expected string literal format and not also need to account for a date string for example. 

> is much faster than trying to first convert the date field to a varchar with a to_char function

That is correct.  However that's just a side effect.  It's a question of inferring intent.  Generally users should be discouraged from relying on implicit conversions like these since the behavior is not standardized. 

Most dbs won't assume the implicit conversion of the other expression type to string by default when the string does not match the expected format.  However Teiid, or more accurately MetaMatrix, chose to assume that the intent was to allow the widening conversion.  There have already been other instances where that behavior has been removed, so I would be fine to introduce an option to do that here as well.  That way at least an exception would be thrown so that the sql can be clarified.

> There is no way around this other than letting clients format the dates differently?

Without a code change, no.  Using something explicit is the best solution.  That could be a parseXXX function, an escape {ts '...'} {d '...'}, or I'll also add the DATE / TIME / TIMESTAMP keyword handling as well.

[bpiepers]

Thanks, Steven.

[shawkins]

I've added [TEIID-3712] Add support for DATE/TIME/TIMESTAMP keyword literals - JBoss Issue Tracker

And will shortly add [TEIID-3722] Add an option to not widen comparisons to string - JBoss Issue Tracker

There may be time to also get in a change to allow the date string form specifically for parsed literals that could be timestamps.  We'll just have it documented as a narrow change.

[shawkins]

With a delay in getting out CR1, I was able to also get in the change to allow string date literals for timestamps - [TEIID-3736] Allow date formatted string literals to be parsed as timestamps - JBoss Issue Tracker

[bpiepers]

Well done Steven, thanks.