Change of behaviour of LibreOffice Base for nested SQL query

[SwissBear]

Hi,
I have been using LibreOffice in order to make requests to a MySQL database for quite a while. The following nested SQL query kept producing correct results:

SELECT * FROM dailydata AS a, (SELECT symbol, MAX(date) as MaxDate FROM
dailydata GROUP BY symbol) as b WHERE a.symbol = b.symbol AND
a.date = b.MaxDate

After upgrading my Linux workstation for Fedora 29 and LibreOffice to 6.1.2.1, the very same query produces an error message “The data content could not be loaded”.

I have used the same query with a similar version of LibreOffice (6.1.3.2) under MacOS and did not notice any problem. The same query is perfectly executed when submitted directly to MySQL via MySQL Workbench. Also, when making a simple query (SELECT * FROM dailydata WHERE symbol = ‘AAPL’) works on both machines.

Any idea why the nested SQL query is suddenly broken on Linux and still works on MacOS ?
Any idea how I should change this query to make it work ?

Thanks in advance

[EasyTrieve]

retagged to just base and mysql

[Ratslinger]

Hello,

Have used statements similar to this in a number of LO versions with MySQL. Currently tested on 6.1.3.2 (TDF version) under Linux Mint 18.04 without a problem with this statement:

SELECT * FROM `Customer` AS a, (SELECT `Customer_ID`, MAX(`Date_In`) as `MaxDate` FROM `RENTAL` GROUP BY `Customer_ID`) as b WHERE a.`CustomerID` = b.`Customer_ID` AND b.`MaxDate` > '2018-10-01'

and Run SQL directly on.

[SwissBear]

Hi, thank you for checking. This is helpful. I need to find out where else the problem lies.

[SwissBear]

It seems that the SQL parser is more rigorous in the Linux version than in the MacOS one.
Replacing * with the exact fields to retrieve in the SELECT statement, removed ambiguities in the statement and solved the problem. Thanks for your help