tracker issue : CF-4121934

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

[ANeff] Bug for: QoQ incorrect casting and casing

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

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

Created: 02/25/2016

Components: Database

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF2016_Final /

Priority/Frequency: Major / Some users will encounter

Locale/System: English / Platforms All

Vote Count: 0

Listed in the version 2016.0.02.299200 Issues Fixed doc
Related Bugs:
CF-4138764 - Similar to


Two issues:
1) CAST(myBitColumn AS VARCHAR) returns YES/NO instead of 1/0
2) a) LOWER(CAST(myVarcharColumn AS VARCHAR)) returns Foo instead of foo; b) UPPER(CAST(myVarcharColumn AS VARCHAR)) returns Foo instead of FOO

First, define DSN and create table:
--------------------------------------------
myDSN = "";
queryExecute("CREATE TABLE myTable (myBitColumn bit NULL, myVarcharColumn varchar(3) NULL)", [], {datasource=myDSN});
queryExecute("INSERT INTO myTable VALUES (1, 'Foo')", [], {datasource=myDSN});

Repro for #1:
--------------------------------------------
q1 = queryExecute("SELECT CAST(myBitColumn AS VARCHAR) AS myVarcharColumn FROM myTable", [], {datasource=myDSN});
q2 = queryExecute("SELECT myBitColumn FROM myTable", [], {datasource=myDSN});
q3 = queryExecute("SELECT CAST(myBitColumn AS VARCHAR) AS myVarcharColumn FROM q2", [], {dbtype="query"});
writeOutput(q1.myVarcharColumn & ' ' & q3.myVarcharColumn);
//Actual result: 1 YES
//Expected result: 1 1

Repro for #2a:
--------------------------------------------
q3 = queryExecute("SELECT LOWER(CAST(myVarcharColumn AS VARCHAR)) AS myVarcharColumn FROM myTable", [], {datasource=myDSN});
q4 = queryExecute("SELECT myVarcharColumn FROM myTable", [], {datasource=myDSN});
q5 = queryExecute("SELECT LOWER(CAST(myVarcharColumn AS VARCHAR)) AS myVarcharColumn FROM q4", [], {dbtype="query"});
writeOutput(q3.myVarcharColumn & ' ' & q5.myVarcharColumn);
//Actual result: foo Foo
//Expected result: foo foo

Repro for #2b:
--------------------------------------------
Same as repro for #2a, except change "LOWER" to "UPPER"
//Actual result: FOO Foo
//Expected result: FOO FOO

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

Watson Bug ID:	4121934

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

Attachments:

Comments:

This is used for dynamically querying multiple columns for a matching string. Thus, the submitted string is lCase()'d and compared w/ LOWER(CAST(AnyColumnNameHere AS VARCHAR)). So CAST() and LOWER() are expected to work. Thanks!, -Aaron
Comment by External U.
4459 | February 25, 2016 02:46:35 PM GMT
* So CAST() and LOWER() and UPPER() are expected to work.
Comment by External U.
4460 | February 25, 2016 02:47:12 PM GMT
This issue is fixed now and the fix for this issue will be available as part of Update 2 of ColdFusion 2016. There are two issues reported in this bug: 1) CAST(myBitColumn AS VARCHAR) returns YES/NO instead of 1/0 2) a) LOWER(CAST(myVarcharColumn AS VARCHAR)) returns Foo instead of foo; b) UPPER(CAST(myVarcharColumn AS VARCHAR)) returns Foo instead of FOO We have taken care of both cases 2(a) and 2(b), but first issue is still not fixed. The recommended Java mapping for the JDBC BIT type is as a Java boolean. The current behavior is ideally right but we are converting BIT to Integer while dumping a query to maintain backward compatibility. We can do the same for QoQ as well but that would be a major change of behavior and is risky in a hot fix. Aaron, I have logged a separate bug #CF-4138764 to track the first issue and targeted it to next major ColdFusion release.
Comment by Nimit S.
4461 | April 12, 2016 02:08:09 AM GMT
test note
Comment by CFwatson U.
4462 | June 07, 2016 04:19:02 AM GMT
The fix for this bug is available as part of the early-access build for ColdFusion 2016 Update 2.
Comment by CFwatson U.
4463 | June 07, 2016 04:25:41 AM GMT
Hi Nimit, I've verified this (#2a and #2b) is fixed in CF2016 Update 3 (build 2016,0,03,300466). (didn't check Update 2, but assuming it's fixed there too) Regarding #1, I'll add a note on CF-4138764 (thanks for filing it!). Thanks! -Aaron
Comment by External U.
4464 | October 29, 2016 01:32:47 AM GMT