ALTER SEQUENCE ... RESTART WITH (SELECT NULL) NO CYCLE is accepted

[monetdb-team]

Date: 2010-08-11 15:14:57 +0200
From: @grobian
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: @njnes

Last updated: 2011-03-28 17:31:38 +0200

Comment 14637

Date: 2010-08-11 15:14:57 +0200
From: @grobian

Setting a sequence value to NULL is allowed, but causes interesting problems afterwards. First a not-NULL violation is caused at the first invocation (it is likely to be on a key column, e.g. serial), then a second invocation yields in a very large negative number (looks like overflow).

I think we should just disallow NULL as valid input here.

Comment 14638

Date: 2010-08-11 15:19:14 +0200
From: @grobian

The parser correctly forces a positive integer, however, it also allows subselects which can return negative integers and nulls such as a simple SELECT NULL. Hence, the check should be done at execution time.

Comment 14639

Date: 2010-08-11 15:20:33 +0200
From: @grobian

sql>alter sequence "sys"."seq_5128" restart with null no cycle;
syntax error, unexpected sqlNULL, expecting sqlINT or '(' in: "alter sequence "sys"."seq_5128" restart with null"
sql>alter sequence "sys"."seq_5128" restart with (select null) no cycle;
operation successful
sql>alter sequence "sys"."seq_5128" restart with (select -1) no cycle;
operation successful
sql>

(typical way to find this is to do a "restart with (select max(id) from ..." on a table with no tuples)

Comment 14830

Date: 2010-08-30 09:21:51 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.

Comment 15267

Date: 2010-12-03 22:34:11 +0100
From: @njnes

could be handled in mvc_restart_seq

Comment 15415

Date: 2011-01-24 21:02:19 +0100
From: @grobian

Changeset 809ced83d8c4 made by Fabian Groffen fabian@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=809ced83d8c4

Changeset description:

sequences: refuse starting/restarting with NULL, bug #2648

Using sub-queries, the parser cannot "see" that the restart value is
actually NULL.  Implement the runtime checks to ensure that we cannot
set restart value for sequences to NULL.

Comment 15416

Date: 2011-01-24 21:03:27 +0100
From: @grobian

Fixed in current, test added.

Comment 15644

Date: 2011-03-28 17:31:38 +0200
From: @sjoerdmullender

The Mar2011 version has been released.