I have an external Mariadb Database and am using the native connector.
Works fine, BUT:
When i try to use the LAG SQL command in a query I always get a syntax error message.
The problem is NOT with the target database, using phpMyAdmin the same syntax works fine.
My actual workaround was to create a view using phpMyAdmin that provides the results of the LAG statement, but this is not very flexible (and will not work if no direct access to the server is granted.).
I tried all sorts of delimiters ", ', ` etc. using the “SQL” tool, but no way to get round the syntax error.
Question: How do I use this? Is there a “raw” or “direct” mode for the SQL interface that does no funny things before the command is issued?
Thanks for tips!
Walter.
The SQL window runs in direct mode by default, and in the SQL view of a query there is menu:Edit>“Run SQL directly”. Direct mode should work in the exact same ways as with specific database tools.
In Tools > SQL…
did you try Run directly?
Because to me here it runs 
both via Tools or Queries GUI in direct mode of course.
Yes, but both fails (costed me a full day to try all options), thought I am stupid enough to not get the syntax of LAG right (and fiddled with everything in reach) etc. Finally I logged directly into the server using a server-local tool and there it worked. Direct mode was enabled.
I am using LO 26.2.1.2 on Apple silicon.
In query editor “direct mode” is the rightmost icon in the toolbbar, in this screenshot of an older version also tagged “sql”
Command looks exactly the same.
SELECT Datum
, Abfahrt
, Ankunft
, (LAG (Orte.Ort) OVER (ORDER BY kmStand)) AS Von
, Orte.Ort AS BIS
FROM Fahrten, Autos, Orte
WHERE Fahrten.Auto = Autos.ID
AND Fahrten.Ort = Orte.ID
ORDER BY KmStand ASC
;
Result: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OVER (ORDER BY kmStand)) AS Von
, Orte.Ort AS BIS
So it looks like the LAG syntax was spoiled when handing to the server.
SELECT `Fahrten`.`Datum`, `Fahrten`.`Abfahrt`, [...]
as long as not ambiguous MariaDB is fine with not naming the table. Datum, Abfahrt, Ankunft and Ort are not ambiguous. I tried the full version (also including the database name), no difference.
Might be this is the reason:
Have a look at LAG (Orte.Ort). Is there a space between LAG and (. Functions won’t work this way. With a space it couldn’t work also in phpMyAdmin.
Thanks, you are right, no blanks AND SQL direct Mode did work!
So
SELECT kmStand
, LAG(kmStand)over(order by kmStand) as alterStand
from Fahrten;
provides good results.