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.