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
Leave feedback about this