cf10 issue with cfquery maxrows

Question

I recently moved an application from cf9 to cf10

When running a query that has maxrows set, I receive the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1000' at line 1

The query runs fine on cf9 and cf8.

Doing a bit of playing around, it looks like cf10 is prepending the query with OPTION SQL_SELECT_LIMIT=1000; but mySQL doesn't recognise it. If I take the maxrows setting out of the query it runs fine.

Also worth noting, the query has two outer joins.

Did anyone else experience similar issues when moving to cf10?

Thanks in advance for any advice

Here is the full query

<cfquery name="details" datasource="#Application.ds#" maxrows="#arguments.maxrows#">
          SELECT b.booking_id,
                    DATEDIFF(b.check_out,b.check_in) as nights,
                    b.package_id,
                    b.beds_cot,
                    b.date_booked,
                    b.beds_king,
                    b.status,
                    b.tstamp as booking_tstamp,
                    g.ext_ref_id as guest_ext_ref_id,
                    g.title,
                    g.first_name,
                    g.surname,
                    g.full_name,
                    g.tstamp as guest_tstamp,
                    r.room_id,
                    r.ext_ref_id as room_ext_ref_id,
                    r.name as room_name,
                    r.description as room_description,
                    p.package_id,
                    p.ext_ref_id as package_ext_ref_id,
                    p.name as package_name,
                    p.description as package_description,
                    p.date_start as pacakge_date_start,
                    p.date_end as package_date_end

          FROM guest_booking as b 
          JOIN guest as g
          LEFT JOIN room as r ON b.room_id = r.room_id
          LEFT JOIN packages as p on b.package_id = p.package_id

          WHERE b.provider_id = #arguments.provider_id#
            and b.guest_id = g.guest_id
          <cfif isdefined("arguments.status")>
              and b.status = #arguments.status#
          </cfif>
          <cfif isdefined("arguments.booking_id")>
              and b.booking_id = #arguments.booking_id#
          </cfif>

          ORDER BY #arguments.order_by#
        </cfquery>

Answer 1

Not sure why that is happening but you may find the mySQL LIMIT clause as an alternative. More here: http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 2

Maxrows has always been something I'd avoid.

I'm not sure how ColdFusion handles this, but I remember hearing (at least when CF7 was around) that CF actually gets a larger data-set, then splits it at the CF server level and NOT at the SQL database level. As you can imagine, that may have an unwanted performance hit,

I am not sure what your exact problem is (sounds like a bug to me), but I would strongly recommend swapping out the maxrows attribute for a combination of LIMIT and OFFSET clauses in your SQL (cfquery).

The benefit, is that this is standard SQL and may actually run faster than CF maxrows anyway, whilst also eliminating your problem.