Changeset: 7f2af1be69a6 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7f2af1be69a6 Added Files: sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.py sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.err sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.out Modified Files: sql/server/rel_dump.c sql/server/rel_select.c sql/server/rel_select.h sql/test/BugTracker-2020/Tests/All Branch: octbugs Log Message:
Added test and fix for bug 6971, ie add table udf support at rel_read diffs (truncated from 309 to 300 lines): diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c --- a/sql/server/rel_dump.c +++ b/sql/server/rel_dump.c @@ -1564,32 +1564,124 @@ rel_read(mvc *sql, char *r, int *pos, li skipIdent(r, pos); e = r+*pos; skipWS(r, pos); - if (r[*pos] != ')') - sql_error(sql, -1, SQLSTATE(42000) "Table: missing ')'\n"); - *e = 0; - (*pos)++; - skipWS(r, pos); - if (!(s = mvc_bind_schema(sql, sname))) - return sql_error(sql, -1, SQLSTATE(3F000) "No such schema '%s'\n", sname); - if (!(t = mvc_bind_table(sql, s, tname))) - return sql_error(sql, -1, SQLSTATE(42S02) "Table missing '%s.%s'\n", sname, tname); - if (isMergeTable(t)) - return sql_error(sql, -1, SQLSTATE(42000) "Merge tables not supported under remote connections\n"); - if (isRemote(t)) - return sql_error(sql, -1, SQLSTATE(42000) "Remote tables not supported under remote connections\n"); - if (isReplicaTable(t)) - return sql_error(sql, -1, SQLSTATE(42000) "Replica tables not supported under remote connections\n"); - rel = rel_basetable(sql, t, tname); - if (!table_privs(sql, t, PRIV_SELECT) && !(rel = rel_reduce_on_column_privileges(sql, rel, t))) - return sql_error(sql, -1, SQLSTATE(42000) "Access denied for %s to table '%s.%s'\n", sqlvar_get_string(find_global_var(sql, mvc_bind_schema(sql, "sys"), "current_user")), s->base.name, tname); + if (r[*pos] == '(') { /* table returning function */ + node *m; + sql_exp *tudf, *next; + list *inputs, *outputs; + sql_subfunc *sf; + int x = *pos, y; /* save current position, after parsing the input relation we have to parse the input parameters */ + bool inside_identifier = false; + + while (r[*pos] && (inside_identifier || r[*pos] != '\n')) { /* the input parameters must be parsed after the input relation, skip them for now */ + (*pos)++; + if (r[*pos] == '"') + inside_identifier = !inside_identifier; + } + if (r[*pos] != '\n') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing ']' for output parameters\n"); + + skipWS(r, pos); /* now parse the input relation */ + if (!(lrel = rel_read(sql, r, pos, refs))) + return NULL; + y = *pos; /* later we have to return here to parse the output identifiers */ + *pos = x; + if (!(inputs = read_exps(sql, lrel, NULL, NULL, r, pos, '(', 0))) + return NULL; + + if (!(s = mvc_bind_schema(sql, sname))) + return sql_error(sql, -1, SQLSTATE(3F000) "No such schema '%s'\n", sname); + *e = 0; /* closing table udf name string */ + if (!(tudf = find_table_function(sql, s, tname, list_empty(inputs) ? NULL : inputs, list_empty(inputs) ? NULL : exp_types(sql->sa, inputs)))) + return sql_error(sql, 02, SQLSTATE(42S02) "No such table returning function '%s.%s'\n", sname, tname); + sf = tudf->f; + if (tudf->type != e_func || sf->func->type != F_UNION) + return sql_error(sql, 02, SQLSTATE(42000) "'%s' does not return a table\n", exp_func_name(tudf)); + + *pos = y; /* now at the end of the input relation */ + skipWS(r, pos); + if (r[*pos] != ')') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing ')' at the end of the input relation\n"); + (*pos)++; + skipWS(r, pos); + + /* Parse identifiers manually, we cannot use read_exps because the labels may not match */ + if (r[*pos] != '[') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing '[' for output parameters\n"); + (*pos)++; + skipWS(r, pos); + m = sf->func->res->h; + outputs = new_exp_list(sql->sa); + while (r[*pos] && r[*pos] != ']' && m) { + sql_arg *a = m->data; + char *nrname, *ncname; - if (!r[*pos]) - return rel; + if (r[*pos] != '"') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing identifier for output parameters\n"); + (*pos)++; + nrname = r+*pos; + skipIdent(r, pos); + if (r[*pos] != '"') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing identifier for output parameters\n"); + e = r+*pos; + *e = 0; + (*pos)++; + if (r[*pos] != '.') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing '.' for output parameters\n"); + (*pos)++; /* skip '.' */ + if (r[*pos] != '"') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing identifier for output parameters\n"); + (*pos)++; + ncname = r+*pos; + skipIdent(r, pos); + if (r[*pos] != '"') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing identifier for output parameters\n"); + e = r+*pos; + *e = 0; + (*pos)++; - /* scan aliases */ - if (!(exps = read_exps(sql, rel, NULL, NULL, r, pos, '[', 0))) - return NULL; - rel->exps = exps; + next = exp_column(sql->sa, nrname, ncname, &a->type, CARD_MULTI, 1, 0); + set_basecol(next); + append(outputs, next); + m = m->next; + skipWS(r, pos); + } + if (r[*pos] != ']') + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: missing ']' for output parameters\n"); + (*pos)++; + skipWS(r, pos); + if (list_length(outputs) != list_length(sf->func->res)) + return sql_error(sql, -1, SQLSTATE(42000) "Table returning function: the number of output parameters don't match the table ones relation outputs: %d != function outputs: %d\n", + list_length(outputs), list_length(sf->func->res)); + rel = rel_table_func(sql->sa, lrel, tudf, outputs, TABLE_FROM_RELATION); + } else { + if (r[*pos] != ')') + sql_error(sql, -1, SQLSTATE(42000) "Table: missing ')'\n"); + *e = 0; + (*pos)++; + skipWS(r, pos); + if (!(s = mvc_bind_schema(sql, sname))) + return sql_error(sql, -1, SQLSTATE(3F000) "No such schema '%s'\n", sname); + if (!(t = mvc_bind_table(sql, s, tname))) + return sql_error(sql, -1, SQLSTATE(42S02) "Table missing '%s.%s'\n", sname, tname); + if (isMergeTable(t)) + return sql_error(sql, -1, SQLSTATE(42000) "Merge tables not supported under remote connections\n"); + if (isRemote(t)) + return sql_error(sql, -1, SQLSTATE(42000) "Remote tables not supported under remote connections\n"); + if (isReplicaTable(t)) + return sql_error(sql, -1, SQLSTATE(42000) "Replica tables not supported under remote connections\n"); + rel = rel_basetable(sql, t, tname); + if (!table_privs(sql, t, PRIV_SELECT) && !(rel = rel_reduce_on_column_privileges(sql, rel, t))) + return sql_error(sql, -1, SQLSTATE(42000) "Access denied for %s to table '%s.%s'\n", + sqlvar_get_string(find_global_var(sql, mvc_bind_schema(sql, "sys"), "current_user")), s->base.name, tname); + + if (!r[*pos]) + return rel; + + /* scan aliases */ + if (!(exps = read_exps(sql, rel, NULL, NULL, r, pos, '[', 0))) + return NULL; + rel->exps = exps; + } if (strncmp(r+*pos, "COUNT", strlen("COUNT")) == 0) { (*pos)+= (int) strlen("COUNT"); skipWS( r, pos); diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c --- a/sql/server/rel_select.c +++ b/sql/server/rel_select.c @@ -524,7 +524,7 @@ find_table_function_type(mvc *sql, sql_s return e; } -static sql_exp* +sql_exp * find_table_function(mvc *sql, sql_schema *s, char *fname, list *exps, list *tl) { sql_subfunc* sf = NULL; diff --git a/sql/server/rel_select.h b/sql/server/rel_select.h --- a/sql/server/rel_select.h +++ b/sql/server/rel_select.h @@ -30,6 +30,7 @@ extern sql_exp *rel_nop_(mvc *sql, sql_r extern sql_rel *rel_with_query(sql_query *query, symbol *q); extern sql_rel *rel_reduce_on_column_privileges(mvc *sql, sql_rel *rel, sql_table *t); extern sql_rel *table_ref(sql_query *query, sql_rel *rel, symbol *tableref, int lateral); +extern sql_exp *find_table_function(mvc *sql, sql_schema *s, char *fname, list *exps, list *tl); extern sql_rel *rel_loader_function(sql_query* query, symbol* s, list *fexps, sql_subfunc **loader_function); diff --git a/sql/test/BugTracker-2020/Tests/All b/sql/test/BugTracker-2020/Tests/All --- a/sql/test/BugTracker-2020/Tests/All +++ b/sql/test/BugTracker-2020/Tests/All @@ -24,3 +24,4 @@ round.Bug-6955 log.Bug-6960 table-udf-column-descriptor.Bug-6964 table-udf-distinct.Bug-6965 +HAVE_PYMONETDB?table-udf-on-remote.Bug-6971 diff --git a/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.py b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.py new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.py @@ -0,0 +1,63 @@ +import os +import socket +import sys +import tempfile + +import pymonetdb + +try: + from MonetDBtesting import process +except ImportError: + import process + + +# Find a free network port +def freeport(): + sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) + sock.bind(('', 0)) + port = sock.getsockname()[1] + sock.close() + return port + + +with tempfile.TemporaryDirectory() as farm_dir: + os.mkdir(os.path.join(farm_dir, 'node1')) + os.mkdir(os.path.join(farm_dir, 'node2')) + + node1_port = freeport() + with process.server(mapiport=node1_port, dbname='node1', + dbfarm=os.path.join(farm_dir, 'node1'), + stdin=process.PIPE, stdout=process.PIPE, + stderr=process.PIPE) as node1_proc: + node1_conn = pymonetdb.connect(database='node1', port=node1_port, autocommit=True) + node1_cur = node1_conn.cursor() + + node1_cur.execute("create function mudf(sx float, sxx float, sxy float, sy float, syy float, n int) returns table(res float) begin return select 0.5; end") + node1_cur.execute("create table lala(sx float, sxx float, sxy float , sy float, syy float, n int)") + node1_cur.execute("insert into lala select 13,85,98,15,113,2") + node1_cur.execute("select * from lala") + print(node1_cur.fetchall()) + node1_cur.execute("select * from mudf((select * from lala))") + print(node1_cur.fetchall()) + + node2_port = freeport() + with process.server(mapiport=node2_port, dbname='node2', + dbfarm=os.path.join(farm_dir, 'node2'), + stdin=process.PIPE, stdout=process.PIPE, + stderr=process.PIPE) as node2_proc: + node2_conn = pymonetdb.connect(database='node2', port=node2_port, autocommit=True) + node2_cur = node2_conn.cursor() + + node2_cur.execute("create function mudf(sx float, sxx float, sxy float, sy float, syy float, n int) returns table(res float) begin return select 0.5; end") + node2_cur.execute("create remote table fofo(sx float, sxx float, sxy float, sy float, syy float, n int) on 'mapi:monetdb://localhost:{}/node1/sys/lala'".format(node1_port)) + node2_cur.execute("select * from fofo") + print(node2_cur.fetchall()) + node2_cur.execute("select * from mudf((select * from fofo))") + print(node2_cur.fetchall()) + + # cleanup: shutdown the monetdb servers and remove tempdir + out, err = node1_proc.communicate() + sys.stderr.write(err) + + out, err = node2_proc.communicate() + sys.stderr.write(err) diff --git a/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.err b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.err @@ -0,0 +1,32 @@ +stderr of test 'table-udf-on-remote.Bug-6971` in directory 'sql/test/BugTracker-2020` itself: + + +# 11:17:10 > +# 11:17:10 > "/usr/bin/python3.8" "table-udf-on-remote.Bug-6971.py" "table-udf-on-remote.Bug-6971" +# 11:17:10 > + +# builtin opt gdk_dbpath = /home/ferreira/repositories/MonetDB-octbugs/BUILD/var/monetdb5/dbfarm/demo +# builtin opt mapi_port = 50000 +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# builtin opt raw_strings = false +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_listenaddr = all +# cmdline opt mapi_port = 53979 +# cmdline opt mapi_usock = /var/tmp/mtest-40340/.s.monetdb.53979 +# cmdline opt gdk_dbpath = /tmp/tmplz1i_wxc/node1/node1 +# builtin opt gdk_dbpath = /home/ferreira/repositories/MonetDB-octbugs/BUILD/var/monetdb5/dbfarm/demo +# builtin opt mapi_port = 50000 +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# builtin opt raw_strings = false +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_listenaddr = all +# cmdline opt mapi_port = 54689 +# cmdline opt mapi_usock = /var/tmp/mtest-40340/.s.monetdb.54689 +# cmdline opt gdk_dbpath = /tmp/tmplz1i_wxc/node2/node2 + +# 11:17:12 > +# 11:17:12 > "Done." +# 11:17:12 > + diff --git a/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.out b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2020/Tests/table-udf-on-remote.Bug-6971.stable.out @@ -0,0 +1,16 @@ +stdout of test 'table-udf-on-remote.Bug-6971` in directory 'sql/test/BugTracker-2020` itself: + + +# 11:17:10 > +# 11:17:10 > "/usr/bin/python3.8" "table-udf-on-remote.Bug-6971.py" "table-udf-on-remote.Bug-6971" +# 11:17:10 > + _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list