MySQL GROUP_CONCAT() Has Buggy Interaction With UNION Clauses

<!---
	First, let's populate the audit-log for testing. Here, we're doing to store the day
	of the week and the transaction ID that took place.
--->
<cfquery name="populate" datasource="testing">

	TRUNCATE TABLE audit_log;


	<cfset transactionIndex = 100000 />

	<!--- For each day of the week, add 1,000 transactions. --->
	<cfloop index="dayIndex" from="1" to="7" step="1">

		<cfloop index="i" from="1" to="1000" step="1">

			INSERT INTO audit_log
			(
				dayOfWeek,
				transactionID
			) VALUES (
				#dayIndex#,
				#( ++transactionIndex )#
			);

		</cfloop>

	</cfloop>

</cfquery>


<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->


<!---
	Now, let's report on the transactions that have taken place. Grouping the log by the
	day of the week, we're going to concatenate the transactionIDs into a grouped-list.
--->
<cfquery name="report" datasource="testing">
	SELECT
		dayOfWeek,

		<!--- Collapse the transaction IDs into a comma-delimited list. --->
		GROUP_CONCAT( transactionID ) AS transactionList
	FROM
		audit_log
	GROUP BY
		dayOfWeek
	ORDER BY
		dayOfWeek ASC
</cfquery>

<!--- Output report aggregates. In "theory", each day should have 1,000 items. --->
<cfoutput query="report">

	Day Of Week (#report.dayOfWeek#): #listLen( report.transactionList )#<br />

</cfoutput>


<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->
<br />
<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->


<!---
	This time, rather than grouping the entire record-set by day, we're going to select
	each aggregate individually by day of the week, then UNION these aggregates together.
	In theory, this should be the exact same result since we're still groupging all
	transactions by day of the week.
--->
<cfquery name="report" datasource="testing">
		(
			SELECT
				dayOfWeek,
				GROUP_CONCAT( transactionID ) AS transactionList
			FROM
				audit_log
			WHERE
				dayOfWeek = 1 <!--- Get only this day of the week. --->
			GROUP BY
				dayOfWeek
		)

	<!--- UNION the other 6 days of the week. --->
	<cfloop index="i" from="2" to="7" step="1">

		UNION ALL
			(
				SELECT
					dayOfWeek,
					GROUP_CONCAT( transactionID ) AS transactionList
				FROM
					audit_log
				WHERE
					dayOfWeek = #i# <!--- Get only this day of the week. --->
				GROUP BY
					dayOfWeek
			)

	</cfloop>

	ORDER BY
		dayOfWeek ASC
</cfquery>

<!--- Output report aggregates. --->
<cfoutput query="report">

	Day Of Week (#report.dayOfWeek#): #listLen( report.transactionList )#<br />

</cfoutput>


<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->
<br />
<!--- ----------------------------------------------------- --->
<!--- ----------------------------------------------------- --->


<!---
	As a final sanity check, let's look at just one of the groupings that we used in
	the previous exammple, to see how it behaves when we don't use a UNION clause.
--->
<cfquery name="report" datasource="testing">
	SELECT
		dayOfWeek,
		GROUP_CONCAT( transactionID ) AS transactionList
	FROM
		audit_log
	WHERE
		dayOfWeek = 1 <!--- Get only this day of the week. --->
	GROUP BY
		dayOfWeek
</cfquery>

<!--- Output report aggregates. --->
<cfoutput query="report">

	Day Of Week (#report.dayOfWeek#): #listLen( report.transactionList )#<br />

</cfoutput>