#683 listTickets.zsql produces invalid SQL for MySQL

[Basil Hussain]

I downloaded and tried to install the latest 2.1.1 release from SVN. It would fail to install completely, producing an error message when adding the MailManager product instance into Zope. The error it failed with was:

Traceback (innermost last):
Module ZPublisher.Publish, line 115, in publish
Module ZPublisher.mapply, line 88, in mapply
Module ZPublisher.Publish, line 41, in call_object
Module Products.MailManager.MailManager, line 279, in manage_addMailManager
Module OFS.ObjectManager, line 333, in _setObject
Module zope.event, line 23, in notify
Module zope.app.event.dispatching, line 66, in dispatch
Module zope.component, line 181, in subscribers
Module zope.component.site, line 89, in subscribers
Module zope.interface.adapter, line 481, in subscribers
Module zope.app.event.objectevent, line 192, in objectEventNotify
Module zope.component, line 181, in subscribers
Module zope.component.site, line 89, in subscribers
Module zope.interface.adapter, line 481, in subscribers
Module OFS.subscribers, line 114, in dispatchObjectMovedEvent
Module OFS.subscribers, line 137, in callManageAfterAdd
Module Products.MailManager.MailManager, line 489, in manage_afterAdd
Module Products.MailManager.migrations, line 134, in migrate
Module Products.MailManager.migrations.v2_1, line 530, in migrate_v2_1_15_v2_1_16
Module Products.MailManager.sql, line 161, in __call__
Module Shared.DC.ZRDB.DA, line 454, in __call__
- <FSZSQLWrapper at /mailmanager/sql/listTickets>
Module Products.ZMySQLDA.db, line 306, in query
ProgrammingError: (1064, "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 'OFFSET 0' at line 49")

I tracked it down to the fact that when the listTickets SQL method is called with the 'sqv_nolimit' parameter set to true (which the migrate_v2_1_15_v2_1_16 method does), it was indeed producing an invalid query. The query it was attempting to run was this (as logged by MySQL server):

SELECT mm_ticket.*, state AS full_state
FROM mm_ticket
INNER JOIN mm_ticket_index ON mm_ticket.id = mm_ticket_index.id
OFFSET 0

Unlike PostgreSQL, in MySQL, an OFFSET clause cannot be used without an accompanying LIMIT clause (in effect, OFFSET is merely an argument to LIMIT).

The solution I found was to remove the "OFFSET 0" part entirely. In MySQL, OFFSET is optional, and specifying an offset of zero is the same as not specifying one at all. I only made this change for MySQL, as I believe OFFSET to always be required in Postgres.

The relevant section of listTickets.zsql now reads:

<dtml-if sqv_offset>
<dtml-if expr="sql_database == 'postgres'">
OFFSET <dtml-var sqv_offset>
<dtml-elif expr="sql_database == 'oracle'">

<dtml-elif expr="sql_database == 'mysql'">
OFFSET <dtml-var sqv_offset>
</dtml-if>
<dtml-else>
<dtml-if expr="sql_database == 'postgres'">
OFFSET 0
<dtml-elif expr="sql_database == 'oracle'">

<dtml-elif expr="sql_database == 'mysql'">

</dtml-if>
</dtml-if>