Download it now!
Bug 105112 - Support aggregate functions EVERY, ANY, SOME, COLLECT, FUSION, INTERSECTION
Summary: Support aggregate functions EVERY, ANY, SOME, COLLECT, FUSION, INTERSECTION
Status: NEW
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 enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2017-01-04 20:36 UTC by Robert Großkopf
Modified: 2018-01-03 21:19 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the query for editing and try to change to the named functions - all doesn't exist. (4.63 KB, application/vnd.oasis.opendocument.database)
2017-01-04 20:36 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-04 20:36:13 UTC
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
Comment 1 Buovjaga 2017-01-05 11:18:23 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 Tor Lillqvist 2018-01-02 19:38:27 UTC
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?
Comment 3 Robert Großkopf 2018-01-02 20:13:39 UTC
(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.
Comment 4 Tor Lillqvist 2018-01-02 21:18:01 UTC
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;
}
Comment 5 Tor Lillqvist 2018-01-02 21:28:00 UTC
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
Comment 6 Tor Lillqvist 2018-01-02 21:32:06 UTC
Except that the css::sdbc::XDatabaseMetaData interface is a published one, so it can't be changed. Sigh.
Comment 7 Tor Lillqvist 2018-01-02 22:08:41 UTC
Suggested patch at https://gerrit.libreoffice.org/47285/
Comment 8 Tor Lillqvist 2018-01-02 22:09:13 UTC
(Nah, ignore that mimer.se documentation, it doesn't mention these functions at all.)
Comment 9 Lionel Elie Mamane 2018-01-03 08:23:09 UTC
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".
Comment 10 Lionel Elie Mamane 2018-01-03 09:08:41 UTC
(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.
Comment 11 Lionel Elie Mamane 2018-01-03 09:14:22 UTC
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.
Comment 12 Tor Lillqvist 2018-01-03 10:05:18 UTC
> 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.)
Comment 13 Lionel Elie Mamane 2018-01-03 15:55:38 UTC
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.
Comment 14 Robert Großkopf 2018-01-03 16:03:46 UTC
(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.
Comment 15 Robert Großkopf 2018-01-03 16:23:03 UTC
(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.
Comment 16 Lionel Elie Mamane 2018-01-03 17:11:39 UTC
(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.