mysql query returns different number of records from coldfusion and navicat

[Endboss_ZA]

Hi

I'm hoping that someone can suggest a basic strategy to debug this.

I have a fairly large and complicated query that is executed by a function in a cfc.

It returns (for example) 100 rows.

If I use cfdump and then copy and paste the SQL of the query (with the variables, of course) into Navicat and execute exactly the same query on the same mySQL database, it returns 130 rows.

Same SQL string, same database, same data - the only difference is that in one instance Navicat submits the query and in the other, Coldfusion does.

Has anyone ever had anything like this happen before?

[Endboss_ZA · Accepted solution]

Ok I found my own bug. Of *course* the sql queries were not identical.. they could not possibly have been. My mistake was thinking that they were.

The problem was part of the WHERE clause:

AND orderid in (500,503,505)

In the coldfusion code this was

AND orderid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#lstOrderID#">)

which of course rendered in mySQL as AND orderid in ('500,503,505')

This was not immediately apparent as the cfdump returns this as AND orderid in (?) with the variable in the array below.