[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 Fabian.Groffen@cwi.nl
On 06-03-2012 14:31:37 +0100, Freddy Priyatna wrote:
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 :
- 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".
- 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.
- SELECT nr, label FROM product WHERE label like "%countries%";
It seems that MonetDB doesn't support regex.
- 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
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@cwi.nl
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 Fabian.Groffen@cwi.nl
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 :
- 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
- 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@cwi.nl
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
Dear Stefan,
OK, thanks, I will report the bugs now.
Regards Freddy
2012/3/6 Stefan Manegold Stefan.Manegold@cwi.nl
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 :
- 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
- 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@cwi.nl
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 Stefan.Manegold@cwi.nl
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 Y.Zhang@cwi.nl
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 :
- 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".
- 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.
- SELECT nr, label FROM product WHERE label like "%countries%";
It seems that MonetDB doesn't support regex.
- 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