Bugs item #1958572, was opened at 2008-05-06 11:18 Message generated for change (Tracker Item Submitted) made by Item Submitter You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1958572&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 2.22 Status: Open Resolution: None Priority: 5 Private: No Submitted By: Nick (niftikhar) Assigned to: Niels Nes (nielsnes) Summary: (insert into ..select) statement Bug Initial Comment: I need to insert some null values into a table via a procedure. I created the table and did insert two rows. One with (insert into) command and second with (insert into ..select) command. Both commands work well and I could see two rows added in result. When I used the same (insert into.. select) command in a procedure. It behaves in a strange fashion. It shows that a row has been affected, but does not show the newly added row. If the rows are counted, it gives the correct count which is (3) in this case but still I could not see the last row which is added via a procedure. '''''''''''''''''''''''''''''''''''''''''''''''''''''' create sequence "ff" as integer start with 1; CREATE TABLE t1(timeid INT DEFAULT NEXT VALUE FOR "ff" PRIMARY KEY, a INT, b INT, c INT, d INT, e INT, f VARCHAR(20), g VARCHAR(20), h INT, i VARCHAR(10), j VARCHAR(10), k VARCHAR(10), l INT, m INT, n VARCHAR(10), o VARCHAR(10), p VARCHAR(10), q VARCHAR(15), r INT ); ''''''''''''''''''''''''''''''''''''''''''''''''''''' And then insert a row ''''''''''''''''''''''''''''''''''''''''''''''''''''' insert into t1(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) values(1, 1, 1, 1, 1, 'first row', 'first row',1, 'first row', 'first row', 'first row',1,1, 'first row', 'first row', 'first row', 'first row', 1); select * from t1; sql>Rows affected 1 sql> +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ ''''''''''''''''''''''''''''''''''''''''''''''''''''' Every thing is fine so far ''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>insert into t1(a, b, c, d, e, f, g, h) select a, b, c, d, e, f, g, h from t1 where timeid = 1; Rows affected 1 sql>select * from t1; +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ ''''''''''''''''''''''''''''''''''''''''''''''''''''' inserted another row and it works fine ''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>create procedure tt() begin insert into t1(a, b, c, d, e, f, g, h) select a, b, c, d, e, f, g, h from t1 where timeid = 1; end; call tt(); select * from t1; more>more>more> sql>Rows affected 1 sql>+------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ '''''''''''''''''''''''''''''''''''''''''''''''''''''' The problem starts now: when I try to insert the row via a procedure it shows that row effected 1 but does not show the row. '''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>select count(*) from t1; +--------+ | count_ | +========+ | 3 | +--------+ sql>select timeid from t1; +--------+ | timeid | +========+ | 1 | | 2 | | 3 | +--------+ sql>select * from t1; +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ sql> '''''''''''''''''''''''''''''''''''''''''''''''''''''' I could not see the 3rd row Regards, ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1958572&group_id=56967 ------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
