sql : sequence value out of bounds #74127

mosquito2333 · 2021-12-21T05:48:13Z

When I used the sequence , I found that the value of the sequence was out of bounds using the following statement, but it did not report an error.

drop sequence seq_mosquito;
CREATE SEQUENCE seq_mosquito INCREMENT  BY 3  MINVALUE 0 MAXVALUE 10;
select setval('seq_mosquito', 8); -- 8
SELECT nextval('seq_mosquito'); -- the result is 11，did not report an error

version：cockroach-v21.1.7.linux-amd64

Jira issue: CRDB-11928
Epic CRDB-14049

The text was updated successfully, but these errors were encountered:

blathers-crl · 2021-12-21T05:48:16Z

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

Join our community slack channel and ask on #cockroachdb.
Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

mosquito2333 · 2021-12-21T05:55:26Z

Why is there an extra illegal value, is this a special design?

drop sequence seq_mosquito;
CREATE SEQUENCE seq_mosquito INCREMENT  BY 3  MINVALUE 0 MAXVALUE 10;
SELECT nextval('seq_mosquito'); -- 0
SELECT nextval('seq_mosquito'); -- 3
SELECT nextval('seq_mosquito'); -- 6
SELECT nextval('seq_mosquito'); -- 9
SELECT nextval('seq_mosquito'); -- 12，did not report an error
SELECT nextval('seq_mosquito'); -- ERROR: nextval(): reached maximum value of sequence "seq_mosquito" (10)

Previously, an additional out-of-range sequence value may have been generated. The original criterion was whether the previous endSequence value exceeded the limit. However, when the current endSequence is 1, the increment is 2 and the MaxValue is 2, the previous endSequence value is 1 and the program does not report an error. However, when the current endSequenceValue 3 exceeds the limit, an error should be reported In incrementSequenceUsingCache, the criterion for determining whether a cache's data is eligible is modified to whether the first data that may be stored in the cache exceeds the limit. Fixes cockroachdb#74127. Release note: None

rafiss · 2021-12-22T15:20:18Z

Thanks for this bug report! No this isn't desired behavior. I believe the problem might be the same as #52552 (comment)

mosquito2333 · 2021-12-22T15:45:49Z

Thanks for this bug report! No this isn't desired behavior. I believe the problem might be the same as #52552 (comment)

Oh, thank you! I am also confused with this question.

mosquito2333 · 2021-12-23T06:33:54Z

Thanks for this bug report! No this isn't desired behavior. I believe the problem might be the same as #52552 (comment)

But I have a feeling that this issue is a separate bug, a boundary judgement issue

Previously, an additional out-of-range sequence value may have been generated. The original criterion was whether the previous endSequence value exceeded the limit. However, when the current endSequence is 1, the increment is 2 and the MaxValue is 2, the previous endSequence value is 1 and the program does not report an error. However, when the current endSequenceValue 3 exceeds the limit, an error should be reported In incrementSequenceUsingCache, the criterion for determining whether a cache's data is eligible is modified to whether the first data that may be stored in the cache exceeds the limit. Fixes cockroachdb#74127. Release note: None

…' were not as expected If the sequenceVal exceeded the old MinValue or MaxValue, we must set sequenceVal to the last valid one. 1. Values is just init, when the sequence is altered before it has ever been used. In this case, last valid sequenceVal is oldStart. Set the initial value to the oldStartValue(just like pg), when last valid sequenceVal in [newMinValue, newMaxValue]. 2. When the sequenceVal out of range result from increment, report an error. Fixed cockroachdb#52552 cockroachdb#74127 Release note (bug fix): A bug with ALTER SEQUENCE that allowed a sequence to return values that are out-of-range has been fixed.

Previously, an additional out-of-range sequence value may have been generated. The original criterion was whether the previous endSequence value exceeded the limit. However, when the current endSequence is 1, the increment is 2 and the MaxValue is 2, the previous endSequence value is 1 and the program does not report an error. However, when the current endSequenceValue 3 exceeds the limit, an error should be reported In incrementSequenceUsingCache, the criterion for determining whether a cache's data is eligible is modified to whether the first data that may be stored in the cache exceeds the limit. Fixes cockroachdb#74127.

81108: *: improve inline code comments r=maryliag a=Azhng Release note: None 81123: sql : sequence value out of bounds r=rafiss a=mosquito2333 Previously, an additional out-of-range sequence value may have been generated. The original criterion was whether the previous endSequence value exceeded the limit. However, when the current endSequence is 1, the increment is 2 and the MaxValue is 2, the previous endSequence value is 1 and the program does not report an error. However, when the current endSequenceValue 3 exceeds the limit, an error should be reported In incrementSequenceUsingCache, the criterion for determining whether a cache's data is eligible is modified to whether the first data that may be stored in the cache exceeds the limit. Fixes #74127. Release note (bug fix): Fixed a bug where sequences could return values that are out-of-bounds in some cases. 81213: ui, changefeeds: better status col in db console r=abarganier,HonoreDB a=dhartunian Previously, when a job contained a `highwater_timestamp` column value (present for changefeeds) the status column in DB console would *always* show that value instead of the job status ("running", "paused", etc.). This would cause confusion for operators because the SQL output for job status always included both a `status` column and a separate `highwater_timestamp` column. This change moves the `highwater_timestamp` into a separate column and always renders the `status` column with the "pill" component that shows the current job status. The highwater timestamp is also moved to the sidebar in the job details page instead of replacing the status pill, for similar consistency. Finally, the highwater timestamp now displays the nanosecond decimal value by default and the human-readable formatted value in the tooltip. This faciliates easier copy/paste behavior from the UI as the decimal is more useful. ![Screenshot 2022-05-12 at 16-09-19 Jobs Cockroach Console](https://user-images.githubusercontent.com/986307/168162734-48a61ec4-c5a5-41e2-9533-845afe08a0b4.png) ![Screenshot 2022-05-12 at 16-09-47 Details Job Cockroach Console](https://user-images.githubusercontent.com/986307/168162742-ee3e3f4c-136e-424f-af54-cf03fde42406.png) Resolves #80496 Release note (ui change): The job status page in the DB Console will now show the status column for changefeed jobs and display the `highwater_timestamp` value in a separate column. Thise more closely matches the SQL output of `SHOW changefeed JOBS`. The highwater timestamp now displays as the nanosecond system time value by default with the human-readable value in the tooltip since the decimal value is copy/pasted more often. Co-authored-by: Azhng <[email protected]> Co-authored-by: mosquito2333 <[email protected]> Co-authored-by: David Hartunian <[email protected]>

mosquito2333 added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Dec 21, 2021

blathers-crl bot added O-community Originated from the community X-blathers-untriaged blathers was unable to find an owner labels Dec 21, 2021

mosquito2333 mentioned this issue Dec 22, 2021

sql : sequence value out of bounds #74194

Closed

rafiss removed the X-blathers-untriaged blathers was unable to find an owner label Dec 22, 2021

blathers-crl bot added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label Dec 22, 2021

rafiss added the A-sql-sequences Sequence handling in SQL label Dec 22, 2021

mosquito2333 mentioned this issue Jan 6, 2022

sql : alter sequence's increment and the results of 'select nextval()' were not as expected #74321

Closed

mosquito2333 closed this as completed Jan 6, 2022

mosquito2333 reopened this Jan 6, 2022

mosquito2333 mentioned this issue May 7, 2022

sql : sequence value out of bounds #81123

Merged

craig bot closed this as completed in 9c88d9e May 24, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql : sequence value out of bounds #74127

sql : sequence value out of bounds #74127

mosquito2333 commented Dec 21, 2021 •

edited by rafiss

Loading

blathers-crl bot commented Dec 21, 2021

mosquito2333 commented Dec 21, 2021

rafiss commented Dec 22, 2021

mosquito2333 commented Dec 22, 2021 •

edited

Loading

mosquito2333 commented Dec 23, 2021

sql : sequence value out of bounds #74127

sql : sequence value out of bounds #74127

Comments

mosquito2333 commented Dec 21, 2021 • edited by rafiss Loading

blathers-crl bot commented Dec 21, 2021

mosquito2333 commented Dec 21, 2021

rafiss commented Dec 22, 2021

mosquito2333 commented Dec 22, 2021 • edited Loading

mosquito2333 commented Dec 23, 2021

mosquito2333 commented Dec 21, 2021 •

edited by rafiss

Loading

mosquito2333 commented Dec 22, 2021 •

edited

Loading