Changeset: 57065455c7d4 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=57065455c7d4
Modified Files:
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/rel_updates.c
sql/test/miscellaneous/Tests/simple_selects.sql
sql/test/miscellaneous/Tests/simple_selects.stable.err
sql/test/subquery/Tests/subquery4.sql
sql/test/subquery/Tests/subquery4.stable.err
sql/test/subquery/Tests/subquery4.stable.out
Branch: pushdown
Log Message:
merged with default
diffs (truncated from 341 to 300 lines):
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
@@ -4687,10 +4687,11 @@ rel_rankop(sql_query *query, sql_rel **r
fargs = sa_list(sql->sa);
if (window_function->token == SQL_RANK) { /* rank function call */
dlist *dl = dn->next->next->data.lval;
- bool is_ntile = !strcmp(aname, "ntile"), is_lag =
!strcmp(aname, "lag"), is_lead = !strcmp(aname, "lead");
+ bool is_lag = !strcmp(aname, "lag"), is_lead = !strcmp(aname,
"lead"),
+ extra_input = !strcmp(aname, "ntile") ||
!strcmp(aname, "rank") || !strcmp(aname, "dense_rank") || !strcmp(aname,
"row_number") || !strcmp(aname, "percent_rank") || !strcmp(aname, "cume_dist");
distinct = dn->next->data.i_val;
- if (!dl || is_ntile) { /* pass an input column for analytic
functions that don't require it */
+ if (extra_input) { /* pass an input column for analytic
functions that don't require it */
in = rel_first_column(sql, p);
if (!in)
return NULL;
@@ -4699,7 +4700,6 @@ rel_rankop(sql_query *query, sql_rel **r
in = exp_ref(sql->sa, in);
append(fargs, in);
in = exp_ref_save(sql, in);
- nfargs++;
}
if (dl)
for (dargs = dl->h ; dargs ; dargs = dargs->next) {
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -505,7 +505,8 @@ exp_rewrite(mvc *sql, sql_rel *rel, sql_
list *rankopargs = e->l;
/* window_bound has partition/orderby as first argument (before
normal expressions), others as second (and have a boolean placeholder) */
int is_wb = (strcmp(sf->func->base.name, "window_bound") == 0);
- node *n = (is_wb)?rankopargs->h:rankopargs->h->next;
+ int is_ntile = (strcmp(sf->func->base.name, "ntile") == 0);
+ node *n =
(is_wb)?rankopargs->h:(is_ntile)?rankopargs->h->next->next:rankopargs->h->next;
sql_exp *pe = n->data;
/* if pe is window_bound function skip */
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -315,7 +315,9 @@ rel_inserts(mvc *sql, sql_table *t, sql_
for (n = r->exps->h, m = collist->h; n && m; n =
n->next, m = m->next) {
sql_column *c = m->data;
sql_exp *e = n->data;
-
+
+ if (inserts[c->colnr])
+ return sql_error(sql, 02,
SQLSTATE(42000) "%s: column '%s' specified more than once", action,
c->base.name);
inserts[c->colnr] = rel_check_type(sql,
&c->type, r, e, type_equal);
}
} else {
@@ -324,8 +326,11 @@ rel_inserts(mvc *sql, sql_table *t, sql_
sql_exp *e;
e = exps_bind_column2( r->exps,
c->t->base.name, c->base.name);
- if (e)
+ if (e) {
+ if (inserts[c->colnr])
+ return sql_error(sql, 02,
SQLSTATE(42000) "%s: column '%s' specified more than once", action,
c->base.name);
inserts[c->colnr] = exp_ref(sql->sa, e);
+ }
}
}
}
diff --git a/sql/test/miscellaneous/Tests/simple_selects.sql
b/sql/test/miscellaneous/Tests/simple_selects.sql
--- a/sql/test/miscellaneous/Tests/simple_selects.sql
+++ b/sql/test/miscellaneous/Tests/simple_selects.sql
@@ -21,26 +21,45 @@ insert into x values (1, 1);
select cast(x as date) from x; --error, cannot cast
select cast(x as time) from x;
select cast(x as timestamp) from x; --error, cannot cast
+select cast(x as real) from x;
+select cast(x as double) from x;
+select cast(x as decimal) from x;
select cast(y as date) from x; --error, cannot cast
select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
select cast(y as timestamp) from x; --error, cannot cast
+select cast(y as real) from x;
+select cast(y as double) from x;
+select cast(y as decimal) from x;
+
insert into x values (null, null);
select cast(x as date) from x; --error, cannot cast
select cast(x as time) from x;
select cast(x as timestamp) from x; --error, cannot cast
+select cast(x as real) from x;
+select cast(x as double) from x;
+select cast(x as decimal) from x;
select cast(y as date) from x; --error, cannot cast
select cast(y as time) from x; --We throw error, but PostgreSQL doesn't
select cast(y as timestamp) from x; --error, cannot cast
+select cast(y as real) from x;
+select cast(y as double) from x;
+select cast(y as decimal) from x;
drop table x;
-create table x (x time, y date, z timestamp);
-insert into x values (null, null, null);
+create table x (x time, y date, z timestamp, w real, a double, b decimal);
+insert into x values (null, null, null, null, null, null);
select cast(x as interval second) from x; --We throw error, but PostgreSQL
doesn't
select cast(x as interval month) from x; --We throw error, but PostgreSQL
doesn't
select cast(y as interval second) from x; --error, cannot cast
select cast(y as interval month) from x; --error, cannot cast
select cast(z as interval second) from x; --error, cannot cast
select cast(z as interval month) from x; --error, cannot cast
+select cast(w as interval second) from x;
+select cast(w as interval month) from x;
+select cast(a as interval second) from x;
+select cast(a as interval month) from x;
+select cast(b as interval second) from x;
+select cast(b as interval month) from x;
drop table x;
select difference('foobar', 'oobar'), difference(NULL, 'oobar'),
difference('foobar', NULL), difference(NULL, NULL),
@@ -65,6 +84,8 @@ select "idontexist"."idontexist"(1) over
select cast(true as interval second); --error, not possible
select cast(true as interval month); --error, not possible
+select cast(cast(1 as interval second) as boolean); --error, not possible
+select cast(cast(1 as interval month) as boolean); --error, not possible
select substring('abc' from 1 for null);
select substring('abc' from null for 2);
diff --git a/sql/test/miscellaneous/Tests/simple_selects.stable.err
b/sql/test/miscellaneous/Tests/simple_selects.stable.err
--- a/sql/test/miscellaneous/Tests/simple_selects.stable.err
+++ b/sql/test/miscellaneous/Tests/simple_selects.stable.err
@@ -117,7 +117,15 @@ MAPI = (monetdb) /var/tmp/mtest-316445/
QUERY = select cast(true as interval month); --error, not possible
ERROR = !types boolean(1,0) and month_interval(3,0) are not equal
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-416559/.s.monetdb.32222
+MAPI = (monetdb) /var/tmp/mtest-108640/.s.monetdb.34144
+QUERY = select cast(cast(1 as interval second) as boolean); --error, not
possible
+ERROR = !types sec_interval(13,0) and boolean(1,0) are not equal
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-108640/.s.monetdb.34144
+QUERY = select cast(cast(1 as interval month) as boolean); --error, not
possible
+ERROR = !types month_interval(3,0) and boolean(1,0) are not equal
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-108640/.s.monetdb.34144
QUERY = CREATE FUNCTION count(input INT) RETURNS INT BEGIN RETURN SELECT 1;
END; --error, ambiguous, there's an aggregate named count with the same
parameters
ERROR = !CREATE FUNCTION: there's an aggregate with the name 'count' and the
same parameters, which causes ambiguous calls
CODE = 42000
diff --git a/sql/test/subquery/Tests/subquery4.sql
b/sql/test/subquery/Tests/subquery4.sql
--- a/sql/test/subquery/Tests/subquery4.sql
+++ b/sql/test/subquery/Tests/subquery4.sql
@@ -102,6 +102,24 @@ SELECT
FROM integers i1;
-- 1
+SELECT (SELECT NTILE(i1.i) OVER ()) mycalc FROM integers i1 ORDER BY mycalc
NULLS LAST;
+ -- 1
+ -- 1
+ -- 1
+ -- NULL
+
+SELECT (SELECT NTILE(i1.i) OVER (PARTITION BY i1.i)) mycalc FROM integers i1
ORDER BY mycalc NULLS LAST;
+ -- 1
+ -- 1
+ -- 1
+ -- NULL
+
+SELECT (SELECT NTILE(i1.i) OVER (PARTITION BY i1.i ORDER BY i1.i)) FROM
integers i1 ORDER BY 1 NULLS LAST;
+ -- 1
+ -- 1
+ -- 1
+ -- NULL
+
UPDATE another_T SET col1 = MIN(col1); --error, aggregates not allowed in
update set clause
UPDATE another_T SET col2 = 1 WHERE col1 = SUM(col2); --error, aggregates not
allowed in update set clause
UPDATE another_T SET col3 = (SELECT MAX(col5)); --error, aggregates not
allowed in update set clause
@@ -161,17 +179,26 @@ UPDATE another_T SET col5 = 1, col5 = 6;
UPDATE another_T SET (col5, col6) = ((select 1,2)), col5 = 6; --error,
multiple assignments to same column "col5"
UPDATE another_T SET (col5, col6) = (SELECT MIN(col1), MAX(col2)); --error,
aggregate functions are not allowed in UPDATE
-UPDATE another_T SET (col7, col8) = (SELECT NTILE(col1) OVER (), MAX(col3)
OVER (PARTITION BY col4)); --4 rows affected
+UPDATE another_T SET col7 = (SELECT NTILE(col1) OVER ()); --4 rows affected
+
+SELECT col7 FROM another_T;
+ -- 1
+ -- 1
+ -- 1
+ -- 1
+
+UPDATE another_T SET (col5, col6) = (SELECT NTILE(col1) OVER (), MAX(col3)
OVER (PARTITION BY col4)); --4 rows affected
UPDATE another_T t1 SET (col1, col2) = (SELECT MIN(t1.col3 + tb.ColID),
MAX(tb.ColID) FROM tbl_ProductSales tb); --4 rows affected
UPDATE another_T t1 SET (col3, col4) = (SELECT COUNT(tb.ColID), SUM(tb.ColID)
FROM tbl_ProductSales tb); --4 rows affected
-SELECT col1, col2, col3, col4, col7, col8 FROM another_T;
+SELECT col1, col2, col3, col4, col5, col6 FROM another_T;
DECLARE x int;
SET x = MAX(1) over (); --error, not allowed
DECLARE y int;
SET y = MIN(1); --error, not allowed
+INSERT INTO another_T (col1,col1) VALUES (1,1); --error, multiple assignments
to same column "col1"
INSERT INTO another_T VALUES (SUM(1),2,3,4,5,6,7,8); --error, not allowed
INSERT INTO another_T VALUES (AVG(1) OVER (),2,3,4,5,6,7,8); --error, not
allowed
INSERT INTO another_T VALUES ((SELECT SUM(1)),(SELECT SUM(2) OVER
()),3,4,5,6,7,8); --allowed
@@ -187,6 +214,9 @@ CALL crashme((SELECT COUNT(1))); --error
CALL crashme((SELECT COUNT(1) OVER ())); --error, subquery at CALL
CALL crashme((SELECT 1 UNION ALL SELECT 2)); --error, subquery at CALL
+SELECT row_number(1) OVER () FROM integers i1; --error, row_number(int)
doesn't exist
+SELECT ntile(1,1) OVER () FROM integers i1; --error, ntile(int,int) doesn't
exist
+
create sequence "debugme" as integer start with 1;
alter sequence "debugme" restart with (select MAX(1));
alter sequence "debugme" restart with (select MIN(1) OVER ());
diff --git a/sql/test/subquery/Tests/subquery4.stable.err
b/sql/test/subquery/Tests/subquery4.stable.err
--- a/sql/test/subquery/Tests/subquery4.stable.err
+++ b/sql/test/subquery/Tests/subquery4.stable.err
@@ -188,7 +188,11 @@ MAPI = (monetdb) /var/tmp/mtest-60261/.
QUERY = SET y = MIN(1); --error, not allowed
ERROR = !MIN: missing group by
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-60261/.s.monetdb.38639
+MAPI = (monetdb) /var/tmp/mtest-64327/.s.monetdb.34611
+QUERY = INSERT INTO another_T (col1,col1) VALUES (1,1); --error, multiple
assignments to same column "col1"
+ERROR = !INSERT INTO: column 'col1' specified more than once
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-64327/.s.monetdb.34611
QUERY = INSERT INTO another_T VALUES (SUM(1),2,3,4,5,6,7,8); --error, not
allowed
ERROR = !SUM: missing group by
CODE = 42000
@@ -196,7 +200,7 @@ MAPI = (monetdb) /var/tmp/mtest-60261/.
QUERY = INSERT INTO another_T VALUES (AVG(1) OVER (),2,3,4,5,6,7,8); --error,
not allowed
ERROR = !AVG: window function 'avg' not allowed inside a list of VALUES
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-60261/.s.monetdb.38639
+MAPI = (monetdb) /var/tmp/mtest-64327/.s.monetdb.34611
QUERY = CALL crashme(COUNT(1)); --error, not allowed
ERROR = !COUNT: missing group by
CODE = 42000
@@ -216,6 +220,14 @@ MAPI = (monetdb) /var/tmp/mtest-92133/.
QUERY = CALL crashme((SELECT 1 UNION ALL SELECT 2)); --error, subquery at CALL
ERROR = !CALL: subqueries not allowed inside CALL statements
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-17674/.s.monetdb.33889
+QUERY = SELECT row_number(1) OVER () FROM integers i1; --error,
row_number(int) doesn't exist
+ERROR = !SELECT: window function 'row_number(int(32))' not found
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-17674/.s.monetdb.33889
+QUERY = SELECT ntile(1,1) OVER () FROM integers i1; --error, ntile(int,int)
doesn't exist
+ERROR = !SELECT: window function 'ntile(int(32), tinyint(1))' not found
+CODE = 42000
# 15:41:18 >
# 15:41:18 > "Done."
diff --git a/sql/test/subquery/Tests/subquery4.stable.out
b/sql/test/subquery/Tests/subquery4.stable.out
--- a/sql/test/subquery/Tests/subquery4.stable.out
+++ b/sql/test/subquery/Tests/subquery4.stable.out
@@ -143,7 +143,34 @@ stdout of test 'subquery4` in directory
% int # type
% 1 # length
[ 1 ]
-#UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales);
+#SELECT (SELECT NTILE(i1.i) OVER ()) mycalc FROM integers i1 ORDER BY mycalc
NULLS LAST;
+% . # table_name
+% mycalc # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ NULL ]
+#SELECT (SELECT NTILE(i1.i) OVER (PARTITION BY i1.i)) mycalc FROM integers i1
ORDER BY mycalc NULLS LAST;
+% . # table_name
+% mycalc # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ NULL ]
+#SELECT (SELECT NTILE(i1.i) OVER (PARTITION BY i1.i ORDER BY i1.i)) mycalc
FROM integers i1 ORDER BY mycalc NULLS LAST;
+% . # table_name
+% %4 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ NULL ]
+#UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales);
--4 rows affected
[ 4 ]
#SELECT col4 FROM another_T;
% sys.another_t # table_name
@@ -184,6 +211,32 @@ stdout of test 'subquery4` in directory
[ 10, 55 ]
[ 10, 555 ]
[ 10, 5555 ]
+#UPDATE another_T SET col7 = (SELECT NTILE(col1) OVER ()); --4 rows affected
+[ 4 ]
+#SELECT col7 FROM another_T;
+% sys.another_t # table_name
+% col7 # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
