-
1. Re: Prevent to_char implicit conversion for TimeStamp field in database
shawkins Sep 23, 2015 11:37 AM (in response to bpiepers)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.
-
2. Re: Prevent to_char implicit conversion for TimeStamp field in database
bpiepers Sep 23, 2015 2:19 PM (in response to shawkins)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?
-
3. Re: Prevent to_char implicit conversion for TimeStamp field in database
shawkins Sep 23, 2015 3:22 PM (in response to bpiepers)> 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.
-
4. Re: Prevent to_char implicit conversion for TimeStamp field in database
bpiepers Sep 23, 2015 3:31 PM (in response to shawkins)Thanks, Steven.
-
5. Re: Prevent to_char implicit conversion for TimeStamp field in database
shawkins Sep 25, 2015 3:52 PM (in response to bpiepers)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.
-
6. Re: Prevent to_char implicit conversion for TimeStamp field in database
shawkins Oct 2, 2015 8:34 AM (in response to 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
-
7. Re: Prevent to_char implicit conversion for TimeStamp field in database
bpiepers Oct 3, 2015 8:51 AM (in response to shawkins)Well done Steven, thanks.