[MonetDB-users] SQLException:SQLengine:Program contains errors

newer
[MonetDB-users] Benefits of sorted...

older
Re: [MonetDB-users] Information...

Charalampos Nikolaou

17 Dec 2011 17 Dec '11

11:23 a.m.

Hi, I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y: CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE ( ( SELECT R.u, R.v, R.r FROM R INTERSECT SELECT R.u, R.v, R.r FROM R ) ); END; Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables. Thanks, Babis

Show replies by date

Charalampos Nikolaou

17 Dec 17 Dec

11:47 a.m.

Just to add the full error that I forgot to post earlier: sql>select * from refine(); SyntaxException:user.refine[0]:RETURN missing TypeException:user.s2_1[5]:'user.refine' undefined in: (_7:bat[:oid,:str], _8:bat[:oid,:str], _9:bat[:oid,:str]) := user.refine() SQLException:SQLengine:Program contains errors Babis On Sat, Dec 17, 2011 at 1:23 PM, Charalampos Nikolaou wrote:

...

Hi,

I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y:

CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE (

( SELECT R.u, R.v, R.r FROM R

INTERSECT

SELECT R.u, R.v, R.r FROM R ) ); END;

Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables.

Thanks, Babis

Stefan Manegold

12:57 p.m.

Hi Babis, this works fine with me with MonetDB Aug2011-SP3: sql>create table r (u varchar(32), v varchar(32), r varchar(5)); operation successful (192.387ms) sql>CREATE FUNCTION refine () more>RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) more>BEGIN more> RETURN TABLE ( more> more> ( SELECT R.u, R.v, R.r FROM R more> more> INTERSECT more> more> SELECT R.u, R.v, R.r FROM R more> ) more> ); more>END; operation successful (87.109ms) sql>select * from refine(); +---+---+---+ | u | v | r | +===+===+===+ +---+---+---+ 0 tuples (73.457ms) sql>drop function refine; operation successful (71.159ms) sql>select * from refine(); SELECT: no such operator 'refine' As you can also see above, the prober way to drop a function definitions to use (AFAIK) standard SQL "DROP FUNCTION <functionname>;". Stefan On Sat, Dec 17, 2011 at 01:23:08PM +0200, Charalampos Nikolaou wrote:

...

Hi,

I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y:

CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE (

( SELECT R.u, R.v, R.r FROM R

INTERSECT

SELECT R.u, R.v, R.r FROM R ) ); END;

Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables.

Thanks, Babis

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Charalampos Nikolaou

2:10 p.m.

On Sat, Dec 17, 2011 at 2:57 PM, Stefan Manegold wrote:

...

Hi Babis,

this works fine with me with MonetDB Aug2011-SP3:

sql>create table r (u varchar(32), v varchar(32), r varchar(5)); operation successful (192.387ms) sql>CREATE FUNCTION refine () more>RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) more>BEGIN more> RETURN TABLE ( more> more> ( SELECT R.u, R.v, R.r FROM R more> more> INTERSECT more> more> SELECT R.u, R.v, R.r FROM R more> ) more> ); more>END; operation successful (87.109ms) sql>select * from refine(); +---+---+---+ | u | v | r | +===+===+===+ +---+---+---+ 0 tuples (73.457ms) sql>drop function refine; operation successful (71.159ms) sql>select * from refine(); SELECT: no such operator 'refine'

Hmm, I am working on the default branch (for my bad luck, for your good :-) ) on which it doesn't work. As for the August branch, it works. Should I report a bug, then?

...

As you can also see above, the prober way to drop a function definitions to use (AFAIK) standard SQL "DROP FUNCTION <functionname>;".

Bad I didn't know that. Thanks, Babis

...

Stefan

On Sat, Dec 17, 2011 at 01:23:08PM +0200, Charalampos Nikolaou wrote:

...
Hi,

I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y:

CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE (

( SELECT R.u, R.v, R.r FROM R

INTERSECT

SELECT R.u, R.v, R.r FROM R ) ); END;

Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables.

Thanks, Babis

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ 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) |

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Fabian Groffen

2:20 p.m.

On 17-12-2011 16:10:47 +0200, Charalampos Nikolaou wrote:

...

Hmm, I am working on the default branch (for my bad luck, for your good :-) ) on which it doesn't work. As for the August branch, it works. Should I report a bug, then?

Unless you have a good reason to do so, I would strongly suggest to stick to release and candidate branches. Dec2011 is the best pick at the moment.

Stefan Manegold

2:42 p.m.

On Sat, Dec 17, 2011 at 04:10:47PM +0200, Charalampos Nikolaou wrote:

...

On Sat, Dec 17, 2011 at 2:57 PM, Stefan Manegold wrote:

...
Hi Babis,

this works fine with me with MonetDB Aug2011-SP3:

sql>create table r (u varchar(32), v varchar(32), r varchar(5)); operation successful (192.387ms) sql>CREATE FUNCTION refine () more>RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) more>BEGIN more> RETURN TABLE ( more> more> ( SELECT R.u, R.v, R.r FROM R more> more> INTERSECT more> more> SELECT R.u, R.v, R.r FROM R more> ) more> ); more>END; operation successful (87.109ms) sql>select * from refine(); +---+---+---+ | u | v | r | +===+===+===+ +---+---+---+ 0 tuples (73.457ms) sql>drop function refine; operation successful (71.159ms) sql>select * from refine(); SELECT: no such operator 'refine'

Hmm, I am working on the default branch (for my bad luck, for your good :-) ) on which it doesn't work. As for the August branch, it works. Should I report a bug, then?

yes, if it doesn't work (as expected) it's a bug, so please file it as such. In fact, is appears the problem also oocurs with the Dec2011 release branch. Stefan

...

...
As you can also see above, the prober way to drop a function definitions to use (AFAIK) standard SQL "DROP FUNCTION <functionname>;".

Bad I didn't know that.

Thanks, Babis

...
Stefan

On Sat, Dec 17, 2011 at 01:23:08PM +0200, Charalampos Nikolaou wrote:

...
Hi,

I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y:

CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE (

( SELECT R.u, R.v, R.r FROM R

INTERSECT

SELECT R.u, R.v, R.r FROM R ) ); END;

Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables.

Thanks, Babis

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ 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) |

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

4760

Age (days ago)

4760

Last active (days ago)

List overview

Download

5 comments

3 participants

participants (3)

Charalampos Nikolaou
Fabian Groffen
Stefan Manegold