Download it now!
Bug 61831 - QUERY support for "SELECT TOP n", "ROWID", ... ways of limiting the number of results
Summary: QUERY support for "SELECT TOP n", "ROWID", ... ways of limiting the number of...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.0.alpha0+ Master
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2013-03-05 05:22 UTC by Lionel Elie Mamane
Modified: 2017-11-05 23:30 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2013-03-05 05:22:24 UTC
LibreOffice now has support for the (non-ISO-SQL-standard) feature "please return at most n results" in a query.

For now, it only supports the SQL syntax (the most common one):
  SELECT foo FROM bar LIMIT n
But Microsoft Jet uses this syntax:
  SELECT TOP n FROM bar
And OracleDB something like this:
  SELECT foo FROM bar WHERE rowid<n

As an improvement, it would be nice to support the different syntaxes. My suggestion would be to add an "advanced" setting (in edit/database/advanced settings). Like we now have a choice "Comparison of Boolean values" that can be set to "Default", "SQL", "Mixed" or "MS Access", we could there have a setting "return max n rows syntax", whose choices would be:

 - LIMIT n
 - TOP n
 - rowid < n


Then, the code that creates an SQL string from SQL design mode can take this setting in account to produce the right kind of syntax.

SDBC connectors that know to which dbms they connect to (such as e.g. the MySQL, PostgreSQL, MS Access and embedded HSQLDB one, but not ODBC or JDBC) can then lock the value of this setting to the right value (like the postgresql sdbc connector now locks the value of "Use ODBC conformant date/time literal" to false, since that's what PostgreSQL requires. (And then the setting does not even appear in the dialog, but is just set under the hood.)
Comment 1 Alex Thurgood 2015-01-03 17:41:27 UTC Comment hidden (no-value)