Base - MySQL: Trouble with Date_Add Function

[Zolerii]

I am using LibreOffice Base to make attractive reports based on data I have stored in my MySQL Server. I am having trouble writing a query. More specifically, I have a query that I cannot make work in LibreOffice.

The first image is proof that the query runs as expected when input directly into MySQL.

As you can see above, there is nothing wrong with the output (other than prehypertension). The same query (with the addition of grave accents (`) where LibreOffice wants them) fails with an SQL Status of HY000 and an Error Code of 1000.

Here is the syntax for the DATE_ADD and DATE_SUB functions in MySQL. It lists the expr part as a string, but enclosing the 2 in single quotes (’), double quotes ("), or grave accents (`) still produces an error. The only way to get the query to save is to enclose the entire INTERVAL 2 WEEK section in single quotes as seen below.

While this will save and allow you to go on your merry little way, it fails when the query is run. I believe this is because this is syntactically correct according to LibreOffice, but it is incorrect according to MySQL.

Before I move this to Bugzilla, am I doing something incorrect? I believe this is a true bug, but before I waste a developer’s time, I wanted to ask the community.

[w_whalley]

I have never had grave accents work in SQL. I believe that MySQL will accept single quotes (apostrophie) or double quotes, but LibreOffice won’t. I got your query to work using double quotes around the column names. You need to run the SQL command directly in base.

select avg("sys"),avg("dia") from "reading" where "date" >=date_sub(now(),interval 2 week);

[Zolerii]

w_whalley: thank you for your answer. Are you sure you’ve connected your LibreOffice Base to an existing MySQL database server when you ran your query? Normally, you are correct. Base will use double quotes, but in this instance, it changes them to grave accents.

[qubit1]

Hi @Zolerii,

Still having issues with MySQL? If installing the latest release doesn’t seem to fix this problem, please file a bug and provide a list of steps that can reproduce your problem. The QA team will be happy to help you track down this issue!

Please post a link to any bugs you file in a comment below using the format “fdo#123456”.

Thanks!

[RealAg]

I am having the exact same problem with Mysql and DATE_ADD(). What is the fix here?