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
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
