[SQL] 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?

Continue reading...