Title:
Query of queries defect when filtering on a varchar field that happens to be in a similar format to YYYY-MM-DD.
| View in TrackerStatus/Resolution/Reason: Closed/Won't Fix/
Reporter/Name(from Bugbase): Mike Callahan / Mike Callahan (Mike Callahan)
Created: 03/03/2015
Components: Database
Versions: 10.0
Failure Type:
Found In Build/Fixed In Build: Final /
Priority/Frequency: Major / All users will encounter
Locale/System: English / Win All
Vote Count: 0
Problem Description: A query of queries with a where clause against a varchar and using the equal sign with a value that contains only numbers expressed in YYYY-MM-DD format, does not work.
Steps to Reproduce:
<cfset testQuery = QueryNew("key,textfield","BigInt,Varchar")>
<cfset x = queryAddRow(testQuery)>
<cfset x = querySetCell(testQuery,"key","1")>
<cfset x = querySetCell(testQuery,"textfield","2015-02-24")>
<cfset x = queryAddRow(testQuery)>
<cfset x = querySetCell(testQuery,"key","2")>
<cfset x = querySetCell(testQuery,"textfield","hello")>
Here's what is stored in the query results:
<cfquery name="getRecords" dbtype="query">
select *
from testQuery
</cfquery>
<cfdump var="#getRecords#">
<br>
Filtering on the textfield value in record 1 does not yield results using an equal sign:
<cfquery name="getRecords" dbtype="query">
select *
from testQuery
where textfield = '2015-02-24'
</cfquery>
<cfdump var="#getRecords#">
<br>
However, filtering on the textfield value in record 1 works if you use LIKE:
<cfquery name="getRecords" dbtype="query">
select *
from testQuery
where textfield LIKE '2015-02-24'
</cfquery>
<cfdump var="#getRecords#">
<br>
For some reason a query of queries with a where clause against a varchar and using the equal sign with a value that contains only numbers expressed in YYYY-MM-DD format, does not work.
<cfabort>
Actual Result: The second cfdump shows searching for a text string in the format of YYYY-MM-DD will not yield results.
Expected Result: Searching for a string that happens to be in YYYY-MM-DD format should return record number 1 in this case.
Any Workarounds: Change the SQL query to use "LIKE" instead of "=" on the WHERE clause. This is seen in the third cfdump results.
----------------------------- Additional Watson Details -----------------------------
Watson Bug ID: 3947941
Reason: BugVerified
External Customer Info:
External Company:
External Customer Name: Mike
External Customer Email:
External Test Config: My Hardware and Environment details: ColdFusion 10 Enterprise Edition running on Windows Server 2003.Attachments:
Comments: