[MonetDB-users] Incorrect SQL update result

Christophe Galerne christophe.galerne at gmail.com
Wed Sep 3 22:39:03 CEST 2008


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 <Fabian.Groffen at cwi.nl>wrote:

> 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 at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20080903/a889dbe1/attachment.html>


More information about the users-list mailing list