Issue 20306 - SQL function LIMIT fails when "Analyze SQL command" is set to YES
Summary: SQL function LIMIT fails when "Analyze SQL command" is set to YES
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 RC5
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: 3.4.0
Assignee: marc.neumann
QA Contact: issues@dba
Depends on:
Reported: 2003-09-29 12:36 UTC by phillg
Modified: 2011-03-25 07:56 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description phillg 2003-09-29 12:36:16 UTC
When setting up a form to use the data as an SQL command you can use the
function LIMIT providing the option "Analyze SQL command" is set to NO, e.g.
select * from Ops where LEFT != 1 limit 0, 25
to get the first 25 rows.

If you set the option to YES you get three error messages displayed, all with
SQL Status: HY000 and Error Code: 1000.  The errors are:
1.  Syntax error in SQL expression
2.  Your SQL string
3.  parse error, expecting `$'

This means that the limit function cannot be used with Paramater Name
Substitution which is a big limitation when dealing with long sub forms.
Comment 1 phillg 2003-09-29 13:58:30 UTC
I've just been playing around with some test cases and it seems that
OOo does not like the LIMIT function being anywhere other than at the
very end of the SQL command.  Could this be why it is failing why PNS
is involved, it has to do something after it reaches the LIMIT
function.  I hope this sheds some more light on the problem.
Comment 2 Frank Schönheit 2003-10-13 09:16:26 UTC
LIMIT isn't SQL92, is it?
We would need to extend our parser to recognize LIMIT ...
I target this for LATER for now, since it doesn't fit into what has
been proposed as product concept for OOo 2.0 
Comment 3 hans_werner67 2004-02-02 12:55:16 UTC
change subcomponent to 'none'
Comment 4 skiani 2008-01-07 17:43:42 UTC
For Postgres you need LIMIT and OFFSET. E.g. 
SELECT * FROM "public"."dbtable" LIMIT 20 OFFSET 10000

Meaning give me the 20 records starting at record number 10000. Very important
feature if you are doing anything real (i.e. big databases).
Comment 5 ocke.janssen 2011-01-04 12:11:07 UTC
Fixed in cws dba34c

Comment 6 ocke.janssen 2011-01-14 08:36:26 UTC
Please verify. Thanks.


SELECT * FROM `testdb`.`employees` AS `employees` LIMIT 10 OFFSET 3

now works
Comment 7 marc.neumann 2011-02-21 09:41:06 UTC
However it's still not possible to switch to the graphic design. The LIMIT and
OFFSET keyword can only be insert into the SQL view.

If someone wants it also in the graphic view please write a new issue.

verified in CWS dba34c

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
Comment 8 r4zoli 2011-03-25 07:56:40 UTC
I checked in DEV300m104 where cws dba34c was integrated.
Works correctly in mysql 5.1 with native connection.

The queries with aliases works correctly:
SELECT * FROM "table_name" "table_name" LIMIT 10 OFFSET 3
SELECT * FROM "table_name" AS "table_name" LIMIT 10 OFFSET 3

Only a remark, to avoid submitting bug later:

SELECT * FROM "table_name" LIMIT 10 OFFSET 3
gives error in odb file with built-in hsqldb 1.8:

The data content could not be loaded.
SQL Status: 37000
Error code: -11
Unexpected token: 10 in statement [SELECT * FROM "PRo" LIMIT 10 OFFSET 3]

I get same error when run query in  database manager, which was connected to external hsqldb server,  this shows that the error not in OOo, it is a database engine problem.