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