MariaDB incorrectly updating sequence?

In 11.4.3-MariaDB I have this very simple sequence: CREATE SEQUENCE test_seq START WITH 1; (My SQL client says it uses InnoDB) and I noticed an issue with it.

I created the query below to be able to reserve several values of a sequence, but it returns nothing and apparently, only 4 values are consumed each time (checked using SELECT NEXTVAL(test_seq);, vs 50 expected.

WITH Seq(Value) AS (
    SELECT nextval(test_seq) FROM seq_1_to_50
)
SELECT VALUE AS LowerBound,
   (
        SELECT MIN(Value)
        FROM Seq Top
        WHERE Value >= Bottom.Value
        AND NOT EXISTS (SELECT 1 FROM Seq WHERE Value = Top.Value + 1)
    ) AS UpperBound
FROM Seq Bottom
WHERE NOT EXISTS (SELECT 1 FROM Seq WHERE Value = Bottom.Value - 1)

Regarding that:

The query is designed to skip contiguous values and thus decrease the network load. This is what makes it complicated.
Of course, I could just run the code from the CTE’s body (SELECT nextval(test_seq) FROM seq_1_to_50); doing that does work but I do not see a valid reason for the complete query only increment the sequence by 4 with no rows returned.
If optimization was the cause, I could understand the sequence not being incremented at all, just not that behavior.
I am more of an expert of Postgres than MariaDB, so I could test an equivalent of the above in Postgres with the slightly modified query here (the only difference is the code inside the CTE):

WITH Seq(Value) AS (
    SELECT nextval('test_seq') FROM generate_series(1, 50)
)
SELECT VALUE AS LowerBound,
   (
        SELECT MIN(Value)
        FROM Seq Top
        WHERE Value >= Bottom.Value
        AND NOT EXISTS (SELECT 1 FROM Seq WHERE Value = Top.Value + 1)
        ) AS UpperBound
FROM Seq Bottom
WHERE NOT EXISTS (SELECT 1 FROM Seq WHERE Value = Bottom.Value - 1)

What am I doing wrong?

You need to sign in to view this answers

Related Post