Apache OpenOffice (AOO) Bugzilla – Issue 20306
SQL function LIMIT fails when "Analyze SQL command" is set to YES
Last modified: 2011-03-25 07:56:40 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.
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.
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
change subcomponent to 'none'
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).
Fixed in cws dba34c
Please verify. Thanks. repro: SELECT * FROM `testdb`.`employees` AS `employees` LIMIT 10 OFFSET 3 now works
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: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300/dba34c
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 1.8.0.10 server, this shows that the error not in OOo, it is a database engine problem.