On Tue, May 26, 2015 at 10:32:14AM -0700, Raffael Marty wrote:

I am having some issues with the SQL syntax in MonetDB. Is it just really picky or am I doing something that is not SQL99 compatible?

Couple examples:

This query works:

select source_address, count(distinct destination_port) as distcount from flows group by source_address having count(distinct destination_port)>1 limit 10;

The following query doesn’t work. I am just trying to use the AS keyword to make things a bit more readable.

select source_address, count(distinct destination_port) as distcount from flows group by source_address having distcount>1 limit 10; SELECT: identifier 'distcount’ unknown

The MonetDB does a single pass over the query, given that the having needs to be handled before the selection part the names (aliases) aren't know yet.

I had numerous other issues where little things like quotes and such made a difference. For example, creating a table would not work like this:

CREATE TABLE foo (range text); syntax error, unexpected RANGE, expecting FOREIGN or PRIMARY or UNIQUE in: "create table foo (range"

RANGE is indeed a keyword on which we may need to relax the semantics a bit. Double quotes indeed solve this.

This works:

CREATE TABLE foo (“range” text);

Third example: (I could never get this working - tried removing the quotes, which made things worse)

sql>INSERT INTO ranges ("range", "title", "external", "description", "routeadvertised", "X", "Y", "Z", "N", "M", "O", "P", "Q", "R", "S", "T", "U", "V", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ) VALUES ('0.0.0.0/0', 'IPv4', ,

^^^ no value.. Which is likely the problem. Niels

'', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, '', '', '', '', '', 1, '1', '' ); syntax error, unexpected ',' in: "insert into ranges ("range", "title", "external", "description", "routeadvert"

Is there a statement length restriction? Same happens whether I use the mclient or go through Python code.

I am running version 11.19.11-20150

Thanks so much!

Raffael

—

Visual Analytics Workshop at #BlackHat US in August: http://bit.ly/1FN1W5e Raffael Marty ceo @ pixlcloud http://pixlcloud.com @raffaelmarty http://raffy.ch

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl