Hi,

My personal experience of using DATE_ADD/SUB functions with OOo and LO is that :

- you generally have to add an alias to your function ;

- sometimes you have to use an explicit cast for the data type, otherwise OOo/LO gets confused and the function fails.

Try defining the DATE_SUB value with an ALIAS and/or CASTing your date_sub to a specific data type.


Alex

For example, the following works for me in 

LibreOffice 3.5.5.3 
Version ID : 7122e39-92ed229-498d286-15e43b4-d70da21


with a JDBC driver connection :

DATE_ADD(Prio_FF_date1, INTERVAL 30 MONTH),DATE_ADD(Filing_date1, INTERVAL 30 MONTH)) as 'NATPHA'

Note that this also works for me :


DATE_SUB(CURDATE(),INTERVAL 30 MONTH) <= Filing_date1 order by filing_date1


and I haven't explicitly CAST or created an ALIAS for the result of my function


Can not confirm, at least not on Mac OSX 10.8

Changing to Windows OS for moment as I can not reproduce on Mac.

Hmm, should've read closer :

"using Tools -> SQL... "

this is the wrong tool for the job, the Tools > SQL can be used for executing queries, but it won't return any visible result (at least not for the time being, but I believe it is being worked on). However, it should at least report that the query was executed with success.

If you want your queries to fiddle with your queries, you need to do so from the Query editor within the ODB document, either via the SQL parser, which will thrown an error like the one you indicated, or by deactivating the LO SQL parser, and using the "DirectSQL" mode which sends the query statement unaltered to the backend db engine.

So, for me, this is not a bug, I can not reproduce it, even from the Tools > SQL menu.


Alex

Having now tested with the master build of 21/08, I can even state that the feature I mentioned as under development works and the result set is now displayed in the Tools > SQL window, as per screenshot attachment. Note that the result set is displayed as a list of comma separated values.


Alex

Created attachment 65882 [details]
screenshot of new resultset display from Tools > SQL statement execution

Hi Alex,

Thank you for your comments. I will try to address them all, and if this is not a true bug, we will mark it as such and move on.

When you connected to MySQL, under 'Connect to an existing database' did you choose MySQL? On the next screen, 'Set up MySQL Connection', is where I specified to use the JDBC. I only stress this because I'm aware of JDBC being an option at the beginning, and (while it probably does) I'm not sure if it works the same.

My query is this:

SELECT AVG( `sys` ) AS `Systolic`, AVG( `dia` ) AS `Diastolic` FROM `Reading` WHERE `date` >= DATE_SUB( NOW( ), INTERVAL 2 WEEK )

I'm using DATE_SUB in the WHERE clause, and I'm not sure how using an alias would help.

As for using Tools -> SQL..., I was merely using it to show that the statement ran successfully. It does in fact, run successfully on mine, too. I don't imagine the parser checks here, so it wouldn't have a problem.

As to using 'Run SQL command directly' inside of the Query Builder, this worked. My statement ran (I took out the grave accents) just fine with this on. I didn't have this on before (I didn't know exactly what it did), and the problem was the INTERVAL 2 WEEK part. If I enclosed it all in a string 'INTERVAL 2 WEEK', the parser was happy, and it let me save and close it with no errors. Obviously, MySQL threw an exception when it was given that statement with 'INTERVAL 2 WEEK' in quotes though.

I leave the future of this report up to you as you have more experience than I. On my end, the SQL parser will not let statements with DATE_ADD or DATE_SUB through, but turning the parser off allows me to run the same statement with no error. That sounds like a bug to me, but as I develop all of my queries in MySQL first, I'll probably never turn the Parser back on anyway (I won't have to add grave accents).

As this is a problem with the SQL Parser (on my end at least), my original bug description may not be valid anymore. Since I can just turn the parser off, I can get back to business.

Despite the fact that the query now runs on its own, (I can double click it and the table view opens up with the correct information) I cannot make a report based on this query. I'm going to add a picture that shows the query is now running with no problems (except prehypertention) and the error dialog box that opens when I try to open a form based on this query. (The form was made with no problems, but won't open.)

Is there anyway to turn the SQL Parser off for the entire program?

Created attachment 65900 [details]
This is a photo that contains both the fact that the query runs with the Parser off, but will not make a usable form.

The workaround for this is to Edit the Form. Show the Properties (F4), look under the Data tab, click the ellipse (...) next to Content which will bring up another Query Builder with the SQL statement. Once again, you have to Run the SQL Statement Directly. The Form will load properly after this is done.

(In reply to comment #8)

Hi Tyler,


> When you connected to MySQL, under 'Connect to an existing database' did you
> choose MySQL? On the next screen, 'Set up MySQL Connection', is where I
> specified to use the JDBC. I only stress this because I'm aware of JDBC being
> an option at the beginning, and (while it probably does) I'm not sure if it
> works the same.

Yes, I have had a JDBC mysql connection set up to one of my work database servers for the last 6 or 7 years, as a backup for the native mysql connector in case that gets broken by LO development (which it has, unfortunately, on more than one occasion). 



> 
> I leave the future of this report up to you as you have more experience than I.
> On my end, the SQL parser will not let statements with DATE_ADD or DATE_SUB
> through, but turning the parser off allows me to run the same statement with no
> error. That sounds like a bug to me, but as I develop all of my queries in
> MySQL first, I'll probably never turn the Parser back on anyway (I won't have
> to add grave accents).
> 
> As this is a problem with the SQL Parser (on my end at least), my original bug
> description may not be valid anymore. Since I can just turn the parser off, I
> can get back to business.

The parser used by LO internally is, ermmm, how can I say, "suboptimal", and has been for a long time. It tries to be all things to all database engines, which unfortunately is a Herculean task, and something in the end that it doesn't do very well when it comes to what many would consider to be basic SQL functions (for that user's respective database). Add to that the fact that sometimes the db engines or their drivers do not always tell LO the truth about their own capabilities (over/understated), and you have a recipe for disaster, or at least, many "gotchas", which only can be avoided by direct SQL processing.

So, I'm going to close this bug as invalid, since it appears to me that this is a limitation of the parser and not a buggy piece of code. If you want to open up a request for enhancement bug relating to support in the parser for "advanced mysql functions", please go ahead, it'll be another one added to the pile ;-)


Alex