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!

Nickell

For what it's worth, I can confirm that it works as expected on my ColdFusion 2018 server. – Vampire

Just out of morbid curiosity, what happens when you try to trim() the string? – Pepi

@JamesAMohler it may do! at least in Firebird 2.1.7 calling Trim function converts CHAR to VARCHAR. On the contrary, typecast without Trim would not do - this query returns 9 not 7: select Char_length( Cast( DECODE( EXTRACT (MONTH from current_date), 8, 'August', 9, 'September', 10, 'October') as Varchar(20) )) from rdb$database – Consumer

Seems related - ColdFusion trimming question of 2017 https://mcmap.net/q/2034225/-should-i-trim-values-in-sql-or-coldfusion – Consumer

@Vampire - are you using Firebird 2.5 as well with the JayBird 2.2.10 driver? – Nickell

@Nickell no. As you have probably gathered from Mark Rotteveel's answer, I never would have been able to re-produce the error without them anyhow. – Vampire

Consider posting an answer with your own solution. Editing the solution into your question is not how Stack Overflow is supposed to work. If you don't want to post your own answer, would you mind if I move that part of your question to my answer? – Mauri

@MarkRotteveel, no that is perfect please do. Your answer is what lead me to find the solution, so I didn't want to remove yours as accepted. Please move my addition to your answer too. – Nickell

@Nickell Thanks, I have made these changes. – Mauri

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.

Mauri

Rottevell - Thank you for your help! That explains it. – Nickell