Changeset: 9ac98d855352 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9ac98d855352
Added Files:
sql/test/miscellaneous/Tests/unique_keys.test
Modified Files:
sql/server/rel_optimizer.c
sql/storage/store.c
sql/test/miscellaneous/Tests/All
sql/test/miscellaneous/Tests/simple_plans.test
Branch: antipush
Log Message:
Update unique constraint when keys are dropped
diffs (250 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -5356,7 +5356,7 @@ find_candidate_join2semi(visitor *v, sql
sql_exp *e = n->data;
ok |= e->type == e_cmp && e->flag == cmp_equal
&& !exp_has_func(e) && !rel_rebind_exp(v->sql, l, e) && !rel_rebind_exp(v->sql,
r, e) &&
- (found == ALL_VALUES_DISTINCT ||
!is_semantics(e) || (!has_nil((sql_exp *)e->l) && !has_nil((sql_exp *)e->r)));
+ (found == ALL_VALUES_DISTINCT ||
!is_semantics(e) || !has_nil((sql_exp *)e->l) || !has_nil((sql_exp *)e->r));
}
}
diff --git a/sql/storage/store.c b/sql/storage/store.c
--- a/sql/storage/store.c
+++ b/sql/storage/store.c
@@ -4021,6 +4021,29 @@ sys_drop_idx(sql_trans *tr, sql_idx * i,
return res;
for (n = i->columns->h; n; n = n->next) {
sql_kc *ic = n->data;
+
+ if (hash_index(i->type)) { /* update new column's unique value
*/
+ int unique = 0;
+ sqlid cid = ic->c->base.id;
+ struct os_iter oi;
+
+ os_iterator(&oi, i->t->s->idxs, tr, NULL);
+ for (sql_base *b = oi_next(&oi); b; b = oi_next(&oi)) {
+ sql_idx *ti = (sql_idx*)b;
+
+ if (ti->base.id != i->base.id &&
hash_index(ti->type)) {
+ bool found = false;
+ for (node *m = ti->columns->h; m &&
!found; m = m->next) {
+ sql_kc *tic = m->data;
+
+ found |= tic->c->base.id == cid;
+ }
+ if (found)
+ unique = MAX(unique,
list_length(ti->columns) == 1 ? 2 : 1);
+ }
+ }
+ ic->c->unique = unique;
+ }
if ((res = sys_drop_ic(tr, i, ic)))
return res;
}
diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All
--- a/sql/test/miscellaneous/Tests/All
+++ b/sql/test/miscellaneous/Tests/All
@@ -17,5 +17,6 @@ deallocate
table_udf_missing_var
create_func_temp
simple_plans
+unique_keys
vessels
prepare
diff --git a/sql/test/miscellaneous/Tests/simple_plans.test
b/sql/test/miscellaneous/Tests/simple_plans.test
--- a/sql/test/miscellaneous/Tests/simple_plans.test
+++ b/sql/test/miscellaneous/Tests/simple_plans.test
@@ -588,80 +588,6 @@ project (
) [ "x"."x", "x"."y" ]
statement ok
-create table testkeys (a int primary key, b int unique)
-
-statement ok rowcount 3
-insert into testkeys values (1,1),(2,2),(3,3)
-
-# The following joins can be converted into semijoins
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a
-project (
-| semijoin (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL UNIQUE HASHCOL ]
-| ) [ "y"."y" = "testkeys"."a" NOT NULL HASHCOL ]
-) [ "y"."y" ]
-
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b
-project (
-| semijoin (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."b" UNIQUE HASHCOL ]
-| ) [ "y"."y" = "testkeys"."b" HASHCOL ]
-) [ "y"."y" ]
-
-query T nosort
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a or (y.y is null and testkeys.a is null)
-----
-project (
-| semijoin (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL UNIQUE HASHCOL ]
-| ) [ "y"."y" = "testkeys"."a" NOT NULL HASHCOL ]
-) [ "y"."y" ]
-
-# Here the inner join cannot be converted to a semijoin
-query T nosort
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b or (y.y is null and testkeys.b is null)
-----
-project (
-| join (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."b" UNIQUE HASHCOL ]
-| ) [ "y"."y" * = "testkeys"."b" HASHCOL ]
-) [ "y"."y" ]
-
-statement ok
-alter table testkeys drop constraint testkeys_a_pkey
-
-statement ok
-alter table testkeys drop constraint testkeys_b_unique
-
-statement ok rowcount 3
-insert into testkeys values (1,1),(2,2),(3,3)
-
-# No more unique properties
-query T nosort
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a
-----
-project (
-| join (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL ]
-| ) [ "y"."y" = "testkeys"."a" NOT NULL ]
-) [ "y"."y" ]
-
-query T nosort
-plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b;
-----
-project (
-| join (
-| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
-| | table("sys"."testkeys") [ "testkeys"."b" ]
-| ) [ "y"."y" = "testkeys"."b" ]
-) [ "y"."y" ]
-
-statement ok
rollback
statement ok
diff --git a/sql/test/miscellaneous/Tests/unique_keys.test
b/sql/test/miscellaneous/Tests/unique_keys.test
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/unique_keys.test
@@ -0,0 +1,106 @@
+statement ok
+create table testkeys (a int primary key, b int unique)
+
+statement ok rowcount 3
+insert into testkeys values (1,1),(2,2),(3,3)
+
+# The following joins can be converted into semijoins
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a
+project (
+| semijoin (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL UNIQUE HASHCOL ]
+| ) [ "y"."y" = "testkeys"."a" NOT NULL HASHCOL ]
+) [ "y"."y" ]
+
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b
+project (
+| semijoin (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."b" UNIQUE HASHCOL ]
+| ) [ "y"."y" = "testkeys"."b" HASHCOL ]
+) [ "y"."y" ]
+
+query T nosort
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a or (y.y is null and testkeys.a is null)
+----
+project (
+| semijoin (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL UNIQUE HASHCOL ]
+| ) [ "y"."y" = "testkeys"."a" NOT NULL HASHCOL ]
+) [ "y"."y" ]
+
+# Here the inner join cannot be converted to a semijoin
+query T nosort
+plan select y from (values (NULL),(1),(2),(cast(3 as int))) y(y) inner join
testkeys on y.y = testkeys.b or (y.y is null and testkeys.b is null)
+----
+project (
+| join (
+| | [ [ int "NULL", int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."b" UNIQUE HASHCOL ]
+| ) [ "y"."y" * = "testkeys"."b" HASHCOL ]
+) [ "y"."y" ]
+
+statement ok
+alter table testkeys drop constraint testkeys_a_pkey
+
+statement ok
+alter table testkeys drop constraint testkeys_b_unique
+
+# No more unique properties
+query T nosort
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a
+----
+project (
+| join (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL ]
+| ) [ "y"."y" = "testkeys"."a" NOT NULL ]
+) [ "y"."y" ]
+
+query T nosort
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b;
+----
+project (
+| join (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."b" ]
+| ) [ "y"."y" = "testkeys"."b" ]
+) [ "y"."y" ]
+
+statement ok
+start transaction
+
+statement ok
+alter table testkeys add constraint testkeys_a_pkey primary key (a)
+
+statement ok
+alter table testkeys add constraint testkeys_b_unique unique (b)
+
+statement ok
+rollback
+
+query T nosort
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.a
+----
+project (
+| join (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."a" NOT NULL ]
+| ) [ "y"."y" = "testkeys"."a" NOT NULL ]
+) [ "y"."y" ]
+
+query T nosort
+plan select y from (values (1),(2),(cast(3 as int))) y(y) inner join testkeys
on y.y = testkeys.b;
+----
+project (
+| join (
+| | [ [ int "1", int "2", int "3" ] as "y"."y" ],
+| | table("sys"."testkeys") [ "testkeys"."b" ]
+| ) [ "y"."y" = "testkeys"."b" ]
+) [ "y"."y" ]
+
+statement ok
+DROP TABLE testkeys
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
