Firebird Jaybird 2.2.10 / ColdFusion 2018 - String Length Bug

Question

I'm not sure if this is a ColdFusion 2018 bug, or if it is something in the way ColdFusion 2018 implements the Jaybird 2.2.10 driver for Firebird 2.5.

I have a table that which contains a column named "item_date". This column has the Firebird "date" data type. In ColdFusion 10, the query below shows the correct string length based on the month returned. However, in ColdFusion 2018, the string length is returning as 9 for all of them. It's almost like ColdFusion is assigning the result set a CHAR(9) data type rather than VARCHAR as it should be.

My example proof of concept code looks like this.

<cfquery name="test" datasource="#application.dsn#">
    SELECT 
        CASE EXTRACT (MONTH from item_date)
        WHEN 1 THEN 'January'
        WHEN 2 THEN 'February'
        WHEN 3 THEN 'March'
        WHEN 4 THEN 'April'
        WHEN 5 THEN 'May'
        WHEN 6 THEN 'June'
        WHEN 7 THEN 'July'
        WHEN 8 THEN 'August'
        WHEN 9 THEN 'September'
        WHEN 10 THEN 'October'
        WHEN 11 THEN 'November'
        WHEN 12 THEN 'December'
        END itemMonth
    FROM    MY_TABLE
</cfquery>

<cfloop query="test">
    #test.itemMonth# - Length: #Len(test.itemMonth)#<br />
</cfloop>

When this runs, one would expect the result to show

September - Length: 9
August - Length: 6
..and so on.

However, what it shows instead is a length of 9 for ALL of them. If the month is June, 4 characters, the result still shows 9. It's like ColdFusion or the Jaybird driver is adding extra whitespace to the end of the field, like it's being defined in the result as CHAR type for some reason.

This does NOT happen on ColdFusion 10 using the same Firebird 2.5 and Jaybird 2.2.10 driver.
This also does NOT happen in ColdFusion 2018 when using SQL Server instead of Firebird

This is quite a problem because it causes string comparisons to fails when checking for results which are equal to "string". For instance, if I pull a Query of Queries looking for where itemMonth = 'October' no results will return, because the result has 'October ' with 2 extra spaces.

Is this a bug in ColdFusion 2018? A bug in the Jaybird driver? Any help is greatly appreciated!

Answer 1

A string literal in Firebird is - unfortunately - a CHAR, not a VARCHAR. Values of type CHAR are padded with spaces up to the declared length. When using string literals in a CASE, the resulting datatype will have the length of the longest literal, which is September, so the type of the result is a CHAR(9), and the shorter values will all be padded with spaces up to a length 9.

I don't know ColdFusion, but it looks like ColdFusion 10 automatically trimmed values and is no longer doing this in ColdFusion 2018. You will need to trim manually - in your ColdFusion code or in in your query - to achieve the desired effect, or look for a setting that controls this behaviour.

As an aside, Jaybird 2.2.10 (March 2016) is not the latest 2.2, that is Jaybird 2.2.15 (April 2019). Jaybird 2.2 is end-of-life and will receive no more updates. The overall latest Jaybird at this time is 3.0.6 (May 2019).

ColdFusion fix

The fix in ColdFusion, as originally added to the question by Phil is:

To anyone who wants this to work as it always has, where ColdFusion trims the trailing spaces automatically, that is possible using the JVM argument below.

-Dcoldfusion.trim.dbresult=true

https://tracker.adobe.com/#/view/CF-4100830

According to that link, the behaviour was changed in ColdFusion 11 to no longer trim by default, and this property was introduced in ColdFusion 11 Update 7 to enable the old behaviour.