MySQL/MariaDB supports the following options in GROUP_CONCAT function: SEPARATOR and DISTINCT. For manual reference, see < https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat > LO Base will not accept/execute query that contains "SEPARATOR" or "DISTINCT" options in GROUP_CONCAT function. The back end MySQL database will, however, process these options as documented. Problem assumed to be with LibreOffice MySQL Native Connector for LibreOffice. FYI, JDBC driver from MySQL also failed to handle those options. DESIRED RESULT: GROUP_CONCAT function in LO Native Connector should not return errors when options SEPARATOR and DISTINCT are provided. Connector should handle syntax consistent with MySQL manual. Workaround, of course, is to create view in back-end MySQL database itself without using LibreOffice front end, so bug given 'minor' status. Bug encountered Windows 7/LO 4.3.0.4/MySQL Native Connector for LibreOffice 4.x 1.0.2 (May 02, 2014)
Testing the following command: SELECT species, GROUP_CONCAT(DISTINCT owner ORDER BY owner DESC SEPARATOR ' ') FROM pet GROUP BY species; +---------+----------------------------------------------------------------+ | species | GROUP_CONCAT(DISTINCT owner ORDER BY owner DESC SEPARATOR ' ') | +---------+----------------------------------------------------------------+ | bird | Gwen | | cat | Harold Gwen | | dog | Harold Diane Benny | | hamster | Diane | | snake | Benny | +---------+----------------------------------------------------------------+ 5 rows in set (0.00 sec) LibreOffice Versión: 4.2.4.2 Id. de compilación: 420m0(Build:2) with JDBC driver is OK, no errors found.
An error is returned, if I try this in the GUI, but when I switch of the design-mode and press "SQL" for direct sending this query to the database it works with my system as expected. Not all commands of all databases could be supported by the GUI. So this button to send the SQL-code directly has been created for special commands. I have tested with OpenSUSE 12.3 64bit rpm Linux. No native connector available, but JDBC for MariaDB. Works with LO 4.2.6.2 and 4.3.1.1.
Confirmed the GUI nit, that is understandable and focus of bug report is raising of error in raw SQL outside GUI. Reconfirmed that adding options DISTINCT or SEPARATOR (with string) on my system raises nondescript Syntax Error in SQL Statement, SQL Status: HY000, Error Code: 1000, and the usual complaint about syntax. Using MariaDB rather than MySQL (drop-in substitute). Copy-paste same command to Heidi/command line, runs fine. Same GROUP_CONCAT phrase runs fine after removing those options. My system Win7 32/LO 4.3.0.4/MariaDB 5.5.5/10.0.13 both JDBC and Native connectors.
This works for me too in Create SQL view and "run sql directly" mode Native mysql connector OSX 10.10 Version: 4.4.0.0.alpha0+ Build ID: d807cba9ee60cb1404b54addf9cd3e54de89f331
I used this query SELECT file_num, GROUP_CONCAT(DISTINCT owner1 ORDER BY owner DESC SEPARATOR ' ') FROM oldfmpro GROUP BY species; against one of my work dbs hosted on LAN mysql instance
The LO built-in SQL parser can unfortunately not reasonably be expected to understand every sql dialect and function set of every existing database implementation, hence the possibility to pass the instruction directly to the backend. There is also the real possibility that connector driver code over/under-states what it is truly capable of, and then expect LO to be able to deal with any inconsistencies, which IMO is unreasonable. For me, this current report is worksforme