tracker issue : CF-3929913

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

[ANeff] Bug for: CF10 vs CF11 wrt displaying q.dateColumn

| View in Tracker

Status/Resolution/Reason: Closed/Won't Fix/

Reporter/Name(from Bugbase): Aaron Neff / Aaron Neff (Aaron Neff)

Created: 01/29/2015

Components: Language

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Normal / Few users will encounter

Locale/System: ALL / Platforms All

Vote Count: 0

Related Bugs:
CF-4064813 - Similar to

CF11 trims time when displaying a timestamp from a date column of a query created via queryNew()

Steps to reproduce:

1) Run this:

  q = queryNew("dateColumn", "date", [[createDateTime(2015,1,2,3,4,5)]]);

2) See CF10 displays {ts '2015-01-02 03:04:05'} (good) and CF11 displays 2015-01-02 (bug)

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

Watson Bug ID:	3929913

External Customer Info:
External Company:  
External Customer Name: Aaron
External Customer Email:



Problem: CF11's writedump()/cfdump trims the time. CF11's writedump()/cfdump should display date *and* time (full timestamp) like CF10 does. Repro is simple. Thanks!, -Aaron
Comment by External U.
8647 | February 07, 2015 03:04:47 PM GMT
It's more like a fix than a regression. Happened due to stringent validation of column types. Timestamp type could be used for the desired output.
Comment by Himavanth R.
8648 | March 06, 2015 03:48:44 AM GMT
Hi Himavanth, No, queryNew()'s doc says: Date: Date (can include time information) This bug is causing time to be trimmed off in existing code. Try my example and then store q.dateColumn into an MSSQL datetime field and see time was trimmed. This ticket should be escalated. Thanks!, -Aaron
Comment by External U.
8649 | April 07, 2015 03:28:33 PM GMT
Timestamp and Object are undocumented, for the columntypelist parameter:
Comment by External U.
8650 | April 07, 2015 03:36:59 PM GMT
Hi Himavanth, Regarding my previous comment about storing q.dateColumn into db, my <cfqueryparam> looked like this: <cfqueryparam cfsqltype="#columnTypeFromCFDBINFO#" value="#trim(myValue)#".. I was dynamically populating both the cfsqltype AND the trimmed value. In CF11, if myValue came from a queryNew() query with column type "date", then the time is trimmed (bad). This didn't happen in CF10. queryNew()'s "timestamp" column type isn't a valid workaround for the broken backward compat since Adobe warns against using undocumented features (column type "timestamp" is undocumented). So queryNew()'s "date" should again support time. If anyone is trimming all simple values, before writing to db, then they may unknowingly be running into this issue (time trimmed off) if they upgraded to CF11 from a previous version. Thanks!, -Aaron
Comment by External U.
8651 | April 07, 2015 06:25:24 PM GMT
Hi Himavanth, I'd actually agree w/ you if "timestamp" was documented *before* removing time information from "date". Also, stringent doesn't apply to queryNew()'s 3rd parameter, for these data types: <cfscript> q1 = queryNew("myCol"); q2 = queryNew("myInteger,myDouble,myVarchar,myBinary,myDate,myTime,myTimestamp", "integer,double,varchar,binary,date,time,timestamp", [[q1,q1,q1,q1,q1,q1,q1]]);//this should throw validation errors, but doesn't (bug #1) writeDump(q2); </cfscript> Also, while stringent does apply to queryNew()'s 3rd parameter for the bit data type, the validation error is thrown on the wrong line: <cfscript> q = queryNew("myBit", "bit", [[queryNew("myCol")]]);//error should be thrown on this line writeDump(q);//error is actually thrown on this line (bug #2) </cfscript> Thanks!, -Aaron
Comment by External U.
8652 | April 08, 2015 01:23:11 PM GMT
We had made the change of honoring the column type in array notations to make it consistent with querysetcell as part of bug CF-3000469 in 2011. This caused lot of backward compatibility issues with cast exceptions being thrown for data that didn't confirm to the column types given. So we made another change to ignore cast exceptions thrown during the type validation as part of bug CF-3143550 in 2012. Result is, if we are able to convert the data to the type specified in column type, we do it. If not, we just let the given data go in to the query. In this case we are able to convert the datetime to date. Also note that java.sql.Date also trims the time part. I think we should document the timestamp bit.
Comment by Himavanth R.
8653 | September 27, 2015 01:36:52 PM GMT
Hi Himavanth, Thank you for summarizing the history. So there are 3 items: 1) "timestamp" should be documented for QueryNew()'s columntypelist 2) "object" should be documented for QueryNew()'s columntypelist 3) whenever you knowingly introduce/allow data loss (as is the case in this ticket b/c "date" query column type no longer retains the time portion), it should be documented in the release notes I agree that the behavior in #3 is now technically correct (date shouldn't include time). HOWEVER, the issue I have is that it's an undocumented backward-compat breaking change. Imagine the data loss that is occurring right now. If CF12 held true to its promise of breaking backward-compat. And if this change were introduced in CF12, then I'd have no problem w/ it. Thanks!, -Aaron
Comment by External U.
8654 | September 28, 2015 12:46:09 AM GMT
Hi Himavanth, Do I need to file doc bugs for #1 and #2 in my previous comment? Thanks!, -Aaron
Comment by External U.
8655 | September 28, 2015 09:50:28 PM GMT
Hi Aaron, We had logged a bug for that. # CF-4064813 I am not sure about object though. There is no such thing. It is the same as not specifying any type for the column.
Comment by Himavanth R.
8656 | September 28, 2015 10:16:31 PM GMT
Hi Himavanth, I'm unable to view CF-4064813. Could it be made visible for commenting/voting? Repro to see "object" is an accepted column type: 1) run: q = queryNew("myColumn", "invalidtype", [["a"]]); 2) see "Object" in error message: "The supported column types are: [ Integer | BigInt | Double | Decimal | VarChar | Binary | Bit | Time | Date | Timestamp | Object]" Perhaps the "Object" type is there b/c I suggested that the default column type have a name. Example: q1 = queryNew("myColumn", "", [[{foo="bar"}]]);//I suggested "" and "any" should be the same q2 = queryNew("myColumn", "object", [[{foo="bar"}]]);//perhaps "object" was added instead of "any"? In the above, I'd suggested "" should be the same as "any". Perhaps "object" was added in place of "any"? Thanks!, -Aaron
Comment by External U.
8657 | September 29, 2015 02:52:26 AM GMT
Aaron, You are right. Object type is treated as java.sql.Types.JAVA_OBJECT. You should be able to view the other bug now.
Comment by Himavanth R.
8658 | September 29, 2015 03:40:15 AM GMT
Hi Himavanth, Thank you for making CF-4064813 visible and for clarification. Regarding java.sql.Types.JAVA_OBJECT, were you confirming that the "object" type is basically an "any" type (i.e. it'll accept anything)? So basically it's the default type? I wasn't sure what exactly you meant.. Thanks!, -Aaron
Comment by External U.
8659 | September 29, 2015 03:50:57 AM GMT
Yes Aaron.
Comment by Himavanth R.
8660 | September 29, 2015 03:58:49 AM GMT
Hi Himavanth, Very cool, thanks very much for confirming and following-up! -Aaron
Comment by External U.
8661 | September 29, 2015 04:05:33 AM GMT
Hi Himavanth, This broke deserialization of WDDX'd timestamp query columns. Filed CF-4158658. Thanks!, -Aaron
Comment by External U.
8662 | May 28, 2016 12:55:57 AM GMT