Changeset: 5547c1ddddb2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5547c1ddddb2
Added Files:
sql/test/merge-partitions/Tests/mergepart16.stable.err
sql/test/merge-partitions/Tests/mergepart16.stable.out
Modified Files:
sql/include/sql_catalog.h
sql/server/rel_propagate.c
sql/server/rel_schema.c
sql/storage/store.c
sql/test/merge-partitions/Tests/mergepart16.sql
Branch: merge-partitions
Log Message:
Progress for nested partitioned tables handling.
There are cases to handle: updates and adding a new child table to a child of
other partitioned table.
diffs (truncated from 509 to 300 lines):
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -500,6 +500,12 @@ typedef enum table_types {
tt_range_partition_exp = 10 /* partitioned by a range of values on an
expression */
} table_types;
+#define TABLE_TYPE_DESCRIPTION(tt)
\
+(tt == tt_table)?"TABLE":(tt == tt_view)?"VIEW":(tt == tt_merge_table)?"MERGE
TABLE": \
+(tt == tt_stream)?"STREAM TABLE":(tt == tt_remote)?"REMOTE TABLE":
\
+(tt == tt_list_partition_col || tt == tt_list_partition_exp)?"LIST PARTITION
TABLE": \
+(tt == tt_range_partition_col || tt == tt_range_partition_exp)?"RANGE
PARTITION TABLE":"REPLICA TABLE"
+
#define isTable(x) (x->type==tt_table)
#define isView(x) (x->type==tt_view)
#define isNonPartitionedTable(x) (x->type==tt_merge_table)
diff --git a/sql/server/rel_propagate.c b/sql/server/rel_propagate.c
--- a/sql/server/rel_propagate.c
+++ b/sql/server/rel_propagate.c
@@ -79,6 +79,23 @@ rel_generate_anti_insert_expression(mvc
{
sql_exp* res = NULL;
+ if((*anti_rel)->op != op_project) { //needed for nested partitions
+ sql_rel *inserts;
+ list *l = new_exp_list(sql->sa);
+ *anti_rel = rel_project(sql->sa, *anti_rel, l);
+
+ inserts = ((sql_rel*)((*anti_rel)->l))->l;
+ for (node *n = t->columns.set->h, *m = inserts->exps->h; n &&
m; n = n->next, m = m->next) {
+ sql_column *col = n->data;
+ sql_exp *before = m->data;
+ sql_exp *help = exp_column(sql->sa, t->base.name,
col->base.name, exp_subtype(before), before->card,
+
has_nil(before), is_intern(before));
+ help->l = sa_strdup(sql->sa, exp_relname(before));
+ help->r = sa_strdup(sql->sa, exp_name(before));
+ list_append(l, help);
+ }
+ }
+
if(isPartitionedByColumnTable(t)) {
int colr = t->part.pcol->colnr;
res = list_fetch((*anti_rel)->exps, colr);
@@ -231,6 +248,7 @@ rel_alter_table_add_partition_range(mvc*
rel_psm->card = CARD_MULTI;
rel_psm->nrcols = 0;
+ sql->caching = 0;
return rel_exception(sql->sa, rel_psm, anti_rel,
list_append(new_exp_list(sql->sa), exception));
}
@@ -309,6 +327,7 @@ rel_alter_table_add_partition_list(mvc *
rel_psm->card = CARD_MULTI;
rel_psm->nrcols = 0;
+ sql->caching = 0;
return rel_exception(sql->sa, rel_psm, anti_rel,
list_append(new_exp_list(sql->sa), exception));
}
@@ -590,8 +609,7 @@ rel_generate_subinserts(mvc *sql, sql_re
}
new_table = rel_basetable(sql, sub, sub->base.name);
- if(list_length(sub->members.set) == 0) //if this table has no
partitions, set it as used
- new_table->p = prop_create(sql->sa, PROP_USED,
new_table->p);
+ new_table->p = prop_create(sql->sa, PROP_USED, new_table->p);
//don't create infinite loops in the optimizer
if(isPartitionedByExpressionTable(t)) {
sql_exp *del;
@@ -771,34 +789,50 @@ rel_subtable_insert(mvc *sql, sql_rel *r
}
sql_rel *
-rel_propagate(mvc *sql, sql_rel *rel, int *changes) //TODO sql->caching = 0;
+rel_propagate(mvc *sql, sql_rel *rel, int *changes)
{
- sql_rel *l = rel->l;
+ bool isSubtable = false;
+ sql_rel *l = rel->l, *propagate = rel;
if(l->op == op_basetable) {
sql_table *t = l->l;
- if((isRangePartitionTable(t) || isListPartitionTable(t)) &&
!find_prop(l->p, PROP_USED)) {
- assert(list_length(t->members.set) > 0);
- if(is_delete(rel->op) || is_truncate(rel->op)) {
//propagate deletions to the partitions
- return rel_propagate_delete(sql, rel, t,
changes);
- } else if(is_insert(rel->op)) { //on inserts create a
selection for each partition
- return rel_propagate_insert(sql, rel, t,
changes);
- } else if(is_update(rel->op)) { //for updates create
both a insertion and deletion for each partition
- return rel_propagate_update(sql, rel, t,
changes);
- } else {
- assert(0);
- }
- } else if(t->p) {
+ if(t->p) {
sql_part *pt = find_sql_part(t->p, t->base.name);
if(!pt) {
t->p = NULL;
} else if((isRangePartitionTable(t->p) ||
isListPartitionTable(t->p)) && !find_prop(l->p, PROP_USED)) {
+ isSubtable = true;
if(is_insert(rel->op)) { //insertion directly
to sub-table (must do validation)
- return rel_subtable_insert(sql, rel, t,
changes);
+ sql->caching = 0;
+ rel = rel_subtable_insert(sql, rel, t,
changes);
+ propagate = rel->l;
}
}
}
+ if(isRangePartitionTable(t) || isListPartitionTable(t)) {
+ assert(list_length(t->members.set) > 0);
+ if(is_delete(propagate->op) ||
is_truncate(propagate->op)) { //propagate deletions to the partitions
+ sql->caching = 0;
+ rel = rel_propagate_delete(sql, rel, t,
changes);
+ } else if(is_insert(propagate->op)) { //on inserts
create a selection for each partition
+ sql->caching = 0;
+ if(isSubtable) {
+ rel->l = rel_propagate_insert(sql,
propagate, t, changes);
+ } else {
+ rel = rel_propagate_insert(sql, rel, t,
changes);
+ }
+ } else if(is_update(propagate->op)) { //for updates
propagate like in deletions
+ sql->caching = 0;
+ if(isSubtable) {
+ rel->l = rel_propagate_update(sql,
propagate, t, changes);
+ } else {
+ rel = rel_propagate_update(sql, rel, t,
changes);
+ }
+ } else {
+ assert(0);
+ }
+ }
}
return rel;
}
diff --git a/sql/server/rel_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -1077,9 +1077,7 @@ rel_create_table(mvc *sql, sql_schema *s
if ((tt == tt_merge_table || tt == tt_list_partition_col || tt
== tt_range_partition_col ||
tt == tt_list_partition_exp || tt ==
tt_range_partition_exp || tt == tt_remote || tt == tt_replica_table) &&
with_data)
return sql_error(sql, 02, SQLSTATE(42000) "CREATE
TABLE: cannot create %s table 'with data'",
- tt == tt_merge_table?"MERGE TABLE":tt ==
tt_remote?"REMOTE TABLE":
- (tt == tt_list_partition_col || tt ==
tt_list_partition_exp)?"LIST PARTITION TABLE":
- (tt == tt_range_partition_col || tt ==
tt_range_partition_exp)?"RANGE PARTITION TABLE":"REPLICA TABLE");
+
TABLE_TYPE_DESCRIPTION(tt));
/* create table */
if ((t = mvc_create_table_as_subquery( sql, sq, s, name,
column_spec, temp, commit_action)) == NULL) {
@@ -1477,7 +1475,12 @@ sql_alter_table(mvc *sql, dlist *qname,
if (te->token == SQL_TABLE) {
if(!extra)
return rel_alter_table(sql->sa,
DDL_ALTER_TABLE_ADD_TABLE, sname, tname, sname, ntname, 0);
- if(extra->token == SQL_MERGE_PARTITION) {
+
+ if ((isMergeTable(pt) || isReplicaTable(pt)) &&
list_empty(pt->members.set))
+ return sql_error(sql, 02,
SQLSTATE(42000) "The %s table %s.%s should have at least one table associated",
+
TABLE_TYPE_DESCRIPTION(pt->type), spt->base.name, pt->base.name);
+
+ if(extra->token == SQL_MERGE_PARTITION) {
//partition to hold null values only
dlist* ll = extra->data.lval;
int update =
ll->h->next->next->next->data.i_val;
diff --git a/sql/storage/store.c b/sql/storage/store.c
--- a/sql/storage/store.c
+++ b/sql/storage/store.c
@@ -629,6 +629,7 @@ load_value_partition(sql_trans *tr, sql_
memset(&vvalue, 0, sizeof(ValRecord));
void *v = table_funcs.column_find_value(tr,
find_sql_column(values, "value"), rid);
ok = VALinit(&vvalue, TYPE_str, v);
+ _DELETE(v);
if(ok) {
if(VALisnil(&vvalue)) { /* check for null value */
diff --git a/sql/test/merge-partitions/Tests/mergepart16.sql
b/sql/test/merge-partitions/Tests/mergepart16.sql
--- a/sql/test/merge-partitions/Tests/mergepart16.sql
+++ b/sql/test/merge-partitions/Tests/mergepart16.sql
@@ -8,11 +8,15 @@ CREATE TABLE subt4 (a int, b varchar(32)
INSERT INTO testnestedpartitions VALUES (1, 'ups'); --error
+ALTER TABLE testnestedpartitions ADD TABLE subnested1 AS PARTITION IN ('1',
'2', '3'); --error
+ALTER TABLE subnested1 ADD TABLE subt1 AS PARTITION BETWEEN '-1' AND '2';
ALTER TABLE testnestedpartitions ADD TABLE subnested1 AS PARTITION IN ('1',
'2', '3');
-INSERT INTO testnestedpartitions VALUES (1, 'ups'); --error
-ALTER TABLE subnested1 ADD TABLE subt1 AS PARTITION BETWEEN '-1' AND '2';
-INSERT INTO testnestedpartitions VALUES (1, 'ups'); --error
+INSERT INTO subnested1 VALUES (-1, 'ups'); --error
+INSERT INTO subnested1 VALUES (1, 'ok');
+
+INSERT INTO testnestedpartitions VALUES (1, 'ok');
+INSERT INTO testnestedpartitions VALUES (3, 'ups'); --error
ALTER TABLE subnested1 DROP TABLE subt1;
ALTER TABLE subnested1 ADD TABLE subt1 AS PARTITION BETWEEN '0' AND '100';
@@ -23,11 +27,12 @@ SELECT a, b FROM subnested1;
SELECT a, b FROM subt1;
ALTER TABLE testnestedpartitions ADD TABLE subnested2 AS PARTITION IN ('3',
'4', '5'); --error
+ALTER TABLE subnested2 ADD TABLE subt2 AS PARTITION BETWEEN '5' AND '99';
+ALTER TABLE testnestedpartitions ADD TABLE subnested2 AS PARTITION IN ('3',
'4', '5'); --error
ALTER TABLE testnestedpartitions ADD TABLE subnested2 AS PARTITION IN ('4',
'5', '6') WITH NULL;
-ALTER TABLE subnested2 ADD TABLE subt2 AS PARTITION BETWEEN '1' AND '99';
-
INSERT INTO testnestedpartitions VALUES (2, 'going'), (5, 'through');
+INSERT INTO testnestedpartitions VALUES (4, 'not'), (2, 'going'), (5,
'through'); --error
SELECT a, b FROM testnestedpartitions;
SELECT a, b FROM subnested1;
@@ -47,6 +52,18 @@ SELECT count(*) FROM subnested2;
SELECT count(*) FROM subt1;
SELECT count(*) FROM subt2;
+INSERT INTO testnestedpartitions VALUES (1, 'another'), (1, 'test'), (5,
'to'), (5, 'go');
+
+TRUNCATE subnested1;
+
+SELECT count(*) FROM testnestedpartitions;
+SELECT count(*) FROM subnested1;
+SELECT count(*) FROM subnested2;
+SELECT count(*) FROM subt1;
+SELECT count(*) FROM subt2;
+SELECT count(*) FROM subt3;
+SELECT count(*) FROM subt4;
+
ALTER TABLE subnested1 DROP TABLE subt1;
ALTER TABLE subnested1 DROP TABLE subt3;
ALTER TABLE subnested2 DROP TABLE subt2;
diff --git a/sql/test/merge-partitions/Tests/mergepart16.stable.err
b/sql/test/merge-partitions/Tests/mergepart16.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-partitions/Tests/mergepart16.stable.err
@@ -0,0 +1,67 @@
+stderr of test 'mergepart16` in directory 'sql/test/merge-partitions` itself:
+
+
+# 16:36:19 >
+# 16:36:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=30967" "--set"
"mapi_usock=/var/tmp/mtest-20495/.s.monetdb.30967" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-merge-partitions/BUILD/var/MonetDB/mTests_sql_test_merge-partitions"
"--set" "embedded_c=true"
+# 16:36:19 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-partitions/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 30967
+# cmdline opt mapi_usock = /var/tmp/mtest-20495/.s.monetdb.30967
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-partitions/BUILD/var/MonetDB/mTests_sql_test_merge-partitions
+# cmdline opt embedded_c = true
+# cmdline opt gdk_debug = 553648138
+
+# 16:36:19 >
+# 16:36:19 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-20495" "--port=30967"
+# 16:36:19 >
+
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = INSERT INTO testnestedpartitions VALUES (1, 'ups'); --error
+ERROR = !INSERT INTO: list partitioned table 'testnestedpartitions' has no
partitions set
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = ALTER TABLE testnestedpartitions ADD TABLE subnested1 AS PARTITION IN
('1', '2', '3'); --error
+ERROR = !The RANGE PARTITION TABLE table sys.subnested1 should have at least
one table associated
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = INSERT INTO subnested1 VALUES (-1, 'ups'); --error
+ERROR = !INSERT: table sys.subnested1 is part of merge table
sys.testnestedpartitions and the insert violates the partition list of values
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = INSERT INTO testnestedpartitions VALUES (3, 'ups'); --error
+ERROR = !INSERT: the insert violates the partition range of values
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = ALTER TABLE testnestedpartitions ADD TABLE subnested2 AS PARTITION IN
('3', '4', '5'); --error
+ERROR = !The RANGE PARTITION TABLE table sys.subnested2 should have at least
one table associated
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = ALTER TABLE testnestedpartitions ADD TABLE subnested2 AS PARTITION IN
('3', '4', '5'); --error
+ERROR = !ALTER TABLE: the new partition is conflicting with the existing
partition sys.subnested1
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = INSERT INTO testnestedpartitions VALUES (4, 'not'), (2, 'going'), (5,
'through'); --error
+ERROR = !INSERT: the insert violates the partition range of values
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-20495/.s.monetdb.30967
+QUERY = ALTER TABLE subnested1 ADD TABLE subt3 AS PARTITION BETWEEN '1' AND
'200'; --error
+ERROR = !ALTER TABLE: conflicting partitions: 1 to 200 and 0 to 100 from table
sys.subt1
+CODE = 42000
+
+# 16:36:19 >
+# 16:36:19 > "Done."
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
