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