Bug Hunting Session
Bug 105075 - Firebird: Limit of query-GUI isn't supported
Summary: Firebird: Limit of query-GUI isn't supported
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha0+
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2017-01-03 18:42 UTC by Robert Großkopf
Modified: 2019-07-04 05:39 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Limit doesn't work with Firebird - ROWS isn't supported by GUI and HSQLDB (5.13 KB, application/vnd.oasis.opendocument.database)
2017-01-03 18:42 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2017-01-03 18:42:57 UTC
Created attachment 130120 [details]
Limit doesn't work with Firebird - ROWS isn't supported by GUI and HSQLDB

Open the attached database.
There are different Queries for the limit of a select.
Query_with_GUI_Limit is designed by the GUI but doesn't work.
Firebird doesn't know anything about the command LIMIT.

The other 3 queries 
(...First_Skip, ...ROWS, ...ROWS_TO) show the limit, but this won't be supported by the GUI.

Don't think we could fix this in an easy way. Firebird doesn't work with LIMIT and HSQLDB doesn't work with ROWS. So we should remove or disable "Limit" and "Query properties" from the query-design in Firebird.
Comment 1 Buovjaga 2017-01-05 09:03:52 UTC
Confirmed.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.4.0.0.alpha0+
Build ID: 1a58cdf8af1aba52ce0a376666dd7d742234d7cf
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on January 4th 2016
Comment 2 Commit Notification 2018-04-14 12:15:59 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f5a2fedaebcf71824ecef9b5e7683237646175b4

tdf#105075 Firebird: Support Limit of query_GUI

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 3 Xisco Faulí 2018-05-29 09:26:28 UTC
A polite ping to Tamas Bunth:
Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing?
Thanks
Comment 4 Xisco Faulí 2018-07-02 22:31:58 UTC
(In reply to Xisco Faulí from comment #3)
> A polite ping to Tamas Bunth:
> Is this bug fixed? if so, could you please close it as RESOLVED FIXED ?
> Otherwise, Could you please explain what's missing?
> Thanks

@Tamas, Could you please reply to the questions above ?
Comment 5 Drew Jensen 2018-07-03 15:15:53 UTC
(In reply to Xisco Faulí from comment #4)
> (In reply to Xisco Faulí from comment #3)
> > A polite ping to Tamas Bunth:
> > Is this bug fixed? if so, could you please close it as RESOLVED FIXED ?
> > Otherwise, Could you please explain what's missing?
> > Thanks
> 
> @Tamas, Could you please reply to the questions above ?

Could I inject here before Tamas. No, it is not fixed in an acceptable way.

The Base firebird parser recognizes the word limit, but the SQL select statement generated then can not be passed directly to the firebird engine because the engine does not recognize the word.

Only if the select statement is run by Base with escape processing enabled will the querydef generate a usable SQL STATEMENT for the firebird engine. Try running the SQL shown to the user and it is rejected.

If a user experienced with Firebird sat down and entered a query by hand and they limited the results in the proper way with a FIRST and SKIP the Base parser would throw an error and the only way to run the querydef is with SQL DIRECT (escape processing off) enabled.
Comment 6 Drew Jensen 2018-07-03 15:45:55 UTC
Also, the Firebird engine does recognize a one word shortcut like LIMIT and used at the back on the SELECT statement and that word is ROWS. 

SELECT DISTINCT "VName", "NName" FROM "Persons" LIMIT 4

in firebird would become

SELECT DISTINCT "VName", "NName" FROM "Persons" ROWS 4

and is the same as 

SELECT FIRST 4 DISTINCT "VName", "NName" FROM "Persons"

but in Base only first style is recognized by the designer

SELECT DISTINCT "VName", "NName" FROM "Persons" LIMIT 4 

which is then quietly translated to the proper syntax when escape processing is handled,

IMO the firebird sdbc should be using the word ROWS not LIMIT for the designer generated SQL STATEMENT.
Comment 7 Drew Jensen 2018-07-03 20:15:01 UTC
well, maybe I should reconsider that a bit.

I'm now looking at a connection in Base to other data types not just HSQL, text and csv and the query designer also uses the LIMIT syntax. 

If LIMIT is to be a common syntax across all the drivers for the designer then that's great and right now that works. It also means the query definitions with escape processing active work as is for LIMIT after a migration from HSQLdb.

So, in that case then close this issue. 

Then open an RFE to update to the SQL parser down the road to additionally recognize the string ROWS in place of limit for the firebird sdbc drivers.
Comment 8 QA Administrators 2019-07-04 02:47:09 UTC Comment hidden (obsolete)
Comment 9 Robert Großkopf 2019-07-04 05:39:08 UTC
We have two different behaviours now:
Start a query in GUI and set LIMIT in the GUI will work with internal Firebird.
Switching the query to direct SQL will throw an error.

Tested all with LO 6.3.0.0 beta2.
I would prefer this bug to set as FIXED, because it will work, if it isn't set to direct SQL.