#1425 Sequence generated only once during insert.

[GL1zdA]

I have a table using nextval('test_seq') as DEFAULT. It works, as long as I insert only 1 row. With more than one row it complains that the primary key contraint is violated. Is this behaviour intended or is this a bug? I'm attaching code to reproduce the problem, tested on HSQLDB 2.3.3. In PostgreSQL such sequence works as I expect - it generates a unique value for every inserted row.

CREATE TABLE test (
    val1 INTEGER,
    val2 INTEGER
);

INSERT INTO test (val1, val2) VALUES (1, 1);
INSERT INTO test (val1, val2) VALUES (1, 1);

SET DATABASE SQL SYNTAX PGS TRUE;

CREATE SEQUENCE test_seq;

CREATE TABLE test2 (
    id INTEGER PRIMARY KEY,
    val1 INTEGER,
    val2 INTEGER
)

ALTER TABLE test2 ALTER COLUMN id SET DEFAULT nextval('test_seq');

INSERT INTO test2 (val1, val2) SELECT val1, val2 FROM test LIMIT 1;

-- fails
INSERT INTO test2 (val1, val2) SELECT val1, val2 FROM test;

CREATE TABLE test4 (
id integer GENERATED BY DEFAULT AS SEQUENCE test_seq PRIMARY KEY,
val1 integer,
val2 integer
)
-- also fails
INSERT INTO test4 (val1, val2) SELECT val1, val2 FROM test;

CREATE TABLE test5 (
id serial PRIMARY KEY,
val1 integer,
val2 integer
)

--this is ok
INSERT INTO test5 (val1, val2) SELECT val1, val2 FROM test;

[Fred Toussi]

Thanks. Fixed and committed to SVN.