MonetDB: Jun2020 - Fix for return statements on SQL functions. I...

[Pedro Ferreira]

Changeset: 03d82bbe1886 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=03d82bbe1886
Modified Files:
        sql/server/rel_psm.c
        sql/test/subquery/Tests/subquery5.sql
        sql/test/subquery/Tests/subquery5.stable.out
Branch: Jun2020
Log Message:

Fix for return statements on SQL functions. If the return statement is 
returning no rows, the output must be null


diffs (92 lines):

diff --git a/sql/server/rel_psm.c b/sql/server/rel_psm.c
--- a/sql/server/rel_psm.c
+++ b/sql/server/rel_psm.c
@@ -428,6 +428,7 @@ rel_psm_return( sql_query *query, sql_su
        sql_exp *res;
        sql_rel *rel = NULL;
        list *l = sa_list(sql->sa);
+       bool requires_proj = false;
 
        if (restypelist)
                ek.card = card_relation;
@@ -436,13 +437,27 @@ rel_psm_return( sql_query *query, sql_su
        res = rel_value_exp2(query, &rel, return_sym, sql_sel, ek);
        if (!res)
                return NULL;
-       if (!rel && exp_is_rel(res))
+       if (!rel && exp_is_rel(res)) {
                rel = exp_rel_get_rel(sql->sa, res);
+               if (rel && !restypelist && !is_groupby(rel->op)) { /* On 
regular functions return zero or 1 rows for every row */
+                       rel->card = CARD_MULTI; 
+                       rel = rel_zero_or_one(sql, rel, ek);
+                       if (list_length(rel->exps) != 1)
+                               return sql_error(sql, 02, SQLSTATE(42000) 
"RETURN: must return a single column");
+                       res = exp_ref(sql->sa, (sql_exp*) rel->exps->t->data);
+                       requires_proj = true;
+               }
+       }
        if (ek.card != card_relation && (!restype || (res = rel_check_type(sql, 
restype, rel, res, type_equal)) == NULL))
                return (!restype)?sql_error(sql, 02, SQLSTATE(42000) "RETURN: 
return type does not match"):NULL;
        else if (ek.card == card_relation && !rel)
                return NULL;
 
+       if (requires_proj) {
+               rel = rel_project(sql->sa, rel, list_append(sa_list(sql->sa), 
res));
+               res = exp_rel(sql, rel);
+       }
+
        if (rel && !is_ddl(rel->op) && ek.card == card_relation) {
                list *exps = sa_list(sql->sa), *oexps = rel->exps;
                node *n, *m;
diff --git a/sql/test/subquery/Tests/subquery5.sql 
b/sql/test/subquery/Tests/subquery5.sql
--- a/sql/test/subquery/Tests/subquery5.sql
+++ b/sql/test/subquery/Tests/subquery5.sql
@@ -38,10 +38,7 @@ CREATE OR REPLACE FUNCTION evilfunction(
 
 CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS INT BEGIN RETURN 
SELECT input, 2; END; --error, more than 1 return
 
-CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS INT 
-BEGIN
-       RETURN SELECT input WHERE FALSE;
-END;
+CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS INT BEGIN RETURN 
SELECT input WHERE FALSE; END;
 
 SELECT evilfunction(1);
        -- NULL
diff --git a/sql/test/subquery/Tests/subquery5.stable.out 
b/sql/test/subquery/Tests/subquery5.stable.out
--- a/sql/test/subquery/Tests/subquery5.stable.out
+++ b/sql/test/subquery/Tests/subquery5.stable.out
@@ -38,6 +38,33 @@ stdout of test 'subquery5` in directory 
 [ 1234,        40      ]
 [ 1234,        40      ]
 [ 1234,        40      ]
+#CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS INT 
+#BEGIN
+#      RETURN SELECT input WHERE FALSE;
+#END;
+#SELECT evilfunction(1);
+% . # table_name
+% %1 # name
+% int # type
+% 1 # length
+[ NULL ]
+#SELECT evilfunction(1);
+% . # table_name
+% %1 # name
+% int # type
+% 1 # length
+[ NULL ]
+#SELECT evilfunction(1), 1;
+% .,   . # table_name
+% %1,  %2 # name
+% int, tinyint # type
+% 1,   1 # length
+[ NULL,        1       ]
+#CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS INT 
+#BEGIN
+#      RETURN SELECT input UNION ALL SELECT input;
+#END;
+#DROP FUNCTION evilfunction(INT);
 #DROP TABLE tbl_ProductSales;
 #DROP TABLE another_T;
 #DROP TABLE integers;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list