[MonetDB-users] Incorrect SQL update result
Hi, I am running: # MonetDB server v5.6.0, based on kernel v1.24.0 # Serving database 'clg' # Compiled for i686-pc-win32/32bit with 32bit OIDs dynamically linked # Copyright (c) 1993-2008 CWI, all rights reserved # Visit http://monetdb.cwi.nl/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.24.0 loaded I am copying some sample SQL which illustrates what I believe is a bug: ################################################################################ CREATE TABLE SOURCE ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE ); INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0); INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0); CREATE TABLE TARGET ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE ); INSERT INTO TARGET VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0); UPDATE TARGET SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE TOTO=SOURCE.TOTO and TITI=SOURCE.TITI); CREATE TABLE TARGETOS ( TATA VARCHAR(5), TUTU VARCHAR(5), VALUE DOUBLE ); INSERT INTO TARGETOS VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0); UPDATE TARGETOS SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE SOURCE.TOTO=TATA and SOURCE.TITI=TUTU); ################################################################################ The expected results is for the tables TARGET and TARGETOS to contain the same result since they only differ by the name of their columns. It seems that monetdb gets confused by the fact that table SOURCE and table TARGET have the column names. Thanks, Christophe
Hi Christophe, Thanks for your interest in MonetDB. On 02-09-2008 23:38:56 +0200, Christophe Galerne wrote:
I am copying some sample SQL which illustrates what I believe is a bug: ####################################################################### ######### CREATE TABLE SOURCE ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE );
INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0); INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0);
CREATE TABLE TARGET ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE );
INSERT INTO TARGET VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0);
UPDATE TARGET SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE TOTO= SOURCE.TOTO and TITI=SOURCE.TITI);
CREATE TABLE TARGETOS ( TATA VARCHAR(5), TUTU VARCHAR(5), VALUE DOUBLE );
INSERT INTO TARGETOS VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0);
UPDATE TARGETOS SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE SOURCE.TOTO=TATA and SOURCE.TITI=TUTU); ####################################################################### #########
The expected results is for the tables TARGET and TARGETOS to contain the same result since they only differ by the name of their columns. It seems that monetdb gets confused by the fact that table SOURCE and table TARGET have the column names.
What is the error you're seeing? You describe what you expect to see, but not what actually comes out.
Fabian,
Here are my results:
sql>select * from target;
+------+-----+-----------------------+
| toto |titi |value |
+======+=====+=======================+
| 1 |1 | 48 |
| 1 |2 | 48 |
| 2 |1 | 48 |
| 2 |2 | 48 |
| 2 |3 | 48 |
+------+-----+-----------------------+
Timer 47.000 msec 5 rows
sql>select * from targetos;
+------+-----+-----------------------+
| tata |tutu |value |
+======+=====+=======================+
| 1 |1 | 10 |
| 1 |2 | 8 |
| 2 |1 | 10 |
| 2 |2 | 10 |
| 2 |3 | 10 |
+------+-----+-----------------------+
Timer 63.000 msec 5 rows
Basically in the first query, monetdb doesn't correctly join the source
table with the target table
but it does on the second.
On Wed, Sep 3, 2008 at 9:41 AM, Fabian Groffen
Hi Christophe,
Thanks for your interest in MonetDB.
On 02-09-2008 23:38:56 +0200, Christophe Galerne wrote:
I am copying some sample SQL which illustrates what I believe is a bug: ####################################################################### ######### CREATE TABLE SOURCE ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE );
INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0); INSERT INTO SOURCE VALUES('1', '1', 5.0) , ('1', '2', 4.0), ('2', '1', 5.0), ('2', '2', 5.0), ('2', '3', 5.0);
CREATE TABLE TARGET ( TOTO VARCHAR(5), TITI VARCHAR(5), VALUE DOUBLE );
INSERT INTO TARGET VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0);
UPDATE TARGET SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE TOTO= SOURCE.TOTO and TITI=SOURCE.TITI);
CREATE TABLE TARGETOS ( TATA VARCHAR(5), TUTU VARCHAR(5), VALUE DOUBLE );
INSERT INTO TARGETOS VALUES('1', '1', 0) , ('1', '2', 0), ('2', '1', 0), ('2', '2', 0), ('2', '3', 0);
UPDATE TARGETOS SET VALUE = ( SELECT SUM(VALUE) FROM SOURCE WHERE SOURCE.TOTO=TATA and SOURCE.TITI=TUTU); ####################################################################### #########
The expected results is for the tables TARGET and TARGETOS to contain the same result since they only differ by the name of their columns. It seems that monetdb gets confused by the fact that table SOURCE and table TARGET have the column names.
What is the error you're seeing? You describe what you expect to see, but not what actually comes out.
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
Christophe Galerne -
Fabian Groffen