[MonetDB-users] some questions regarding queries in MonetDB
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 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. 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/ExploreUseC...
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
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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/ExploreUseC...
------------------------------------------------------------------------------ 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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) |
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
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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
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@lists.sourceforge.net 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@lists.sourceforge.net 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) |
Dear Stefan,
OK, thanks, I will report the bugs now.
Regards
Freddy
2012/3/6 Stefan Manegold
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
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@lists.sourceforge.net 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@lists.sourceforge.net 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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
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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
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@dev.monetdb.org (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
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/ExploreUseC...
------------------------------------------------------------------------------
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@lists.sourceforge.net 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (5)
-
Fabian Groffen -
Freddy Priyatna -
Freddy Priyatna -
Stefan Manegold -
Ying Zhang