Hi There,
I have some problem with the row_number() over() function.
Consider the following table:
create table "table1" ("customer" varchar(40), "product" varchar(40),
"price" double)
insert into "table1" values ('cust1', 'p1', 100)
insert into "table1" values ('cust1', 'p2', 200)
insert into "table1" values ('cust1', 'p3', 150)
insert into "table1" values ('cust2', 'p1', 300)
insert into "table1" values ('cust2', 'p3', 200)
The following query over this table:
SELECT "customer",
"product",
"sumprice",
(Row_number() OVER(PARTITION BY "customer" ORDER BY "sumprice")) as
"rank"
FROM ( SELECT "customer",
"product",
(Sum("price")) AS "sumprice"
FROM "table1"
GROUP BY "customer",
"product") AS "temp"
Returns:
Customer product sumprice rank
Cust1 p1 100 1
Cust1 p2 200 2
Cust1 p3 150 3
Cust2 p1 300 1
Cust2 p3 200 2
But doesn’t it suppose to return the following result set?:
Customer product sumprice rank
Cust1 p1 100 1
Cust1 p3 150 2
Cust1 p2 200 3
Cust2 p3 200 1
Cust2 p1 200 2
Thanks
--
Leonard Forgge
--
View this message in context: http://www.nabble.com/Possible-wrong-results-when-Querying-tp22821802p22821…
Sent from the monetdb-users mailing list archive at Nabble.com.
Hi,
I've got a very simple table structure in SQL, with a non-unique
varchar field and a few other fields including a CLOB.
I found that it's abnormally slow to filter the varchar field with an
'IN' statement and simply return the CLOB.
i.e.:
select clob from x where varchar in ('a','b','c')
With a couple million rows, the query is in multiple seconds if the
results are around a couple thousand rows. Is there something I'm
doing wrong?
I should be just the varchar BAT filter, then join on the CLOB BAT.
Should I manually assign the varchars to integers in another table and
use ints on this table? That would be surprising...
Thanks.
H
Hi all,
I've been testing MonetDB and I've been getting strange memory
allocation errors while running large 'group by' queries. From a dataset
of around 60GB, I'm running 'group by' queries against it to test for
speed. Queries of the sort:
select keyword, count(*) as c
from raw_data
group by keyword
order by c desc
limit 100;
What's happening is that it will trundle along ok for a while, generally
consuming all the available memory (98%) on 3GB box. But after a while I
get these messages on the server:
# Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
# MonetDB/SQL module v2.28.4 loaded
>#GDKmmap(10412883968) fails, try to free up space [memory in
use=72431192,virtual memory in use=71134150656]
#GDKmmap(10412883968) result [mem=71387384,vm=71134150656]
#GDKvmalloc(10412883984) fails, try to free up space [memory in
use=71386904,virtual memory in use=71134150656]
#GDKvmalloc(10412883984) result [mem=71130480,vm=71134150656]
!ERROR: HEAPalloc: Insufficient space for HEAP of 10412883968 bytes.
Also while checking top it will reports a much large RES size than
physical memory. E.g. (250% for %MEM).
I haven't changed any out of the box settings. Is there a minimum memory
size that MonetDB requires to play nice? Is there anywhere I can go to
limit the amount of memory MonetDB requires?
The version I'm using is:
mserver5 --version
MonetDB server v5.10.4 (64-bit), based on kernel v1.28.4 (64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Configured for prefix: /usr
Libraries:
libpcre: 6.6 06-Feb-2006 (compiled with 6.6)
openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL
0.9.8e-fips-rhel5 01 Jul 2008)
libxml2: 2.6.26 (compiled with 2.6.26)
Compiled by: mockbuild(a)surya.karan.org
Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -std=c99
-O6 -fomit-frame-pointer -finline-functions
-falign-loops=4 -falign-jumps=4 -falign-functions=4
-fexpensive-optimizations -funroll-loops
-frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize
Linking : ld -IPA -m elf_x86_64
Cheers
Hi all,
I'm having problems with MonetDB5 while performing LIKE queries against
it. Normal queries work fine, but anything with a LIKE in it gets this
response:
sql> select * from raw_data where keyword like 'apple%' limit 1;
!MALException:batcalc.str:can not create bat
0 tuples
Any ideas?
This is the version of mserver5 I'm using:
MonetDB server v5.10.4 (64-bit), based on kernel v1.28.4 (64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Configured for prefix: /usr
Libraries:
libpcre: 6.6 06-Feb-2006 (compiled with 6.6)
openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL
0.9.8e-fips-rhel5 01 Jul 2008)
libxml2: 2.6.26 (compiled with 2.6.26)
Compiled by: mockbuild(a)surya.karan.org
Compilation: gcc -O2 -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -std=c99
-O6 -fomit-frame-pointer -finline-functions
-falign-loops=4 -falign-jumps=4 -falign-functions=4
-fexpensive-optimizations -funroll-loops
-frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize
Linking : ld -IPA -m elf_x86_64
Cheers
Hello,
I have problems starting the monetDB xquery server under windowsXP sp3. When
I click on the link in the start menu, dos window appears but then it closes
immediatelly and I can't see what's there (after pausing I can see only the
first 6 lines of the program output as described below, then after unpausing
it immediatelly quits and closes the window). If I run MXQserver.bat, or
Mserver.bat --dbinit="module(pathfinder);" I get only this:
# MonetDB Server v4.28.4
# based on GDK v1.28.4
# Copyright (c) 1993-July 2008, CWI. All rights reserved.
# Copyright (c) August 2008-2009, MonetDB B.V.. All rights reserved.
# Compiled for i686-pc-win32/32bit with 32bit OIDs; dynamically linked.
# Visit http://monetdb.cwi.nl/ for further information.
# PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah
That's all. The server immediatelly ends. However If I run only Mserver.bat,
the server "runs" - I get the prompt, but for example I can't connect to the
web interface on the port 50000.
Does anyone have any advice what to do? How to make it work?
Any advice appreciated,
Best regards
Michael
I'm having problems connecting to a MonetDB 5 SQL database via the
Python interface. I am working on a Mac OS X (10.5.6) system, with
python 2.5.1. I downloaded and compiled the Feb 2009 SP2 release from
source and added the appropriate directory to my PYTHONPATH. When
running the connect command, I get some errors:
>>> import MonetSQLdb
>>> x = MonetSQLdb.connect(host='greensburg.local',user='scott',password='notmyrealpassword',lang='sql',dbname='HistoricalData')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "MonetSQLdb/__init__.py", line 131, in __init__
self._mapi = Mapi(kwargs['host'], kwargs['port'], kwargs['user'],
kwargs['password'], kwargs['lang'], kwargs['dbname'])
File "MonetDB/CMapi.py", line 30, in __init__
raise IOError(self.error_str())
IOError: !InvalidCredentialsException:checkCredentials:Invalid
credentials for user 'scott'
I have checked and double checked that my user name and password are
correct: I am able to login to the database from the same machine
using SQL Explorer in Eclipse with the same credentials. The
connection shows up in the merovingian log on the server as being
proxied to the appropriate databse. The weird thing is, it doesn't
show the connection ending until I quit() the python interactive
session, or try connecting again, or do something else with the
connection object (c = x.cursor() also causes a disconnect, and thows
an exception that complains that x is not defined).
Ideas anyone? The only thing I can think of that may be wrong is I'm
not using the same releases of MonetDB for the python library and the
server. mserver5 --version says I'm running MonetDB server v5.6.0
based on kernel v1.24.0 (I can't seem to remember/figure out which
named release this corresponds to). Do the versions need to be the
same?
Thanks,
Scott
Hi,
Last year I reported problems with inserting non-ascii data using ODBC
interface. I submitted a bug #2014118 and it was resolved. At least I
hope so.
Unfortunately I had no time to play with MonetDB and had no opportunity
to use it for real data. It has recently changed so I'm back with testing.
I have to report that the problem is not resolved, at least it's back in
Windows 32-bit Feb2009-SP2 release.
I have a problem with using queries with properly encoded utf-8 data.
The same data inserted using Python bindings is perfect.
I have noticed that broken "LIKE" queries give proper result on broken
inserted data. It means that the same characters are being recognized.
ODBC driver version is 1.28.04.01
Regards,
Pawel Lewicki
My monetdb has been constantly crashing in the past several days and I
am not able to even get to the SQL prompt of my database. Its just
hanging
Some information
MSG DataBase[29614]: # Compiled for x86_64-unknown-linux-gnu/64bit
with 64bit OIDs dynamically linked
-bash-3.2$ /home/dbuser/MonetDB/bin/monetdb status
name state uptime health last crash
DataBase running 2h 34m 33s 40%, 2h 2009-05-12 16:49:50
demo stopped 100%, 4s -
DataBase.1 stopped 100%, 2h -
-bash-3.2$ /home/dbuser/MonetDB/bin/monetdb version
MonetDB Database Server Toolkit v0.4
MSG merovingian[18807]: database 'DataBase' already running since
2009-05-12 16:52:20, up min/avg/max: 8609/8609/8609, crash average:
0.00 0.30 0.10 (5-1=3)
MSG merovingian[18807]: proxying client 127.0.0.1:38462 for database
'DataBase' to mapi:monetdb://server01:50001/
MSG merovingian[18807]: client has 127.0.0.1:38462 disconnected from proxy
Anything I should do to fix this?
TIA
