some questions regarding queries in MonetDB
|
View:
New views
12 Messages
—
Rating Filter:
Alert me
|
 |
some questions regarding queries in MonetDB
by Freddy Priyatna-2
::
Rate this Message:
Dear all,
I've been using MonetDB version 5 on Ubuntu machine for evaluating BSBM queries. The dataset and queries can be found here[1]. It seems that there are some queries that are not supported on MonetDB :
1) SELECT p.nr, p.label FROM product p, producttypeproduct ptp
WHERE p.nr=ptp.product AND "productType"=35
AND "propertyNum1">500 AND "propertyNum3"<2000
AND 168 IN (SELECT "productFeature" FROM productfeatureproduct WHERE productfeatureproduct.product=p.nr)
AND 147 NOT IN (SELECT "productFeature" FROM productfeatureproduct WHERE product=p.nr)
From the error, the subquery seems can't refer to the table alias "p". 2) SELECT distinct p.nr, p.label
FROM product p, product po, (Select distinct pfp1.product FROM productfeatureproduct pfp1,
(SELECT "productFeature" FROM productfeatureproduct WHERE product=2) pfp2 WHERE pfp2."productFeature"=pfp1."productFeature") pfp
AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > (po."propertyNum1"-120)
AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > (po."propertyNum2"-170)
This is strange, because if I just comment one of the last 2 lines, the query works. But the last two lines can't be used together. 3) SELECT nr, label
FROM product WHERE label like "%countries%"; It seems that MonetDB doesn't support regex.
4) SELECT * FROM (select label from product where nr=2) p left join
o.vendor=v.nr AND v.country='GB' AND o."validTo">'2008-07-01') ov right join (select r.nr as rnr, r.title, pn.nr as pnnr, pn.name, r.rating1, r.rating2 from review r, person pn where r.product=2 AND
r.person=pn.nr) rpn on (1=1)) on (1=1); It seems that MonetDB can't do join with true condition because when I tried to run a simple query :
SELECT * FROM product p RIGHT JOIN offer o ON (1=1) Then MonetDB gives an error. Could anyone please help me how to run the queries above?
Many thanks in advance. Freddy ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Fabian Groffen
::
Rate this Message:
On 06-03-2012 14:31:37 +0100, Freddy Priyatna wrote:
> 3) SELECT nr, label > > FROM product > > WHERE label like "%countries%"; > > It seems that MonetDB doesn't support regex. Use used double quotes by mistake. ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna
::
Rate this Message:
Dear Fabian,
I use single quotes now and it works. Thanks. Do you have any idea wrt the other queries? Regards Freddy
2012/3/6 Fabian Groffen <Fabian.Groffen@...>
------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Ying Zhang-6
::
Rate this Message:
Hello Freddy,
On Tue, Mar 06, 2012 at 02:31:37PM +0100, Freddy Priyatna wrote: > Dear all, > > I've been using MonetDB version 5 on Ubuntu machine for evaluating BSBM Which monetdb version are you using ('5' is not the version of the software package)? Can you please send us the output of the command 'mserver5 --version'? > queries. The dataset and queries can be found here[1]. Can you please give us the CREATE TABLE statements? We can not run your queries without creating the tables first. > > It seems that there are some queries that are not supported on MonetDB : > 1) SELECT p.nr, p.label > FROM product p, producttypeproduct ptp > WHERE p.nr=ptp.product > AND "productType"=35 > AND "propertyNum1">500 > AND "propertyNum3"<2000 > AND 168 IN (SELECT "productFeature" FROM productfeatureproduct WHERE > productfeatureproduct.product=p.nr) > AND 147 NOT IN (SELECT "productFeature" FROM productfeatureproduct WHERE > product=p.nr) > >From the error, the subquery seems can't refer to the table alias "p". > > 2) SELECT distinct p.nr, p.label > FROM product p, product po, > (Select distinct pfp1.product FROM productfeatureproduct pfp1, > (SELECT "productFeature" FROM productfeatureproduct WHERE product=2) > pfp2 > WHERE pfp2."productFeature"=pfp1."productFeature") pfp > WHERE p.nr=pfp.product AND po.nr=2 AND p.nr <> po.nr > AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > > (po."propertyNum1"-120) > AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > > (po."propertyNum2"-170) > This is strange, because if I just comment one of the last 2 lines, the > query works. But the last two lines can't be used together. > > 3) SELECT nr, label > FROM product > WHERE label like "%countries%"; > It seems that MonetDB doesn't support regex. > > 4) SELECT * > FROM (select label from product where nr=2) p left join > ((select o.nr as onr, o.price, v.nr as vnr, v.label from offer o, > vendor v where 2=o.product AND > o.vendor=v.nr AND v.country='GB' AND o."validTo">'2008-07-01') ov > right join > (select r.nr as rnr, r.title, pn.nr as pnnr, pn.name, r.rating1, > r.rating2 from review r, person pn where r.product=2 AND > r.person=pn.nr) rpn on (1=1)) on (1=1); > It seems that MonetDB can't do join with true condition because when I > tried to run a simple query : > SELECT * FROM product p RIGHT JOIN offer o ON (1=1) > Then MonetDB gives an error. With the latest release, i.e., Dec2011-SP1, this simple query seems working: $ mclient Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.7.8 (unreleased), 'demo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>create table t1 (x int, y int, v float); operation successful (25.398ms) sql>create table t2 (x int, y int, v float); operation successful (26.987ms) sql>SELECT * FROM t1 t3 RIGHT JOIN t2 t4 ON (1=1); +---+---+---+---+---+---+ | x | y | v | x | y | v | +===+===+===+===+===+===+ +---+---+---+---+---+---+ 0 tuples (4.595ms) Regards, Jennie > > Could anyone please help me how to run the queries above? > > Many thanks in advance. > Freddy > > > > > [1] > http://www4.wiwiss.fu-berlin.de/bizer/BerlinSPARQLBenchmark/spec/ExploreUseCase/index.html#queriesRelational > ------------------------------------------------------------------------------ > Keep Your Developer Skills Current with LearnDevNow! > The most comprehensive online learning library for Microsoft developers > is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, > Metro Style Apps, more. Free future releases when you subscribe now! > http://p.sf.net/sfu/learndevnow-d2d > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@... > https://lists.sourceforge.net/lists/listinfo/monetdb-users ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Stefan Manegold
::
Rate this Message:
Hi Freddy,
On Tue, Mar 06, 2012 at 03:03:27PM +0100, Ying Zhang wrote: > Hello Freddy, > > > > On Tue, Mar 06, 2012 at 02:31:37PM +0100, Freddy Priyatna wrote: > > Dear all, > > > > I've been using MonetDB version 5 on Ubuntu machine for evaluating BSBM > > Which monetdb version are you using ('5' is not the version of the > software package)? Can you please send us the output of the command > 'mserver5 --version'? > > > queries. The dataset and queries can be found here[1]. > > Can you please give us the CREATE TABLE statements? We can not run your > queries without creating the tables first. In addition or as initial alternative, it would be very helpful for us, if you could share the excat error messages with us. Thanks! Stefan -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna
::
Rate this Message:
Dear Jenni,
- Here you have the output of 'mserver5 --version' MonetDB 5 server v11.7.5 "Dec2011" (32-bit, 32-bit oids) Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 3.4GiB available memory, 2 available cpu cores
Libraries: libpcre: 8.12 2011-01-15 (compiled with 8.12) openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e 6 Sep 2011) libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: root@... (i686-pc-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -O2 -Wall Linking : /usr/bin/ld -Wl,-Bsymbolic-functions
- The DDL can be download from the BSBM site I gave the link on the previous email. However, here you have the file : http://dl.dropbox.com/u/531378/bsbm/bsbm250k-monetdb-ddl.zip
Regards Freddy 2012/3/6 Ying Zhang <Y.Zhang@...> Hello Freddy, ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna-2
::
Rate this Message:
Dear Stefan,
You are right, here you have the error message for each query : 1) SELECT p.nr, p.label
FROM product p, producttypeproduct ptp WHERE p.nr=ptp.product
AND "productType"=35 AND "propertyNum1">500
AND "propertyNum3"<2000 AND 168 IN (SELECT "productFeature" FROM productfeatureproduct WHERE productfeatureproduct.product=p.nr)
AND 147 NOT IN (SELECT "productFeature" FROM productfeatureproduct WHERE product=p.nr)
FROM product p, product po, (Select distinct pfp1.product FROM productfeatureproduct pfp1,
(SELECT "productFeature" FROM productfeatureproduct WHERE product=2) pfp2 WHERE pfp2."productFeature"=pfp1."productFeature") pfp
AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > (po."propertyNum1"-120)
AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > (po."propertyNum2"-170)
Error: TypeException:user.s945_1[155]:'algebra.join' undefined in: _267:bat[:oid,:int] := algebra.join(_266:bat[:oid,:void], _115:bat[:oid,:int])
39000!program contains errors SQLState: null ErrorCode: 0 4) SELECT *
FROM (select label from product where nr=2) p left join
o.vendor=v.nr AND v.country='GB' AND o."validTo">'2008-07-01') ov right join
(select r.nr as rnr, r.title, pn.nr as pnnr, pn.name, r.rating1, r.rating2 from review r, person pn where r.product=2 AND
r.person=pn.nr) rpn on (1=1)) on (1=1);
. . . TypeException:user.s946_1[472]:'algebra.leftjoin' undefined in: _639:any := algebra.leftjoin(_324:any, _638:any)
TypeException:user.s946_1[474]:'bat.append' undefined in: _641:any := bat.append(_640:bat[:oid,:int], _639:any, _637:bit) TypeException:user.s946_1[475]:'bat.append' undefined in: _642:any := bat.append(_641:any, _614:any, _637:bit)
39000!program contains errors SQLState: null ErrorCode: 0 Thanks in advance Freddy 2012/3/6 Stefan Manegold <Stefan.Manegold@...> Hi Freddy, ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna-2
::
Rate this Message:
Dear Stefan,
If this may help, I am using Squirrel SQL client version 3.3.0 and monetdb-jdbc-2.1.jar I have sent the error message on my previous email. Thank you and regards
Freddy 2012/3/6 Stefan Manegold <Stefan.Manegold@...> Hi Freddy, ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Fabian Groffen
::
Rate this Message:
Freddy,
On 06-03-2012 15:18:03 +0100, Freddy Priyatna wrote: > Error: 42S22!SELECT: no such column '[5]p.nr' > > SQLState: null > > ErrorCode: 0 Off-topic, but what client do you use that produces this output? ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna
::
Rate this Message:
Dear Fabian
I am using Squirrel SQL client version 3.3.0 and monetdb-jdbc-2.1.jar Regards
Freddy
2012/3/6 Fabian Groffen <Fabian.Groffen@...> Freddy, ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Stefan Manegold
::
Rate this Message:
Freddy,
(2) & (4) seems to be bugs, plealse file bugs reports via bugs.monetdb.org . I'm not quite sure about (1). Just in case, file a bug report as well, and we'll see. Thanks, Stefan On Tue, Mar 06, 2012 at 03:18:03PM +0100, Freddy Priyatna wrote: > Dear Stefan, > > You are right, here you have the error message for each query : > 1) SELECT p.nr, p.label > FROM product p, producttypeproduct ptp > WHERE p.nr=ptp.product > AND "productType"=35 > AND "propertyNum1">500 > AND "propertyNum3"<2000 > AND 168 IN (SELECT "productFeature" FROM productfeatureproduct WHERE > productfeatureproduct.product=p.nr) > AND 147 NOT IN (SELECT "productFeature" FROM productfeatureproduct WHERE > product=p.nr) > > Error: 42S22!SELECT: no such column 'p.nr' > SQLState: null > ErrorCode: 0 > > 2)SELECT distinct p.nr, p.label > FROM product p, product po, > (Select distinct pfp1.product FROM productfeatureproduct pfp1, > (SELECT "productFeature" FROM productfeatureproduct WHERE product=2) > pfp2 > WHERE pfp2."productFeature"=pfp1."productFeature") pfp > WHERE p.nr=pfp.product AND po.nr=2 AND p.nr <> po.nr > AND p."propertyNum1" < (po."propertyNum1"+120) AND p."propertyNum1" > > (po."propertyNum1"-120) > AND p."propertyNum2" < (po."propertyNum2"+170) AND p."propertyNum2" > > (po."propertyNum2"-170) > > Error: TypeException:user.s945_1[155]:'algebra.join' undefined in: > _267:bat[:oid,:int] := algebra.join(_266:bat[:oid,:void], > _115:bat[:oid,:int]) > 39000!program contains errors > SQLState: null > ErrorCode: 0 > > 4) SELECT * > FROM (select label from product where nr=2) p left join > ((select o.nr as onr, o.price, v.nr as vnr, v.label from offer o, > vendor v where 2=o.product AND > o.vendor=v.nr AND v.country='GB' AND o."validTo">'2008-07-01') ov > right join > (select r.nr as rnr, r.title, pn.nr as pnnr, pn.name, r.rating1, > r.rating2 from review r, person pn where r.product=2 AND > r.person=pn.nr) rpn on (1=1)) on (1=1); > > . > . > . > TypeException:user.s946_1[472]:'algebra.leftjoin' undefined in: _639:any := > algebra.leftjoin(_324:any, _638:any) > TypeException:user.s946_1[474]:'bat.append' undefined in: _641:any := > bat.append(_640:bat[:oid,:int], _639:any, _637:bit) > TypeException:user.s946_1[475]:'bat.append' undefined in: _642:any := > bat.append(_641:any, _614:any, _637:bit) > 39000!program contains errors > SQLState: null > ErrorCode: 0 > > Thanks in advance > Freddy > > > 2012/3/6 Stefan Manegold <Stefan.Manegold@...> > > > Hi Freddy, > > > > On Tue, Mar 06, 2012 at 03:03:27PM +0100, Ying Zhang wrote: > > > Hello Freddy, > > > > > > > > > > > > On Tue, Mar 06, 2012 at 02:31:37PM +0100, Freddy Priyatna wrote: > > > > Dear all, > > > > > > > > I've been using MonetDB version 5 on Ubuntu machine for evaluating BSBM > > > > > > Which monetdb version are you using ('5' is not the version of the > > > software package)? Can you please send us the output of the command > > > 'mserver5 --version'? > > > > > > > queries. The dataset and queries can be found here[1]. > > > > > > Can you please give us the CREATE TABLE statements? We can not run your > > > queries without creating the tables first. > > > > In addition or as initial alternative, it would be very helpful for us, if > > you could share the excat error messages with us. > > > > Thanks! > > > > Stefan > > > > -- > > | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | > > | http://CWI.nl/~manegold/ | Science Park 123 (L321) | > > | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | > > > > > > ------------------------------------------------------------------------------ > > Keep Your Developer Skills Current with LearnDevNow! > > The most comprehensive online learning library for Microsoft developers > > is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, > > Metro Style Apps, more. Free future releases when you subscribe now! > > http://p.sf.net/sfu/learndevnow-d2d > > _______________________________________________ > > MonetDB-users mailing list > > MonetDB-users@... > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > ------------------------------------------------------------------------------ > Keep Your Developer Skills Current with LearnDevNow! > The most comprehensive online learning library for Microsoft developers > is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, > Metro Style Apps, more. Free future releases when you subscribe now! > http://p.sf.net/sfu/learndevnow-d2d > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@... > https://lists.sourceforge.net/lists/listinfo/monetdb-users -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
|
|
Re: some questions regarding queries in MonetDB
by Freddy Priyatna
::
Rate this Message:
Dear Stefan,
OK, thanks, I will report the bugs now. Regards Freddy
2012/3/6 Stefan Manegold <Stefan.Manegold@...> Freddy, ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ MonetDB-users mailing list MonetDB-users@... https://lists.sourceforge.net/lists/listinfo/monetdb-users |
signature.asc (498 bytes) | Free embeddable forum powered by Nabble | Forum Help |
