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.