Changeset: e558e4633e42 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e558e4633e42
Added Files:
sql/test/merge-partitions/Tests/mergepart24.sql
sql/test/merge-partitions/Tests/mergepart24.stable.err
sql/test/merge-partitions/Tests/mergepart24.stable.out
Modified Files:
sql/backends/monet5/sql_cat.c
sql/common/sql_list.c
sql/server/rel_propagate.c
sql/server/sql_parser.y
sql/test/merge-partitions/Tests/All
sql/test/merge-partitions/Tests/mergepart23.py
sql/test/merge-partitions/Tests/mergepart23.stable.err
sql/test/merge-partitions/Tests/mergepart23.stable.out
Branch: merge-partitions
Log Message:
Fixes on expression type casting and added another test.
diffs (truncated from 500 to 300 lines):
diff --git a/sql/backends/monet5/sql_cat.c b/sql/backends/monet5/sql_cat.c
--- a/sql/backends/monet5/sql_cat.c
+++ b/sql/backends/monet5/sql_cat.c
@@ -225,9 +225,10 @@ alter_table_add_range_partition(mvc *sql
} else if(atomtostr(&err_max, &length, max) <
0) {
msg =
createException(SQL,"sql.alter_table_add_range_partition",SQLSTATE(HY001)
MAL_MALLOC_FAIL);
} else {
+ sql_table *errt = mvc_bind_table(sql,
mt->s, err->base.name);
msg =
createException(SQL,"sql.alter_table_add_range_partition",SQLSTATE(42000)
"ALTER TABLE: conflicting partitions: %s to %s and %s to %s from table %s.%s",
-
err_min, err_max, conflict_err_min, conflict_err_max, err->t->s->base.name,
err->t->base.name);
+
err_min, err_max, conflict_err_min, conflict_err_max, errt->s->base.name,
errt->base.name);
}
}
break;
diff --git a/sql/common/sql_list.c b/sql/common/sql_list.c
--- a/sql/common/sql_list.c
+++ b/sql/common/sql_list.c
@@ -179,7 +179,7 @@ list_append_with_validate(list *l, void
void*
list_append_sorted(list *l, void *data, fcmpvalidate cmp)
{
- node *n = node_create(l->sa, data), *m;
+ node *n = node_create(l->sa, data), *m, *prev = NULL;
int first = 1, comp = 0;
void* err = NULL;
@@ -196,6 +196,7 @@ list_append_sorted(list *l, void *data,
if(comp < 0)
break;
first = 0;
+ prev = m;
}
if(first) {
n->next = l->h;
@@ -203,8 +204,11 @@ list_append_sorted(list *l, void *data,
} else if(!m) {
l->t->next = n;
l->t = n;
- } else
- m->next = n;
+ } else {
+ assert(prev);
+ n->next = m;
+ prev->next = n;
+ }
}
l->cnt++;
MT_lock_set(&l->ht_lock);
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
@@ -179,11 +179,11 @@ rel_alter_table_add_partition_range(mvc*
return NULL;
e1 = exp_copy(sql->sa, pmin);
- if (subtype_cmp(&e1->tpe, &tpe) != 0)
+ if (subtype_cmp(exp_subtype(e1), &tpe) != 0)
e1 = exp_convert(sql->sa, e1, &e1->tpe, &tpe);
e2 = exp_copy(sql->sa, pmax);
- if (subtype_cmp(&e2->tpe, &tpe) != 0)
+ if (subtype_cmp(exp_subtype(e2), &tpe) != 0)
e2 = exp_convert(sql->sa, e2, &e2->tpe, &tpe);
anti_exp = exp_compare2(sql->sa, anti_le, e1, e2, 3);
@@ -257,8 +257,8 @@ rel_alter_table_add_partition_list(mvc *
for (dnode *dn = values->h; dn ; dn = dn->next) { /* parse the
atoms and generate the expressions */
symbol* next = dn->data.sym;
sql_exp *pnext = generate_partition_limits(sql,
&rel_psm, next, tpe);
- if (subtype_cmp(&pnext->tpe, &tpe) != 0)
- pnext = exp_convert(sql->sa, pnext,
&pnext->tpe, &tpe);
+ if (subtype_cmp(exp_subtype(pnext), &tpe) != 0)
+ pnext = exp_convert(sql->sa, pnext,
exp_subtype(pnext), &tpe);
if(next->token == SQL_NULL)
return sql_error(sql, 02, SQLSTATE(42000)
"ALTER TABLE: a list value cannot be null");
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -1513,8 +1513,7 @@ opt_partition_by:
;
partition_list_value:
- literal
- | null
+ simple_scalar_exp { $$ = $1; }
;
partition_range_from:
diff --git a/sql/test/merge-partitions/Tests/All
b/sql/test/merge-partitions/Tests/All
--- a/sql/test/merge-partitions/Tests/All
+++ b/sql/test/merge-partitions/Tests/All
@@ -22,3 +22,4 @@ mergepart20
mergepart21
mergepart22
mergepart23
+mergepart24
diff --git a/sql/test/merge-partitions/Tests/mergepart23.py
b/sql/test/merge-partitions/Tests/mergepart23.py
--- a/sql/test/merge-partitions/Tests/mergepart23.py
+++ b/sql/test/merge-partitions/Tests/mergepart23.py
@@ -24,24 +24,30 @@ CREATE TABLE subtable3 (a int, b varchar
CREATE TABLE subtable4 (a int, b varchar(32));\
ALTER TABLE testme ADD TABLE subtable1 AS PARTITION BETWEEN 5 AND 10;\
CREATE MERGE TABLE anothertest (a int, b varchar(32)) PARTITION BY RANGE USING
(a + 1);\
-ALTER TABLE anothertest ADD TABLE subtable3 AS PARTITION BETWEEN 11 AND 20;
+ALTER TABLE anothertest ADD TABLE subtable3 AS PARTITION BETWEEN 11 AND 20;\
+SELECT "minimum", "maximum" FROM range_partitions;
'''
script2 = '''\
-ALTER TABLE anothertest ADD TABLE subtable1 AS PARTITION BETWEEN 11 AND 20;
+SELECT "minimum", "maximum" FROM range_partitions;\
+ALTER TABLE anothertest ADD TABLE subtable1 AS PARTITION BETWEEN 11 AND 20;\
+SELECT "minimum", "maximum" FROM range_partitions;
'''
script3 = '''\
+SELECT "minimum", "maximum" FROM range_partitions;\
ALTER TABLE testme ADD TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;\
-ALTER TABLE anothertest ADD TABLE subtable4 AS PARTITION BETWEEN 11 AND 20;\
-INSERT INTO testme VALUES (1, 'one'), (12, 'two'), (13, 'three'), (15,
'four');\
-INSERT INTO anothertest VALUES (1, 'one'), (12, 'two'), (13, 'three'), (15,
'four');\
+ALTER TABLE anothertest ADD TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;\
+INSERT INTO testme VALUES (5, 'one'), (12, 'two'), (13, 'three'), (15,
'four');\
+INSERT INTO anothertest VALUES (11, 'one'), (12, 'two'), (13, 'three'), (15,
'four');\
SELECT a,b FROM testme;\
SELECT a,b FROM anothertest;\
+SELECT "minimum", "maximum" FROM range_partitions;\
ALTER TABLE testme DROP TABLE subtable1;\
ALTER TABLE testme DROP TABLE subtable2;\
ALTER TABLE anothertest DROP TABLE subtable3;\
ALTER TABLE anothertest DROP TABLE subtable4;\
+SELECT "minimum", "maximum" FROM range_partitions;
DROP TABLE testme;\
DROP TABLE subtable1;\
DROP TABLE subtable2;\
diff --git a/sql/test/merge-partitions/Tests/mergepart23.stable.err
b/sql/test/merge-partitions/Tests/mergepart23.stable.err
--- a/sql/test/merge-partitions/Tests/mergepart23.stable.err
+++ b/sql/test/merge-partitions/Tests/mergepart23.stable.err
@@ -22,8 +22,8 @@ stderr of test 'mergepart23` in director
# cmdline opt monet_prompt =
# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-partitions/BUILD/var/MonetDB/mTests_sql_test_merge-partitions
# cmdline opt gdk_debug = 553648138
-MAPI = (monetdb) /var/tmp/mtest-20394/.s.monetdb.38240
-QUERY = ALTER TABLE anothertest ADD TABLE subtable1 AS PARTITION BETWEEN 11
AND 20;
+MAPI = (monetdb) /var/tmp/mtest-24606/.s.monetdb.34023
+QUERY = SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE
anothertest ADD TABLE subtable1 AS PARTITION BETWEEN 11 AND 20;SELECT
"minimum", "maximum" FROM range_partitions;
ERROR = !ALTER TABLE: table sys.subtable1 is already part of another range
partition table
CODE = 42000
# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-merge-partitions/BUILD/var/monetdb5/dbfarm/demo
diff --git a/sql/test/merge-partitions/Tests/mergepart23.stable.out
b/sql/test/merge-partitions/Tests/mergepart23.stable.out
--- a/sql/test/merge-partitions/Tests/mergepart23.stable.out
+++ b/sql/test/merge-partitions/Tests/mergepart23.stable.out
@@ -5,14 +5,21 @@ stdout of test 'mergepart23` in director
# 16:51:10 > "/usr/bin/python" "mergepart23.py" "mergepart23"
# 16:51:10 >
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
-#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+#CREATE MERGE TABLE testme (a int, b varchar(32)) PARTITION BY RANGE ON
(a);CREATE TABLE subtable1 (a int, b varchar(32));CREATE TABLE subtable2 (a
int, b varchar(32));CREATE TABLE subtable3 (a int, b varchar(32));CREATE TABLE
subtable4 (a int, b varchar(32));ALTER TABLE testme ADD TABLE subtable1 AS
PARTITION BETWEEN 5 AND 10;CREATE MERGE TABLE anothertest (a int, b
varchar(32)) PARTITION BY RANGE USING (a + 1);ALTER TABLE anothertest ADD TABLE
subtable3 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 2, 2 # length
+[ "5", "10" ]
+[ "11", "20" ]
# MonetDB 5 server v11.30.0
# This is an unreleased version
# Serving database 'mTests_sql_test_merge-partitions', using 8 threads
@@ -66,6 +73,20 @@ stdout of test 'mergepart23` in director
# loading sql script: 90_generator.sql
# loading sql script: 90_generator_hge.sql
# loading sql script: 99_system.sql
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE anothertest ADD
TABLE subtable1 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 2, 2 # length
+[ "5", "10" ]
+[ "11", "20" ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE anothertest ADD
TABLE subtable1 AS PARTITION BETWEEN 11 AND 20;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 2, 2 # length
+[ "5", "10" ]
+[ "11", "20" ]
# MonetDB 5 server v11.30.0
# This is an unreleased version
# Serving database 'mTests_sql_test_merge-partitions', using 8 threads
@@ -78,6 +99,61 @@ stdout of test 'mergepart23` in director
# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-1771/.s.monetdb.33958
# MonetDB/GIS module loaded
# MonetDB/SQL module loaded
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 2, 2 # length
+[ "5", "10" ]
+[ "11", "20" ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+[ 4 ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+[ 4 ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+% .testme, .testme # table_name
+% a, b # name
+% int, varchar # type
+% 2, 5 # length
+[ 5, "one" ]
+[ 12, "two" ]
+[ 13, "three" ]
+[ 15, "four" ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+% .anothertest, .anothertest # table_name
+% a, b # name
+% int, varchar # type
+% 2, 5 # length
+[ 11, "one" ]
+[ 12, "two" ]
+[ 13, "three" ]
+[ 15, "four" ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 2, 2 # length
+[ "5", "10" ]
+[ "11", "20" ]
+[ "11", "20" ]
+[ "21", "30" ]
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+#SELECT "minimum", "maximum" FROM range_partitions;ALTER TABLE testme ADD
TABLE subtable2 AS PARTITION BETWEEN 11 AND 20;ALTER TABLE anothertest ADD
TABLE subtable4 AS PARTITION BETWEEN 21 AND 30;INSERT INTO testme VALUES (5,
'one'), (12, 'two'), (13, 'three'), (15, 'four');INSERT INTO anothertest VALUES
(11, 'one'), (12, 'two'), (13, 'three'), (15, 'four');SELECT a,b FROM
testme;SELECT a,b FROM anothertest;SELECT "minimum", "maximum" FROM
range_partitions;ALTER TABLE testme DROP TABLE subtable1;ALTER TABLE testme
DROP TABLE subtable2;ALTER TABLE anothertest DROP TABLE subtable3;ALTER TABLE
anothertest DROP TABLE subtable4;SELECT "minimum", "maximum" FROM
range_partitions;
+% .range_partitions, .range_partitions # table_name
+% minimum, maximum # name
+% varchar, varchar # type
+% 0, 0 # length
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
+#DROP TABLE testme;DROP TABLE subtable1;DROP TABLE subtable2;DROP TABLE
anothertest;DROP TABLE subtable3;DROP TABLE subtable4;
# MonetDB 5 server v11.30.0
# This is an unreleased version
# Serving database 'mTests_sql_test_merge-partitions', using 8 threads
diff --git a/sql/test/merge-partitions/Tests/mergepart24.sql
b/sql/test/merge-partitions/Tests/mergepart24.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-partitions/Tests/mergepart24.sql
@@ -0,0 +1,44 @@
+CREATE MERGE TABLE testagain (a int, b varchar(32)) PARTITION BY VALUES ON (a);
+CREATE TABLE sublimits1 (a int, b varchar(32));
+CREATE TABLE sublimits2 (a int, b varchar(32));
+
+CREATE FUNCTION addone(a int) RETURNS INT BEGIN RETURN a + 1; END;
+CREATE FUNCTION addtwo(a tinyint) RETURNS TINYINT BEGIN RETURN a + 2; END;
+
+ALTER TABLE testagain ADD TABLE sublimits1 AS PARTITION IN (2, -2, addone(0),
1 + 2);
+ALTER TABLE testagain ADD TABLE sublimits2 AS PARTITION IN (addone(10),
addone(9) + 1); --error
+ALTER TABLE testagain ADD TABLE sublimits2 AS PARTITION IN (50, '60',
addone(0)); --error
+ALTER TABLE testagain ADD TABLE sublimits2 AS PARTITION IN (-100 * 2, '-90',
'120', addtwo(55), 11.2);
+
+SELECT "value" FROM value_partitions;
+
+ALTER TABLE testagain DROP TABLE sublimits1;
+ALTER TABLE testagain DROP TABLE sublimits2;
+
+SELECT "value" FROM value_partitions;
+
+DROP TABLE testagain;
+DROP TABLE sublimits1;
+DROP TABLE sublimits2;
+
+CREATE MERGE TABLE testing (a int, b varchar(32)) PARTITION BY RANGE USING (a
- 2);
+CREATE TABLE sublimits1 (a int, b varchar(32));
+CREATE TABLE sublimits2 (a int, b varchar(32));
+
+ALTER TABLE testing ADD TABLE sublimits1 AS PARTITION BETWEEN 28 + 2 AND 72 -
2;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
