Merge table error with one table
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
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
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 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
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 <johnfouf@di.uoa.gr 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
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 <johnfouf@di.uoa.gr 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
participants (3)
-
ferreira -
Ioannis Foufoulas -
Joeri van Ruth