Skip to main content
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Matt Gifford
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with: Matt Gifford ( @coldfumonkeh )

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

Published in ,

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">
		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

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

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

		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. --->

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



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):

Using bit values in a COALESCE() statement in MySQL results in a binary values being returned.

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.

Reader Comments

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel