Using Bit Values In COALESCE() In MySQL Results In Binary Values

Every now and then, this one throws me through a loop: if you use a COALESCE() statement in MySQL, and one of the values is a BIT, MySQL returns a binary value, not a numeric value. In ColdFusion, this causes a problem because ColdFusion sees the resultant value as a Byte Array (like a Blob); as such, you can't [easily] compare it to a simple value like a string or number.

To see this in action, I've creates a SQL statement with a number of COALESCE() calls that use bit-literal syntax. I'm using bit-literals for the sake of simplicity; but, the same behavior is expressed if you are using COALESCE() across a set of table columns and one of the column-types is a BIT:

<!--- Here, we're mixing BIT and NON-BIT values in COALESCE() statements. --->
<cfquery name="test" datasource="testing">
	SELECT
		COALESCE( 1, b'0' ) AS valueA,
		COALESCE( NULL, b'00001110' ) AS valueB,
		COALESCE( b'0', b'1' ) AS valueC,

		-- Cast the value to an explicitly numeric type.
		CAST( COALESCE( b'11', b'0001' ) AS UNSIGNED ) AS valueD
	;
</cfquery>

<cfdump var="#test#" label="COALESCE() Results" />
<br />


<!--- Try to compare the database results to simple values in ColdFusion. --->
<cfoutput>
	<cftry>

		ValueA eq 1: #( test.valueA eq 1 )#<br />
		ValueB eq 14: #( test.valueB eq 14 )#<br />
		ValueC eq 0: #( test.valueC eq 0 )#<br />

		<!--- Catch any errors related to data-type comparisons. --->
		<cfcatch>

			<strong>Error:</strong> #htmlEditFormat( cfcatch.message )#

		</cfcatch>

	</cftry>
</cfoutput>

As you can see, in some cases, the bit-literal value is the selected value; but, in other cases, the non-bit value is returned. However, when we CFDump out the results, all of the returned values are binary (unless explicitly cast):

And, the value comparisons that we do after the CFDump result in the following ColdFusion error:

Error: ByteArray objects cannot be converted to strings.

Furthermore, the value of the Byte Array seems to be inconsistent. If you look at the binary value that was returned for "1", it's "49", which is the ASCII values for character "1". However, when we deal directly with bit-literals, we don't get the ASCII-equivalent, we get the base-10 value of the bits. This adds a further layer of complexity when it comes to trying to consume such a value in ColdFusion.

To get around this, just make sure that you never mix a BIT value (literal or column-type) in a COALESCE() call. Or, if you absolutely need to, just be sure to explicitly CAST() or CONVERT() the value to something numeric (ex, UNSIGNED INTEGER) before you return it to a ColdFusion context.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/2871