Aggregate Bug? with ColdFusion (8.0.1) Query of Queries

Posted At : 21 July 2008 11:18 | Posted By : Dan Lancelot
Related Categories: ColdFusion

I've been having the following issue with CF8 Query of Queries on ColdFusion 8.0.2:

Iniital query resultset:

attraction_latitudeattraction_longitude
51.4808-0.155075
51.564617-0.092513
51.558144-0.316379

This queryresultset (other fields exist which have been omitted) is passed in to the following cfquery as arguments.attractions

<cfquery name="localvars.bounds" dbtype="query">
select
      min( attraction_latitude ) as min_latitude, max( attraction_latitude ) as max_latitude,
      min( attraction_longitude ) as min_longitude, max( attraction_longitude ) as max_longitude
      from arguments.attractions
</cfquery>

Aggregate Resultset:

MAX_LATITUDEMAX_LONGITUDEMIN_LATITUDEMIN_LONGITUDE
51.5646171574.9E-32451.4808006287-0.316379010677

Eh?

So Max Latitude, Min Latitude and Min Longitude are all calculated correctly - but Max longitude isn't - despite being based on exactly the same data as min longitude?

I can get round it in this particular case by repeating the initial queries against the database (which as expected works correctly).

This certainly looks likes a bug to me, unless I have overlooked something...

Comments (6) | Print | Send | del.icio.us | Linking Blogs | 5637 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
Big Mad Kev's Gravatar We've had loads of issues trying to do some stuff with queries of queries in CF8 and I think they broke it ;)

Off the top of my head I can't remember the exact issue, but certainly sounds similar to this ;)
# Posted By Big Mad Kev | 21/07/08 12:02
duncan's Gravatar This is broken in CF7 too.

&lt;cfset test = QueryNew("testvalue", "Double")&gt;
&lt;cfset rows = 10&gt;   &lt;!--- how many rows do we want to test with? ---&gt;
&lt;cfset QueryAddRow(test, rows)&gt;

&lt;cfloop index="i" from="1" to="#rows#"&gt;
   &lt;cfset QuerySetCell(test, "testvalue", RandRange(-10, -1), i)&gt;
&lt;/cfloop&gt;

&lt;cfdump var="#test#"&gt;

&lt;cfquery name="testMax" dbtype="query"&gt;
   SELECT MIN(testvalue) AS theMin, MAX(testvalue) AS theMax
   FROM test
&lt;/cfquery&gt;

&lt;cfdump var="#testMax#"&gt;

If I set the values to only be negative numbers, the MAX doesn't work. If I change it to include negative and positives (change the upper limit on the RandRange to a positive), it works fine. Oddly, this is only if I specify the column type as Decimal. If I change it to Integer, it's ok (even though I'm only assigning integers anyway). And I think I've discovered another bug, in RandRange. More on that later perhaps ;-)
# Posted By duncan | 22/07/08 11:50
duncan's Gravatar ignore the code in the above comment, I assumed I'd need to convert my tags.

<cfset test = QueryNew("testvalue", "Decimal")>
<cfset rows = 10>   <!--- how many rows do we want to test with? --->
<cfset QueryAddRow(test, rows)>

<cfloop index="i" from="1" to="#rows#">
   <cfset QuerySetCell(test, "testvalue", RandRange(-10, -1), i)>
</cfloop>

<cfdump var="#test#">

<cfquery name="testMax" dbtype="query">
   SELECT MIN(testvalue) AS theMin, MAX(testvalue) AS theMax
   FROM test
</cfquery>

<cfdump var="#testMax#">
# Posted By duncan | 22/07/08 11:52
duncan's Gravatar "this is only if I specify the column type as Decimal." - I should have said Double. I think both Integer and Decimal were ok (although I haven't re-tested my code), at least on CF 7.
# Posted By duncan | 22/07/08 18:40
duncan's Gravatar The RandRange bug I noticed; let me know if you see the same effects:
http://duncan99.wordpress.com/2008/07/22/coldfusio...
# Posted By duncan | 22/07/08 19:28
duncan's Gravatar Dan, can you replace the LyleCaptcha with comment moderation or something more effective? I've been noticing a lot of spam coming through lately on many CF blogs using captchas, not much fun for anyone subscribed to the post, even less fun for the blog owners.
# Posted By duncan | 11/11/08 08:37
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is hosted by DotLance Ltd.