Mariadb and SQL generated by Gemini

General
1

I run Mariadb on a Raspberry Pi
mysql -V shows the version to be 10.11.11-MariaDB.
Node-red has node-red-node-mysql v 2.0.0.

There is a table socketevent containing socketid (primary key), and ontime (datetime).

Gemini gives this SQL, strongly asserting that the RANGE BETWEEN INTERVAL syntax (which I'm not familiar with) works for interval units in Mariadb from v10.2.2 onwards

SELECT
    ontime,
    MIN(ontime) OVER (ORDER BY ontime RANGE BETWEEN INTERVAL 2 WEEK PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM    socketevent
LIMIT 10;

It gives an error

ERROR 1064 (42000): 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 'INTERVAL 2 WEEK PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM
   ...' at line 3

Can anyone with understanding of the node and mariadb shed any light on this?

Gemini has suggested checking a couple of settings but in the end we are both stumped.

A template to define and populate socketevent:

[{"id":"db1dcd7eb57f8ed2","type":"template","z":"1138ada3048009d3","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE socketevent (socketid INT(11) NOT NULL, ontime DATETIME NOT NULL, PRIMARY KEY (SOCKETID));\nINSERT INTO socketevent (socketid, ontime) VALUES (1, '2025-08-06 06:44:13'), \n(2, '2025-08-06 06:59:26'),\n(3, '2025-08-06 11:07:02'),\n(4, '2025-08-06 18:15:51'),\n(5, '2025-08-07 07:15:57');","output":"str","x":260,"y":140,"wires":[["543c129233adda81"]]}]
2

Do you have a MariaDB management tool? If not, there are a number that you can use with VS Code. Best to try the query in one of those first to eliminate possible issues with the node.

3

Also worth asking a similar question on a different AI to see if you get a different answer.

4

I have had long discussions on sql with chatgpt (not pro - no subscription) but it seems less capable of writing working code than gemini.

Well I tried the query on the mysql command line and it errors there too.
I know, this absolves node-red-node-mysql of blame.
I was sort of hoping there might be a guru reading this with the answer at his fingertips, the developers of the node maybe. :smiley:

Or perhaps someone might try the query on a different platform and say if it works for them.

5

Sorry, your SQL example is well beyond my aging rusty SQL I'm afraid.

What is the SQL table schema though and what outcome are you trying to achieve?

6

Mine too!

I simplified the schema above.

The required outcome is infinitely complicated, two tables joined to each other multiple times to include various summary and calculated values.

This relates to my first Node-red project, controlling and analyzing my aged espresso machine.
It's powered through a smart plug with power monitoring, and the on/off events are stored in the database.

I do have a working version but it depends on a view definition joining two other views so it's inefficient and slow, thus I was hoping AI would give me a more efficient approach (my version is faster than chatgpt's best effort! :upside_down_face:. Probably I didn't explain the requirement well enough)
I won't inflict it on you!

7

But you see, you've peaked my interest now!

You probably don't want to here me say this but I doubt MariaDB is the right tool here. But that was why I asked about your aims. What you appear to have is a timeseries of on/off events. To analyse that data, it is unlikely in my view that SQL will be the easiest tool. Depending on the analysis, you are more likely to be better off either with a timeseries db such as InfluxDB or, if data sizes permit, simply holding the data in memory and processing using an analytics processing app.

If looking at processing in memory, while Python is better at that than Node.js, there is the Danfo package which gives you similar abilities and stays very close to Python terminology and API's.

Danfo - powerful javascript data analysis toolkit. Similar API to Python Pandas. Rich HTML output and Plotting (via integrated plotly).

You can use it with a function node and I've done that in the past.

8

Try putting a single quote before and after the 2 after RANGE BETWEEN INTERVAL as shown below:

SELECT ontime, MIN(ontime) OVER (ORDER BY ontime RANGE BETWEEN INTERVAL ‘2’ WEEK PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks FROM socketevent LIMIT 10;

9

Good suggestion but it does not fix it.
I've also seen suggestions to cast ontime to unix_timestamp (?) and convert 2 weeks to seconds, but Gemini is not confident this will work with Mariadb.

She (They?) can give me a work-around using correlated subqueries but they really impact performance.

10

Is MIN a supported Windows function as according to mariadb.com docs

Aggregate functions that are currently supported as window functions are: COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR
11

Hmm. I suspect your page got truncated?
According to My copy of the docs

It is possible to use aggregate functions as window functions. An aggregate function used as a window function must have the OVER clause. For example, here's COUNT() used as a window function:

SELECT COUNT(*) OVER (ORDER BY column) FROM table;

(So that explains that weird syntax)

MariaDB currently allows these aggregate functions to be used as window functions:

    AVG
    BIT_AND
    BIT_OR
    BIT_XOR
    COUNT
    MAX
    MIN
    STD
    STDDEV
    STDDEV_POP
    STDDEV_SAMP
    SUM
    VAR_POP
    VAR_SAMP
    VARIANCE
12

I tried using unix_timestamp() which Gemini didn't like.
Though it does not work with BETWEEN (2 * 7 * 24 * 60 * 60) PRECEDING AND CURRENT ROW, it accepts BETWEEN 1209600 PRECEDING AND CURRENT ROW. I have yet not verified it returns the expected data

SELECT
    ontime,
    MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks
FROM    socketevent
LIMIT 10;
13

Weird, if you scroll down the page a bit to Scope you will see the allowed aggregate function list I showed

14

As per perplexity:

The error you encountered with the syntax RANGE BETWEEN INTERVAL 2 WEEK PRECEDING AND CURRENT ROW in your MariaDB 10.11.11 comes from a limitation in MariaDB’s implementation of window functions:
Key Points
• MariaDB supports window functions and frames of the form ROWS BETWEEN ... and RANGE BETWEEN .... However, its support for RANGE with INTERVAL or precise temporal offsets is not as broad as in PostgreSQL or Oracle.
• In practice, MariaDB only allows numeric range offsets (not intervals or date/time expressions) with RANGE frames. Using INTERVAL with RANGE (e.g., RANGE BETWEEN INTERVAL 2 WEEK PRECEDING...) is not supported and will cause a syntax error, even in 10.11.x.
• This is a documented and widely discussed limitation in the MariaDB community. The correct usage of RANGE is currently with numeric types. When ordering by a datetime column, you cannot specify an INTERVAL directly in the frame specification. This is true for your version and all 10.x series of MariaDB.

To achieve a rolling minimum or similar logic over a time interval (e.g., previous 2 weeks) in MariaDB, you should use a correlated subquery or a JOIN, for example:

SELECT
    a.ontime,
    (
        SELECT MIN(b.ontime)
        FROM socketevent b
        WHERE b.ontime BETWEEN a.ontime - INTERVAL 2 WEEK AND a.ontime
    ) AS min_ontime_prev_2weeks
FROM socketevent a
ORDER BY a.ontime
LIMIT 10;

The SQL generated by Gemini is not supported in MariaDB 10.11.11. You cannot use RANGE BETWEEN INTERVAL ... when ordering by a datetime—this is not a limitation of your installation but of MariaDB itself. You should use subqueries or joins for sliding time window calculations.
If you upgrade to a database engine with broader SQL/Analytic support (such as PostgreSQL), this syntax will work as expected. For MariaDB, stick with the subquery or join approach for now.

15

interesting, this is what Google Gemini gave me as probable solution

SELECT
  a.ontime,
  (SELECT MIN(b.ontime)
   FROM socketevent b
   WHERE b.ontime BETWEEN a.ontime - INTERVAL 2 WEEK AND a.ontime
  ) AS min_ontime_prev_2weeks
FROM socketevent a
ORDER BY a.ontime
LIMIT 10;
16

Thanks for the suggestions folks :grinning_face:

So there are two work-arounds suggested: A correlated subquery

SELECT ontime,
MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks 
FROM  socketevent ;

And a window function with ontime cast to unix_timestamp()

 SELECT ontime,     
MIN(ontime) OVER (ORDER BY UNIX_TIMESTAMP(ontime) RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW) AS min_ontime_prev_2weeks 
FROM  socketevent LIMIT 10;

Chatgpt and Gemini both expect the second version to be more efficient.
I asked them if it could be optimised.
Gemini suggested adding an index on the ontime column and using a machine with plenty of memory.
Chatgpt said

Using UNIX_TIMESTAMP() prevents MariaDB from using an index on ontime because it applies a function on the column.
Solution: Use a generated column for UNIX timestamp

ALTER TABLE socketevent
ADD COLUMN ontime_unix BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(ontime)) STORED,
ADD INDEX idx_ontime_unix (ontime_unix);

Then change the query:
SELECT 
    ontime, 
    MIN(ontime) OVER (
        ORDER BY ontime_unix 
        RANGE BETWEEN 1209600 PRECEDING AND CURRENT ROW
    ) AS min_ontime_prev_2weeks
FROM socketevent;
This will be far more efficient than the original and scales well for analytical workloads.

Chatgpt gets the rosette for most helpful ai in this instance. :sports_medal:

17

Is the take away from this thread just that you are drinking too much coffee :winking_face_with_tongue:

18

Is that even a thing? :hot_beverage: :hot_beverage: :hot_beverage: :hot_beverage:

1 Like
19

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.