Changeset: 9345c67fc5ce for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9345c67fc5ce
Modified Files:
sql/server/rel_dump.c
sql/test/2024/Tests/returning.test
Branch: returning
Log Message:
fix reading/dumping relational plans with returning and add more tests
diffs (204 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
@@ -691,8 +691,6 @@ rel_print_rel(mvc *sql, stream *fout, s
mnstr_printf(fout, ")");
if (rel->op != op_truncate && rel->op != op_merge && rel->exps)
exps_print(sql, fout, rel->exps, depth, refs, 1, 0,
decorate, 0);
- if ((is_insert(rel->op) || is_update(rel->op) ||
is_delete(rel->op)) && rel->attr) /* returning lists */
- exps_print(sql, fout, rel->attr, depth, refs, 1, 0,
decorate, 0);
} break;
default:
assert(0);
@@ -1934,11 +1932,6 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_insert(sql, lrel, rrel)) || !(rel =
read_rel_properties(sql, rel, r, pos)))
return NULL;
-
- skipWS(r, pos);
- /* returning clause is signalled by a second expression list */
- if (r[*pos] == '[' && !(rel->attr = read_exps(sql, lrel, NULL,
NULL, r, pos, '[', 0, 1)))
- return NULL;
return rel;
}
@@ -1963,10 +1956,6 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_delete(sql->sa, lrel, rrel)) || !(rel =
read_rel_properties(sql, rel, r, pos)))
return NULL;
- skipWS(r, pos);
- /* returning clause is signalled by a second expression list */
- if (r[*pos] == '[' && !(rel->attr = read_exps(sql, lrel, NULL,
NULL, r, pos, '[', 0, 1)))
- return NULL;
return rel;
}
@@ -2025,7 +2014,7 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(exps = read_exps(sql, lrel, rrel, NULL, r, pos, '[', 0,
1))) /* columns to be updated */
return NULL;
- for (node *n = rel->exps->h ; n ; n = n->next) {
+ for (node *n = exps->h ; n ; n = n->next) {
sql_exp *e = (sql_exp *) n->data;
const char *cname = exp_name(e);
@@ -2043,11 +2032,6 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(rel = rel_update(sql, lrel, rrel, NULL, nexps)) || !(rel
= read_rel_properties(sql, rel, r, pos)))
return NULL;
- skipWS(r, pos);
- /* returning clause is signalled by a second expression list */
- if (r[*pos] == '[' && !(rel->attr = read_exps(sql, lrel, NULL,
NULL, r, pos, '[', 0, 1))) /* columns to be updated */
- return NULL;
-
return rel;
}
@@ -2262,7 +2246,8 @@ rel_read(mvc *sql, char *r, int *pos, li
(*pos)++;
skipWS(r, pos);
- if (!(exps = read_exps(sql, nrel, NULL, NULL, r, pos, '[', 0,
1)))
+ bool is_modify = (is_insert(nrel->op) || is_update(nrel->op) ||
is_delete(nrel->op));
+ if (!(exps = read_exps(sql, is_modify?nrel->l : nrel, NULL,
NULL, r, pos, '[', 0, 1)))
return NULL;
rel = rel_project(sql->sa, nrel, exps);
set_processed(rel);
@@ -2375,6 +2360,8 @@ rel_read(mvc *sql, char *r, int *pos, li
rel->exps = new_exp_list(sql->sa); /* empty projection
list for now */
set_processed(rel); /* don't search beyond the group by
*/
/* first group projected expressions, then group by
columns, then left relation projections */
+ if (is_insert(nrel->op) || is_update(nrel->op) ||
is_delete(nrel->op))
+ nrel = nrel->l;
if (!(exps = read_exps(sql, rel, nrel, NULL, r, pos,
'[', 1, 1)))
return NULL;
rel->exps = exps;
diff --git a/sql/test/2024/Tests/returning.test
b/sql/test/2024/Tests/returning.test
--- a/sql/test/2024/Tests/returning.test
+++ b/sql/test/2024/Tests/returning.test
@@ -162,41 +162,110 @@ insert into foo values (1,10), (-1,-10)
statement ok
create function read_dump_rel(rel STRING) RETURNS STRING external name
sql.read_dump_rel
+statement ok
+truncate table foo;
+
+statement ok
+insert into foo values (1, 10), (2, 10), (3, 10);
+
+# insert into foo(j) values (40), (50) returning sum(i)
+query T nosort
+select read_dump_rel(
+'project (
+group by (
+insert(
+table("sys"."foo") [ "foo"."i" NOT NULL MIN "1" MAX "3" NUNIQUES 3.000000,
"foo"."j" NOT NULL MIN "10" MAX "30" NUNIQUES 3.000000, "foo"."%TID%" NOT NULL
UNIQUE ] COUNT 3
+[ [ int(31) NULL, int(31) NULL ] as "%4"."%4", [ int(31) "40", int(31) "50"
] as "%1"."%1" ]
+)
+) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL NUNIQUES 3.000000 MAX "3" MIN
"1") as "%5"."%5" ]
+) [ "%5"."%5" ]')
+----
+@project (@group by (@insert(@table("sys"."foo") [ "foo"."i" NOT NULL,
"foo"."j" NOT NULL, "foo"."%TID%" NOT NULL UNIQUE ]@ [ [ int(31) NULL, int(31)
NULL ] as "%4"."%4", [ int(31) "40", int(31) "50" ] as "%1"."%1" ]@)@) [ ] [
"sys"."sum" no nil ("foo"."i" NOT NULL) as "%5"."%5" ]@) [ "%5"."%5" ]
+
+# update foo set j = -j where j > 10 returning sum(i)
query T nosort
select read_dump_rel(
'REF 1 (2)
-table("sys"."foo")[ "foo"."i", "foo"."j", "foo"."%TID%" ]
+table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES
3.000000, "foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000,
"foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+project (
+group by (
update(
& REF 1
project (
select (
& REF 1
-) [ ("foo"."i") < (int(31) "3") ]
-) [ "foo"."%TID%" , "sys"."sql_neg"("foo"."j") as "foo"."j" ]
-) [ "foo"."%TID%" , "foo"."j" ] [ "sys"."sql_add"("foo"."j" , int(3) "5") as
"bla", "sys"."sql_add"("foo"."i", "foo"."i") ]')
+) [ ("foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000) > (int(5)
"10") ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE
MIN "10" MAX "30" NUNIQUES 3.000000) NOT NULL NUNIQUES 3.000000 MIN "-30" MAX
"-10" as "foo"."j" ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL ]
+) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE MIN "1" MAX "3"
NUNIQUES 3.000000) UNIQUE NUNIQUES 1.000000 as "%1"."%1" ] COUNT 1
+) [ "%1"."%1" UNIQUE NUNIQUES 1.000000 ] COUNT 1')
----
-@ REF 1 (2)@table("sys"."foo") [ "foo"."i", "foo"."j" NOT NULL, "foo"."%TID%"
NOT NULL UNIQUE ]@update(@& REF 1 @project (@select (@& REF 1 @) [ ("foo"."i")
< (int(31) "3") ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j"
NOT NULL) NOT NULL as "foo"."j" ]@) [ "foo"."i", "foo"."j" NOT NULL,
"foo"."%TID%" NOT NULL UNIQUE ] [ "sys"."sql_add"("foo"."j" NOT NULL, int(3)
"5") NOT NULL as "bla", "sys"."sql_add"("foo"."i", "foo"."i") ]
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@group by (@update(@& REF 1
@project (@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10")
]@) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE)
NOT NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE ]@) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE) UNIQUE as
"%1"."%1" ]@) [ "%1"."%1" UNIQUE ]
+# delete from foo where j > 10 returning sum(i)
query T nosort
select read_dump_rel(
'REF 1 (2)
-table("sys"."foo") [ "foo"."i" , "foo"."j" , "foo"."%TID%" ]
+table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES
3.000000, "foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000,
"foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+project (
+group by (
delete(
& REF 1
project (
select (
& REF 1
-) [ ("foo"."i" ) > (int(31) "0") ]
-) [ "foo"."%TID%" ]
-) [ "foo"."j" ]')
+) [ ("foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000) > (int(5)
"10") ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+)
+) [ ] [ "sys"."sum" no nil ("foo"."i" NOT NULL UNIQUE MIN "1" MAX "3"
NUNIQUES 3.000000) UNIQUE NUNIQUES 1.000000 as "%1"."%1" ] COUNT 1
+) [ "%1"."%1" UNIQUE NUNIQUES 1.000000 ] COUNT 1')
----
-@ REF 1 (2)@table("sys"."foo") [ "foo"."i", "foo"."j" NOT NULL, "foo"."%TID%"
NOT NULL UNIQUE ]@delete(@& REF 1 @project (@select (@& REF 1 @) [ ("foo"."i")
> (int(31) "0") ]@) [ "foo"."%TID%" NOT NULL UNIQUE ]@) [ "foo"."j" NOT NULL ]
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@group by (@delete(@& REF 1
@project (@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10")
]@) [ "foo"."%TID%" NOT NULL UNIQUE ]@)@) [ ] [ "sys"."sum" no nil ("foo"."i"
NOT NULL UNIQUE) UNIQUE as "%1"."%1" ]@) [ "%1"."%1" UNIQUE ]
+# insert into foo(j) values (40), (50) returning i
+query T nosort
+select read_dump_rel(
+'project (
+insert(
+table("sys"."foo") [ "foo"."i" NOT NULL MIN "1" MAX "3" NUNIQUES 3.000000,
"foo"."j" NOT NULL MIN "10" MAX "30" NUNIQUES 3.000000, "foo"."%TID%" NOT NULL
UNIQUE ] COUNT 3
+[ [ int(31) NULL, int(31) NULL ] as "%4"."%4", [ int(31) "40", int(31) "50"
] as "%1"."%1" ]
+)
+) [ "foo"."i" NOT NULL NUNIQUES 3.000000 MAX "3" MIN "1" ]')
+----
+@project (@insert(@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j"
NOT NULL, "foo"."%TID%" NOT NULL UNIQUE ]@ [ [ int(31) NULL, int(31) NULL ] as
"%4"."%4", [ int(31) "40", int(31) "50" ] as "%1"."%1" ]@)@) [ "foo"."i" NOT
NULL UNIQUE ]
+
+# update foo set j = -j where j > 10 returning i
query T nosort
select read_dump_rel(
-'insert(
-table("sys"."foo") [ "foo"."i", "foo"."j", "foo"."%TID%" ]
-[ [ int(31) NULL, int(31) NULL ] as "%4"."%4", [ int(31) "10",
int(31)["sys"."sql_neg"(tinyint(4) "10") ] ] as "%1"."%1" ]
-) [ "foo"."i" ]')
+'REF 1 (2)
+table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES
3.000000, "foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000,
"foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+project (
+update(
+& REF 1
+project (
+select (
+& REF 1
+) [ ("foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000) > (int(5)
"10") ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE
MIN "10" MAX "30" NUNIQUES 3.000000) NOT NULL NUNIQUES 3.000000 MIN "-30" MAX
"-10" as "foo"."j" ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL ]
+) [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES 3.000000 ]')
----
-@insert(@table("sys"."foo") [ "foo"."i", "foo"."j" NOT NULL, "foo"."%TID%" NOT
NULL UNIQUE ]@ [ [ int(31) NULL, int(31) NULL ] as "%4"."%4", [ int(31) "10",
int(31)["sys"."sql_neg"(tinyint(4) "10") NOT NULL] NOT NULL ] as "%1"."%1" ]@)
[ "foo"."i" ]
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@update(@& REF 1 @project
(@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10") ]@) [
"foo"."%TID%" NOT NULL UNIQUE, "sys"."sql_neg"("foo"."j" NOT NULL UNIQUE) NOT
NULL as "foo"."j" ]@) [ "foo"."%TID%" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE ]@) [ "foo"."i" NOT NULL UNIQUE ]
+
+# delete from foo where j > 10 returning i
+query T nosort
+select read_dump_rel(
+'REF 1 (2)
+table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES
3.000000, "foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000,
"foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+project (
+delete(
+& REF 1
+project (
+select (
+& REF 1
+) [ ("foo"."j" NOT NULL UNIQUE MIN "10" MAX "30" NUNIQUES 3.000000) > (int(5)
"10") ] COUNT 3
+) [ "foo"."%TID%" NOT NULL UNIQUE ] COUNT 3
+)
+) [ "foo"."i" NOT NULL UNIQUE MIN "1" MAX "3" NUNIQUES 3.000000 ]');
+----
+@ REF 1 (2)@table("sys"."foo") [ "foo"."i" NOT NULL UNIQUE, "foo"."j" NOT NULL
UNIQUE, "foo"."%TID%" NOT NULL UNIQUE ]@project (@delete(@& REF 1 @project
(@select (@& REF 1 @) [ ("foo"."j" NOT NULL UNIQUE) > (int(5) "10") ]@) [
"foo"."%TID%" NOT NULL UNIQUE ]@)@) [ "foo"."i" NOT NULL UNIQUE ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
