Ask Your Question
1

Change of behaviour of LibreOffice Base for nested SQL query

asked 2018-11-23 14:06:16 +0200

SwissBear gravatar image

updated 2018-11-27 18:43:27 +0200

EasyTrieve gravatar image

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 ;)

edit retag flag offensive close merge delete

Comments

retagged to just base and mysql

EasyTrieve gravatar imageEasyTrieve ( 2018-11-27 18:43:59 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-11-24 11:11:15 +0200

SwissBear gravatar image

updated 2018-11-24 13:12:50 +0200

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 ;)

edit flag offensive delete link more
0

answered 2018-11-24 03:24:30 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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

SwissBear gravatar imageSwissBear ( 2018-11-24 08:37:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-23 14:06:16 +0200

Seen: 89 times

Last updated: Nov 24 '18