Monday, June 17, 2013

LIMIT improvements in LibreOffice 4.1

Some new things get into this version of LO.

1. The primary improvement is adding a combobox to the design view and so not needed to use SQL view to limit the number of resulting rows. This new edit is get place on the query design toolbar and in the Query Properties dialog (Edit -> Query Properties).

Place of the new toolbar item is on the rightmost side of the query toolbar.
New toolbar item to limit a query result
in query design view.

Query properties dialog has the same box as the query design toolbar. These two box are in sync.
New dialog to add query properties like limit and distinct values.

2. LO uses HSQLDB (version 1.8.0) as an embedded\default database so this is an commonly used driver for Base. However it has a bug which lead to an exception when user intend to run a query like "SELECT * FROM table LIMIT n". The problem is HSQLDB's parser does not handle "LIMIT" as an keyword and so in this case "LIMIT" parsed as a table alias and the following number as an unexpected token. Inside LO this bug is fixed by now.

An error box saying the number after the LIMIT is unexpected.
In older versions, HSQLDB throws an exception when
use LIMIT directly after from clause.

The rows are listed with expected number.
LO 4.1 doesn't throw the HSQLDB's exception and return
with the expected result.

Of course it also means LIMIT keyword can't use as a table alias anymore in LO as it was used before. These usage is stand only when use "Run SQL command directly" option. (LO has an own parser which notice that LIMIT is a keyword and so can't be a table alias, but with this option this syntax check can be avoided)

Limit used as an table alias in SQL statement and Base return with all elements of the table without problem.
Before, user was able to use LIMIT as a table alias.

An error box is opened which say that LIMIT is unexpected token.
LO 4.1 throws an exception, when use LIMIT as a name
(get from fixed HSQLDB)

3. This is my favorite. In SQL mode, in the SQL edit the LIMIT keyword was highlighted wrong as a single name (green colour) and not as a keyword (blue colour). The origin of this problem may be the same as in case of HSQLDB's bug. The LIMIT keyword got into the code later as other keywords and it was missed to add all of places where a keyword has to appear.

In the SQL edit the LIMIT word has green color.
Before, the LIMIT keyword was highlighted
with green color like a simple name.

In the SQL edit the LIMIT word has blue color just as the oder keywords like SELECT, FROM and so on.
In LO 4.1, the LIMIT keyword has the same highlighting as
other keywords (blue character color).

Thanks for Foundation for funding this work!