Changeset: 02b2a3651773 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=02b2a3651773 Modified Files: sql/server/rel_select.c sql/server/sql_partition.c sql/server/sql_semantic.c sql/test/group-concat/Tests/groupconcat05.sql sql/test/group-concat/Tests/groupconcat05.stable.out sql/test/merge-partitions/Tests/mergepart20.sql sql/test/merge-partitions/Tests/mergepart20.stable.err sql/test/subquery/Tests/subquery3.stable.err sql/test/subquery/Tests/subquery4.stable.err sql/test/subquery/Tests/subquery4.stable.out sql/test/testdb-reload/Tests/reload.stable.out Branch: Jun2020 Log Message:
Small cleanup on compilation expressions and other tests approval diffs (297 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 @@ -3300,7 +3300,7 @@ static sql_exp * return e; } else if (is_sql_values(f)) { char *uaname = GDKmalloc(strlen(aname) + 1); - sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed inside a list of VALUES", + sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed on an unique value", uaname ? toUpperCopy(uaname, aname) : aname); if (uaname) GDKfree(uaname); @@ -3412,7 +3412,7 @@ static sql_exp * return e; } else if (is_sql_values(f)) { char *uaname = GDKmalloc(strlen(aname) + 1); - sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed inside a list of VALUES", + sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate functions not allowed on an unique value", uaname ? toUpperCopy(uaname, aname) : aname); if (uaname) GDKfree(uaname); @@ -3462,7 +3462,7 @@ static sql_exp * if (is_sql_aggr(sql_state)) return sql_error(sql, 05, SQLSTATE(42000) "SELECT: aggregate function calls cannot be nested"); if (is_sql_values(sql_state)) - return sql_error(sql, 05, SQLSTATE(42000) "SELECT: aggregate functions not allowed inside a list of VALUES"); + return sql_error(sql, 05, SQLSTATE(42000) "SELECT: aggregate functions not allowed on an unique value"); if (is_sql_update_set(sql_state)) return sql_error(sql, 05, SQLSTATE(42000) "SELECT: aggregate functions not allowed in SET clause"); if (is_sql_join(sql_state)) @@ -4719,7 +4719,7 @@ rel_rankop(sql_query *query, sql_rel **r if (is_sql_update_set(f) || is_sql_values(f) || is_sql_join(f) || is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) || is_psm_call(f) || is_sql_from(f)) { char *uaname = GDKmalloc(strlen(aname) + 1); - const char *clause = is_sql_update_set(f)?"in SET clause (use subquery)":is_sql_values(f)?"inside a list of VALUES": + const char *clause = is_sql_update_set(f)?"in SET clause (use subquery)":is_sql_values(f)?"on an unique value": is_sql_join(f)?"in JOIN conditions":is_sql_where(f)?"in WHERE clause":is_sql_groupby(f)?"in GROUP BY clause": is_psm_call(f)?"in CALL":is_sql_from(f)?"in functions in FROM":"in HAVING clause"; (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window function '%s' not allowed %s", diff --git a/sql/server/sql_partition.c b/sql/server/sql_partition.c --- a/sql/server/sql_partition.c +++ b/sql/server/sql_partition.c @@ -10,6 +10,7 @@ #include "sql_partition.h" #include "rel_rel.h" +#include "rel_exp.h" #include "sql_mvc.h" #include "sql_catalog.h" #include "sql_relation.h" @@ -217,48 +218,6 @@ exp_find_table_columns(mvc *sql, sql_exp } } -static str -find_expression_type(sql_exp *e, sql_subtype *tpe) -{ - switch (e->type) { - case e_convert: { - assert(list_length(e->r) == 2); - *tpe = *(sql_subtype *)list_fetch(e->r, 1); - } break; - case e_atom: { - if (e->l) { - atom *a = e->l; - *tpe = a->tpe; - } else if (e->r) { - *tpe = e->tpe; - } else if (e->f) { - throw(SQL,"sql.partition", SQLSTATE(42000) "List of values not allowed in expressions"); - } else { - throw(SQL,"sql.partition", SQLSTATE(42000) "Variables/parameters are not allowed in expressions"); - } - } break; - case e_func: { - sql_subfunc *f = e->f; - sql_func *func = f->func; - if (list_length(func->res) != 1) - throw(SQL,"sql.partition", SQLSTATE(42000) "An expression should return a single value"); - *tpe = *(sql_subtype *)f->res->h->data; - } break; - case e_cmp: { - sql_subtype *other = sql_bind_localtype("bit"); - *tpe = *other; - } break; - case e_column: { - *tpe = e->tpe; - } break; - case e_psm: - throw(SQL,"sql.partition", SQLSTATE(42000) "PSM calls are not allowed in expressions"); - case e_aggr: - throw(SQL,"sql.partition", SQLSTATE(42000) "Aggregation functions are not allowed in expressions"); - } - return NULL; -} - str bootstrap_partition_expression(mvc* sql, sql_allocator *rsa, sql_table *mt, int instantiate) { @@ -288,9 +247,7 @@ bootstrap_partition_expression(mvc* sql, mt->part.pexp->cols = sa_list(rsa); exp_find_table_columns(sql, exp, mt, mt->part.pexp->cols); - if ((msg = find_expression_type(exp, &(mt->part.pexp->type))) != NULL) - return msg; - + mt->part.pexp->type = *exp_subtype(exp); sql_ec = mt->part.pexp->type.type->eclass; if (!(sql_ec == EC_BIT || EC_VARCHAR(sql_ec) || EC_TEMP(sql_ec) || sql_ec == EC_POS || sql_ec == EC_NUM || EC_INTERVAL(sql_ec)|| sql_ec == EC_DEC || sql_ec == EC_BLOB)) { @@ -306,8 +263,7 @@ bootstrap_partition_expression(mvc* sql, if (instantiate) { r = rel_project(sql->sa, r, NULL); - r->exps = sa_list(sql->sa); - list_append(r->exps, exp); + exp = rel_project_add_exp(sql, r, exp); if (r) r = sql_processrelation(sql, r, 0); diff --git a/sql/server/sql_semantic.c b/sql/server/sql_semantic.c --- a/sql/server/sql_semantic.c +++ b/sql/server/sql_semantic.c @@ -472,13 +472,12 @@ symbol2string(mvc *sql, symbol *se, int _DELETE(tpe); return res; } - case SQL_AGGR: - case SQL_SELECT: - case SQL_CASE: - case SQL_COALESCE: - case SQL_NULLIF: - default: - return NULL; + default: { + const char *msg = "SQL feature not yet available for expressions and default values: "; + char *tok_str = token2string(se->token); + if ((*err = NEW_ARRAY(char, strlen(msg) + strlen(tok_str) + 1))) + stpcpy(stpcpy(*err, msg), tok_str); + } } return NULL; } diff --git a/sql/test/group-concat/Tests/groupconcat05.sql b/sql/test/group-concat/Tests/groupconcat05.sql --- a/sql/test/group-concat/Tests/groupconcat05.sql +++ b/sql/test/group-concat/Tests/groupconcat05.sql @@ -28,6 +28,8 @@ select group_concat(a, 8) from testmore; select group_concat(a, b) from testmore; select group_concat(b, a) from testmore; +select group_concat('😀', '😁') over () as "😃" from (values (1),(2),(3), (NULL)) v; + /* listagg is the SQL standard name of group_concat */ select listagg(a) from testmore; select listagg(b) from testmore; diff --git a/sql/test/group-concat/Tests/groupconcat05.stable.out b/sql/test/group-concat/Tests/groupconcat05.stable.out --- a/sql/test/group-concat/Tests/groupconcat05.stable.out +++ b/sql/test/group-concat/Tests/groupconcat05.stable.out @@ -141,6 +141,15 @@ stdout of test 'groupconcat05` in direct % clob # type % 53 # length [ "another1testing1todo2lets3get2harder3even2more13even1" ] +#select group_concat('😀', '😁') over () as "😃" from (values (1),(2),(3), (NULL)) v; +% . # table_name +% 😃 # name +% clob # type +% 14 # length +[ "😀😁😀😁😀😁😀" ] +[ "😀😁😀😁😀😁😀" ] +[ "😀😁😀😁😀😁😀" ] +[ "😀😁😀😁😀😁😀" ] #select listagg(a) from testmore; % sys.%1 # table_name % %1 # name diff --git a/sql/test/merge-partitions/Tests/mergepart20.sql b/sql/test/merge-partitions/Tests/mergepart20.sql --- a/sql/test/merge-partitions/Tests/mergepart20.sql +++ b/sql/test/merge-partitions/Tests/mergepart20.sql @@ -58,3 +58,19 @@ CREATE MERGE TABLE testme(d int, e int, SELECT column_id, expression FROM table_partitions; DROP TABLE testme; DROP FUNCTION iamdummy; + +/* Testing bad expressions */ + +CREATE FUNCTION iamdummy(a int) RETURNS INT BEGIN RETURN SELECT a UNION ALL SELECT a; END; +CREATE MERGE TABLE testme(a int) PARTITION BY RANGE USING (iamdummy(a)); +CREATE TABLE subtable1 (a int); +ALTER TABLE testme ADD TABLE subtable1 AS PARTITION FROM RANGE MINVALUE TO RANGE MAXVALUE; +INSERT INTO testme VALUES (1); --error, more than one row + +ALTER TABLE testme DROP TABLE subtable1; +DROP TABLE testme; +DROP TABLE subtable1; +DROP FUNCTION iamdummy; + +CREATE MERGE TABLE testme(a int) PARTITION BY RANGE USING (SUM(a)); --error aggregations not allowed in expressions +CREATE MERGE TABLE testme(a int) PARTITION BY RANGE USING (AVG(a) OVER ()); --error window functions not not allowed in expressions diff --git a/sql/test/merge-partitions/Tests/mergepart20.stable.err b/sql/test/merge-partitions/Tests/mergepart20.stable.err --- a/sql/test/merge-partitions/Tests/mergepart20.stable.err +++ b/sql/test/merge-partitions/Tests/mergepart20.stable.err @@ -44,6 +44,18 @@ MAPI = (monetdb) /var/tmp/mtest-26529/. QUERY = INSERT INTO subtable1 VALUES (2, 'seventh'); --error ERROR = !INSERT: table sys.subtable1 is part of merge table sys.testme and the insert violates the partition range of values CODE = M0M29 +MAPI = (monetdb) /var/tmp/mtest-100770/.s.monetdb.31006 +QUERY = INSERT INTO testme VALUES (1); --error, more than one row +ERROR = !Cardinality violation, scalar value expected +CODE = 21000 +MAPI = (monetdb) /var/tmp/mtest-100770/.s.monetdb.31006 +QUERY = CREATE MERGE TABLE testme(a int) PARTITION BY RANGE USING (SUM(a)); --error aggregations not allowed in expressions +ERROR = !SUM: aggregate functions not allowed on an unique value +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-100770/.s.monetdb.31006 +QUERY = CREATE MERGE TABLE testme(a int) PARTITION BY RANGE USING (AVG(a) OVER ()); --error window functions not not allowed in expressions +ERROR = !CREATE MERGE TABLE: error compiling expression 'SQL feature not yet available for expressions and default values: WINDOW' +CODE = 42000 # 10:47:21 > # 10:47:21 > "Done." diff --git a/sql/test/subquery/Tests/subquery3.stable.err b/sql/test/subquery/Tests/subquery3.stable.err --- a/sql/test/subquery/Tests/subquery3.stable.err +++ b/sql/test/subquery/Tests/subquery3.stable.err @@ -161,7 +161,7 @@ MAPI = (monetdb) /var/tmp/mtest-60261/. QUERY = SELECT (SELECT 1 FROM (VALUES (MAX(2))) as i2) FROM integers i1; --error, aggregate functions are not allowed in VALUES -ERROR = !MAX: aggregate functions not allowed inside a list of VALUES +ERROR = !MAX: aggregate functions not allowed on an unique value CODE = 42000 MAPI = (monetdb) /var/tmp/mtest-60261/.s.monetdb.38639 QUERY = SELECT 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 @@ -234,7 +234,7 @@ ERROR = !SUM: missing group by CODE = 42000 MAPI = (monetdb) /var/tmp/mtest-60261/.s.monetdb.38639 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 +ERROR = !AVG: window function 'avg' not allowed on an unique value CODE = 42000 MAPI = (monetdb) /var/tmp/mtest-64327/.s.monetdb.34611 QUERY = CALL crashme(COUNT(1)); --error, not allowed 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 @@ -161,8 +161,8 @@ stdout of test 'subquery4` in directory [ 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 +#SELECT (SELECT NTILE(i1.i) OVER (PARTITION BY i1.i ORDER BY i1.i)) FROM integers i1 ORDER BY 1 NULLS LAST; +% .%4 # table_name % %4 # name % int # type % 1 # length diff --git a/sql/test/testdb-reload/Tests/reload.stable.out b/sql/test/testdb-reload/Tests/reload.stable.out --- a/sql/test/testdb-reload/Tests/reload.stable.out +++ b/sql/test/testdb-reload/Tests/reload.stable.out @@ -101297,6 +101297,26 @@ CREATE TABLE "testschema"."subtable3" ( "a" INTEGER, "b" VARCHAR(32) ); +CREATE MERGE TABLE "testschema"."testme2" ( + "a" INTEGER, + "b" VARCHAR(32) +) PARTITION BY RANGE ON (a); +CREATE TABLE "testschema"."subtable4" ( + "a" INTEGER, + "b" VARCHAR(32) +); +CREATE TABLE "testschema"."subtable5" ( + "a" INTEGER, + "b" VARCHAR(32) +); +CREATE MERGE TABLE "testschema"."testme3" ( + "a" INTEGER, + "b" VARCHAR(32) +) PARTITION BY RANGE ON (a); +CREATE TABLE "testschema"."subtable6" ( + "a" INTEGER, + "b" VARCHAR(32) +); CREATE MERGE TABLE "testschema"."testvaluespartitions" ( "a" INTEGER, "b" VARCHAR(32) @@ -101335,6 +101355,9 @@ CREATE TABLE "testschema"."""" ( ALTER TABLE "testschema"."testme" ADD TABLE "testschema"."subtable1" AS PARTITION FROM RANGE MINVALUE TO '11' WITH NULL VALUES; ALTER TABLE "testschema"."testme" ADD TABLE "testschema"."subtable2" AS PARTITION FROM '11' TO '20'; ALTER TABLE "testschema"."testme" ADD TABLE "testschema"."subtable3" AS PARTITION FROM '21' TO RANGE MAXVALUE; +ALTER TABLE "testschema"."testme2" ADD TABLE "testschema"."subtable4" AS PARTITION FROM RANGE MINVALUE TO RANGE MAXVALUE; +ALTER TABLE "testschema"."testme2" ADD TABLE "testschema"."subtable5" AS PARTITION FOR NULL VALUES; +ALTER TABLE "testschema"."testme3" ADD TABLE "testschema"."subtable6" AS PARTITION FROM RANGE MINVALUE TO RANGE MAXVALUE WITH NULL VALUES; ALTER TABLE "testschema"."testvaluespartitions" ADD TABLE "testschema"."sublimits1" AS PARTITION IN ('1', '2', '3'); ALTER TABLE "testschema"."testvaluespartitions" ADD TABLE "testschema"."sublimits2" AS PARTITION IN ('4', '5', '6') WITH NULL VALUES; ALTER TABLE "testschema"."testvaluespartitions" ADD TABLE "testschema"."sublimits3" AS PARTITION IN ('7', '8', '9'); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list