Bugs item #2864313, was opened at 2009-09-22 15:26
Message generated for change (Comment added) made by sjoerd
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2864313&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: 9
Private: No
Submitted By: Stefan Manegold (stmane)
Assigned to: Niels Nes (nielsnes)
Summary: SQL: Update changes all columns with same type and default
Initial Comment:
When updating a not yet filled column, all columns of the same type and with
the same default get modified:
sql>CREATE TABLE "t0" (
more> "d0" double
more>);
Operation successful
sql>CREATE TABLE "t1" (
more> "d1" double,
more> "v1" varchar(255),
more> "v2" varchar(255)
more>);
Operation successful
sql>COPY 1025 RECORDS INTO t0 FROM '/tmp/err2.data' USING DELIMITERS '\t';
Rows affected 1025
sql>select count(*) from t0;
+-------+
| L1 |
+=======+
| 1025 |
+-------+
1 tuple
sql>INSERT INTO t1 (d1) SELECT d0 FROM t0;
Rows affected 1025
sql>select count(*) from t1;
+-------+
| L2 |
+=======+
| 1025 |
+-------+
1 tuple
sql>select d1, v2, v1 from t1 limit 1;
+------------------------+---------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+
| d1 | v2
| v1
|
+========================+=========================================================================================================+=========================================================================================================+
| 0 | null
| null
|
+------------------------+---------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+
1 tuple
sql>Update t1 Set v2 = 'AA';
Rows affected 1025
sql>select d1, v2, v1 from t1 limit 1;
+------------------------+-------+-------+
| d1 | v2 | v1 |
+========================+=======+=======+
| 0 | AA | AA |
+------------------------+-------+-------+
1 tuple
sql>Update t1 Set d1 = -1.2;
Rows affected 1025
sql>select d1, v2, v1 from t1 limit 1;
+------------------------+-------+-------+
| d1 | v2 | v1 |
+========================+=======+=======+
| -1.2 | AA | AA |
+------------------------+-------+-------+
1 tuple
sql>Update t1 Set v1 = 'BB';
Rows affected 1025
sql>select d1, v2, v1 from t1 limit 1;
+------------------------+-------+-------+
| d1 | v2 | v1 |
+========================+=======+=======+
| -1.2 | BB | BB |
+------------------------+-------+-------+
1 tuple
sql>drop table t1;
Operation successful
sql>drop table t0;
Operation successful
It works fine when bulkloading <= 1024 tuples, or when using different defaults
for all non-initialized columns, e.g.,
"v1" varchar(255) default 'x',
"v2" varchar(255) default 'y'
I will add a test to CVS.
----------------------------------------------------------------------
>Comment By: Sjoerd Mullender (sjoerd)
Date: 2009-09-22 22:24
Message:
I think the problem is in tr_update_delta in bat_storage.mx. There is a
line there
if (!BATcount(cur) && BATcount(ins) > snapshot_minsize){
which makes the difference between the 1024 and 1025 cases
(snapshot_minsize==1024).
The original BAT (cur) is empty in both the v1 and v2 columns, and the
inserted BAT is longer than 1024, so the then branch is taken where the BAT
representing the column is replaced by the inserted BAT.
But because the inserted BATs for the v1 and v2 columns are identical
(look at the explain output for the INSERT where the same BAT is appended
to both columns), the replaced BAT in the code in tr_udpate_delta is the
same BAT for both columns.
The subsequent UPDATE then updates the bat for the one column, but since
it is now shared, it affects the other columns as well.
One solution would be to remove the then case and always do the else case
which will append the values to the cur BAT (which initially are different
empty BATs).
Another solution wouls be to generate code to append different BATs to the
two columsn, thereby avoiding the problem.
----------------------------------------------------------------------
Comment By: Sjoerd Mullender (sjoerd)
Date: 2009-09-22 16:44
Message:
This needs to be fixed before the Aug2009-SP1 bug fix release.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2864313&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
