Bugs item #2873564, was opened at 2009-10-06 11:26 Message generated for change (Tracker Item Submitted) made by smokinn You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2873564&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: SQL/Core Group: SQL "stable" Status: Open Resolution: None Priority: 5 Private: No Submitted By: Guillaume Theoret (smokinn) Assigned to: Niels Nes (nielsnes) Summary: Cannot create unique constraint on char column Initial Comment: When creating a unique constraint, if one of the columns is of type char, the unique constraint is ignored. To reproduce: ql>CREATE TABLE test.keytest ( more> date_added int NOT NULL, more> hash_key char(32) NOT NULL, more> dimension1 int NOT NULL, more> dimension2 int NOT NULL, more> metric1 int NOT NULL, more> metric2 int NOT NULL, more> more> CONSTRAINT key_test UNIQUE (hash_key,date_added) more>); Operation successful sql>insert into test.keytest (date_added, hash_key, dimension1, dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2); Rows affected 1 Timer 12.126 msec 0 rows sql>insert into test.keytest (date_added, hash_key, dimension1, dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2); Rows affected 1 Timer 18.345 msec 0 rows sql>select * from test.keytest; select * from test.keytest; +------------+----------------------------------+------------+------------+---------+---------+ | date_added | hash_key | dimension1 | dimension2 | metric1 | metric2 | +============+==================================+============+============+=========+=========+ | 1 | a | 1 | 1 | 2 | 2 | | 1 | a | 1 | 1 | 2 | 2 | +------------+----------------------------------+------------+------------+---------+---------+ 2 tuples Timer 1.997 msec 2 rows sql>CREATE UNIQUE INDEX unique_key_test ON test.keytest (date_added, hash_key); Operation successful Timer 25.904 msec 2 rows sql>insert into test.keytest (date_added, hash_key, dimension1, dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2); Rows affected 1 Timer 78.637 msec 2 rows sql>insert into test.keytest (date_added, hash_key, dimension1, dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2); Rows affected 1 Timer 31.788 msec 2 rows sql>select * from test.keytest; select * from test.keytest; +------------+----------------------------------+------------+------------+---------+---------+ | date_added | hash_key | dimension1 | dimension2 | metric1 | metric2 | +============+==================================+============+============+=========+=========+ | 1 | a | 1 | 1 | 2 | 2 | | 1 | a | 1 | 1 | 2 | 2 | | 1 | a | 1 | 1 | 2 | 2 | | 1 | a | 1 | 1 | 2 | 2 | +------------+----------------------------------+------------+------------+---------+---------+ 4 tuples Timer 18.083 msec 4 rows Unique constraint on all int columns is not affected: sql>CREATE TABLE test.keytest2 ( more> date_added int NOT NULL, more> key int NOT NULL, more> dimension1 int NOT NULL, more> dimension2 int NOT NULL, more> metric1 int NOT NULL, more> metric2 int NOT NULL, more> more> CONSTRAINT key_test2 UNIQUE (key,date_added) more>); Operation successful Timer 167.384 msec 0 rows sql>insert into test.keytest2 (date_added, key, dimension1, dimension2, metric1, metric2) VALUES (1, 1, 1, 1, 2, 2); Rows affected 1 Timer 1.693 msec 0 rows sql>insert into test.keytest2 (date_added, key, dimension1, dimension2, metric1, metric2) VALUES (1, 1, 1, 1, 2, 2); !SQLException:assert:INSERT INTO: UNIQUE constraint 'keytest2.key_test2' violated 0 tuples Timer 66.634 msec 0 rows sql>select * from test.keytest2; select * from test.keytest2; +------------+-------+------------+------------+---------+---------+ | date_added | key | dimension1 | dimension2 | metric1 | metric2 | +============+=======+============+============+=========+=========+ | 1 | 1 | 1 | 1 | 2 | 2 | +------------+-------+------------+------------+---------+---------+ ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2873564&group_id=56967 ------------------------------------------------------------------------------ Come build with us! The BlackBerry® Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9-12, 2009. Register now! http://p.sf.net/sfu/devconf _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
