Created attachment 130158 [details] Open the query for editing and try to change to the named functions - all doesn't exist. Many function appear in the GUI when creating a query for Firebird. The following functions won't work with Firebird: EVERY, ANY, SOME, COLLECT, FUSION, INTERSECTION Try the attached database and the query. Change to every of the function. start the query. Function is unknown. ANY and SOME are used in Firebird for subqueries only and have other sense. All other functions are unknown. All these functions shouldn't appear when creating a query with Firebird with the GUI. Tested all these with Version: 5.4.0.0.alpha0+ Build ID: 2a4cd80abcf9e515d1ce3b3a944b573bdc42bff2 CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-22_00:18:04 Locale: de-DE (de_DE.UTF-8); Calc: group
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
I guess those function aren't available in Firebird. Are they in HSQLDB? If not, I would say it is not a bug that they aren't in Firebird either. Firebird is supposed to be a replacement for HSQLDB to the extent possible, not for some external maximally-featured database accessed through Base. The GUI to create queries probably shows such functions because *some* external databases that Base might connect to will have them?
(In reply to Tor Lillqvist from comment #2) > I guess those function aren't available in Firebird. Are they in HSQLDB? If > not, I would say it is not a bug that they aren't in Firebird either. > Firebird is supposed to be a replacement for HSQLDB to the extent possible, > not for some external maximally-featured database accessed through Base. > > The GUI to create queries probably shows such functions because *some* > external databases that Base might connect to will have them? No, the GUI shows only the functions, which are available in databases. If I open, for example, a Base-file connected to dBase there are no functions available. If I connect it to a Calc-table there are some functions. The following function are the functions for internal HSQLDB: COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP Now look on the GUI for Firebird-databases in Base: EVERY, ANY, SOME, COLLECT, FUSION, INTERSECTION aren't working with Firebird in a normal way. Don't know where ANY, COLLECT, FUSION, INTERSECTION had come from. Not from HSQLDB. Must be coded in the driver, which connects to Firebird.
There is an interesting comment in connectivity/source/drivers/postgresql/pq_databasemetadata.cxx (of all places): sal_Bool DatabaseMetaData::supportsCoreSQLGrammar( ) { // LEM: jdbc driver says not, although the comments in it seem old // fdo#45249 Base query design won't use any aggregate function // (except COUNT(*) unless we say yes, so say yes. // Actually, Base assumes *also* support for aggregate functions "collect, fusion, intersection" // as soon as supportsCoreSQLGrammar() returns true. // Those are *not* Core SQL, though. They are in optional feature S271 "Basic multiset support" return true; }
That supportsCoreSQLGrammar() function for Firebird returns true, too. So I guess what could be done would be to fix Base so that it doesn't assume that the functions that aren't part of Core SQL are present even if supportsCoreSQLGrammar() returns true. But then we would need to add another function to the database drivers to tell whether the EVERY, ANY etc ones are present. Why can't the Base - connectivity Api be such that Base would ask separately for each function whether the driver database supports it? I can't find any normative specification of "Core SQL", but this seems like a good summary: http://developer.mimer.se/standard/features/core-sql-features.tml
Except that the css::sdbc::XDatabaseMetaData interface is a published one, so it can't be changed. Sigh.
Suggested patch at https://gerrit.libreoffice.org/47285/
(Nah, ignore that mimer.se documentation, it doesn't mention these functions at all.)
supportsCoreSQL in the documentation refers to "the ODBC Core SQL grammar", but the ODBC documentation (https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-minimum-grammar) in my understanding just refers back to the 1992 version of the ISO/IEC 9075 standard (the official standard is "pay to get it", but working drafts "can be found") for definition of "Core SQL", which means "conformant to all non-optional requirements of the standard".
(In reply to robert from comment #3) > (In reply to Tor Lillqvist from comment #2) >> I guess those function aren't available in Firebird. Are they in HSQLDB? If >> not, I would say it is not a bug that they aren't in Firebird either. >> Firebird is supposed to be a replacement for HSQLDB to the extent possible, >> not for some external maximally-featured database accessed through Base. >> The GUI to create queries probably shows such functions because *some* >> external databases that Base might connect to will have them? > No, the GUI shows only the functions, which are available in databases. That is not true, at least not to the granularity of each function, function by function. > If I open, for example, a Base-file connected to dBase there are no functions > available. Yes, *no* functions is a supported state. > If I connect it to a Calc-table there are some functions. In my testing, in LibreOffice 5.4.3.2 (TDF deb amd64 build) there is _no_ function available. > The following function are the functions for internal HSQLDB: > COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | > STDDEV_POP > | STDDEV_SAMP In my testing, in LibreOffice 5.4.3.2 (TDF deb amd64 build), the GUI proposes: Average, Count, Maximum, Minimum, Sum, Every, Any, Some, STDDEV_POP, STDDEV_SAMP, VAR_SAMP, VAR_POP, Collect, Fusion, Intersection, Group And Collect, Fusion, Intersection and Group DO NOT WORK in embedded HSQLDB. Meaning, we have the _same_ situation with HSQLDB than you describe with Firebird. You say it is a "regression" in the switch from HSQLDB from Firebird, in my testing it is not. > Don't know where ANY, COLLECT,FUSION, INTERSECTION had come from. > Not from HSQLDB. Must be coded in the driver, which connects to Firebird. No, they are proposed with _all_ databases that are tagged to support _any_ aggregate function. The LibreOffice GUI either displays _NO_ aggregate function or displays _this_ _whole_ _set_, no more no less.
I reproduced my tests in version 5.2.7.2, too. No aggregate function when connecting to a Calc sheet, same set (including COLLECT, FUSION, INTERSECTION) when connecting to embedded HSQLDB. Please give details on where/how you got the LibreOffice GUI to behave differently, that is: * propose some aggregate functions for HSQLDB, but not COLLECT, FUSION, INTERSECTION * propose any aggregate function for Calc exact version, attach example odb files (and ods file for Calc), ... In the meantime (until that different behaviour is shown), my opinion is that this is at most an enhancement request, not a bug.
> Collect, Fusion, Intersection and Group DO NOT WORK in embedded HSQLDB Oh, interesting! So *this* bug is then not really a blocker for bug #51780 ? (And https://gerrit.libreoffice.org/#/c/47285/ can be abandoned, or alternatively, should be changed to skip those for HSQLDB, too.)
To be complete, there are some aggregate functions that are supported by HSQLDB 1.8, but not by Firebird, namely (according to the documentation): SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP As much as we would like to have a feature-by-feature exact match, it will not happen unless we add these to Firebird upstream (or ship them as UDF (user-defined function), but if we do so, why not contribute them upstream?). ANY is not supported by HSQLDB according the documentation, and neither are COLLECT, FUSION, INTERSECTION.
(In reply to Lionel Elie Mamane from comment #13) > > ANY is not supported by HSQLDB according the documentation, and neither are > COLLECT, FUSION, INTERSECTION. You are right. Have written the same in Base-Handbook and ... forgotten. Seems this functions are standard of ORACLE. So this bug doesn't block Firebird, but should be a feature-request for both: Firebird and HSQLDB The listbox should only offer the possibilities of the database.
(In reply to Lionel Elie Mamane from comment #13) > To be complete, there are some aggregate functions that are supported by > HSQLDB 1.8, but not by Firebird, namely (according to the documentation): > > SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP Please have a look: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s05.html VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP I could find there. SOME I could find in description for Firebird 2.5: "Description: The ANY (or SOME ) predicate now allows a NULL as the test value. Notice that this brings no practical benefits. In particular, a NULL test value will not be considered equal to a NULL in the subquery result set." But SOME seems to be used there in other sense - so no aggregate function.
(In reply to robert from comment #15) > (In reply to Lionel Elie Mamane from comment #13) > > To be complete, there are some aggregate functions that are supported by > > HSQLDB 1.8, but not by Firebird, namely (according to the documentation): > > > > SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP > > Please have a look: > https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/ > bk02ch09s05.html > VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP I could find there. Ah yes, I missed it.
(In reply to How can I remove my account? from comment #6) > Except that the css::sdbc::XDatabaseMetaData interface is a published one, > so it can't be changed. Sigh. Perhaps we may add a function isFunctionSupported(<functionName>) in XDatabaseMetaData2 (which isn't published if I don't misunderstand https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/sdbc/XDatabaseMetaData2.idl?r=5687eba4) ? I'm not an expert but I imagine this plan once the function is added in XDatabaseMetaData2: - every DB driver should include XDatabaseMetaData2 in addition to XDatabaseMetaData - implements isFunctionSupported - use of isFunctionSupported is it wrong? naive? incomplete?
(In reply to Julien Nabet from comment #17) > Perhaps we may add a function isFunctionSupported(<functionName>) in > XDatabaseMetaData2 (which isn't published if I don't misunderstand > https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/sdbc/ > XDatabaseMetaData2.idl?r=5687eba4) ? Yes it's not published; but even if it were, we could introduce XDatabaseMetaData3, and do it there. There's no problem in adding new derived interfaces. > I'm not an expert but I imagine this plan once the function is added in > XDatabaseMetaData2: > - every DB driver should include XDatabaseMetaData2 in addition to > XDatabaseMetaData > - implements isFunctionSupported > - use of isFunctionSupported It could be tested, if the driver supports the interface, in the usual way; and *if it does*, fine-tune the existing behavior accordingly. It's a reasonable plan, please feel free to work on that, if you feel line that.
(In reply to Mike Kaganski from comment #18) > ... > It could be tested, if the driver supports the interface, in the usual way; > and *if it does*, fine-tune the existing behavior accordingly. It's a > reasonable plan, please feel free to work on that, if you feel line that. Thank you for the feedback, I've submitted this patch https://gerrit.libreoffice.org/c/core/+/173332 to start this part.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/afcfb89e3901c303f0fb104a9f91193052f5595b Related tdf#105112: use XDatabaseMetadata3 in Firebird + implement getFunctions It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I like to know the target of commits even if they are "only" related, so adding it back.
Reading about getFunctions, it seems it retrieves user defined functions (at least for Firebird). So I thought about doing a union between the user functions + functions we need (AVG, COUNT, etc.) It doesn't fully respect JDBC way but JDBC doesn't seem to provide an API to know if these kind of "system" functions are available or not (unless I missed it). First I've submitted on gerrit a patch for fix the FB request where there was missing spaces and quotes, see https://gerrit.libreoffice.org/c/core/+/176669
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f59ca5c9f9dfe30d755567a74806c42b1f795892 Related tdf#105112: FB fix request for getFunctions It will be available in 25.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Rethinking about this, doing an UNION means: - we don't respect getFunctions from JDBC (not very clean but perhaps we may consider this ok if there wasn't the second point below) - it can work with Firebird, HSQLDB but what do put in the UNION with ODBC and JDBC since these are just middle men and we don't know what's the real database used I also thought about testing each keyword with a select. If it gives an exception, the DB doesn't know the keyword. The pb is there's no generic SQL to make these tests so again it can't work with ODBC and JDBC. In brief, the commits I pushed just allowed to synchro LO interface with last JDBC standards + begin to implement the added functions for Firebird. But it won't help for this bugtracker and have no idea what to do here. (and so I think we should remove the target 25.2.0 in Whiteboard)
(In reply to Julien Nabet from comment #24) > In brief, the commits I pushed just allowed to synchro LO interface with > last JDBC standards + begin to implement the added functions for Firebird. > But it won't help for this bugtracker and have no idea what to do here. > > (and so I think we should remove the target 25.2.0 in Whiteboard) But there is no harm in them being in Whiteboard.