tracker issue : CF-3573384

select a category, or use search below
(searches all categories and all time range)
Title:

hand-built query objects coerce timestamps to wrong type

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/NotABug

Reporter/Name(from Bugbase): Tim Parker / Tim Parker (Tim Parker)

Created: 06/05/2013

Components: Language

Versions: 10.0

Failure Type: Data Loss

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / Some users will encounter

Locale/System: English / Win All

Vote Count: 0

Problem Description: when a query is built by hand (QueryNew/QueryAddRow/QuerySetCell) and a timestamp is inserted into a cell, the underlying logic creates an object of type java.sql.Date - resulting in loss of the time portion of the timestamp.  If the data includes time, this should use java.sql.Timestamp instead [also seen with CF9.0.2]

Steps to Reproduce: run the attached code (this code was originally built to demonstrate this problem with Query-of-Query output, but further investigation shows that the original query is the source of the problem, and that the added step of running a query-of-queries is not needed to demonstrate the problem - however, when using java code to process the query, there do appear to be differences between the original query and the query-of-query results.  I'm assuming that fixing the problem in the original query will resolve the problem with the query-of-query results also.

Actual Result: when given a value of '2013-06-04 12:14:15', we get back '2013-06-04'

Expected Result: full timestamp as assigned (not truncated)

Any Workarounds: when timestamps are used in hand-built query objects, don't allow ColdFusion to guess at field types - use the 'columnTypeList' parameter to QueryNew() and specify 'varchar' for the timestamp field(s).  Also, if adding a column with QueryAddColumn, explicitly specify the field type (again, don't let CF guess - the bug here is that CF is getting it wrong)

>> This problem was originally encountered in processing query results with Java code (extracting data from a result set in preparation for serialization) - our Java code loops over the result set and extracts field data using getObject() for each cell - the resulting object is of the wrong type, so we lose the time portion of the timestamp.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3573384

External Customer Info:
External Company:  
External Customer Name: TParker
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

  1. June 05, 2013 00:00:00: 1_q-of-q.cfm

Comments:

I'm using using CF10 Developer 10,0,9,284568 with Java 1.7.0_15 on Windows Server 2008 R2 and the query and query-of-queries look exactly the same. When using QueryNew(), identify your column as "Timestamp" and you'll get the results you are expecting. QueryNew("name,flag,when", "Varchar,Integer,Timestamp")
Comment by External U.
15249 | June 05, 2013 01:04:09 PM GMT
The problem that exists with this is what if the data looks like a date stamp but isn't (ie, the first record contains a formatted date/time stamp and the rest are text values). What if many of the first records are NULL, empty or don't contain time. Should ColdFusion somehow review all possible row values looking for a single value with date & time and then magically modify the data type to Timestamp? I think the SQL schema should be properly defined before populating it with any data.
Comment by External U.
15250 | June 05, 2013 01:17:09 PM GMT
This is not a bug. You should specify the datatype when you are creating the query. In case the datatype is not specified, CF will only look at the first row and try to guess the datatype based on the values.
Comment by Rupesh K.
15251 | June 07, 2013 06:34:32 AM GMT