[MonetDB-users] Incorrect SQL update result

Fabian Groffen Fabian.Groffen at cwi.nl
Wed Sep 3 09:41:52 CEST 2008


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.




More information about the users-list mailing list