[MonetDB-users] Incorrect SQL update result

Christophe Galerne christophe.galerne at gmail.com
Tue Sep 2 23:38:56 CEST 2008


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20080902/758aebd1/attachment.html>


More information about the users-list mailing list