MySQL Bugs: #13686: 1064 - You have an error in your SQL syntax; check the manual that corresponds

Bug #13686	1064 - You have an error in your SQL syntax; check the manual that corresponds
Submitted:	2 Oct 2005 4:19	Modified:	2 Oct 2005 5:00
Reporter:	Cash Pawley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13-standard	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I am using a program called oscommerce (http://oscommerce.com) as a shopping cart, but am receiving the following error for several areas of the script (in the admin functions area) such as 'Specials', 'Manufacturers', (under the heading 'Catalog') and ('orders' under the heading 'Customers').  I cannot find the problem with the SQL DB.  Can anyone assist?

The error code: 
=========================================
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 '-20, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC limit -20, 20

[TEP STOP]
=========================================

*** This particular error code came when I pressed the 'ORDERS' link under 'CUSTOMERS'.

Thanks,

Cash Pawley

How to repeat:
I am using a program called oscommerce (http://oscommerce.com) as a shopping cart, but am receiving the following error for several areas of the script (in the admin functions area) such as 'Specials', 'Manufacturers', (under the heading 'Catalog') and ('orders' under the heading 'Customers').

*** This particular error code came when I pressed the 'ORDERS' link under 'CUSTOMERS'.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

The problem is with MySQL... Not the program I am using (according to the error message) !!

I have checked the program on another server and it is working fine.. that is why I am trying to find out the problem with MySQL on this server.

Please, reread the manual (http://dev.mysql.com/doc/mysql/en/select.html):

"The LIMIT clause can be used to constrain the number of rows returned by the SELECT  statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):"

That words about non-negative constants... If oscommerce (good product, really) people do not read the manuals (of the database server they use) regularly - bad for them. Just inform them about the problem in their code.