Merge table error with one table

newer
Internals of intermediate results...

older
Recommended compiler version and...

Ioannis Foufoulas

16 Sep 2020 16 Sep '20

12:34 p.m.

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table. Best, Yannis

Attachments:

attachment.html (text/html — 970 bytes)

Show replies by date

Joeri van Ruth

16 Sep 16 Sep

12:44 p.m.

Hi Yannis! Merge tables are allowed to contain only a single partition. For example, sql>*create table part(i int);* create table part(i int); operation successful sql>*create merge table merg(like part)*; create merge table merg(like part); operation successful sql>*alter table merg add table part;* alter table merg add table part; operation successful sql>*insert into part values (42);* insert into part values (42); 1 affected row sql>*select * from merg;* select * from merg; +------+ | i | +======+ | 42 | +------+ 1 tuple I've seen the kind error message you get before, it is probably related to an internal bug which mangles the query when it's sent to the remote host. Could you create a SQL script that reproduces the bug? There is no need for data, the CREATE statements on both hosts and the SELECT query are probably enough. If so, please file a bug on the MonetDB issue tracker at https://www.monetdb.org/bugzilla/. Best regards, Joeri van Ruth On 9/16/20 2:34 PM, Ioannis Foufoulas wrote:

...

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table.

Best, Yannis

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

Ioannis Foufoulas

17 Sep 17 Sep

11:40 a.m.

Hi, Some more info on this error, finally this is does not related to merge tables but to remote tables. Steps to reproduce: In one server: create table lala(sx float, sxx float, sxy float , sy float, syy float, n int); insert into lala select 13,85,98,15,113,2; In another remote server: CREATE REMOTE TABLE fofo(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) on 'mapi:monetdb://88.197.53.136:9999/voc10/sys/lala'; CREATE or replace FUNCTION mudf(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) RETURNS TABLE(res FLOAT) LANGUAGE PYTHON { return 0.5 }; After that I am getting the following: sql>select * from mudf((select * from fofo)); (mapi:monetdb://monetdb@88.197.53.136/voc10) Table: missing ‘)' If I materialise the remote table into a local table, and then use the udf it runs: sql>create table localtable as select * from fofo; operation successful sql>select * from mudf((select * from localtable)); +--------------------------+ | res | +==========================+ | 0.5 | +--------------------------+ 1 tuple sql> Best, Yannis

...

On 16 Sep 2020, at 3:34 PM, Ioannis Foufoulas wrote:

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table.

Best, Yannis

Joeri van Ruth

12:58 p.m.

Hi Ioannis! This is a great reproducer. I tried it on Oct2020 and I see the same behaviour. Could you file it as a new bug at https://www.monetdb.org/bugzilla/? If you prefer I can do it on your behalf. Best regards, Joeri On 9/17/20 1:40 PM, Ioannis Foufoulas wrote:

...

Hi,

Some more info on this error, finally this is does not related to merge tables but to remote tables. Steps to reproduce:

In one server: create table lala(sx float, sxx float, sxy float , sy float, syy float, n int); insert into lala select 13,85,98,15,113,2;

In another remote server: CREATE REMOTE TABLE fofo(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) on 'mapi:monetdb://88.197.53.136:9999/voc10/sys/lala';

CREATE or replace FUNCTION mudf(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) RETURNS TABLE(res FLOAT) LANGUAGE PYTHON { return 0.5 };

After that I am getting the following: sql>select * from mudf((select * from fofo)); (mapi:monetdb://monetdb@88.197.53.136/voc10) Table: missing ‘)'

If I materialise the remote table into a local table, and then use the udf it runs:

sql>create table localtable as select * from fofo; operation successful sql>select * from mudf((select * from localtable)); +--------------------------+ | res | +==========================+ | 0.5 | +--------------------------+ 1 tuple sql>

Best, Yannis

...
On 16 Sep 2020, at 3:34 PM, Ioannis Foufoulas mailto:johnfouf@di.uoa.gr> wrote:

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table.

Best, Yannis

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

ferreira

1:13 p.m.

The issue is the relation read code doesn't handle table returning functions. I'm adding it. Best regards, Pedro On 9/17/20 2:58 PM, Joeri van Ruth wrote:

...

Hi Ioannis!

This is a great reproducer. I tried it on Oct2020 and I see the same behaviour.

Could you file it as a new bug at https://www.monetdb.org/bugzilla/? If you prefer I can do it on your behalf.

Best regards,

Joeri

On 9/17/20 1:40 PM, Ioannis Foufoulas wrote:

...
Hi,

Some more info on this error, finally this is does not related to merge tables but to remote tables. Steps to reproduce:

In one server: create table lala(sx float, sxx float, sxy float , sy float, syy float, n int); insert into lala select 13,85,98,15,113,2;

In another remote server: CREATE REMOTE TABLE fofo(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) on 'mapi:monetdb://88.197.53.136:9999/voc10/sys/lala';

CREATE or replace FUNCTION mudf(sx FLOAT, sxx FLOAT, sxy FLOAT, sy FLOAT, syy FLOAT, n INT) RETURNS TABLE(res FLOAT) LANGUAGE PYTHON { return 0.5 };

After that I am getting the following: sql>select * from mudf((select * from fofo)); (mapi:monetdb://monetdb@88.197.53.136/voc10) Table: missing ‘)'

If I materialise the remote table into a local table, and then use the udf it runs:

sql>create table localtable as select * from fofo; operation successful sql>select * from mudf((select * from localtable)); +--------------------------+ | res | +==========================+ | 0.5 | +--------------------------+ 1 tuple sql>

Best, Yannis

...
On 16 Sep 2020, at 3:34 PM, Ioannis Foufoulas mailto:johnfouf@di.uoa.gr> wrote:

Hi, we encounter an error when only one table is added to a merge table definition During a query like "select * from merge table” we get: “Table: missing ')'" Probably merge tables cannot contain just one table but the returned error looks like a syntax error. Some more info to reproduce: the table that has been added to the merge table is a remote table.

Best, Yannis

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

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

1563

Age (days ago)

1564

Last active (days ago)

List overview

Download

4 comments

3 participants

participants (3)

ferreira
Ioannis Foufoulas
Joeri van Ruth