Feature Requests item #1834820, was opened at 2007-11-19 23:44
Message generated for change (Settings changed) made by mr-meltdown
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=1834820&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
Priority: 5
Private: No
Submitted By: mharrison (matt_harrison)
Assigned to: Niels Nes (nielsnes)
Summary: SQL:AUTO_INCREMENT failures...
Initial Comment:
After inserting a primary key that is autoincremented, if you insert without
one it will fail the first time, but later succeed.
Here's the test case....
CREATE TABLE testusers (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(40),
fullname VARCHAR(100),
PRIMARY KEY (id)
);
INSERT INTO testusers (id, name, fullname) VALUES (1, 'wendy', 'Wendy Wones');
-- This will fail the first time
INSERT INTO testusers (name, fullname) VALUES ('fred', 'Fred Flintstone');
-- Now it will succeed
INSERT INTO testusers (name, fullname) VALUES ('fred', 'Fred Flintstone');
----------------------------------------------------------------------
>Comment By: Fabian (mr-meltdown)
Date: 2009-04-28 14:40
Message:
this looks like correct behaviour to me, the bug as invalid, and no feature
to request :)
----------------------------------------------------------------------
Comment By: mharrison (matt_harrison)
Date: 2007-11-22 06:29
Message:
Logged In: YES
user_id=1164236
Originator: YES
I didn't realize the this only fails when the first specified PK is one
more than the number in the table. So this shouldn't happen often. I
thought (without testing) that after specifying the PK, the next insert
would always fail. This isn't the case. Feel free to close or do whatever
you guys think is best. Thanks for the explanation Romulo
----------------------------------------------------------------------
Comment By: Niels Nes (nielsnes)
Date: 2007-11-20 14:38
Message:
Logged In: YES
user_id=43556
Originator: NO
As romulo put it, was/is sort of a feature that we do not update a
sequence number when inserts supply the value (normally derived from the
sequence number). Reason for this is that sequence numbers are sort of
independed from the column value. They are incremented etc by functions.
Our auto_increment is therefor simply implemented by a default
value(function) applied on insert. So currently we you insert a value your
self you should call, ALTER SEQUENCE "sequence_number" RESTART WITH
next_value.
As auto_increment, isn't part of the sql specification, there is no clear
semantics here. So I'l move this for now into a feature request.
----------------------------------------------------------------------
Comment By: Romulo Goncalves (romulog)
Date: 2007-11-20 12:51
Message:
Logged In: YES
user_id=1498628
Originator: NO
Well, after some debug I realized that this bug might not be a bug (maybe
we will need a new error message or warning).
If you use auto-increment to set your pkey and then you also insert the
values for the pkey by hand the auto-increment (in our case a sequence) is
not aware of that insertion.
To be more specific I will give an example.
In the example used to open this bug, the first insertion uses id = 1.
However, our sequence was not selected so its value remains 1. The next
insertion is without the id value, in this case the sequence is called to
give a value for the id. It will fail because the pkey with value 1 already
exists, but it will increment the sequence to 2. The next insertion works
because it does not break any constraint (there is no pkey with value 2).
Now imagine that the user do the following insertion:
INSERT INTO testusers (id, name, fullname) VALUES (3, 'wendy',
'WendyWones');
In this case only the third insertion without the value for id column will
give a constraint error (pkey violated).
To conclude, the auto-increment is not aware of the values already used by
the user. One solution is to improve the auto-increment, the other one is
to not allow the user defined the value for the column that was set
auto-increment.
Now is the SQL god father who has to give an opinion...
Regards,
Romulo
----------------------------------------------------------------------
Comment By: Martin Kersten (mlkersten)
Date: 2007-11-20 07:50
Message:
Logged In: YES
user_id=490798
Originator: NO
error confirmed and test file added.
analysis pending.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=1834820&group_id=56967
------------------------------------------------------------------------------
Register Now & Save for Velocity, the Web Performance & Operations
Conference from O'Reilly Media. Velocity features a full day of
expert-led, hands-on workshops and two days of sessions from industry
leaders in dedicated Performance & Operations tracks. Use code vel09scf
and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
