This message was forwarded from developers-l...@monetdb.org. The MonetDB
mailing lists have moved to monetdb.org. Please subscribe to
developers-l...@monetdb.org, and unsubscribe from this list.
See: http://mail.monetdb.org/mailman/listinfo/developers-list
Send developers-list mailing list submissions to
developers-l...@monetdb.org
To subscribe or unsubscribe via the World Wide Web, visit
http://mail.monetdb.org/mailman/listinfo/developers-list
or, via email, send a message with subject or body 'help' to
developers-list-requ...@monetdb.org
You can reach the person managing the list at
developers-list-ow...@monetdb.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of developers-list digest..."
Today's Topics:
1. Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post (Niels Nes)
2. Announcement: New Oct2012 Feature release of MonetDB suite
(Fabian Groffen)
----------------------------------------------------------------------
Message: 1
Date: Mon, 22 Oct 2012 19:20:07 +0200
From: Niels Nes <niels....@cwi.nl>
To: "Communication channel for developers of the MonetDB suite."
<developers-l...@monetdb.org>
Subject: Re: ANSI SQL 2003 Extension OVER() appears to be missing in
MonetDB Click to flag this post
Message-ID: <20121022172007.ga8...@niels.cwi.nl>
Content-Type: text/plain; charset="utf-8"
On Thu, Oct 18, 2012 at 09:32:45PM +0000, James Becker III wrote:
> Niels?
>
> > The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
> > to add. The dense rank is and should be directly usable.
>
> Yes, I have tweaked that out of what I am working with currently.
>
> > Currently indeed the OVER isn't fully described in the documenation.
>
> Is how this is implemented in Monet documented at all? I can't find any
> reference to it.
>
>
> I have been testing things that work against other vendors DB's in Monet
> to get a feel for it with mixed results.
>
> If not, in which section of the ANSI documents might I find this bit of
> information?
OVER is in the windowed function section of the SQL standaard (section 6.10 in
the 2008 standard, ISO_9075-02-Foundation_20081)
Niels
>
> Thx.
>
> ~jj
>
> On 10/18/12 4:10 PM, "Niels Nes" <niels....@cwi.nl> wrote:
>
> >On Thu, Oct 18, 2012 at 04:25:46PM +0000, James Becker III wrote:
> >>Hi Niels,
> >>Thanks for the fast reply.
> >>The reason I thought that it wasn't supported is that I didn't find over
> >>in sys.functions.
> >>sql>select name from sys.functions where name like 'o%';
> >>+-----------------+
> >>| name |
> >>+=================+
> >>| or |
> >>| octet_length |
> >>| octet_length |
> >>| octet_length |
> >>| optimizer_stats |
> >>| optimizers |
> >>+-----------------+
> >>Is this the wrong place to look for this type of function?
> >
> >Well OVER isn't implemented as function more as a language feature. So
> >no you won't find the 'over' functions in the functions table.
> >>Before I inundate you fine people with my need to understand, is there
> >>any
> >>documentation for Monet's OVER implementation? This would save me from
> >>prematurely posting potentially na?ve questions like this to this list.
> >>Here is a sample of the code in vertica that needs to be ported. There
> >>are
> >>more complex examples, but for the spike, I simply need a fair sample
> >>query set to take Monet into a full POC phase of testing.
> >>SELECT
> >>col1
> >>,col2
> >>, FIRST_VALUE(school_name) over (partition by school_loc_code order by
> >>academic_year_code::integer desc)
> >> as school_name
> >>,blah?
> >>-- large array of nested subqueries
> >>-- then the dense_rank()over(
> >>, dense_rank()over(
> >> order by account_code
> >> , school_group_inst_code
> >> , school_loc_code
> >> , grade_code
> >> , subject_code
> >> , course_code
> >> , staff_sid
> >> , section_sid
> >> , student_sid
> >> , attribute_category_code
> >> , attribute_value_code) entity_num
> >>I have attached the entire query in it's unmodified vertica form for
> >>deeper analysis, if you are willing to give it a gander. It's kind of
> >>hairy, but the OVER() bits are rather discreet.
> >
> >The FIRST_VALUE isn't implemented, but shouldn't be all that difficult
> >to add. The dense rank is and should be directly usable.
> >
> >Currently indeed the OVER isn't fully described in the documenation.
> >
> >Niels
> >>Thanks again.
> >>~jj
> >>On 10/15/12 3:13 PM, "Niels Nes" <niels....@cwi.nl> wrote:
> >>>On Mon, Oct 15, 2012 at 05:55:10PM +0000, James Becker III wrote:
> >>>> Hello MonetDB Community.
> >>>>
> >>>> My name is James Becker, my friends call me Jaimi, and I am new to
> >>>> posting messages here, so I hope you will bear with me.
> >>>>
> >>>> I am conducting a POC on MonetDB to see if we can use it to replace
> >>>> Vertica for a large scale read mostly educational reporting
> >>>> application. So far things are showing promise, but the legacy reports
> >>>> lean heavily on OVER(), which doesn't appear to be part of the
> >>>>11.11.11
> >>>> release. Am I missing something, or is this the case?
> >>>>
> >>>> If anyone who has run into a similar limitations has any insights as
> >>>>to
> >>>> where I have made a wrong turn, or how to either implement OVER() in
> >>>> MonetDB using either MAL/C, or any suggestions for query work-arounds
> >>>> that would yield similar results, this would be greatly appreciated.
> >>>>
> >>>> Thx.
> >>>>
> >>>> ~jj
> >>>
> >>>
> >>>James
> >>>
> >>>OVER is supported, but no all of its functionality. Could specify which
> >>>functions you would like to use with over? Also which windowing
> >>>limitations would be required?
> >>>We sofar focused on the row_number and rank functions.
> >>>
> >>>Niels
> >>>
> >>>> _______________________________________________
> >>>> developers-list mailing list
> >>>> developers-l...@monetdb.org
> >>>> http://mail.monetdb.org/mailman/listinfo/developers-list
> >>>
> >>>
> >>>--
> >>>Niels Nes, Centrum Wiskunde & Informatica (CWI)
> >>>Science Park 123, 1098 XG Amsterdam, The Netherlands
> >>>room L3.14, phone ++31 20 592-4098 sip:4...@sip.cwi.nl
> >>>url: http://www.cwi.nl/~niels e-mail: niels....@cwi.nl
> >
> >
> >>_______________________________________________
> >>developers-list mailing list
> >>developers-l...@monetdb.org
> >>http://mail.monetdb.org/mailman/listinfo/developers-list
> >
> >
> >--
> >Niels Nes, Centrum Wiskunde & Informatica (CWI)
> >Science Park 123, 1098 XG Amsterdam, The Netherlands
> >room L3.14, phone ++31 20 592-4098 sip:4...@sip.cwi.nl
> >url: http://www.cwi.nl/~niels e-mail: niels....@cwi.nl
> >
>
> _______________________________________________
> developers-list mailing list
> developers-l...@monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list
--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14, phone ++31 20 592-4098 sip:4...@sip.cwi.nl
url: http://www.cwi.nl/~niels e-mail: niels....@cwi.nl
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: not available
URL:
<http://mail.monetdb.org/pipermail/developers-list/attachments/20121022/8da1c777/attachment.sig>
------------------------------
Message: 2
Date: Tue, 23 Oct 2012 11:50:11 +0200
From: Fabian Groffen <fab...@monetdb.org>
To: MonetDB Developers <developers-l...@monetdb.org>, MonetDB Users
<users-l...@monetdb.org>, MonetDB Announcements
<announce-l...@monetdb.org>
Subject: Announcement: New Oct2012 Feature release of MonetDB suite
Message-ID: <135098581131...@dev.monetdb.org>
Content-Type: text/plain; charset=utf-8
The MonetDB team at CWI/MonetDB BV is pleased to announce the
Oct2012 feature release of the MonetDB suite of programs.
More information about MonetDB can be found on our website at
<http://www.monetdb.org/>.
For details on this release, please see the release notes at
<http://www.monetdb.org/Downloads/ReleaseNotes>.
As usual, the download location is <http://dev.monetdb.org/downloads/>.
Due to a packaging problem on Debian and Ubuntu, no binary packages for
these systems exist. We apologise for the inconvenience. We expect
this problem to be resolved by the Oct2012-SP1 release.
Oct 2012 feature release
Build Environment *?Removed --enable-noexpand configure option.
Java Module *?Fixed problem with PreparedStatements and setXXX()
methods using column numbers instead of names, bug
#3158
Client Package *?mclient now accepts URIs as database to connect to.
*?all strings returned by python2 are unicode, removed
use_unicode option
*?python2 and 3 type convertion speed improvements
*?python2 uses new style objects now (bug #3104)
*?split python2 and python3
MonetDB5 Server *?Removed module attach since it wasn't used or even
tested.
*?The MAL-to-C Compiler (mcc) was removed. The code
wasn't tested and most likely non-functional.
Merovingian *?Fixed problem where monetdbd would refuse to startup
when discovery was set to false, bug #3155
MonetDB Common *?Removed the gdk_embedded (and embedded) option. The
code wasn't tested and most likely non-functional.
*?BAT-of-BATs is no longer allowed. It was already not
allowed to make these types of BATs persistent, but
now they can't be created at all anymore.
Bug Fixes *?3084: Timestamp arithmetic very slow (especially on
Windows)
*?3125: Python tests fail after recent Python API
changes
*?3155: Provide an option for setting discovery to
false
------------------------------
_______________________________________________
developers-list mailing list
developers-l...@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
End of developers-list Digest, Vol 2, Issue 9
*********************************************
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_sfd2d_oct
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
