From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E788B2ABFD for ; Tue, 9 Oct 2018 08:38:08 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dvscCiVIh5RR for ; Tue, 9 Oct 2018 08:38:08 -0400 (EDT) Received: from smtp33.i.mail.ru (smtp33.i.mail.ru [94.100.177.93]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id A42482A95B for ; Tue, 9 Oct 2018 08:38:08 -0400 (EDT) From: AKhatskevich Subject: [tarantool-patches] [PATCH] sql: fix fk set null clause Date: Tue, 9 Oct 2018 15:37:50 +0300 Message-Id: <20181009123750.3330-1-avkhatskevich@tarantool.org> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: korablev@tarantool.org, tarantool-patches@freelists.org After changing behavior of the `IS` operator (#b3a3ddb571), `SET NULL` was rewritten to use `EQ` instead. Which do not respect NULLs. This commit fixes the null related behavior by emitting logical constructions equivalent for this case to old `IS`. Those constructions are not equal to the old `EQ` in common case. Before: `oldval` old_is `newval` Now: `oldval` is_null or (`newval` is_not_null and `oldval` eq `newval`) Closes #3645 --- Issue: https://github.com/tarantool/tarantool/issues/3645 Branch: https://github.com/tarantool/tarantool/tree/kh/gh-3642-set-null src/box/sql/fkey.c | 49 ++++++++++++++------ test/sql-tap/gh3645-set-null.test.lua | 84 +++++++++++++++++++++++++++++++++++ 2 files changed, 119 insertions(+), 14 deletions(-) create mode 100755 test/sql-tap/gh3645-set-null.test.lua diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c index 091778fc8..0abf42c84 100644 --- a/src/box/sql/fkey.c +++ b/src/box/sql/fkey.c @@ -787,24 +787,45 @@ fkey_action_trigger(struct Parse *pParse, struct Table *pTab, struct fkey *fkey, /* * For ON UPDATE, construct the next term of the - * WHEN clause. The final WHEN clause will be like + * WHEN clause, which should return false in case + * there is a reason to for a broken constrant in + * a parent table: + * no_action_needed := `oldval` IS NULL OR + * (`newval` IS NOT NULL AND + * `newval` = `oldval`) + * + * The final WHEN clause will be like * this: * - * WHEN NOT(old.col1 = new.col1 AND ... AND - * old.colN = new.colN) + * WHEN NOT( no_action_needed(col1) AND ... + * no_action_needed(colN)) */ if (is_update) { - struct Expr *l, *r; - l = sqlite3PExpr(pParse, TK_DOT, - sqlite3ExprAlloc(db, TK_ID, &t_old, 0), - sqlite3ExprAlloc(db, TK_ID, &t_to_col, - 0)); - r = sqlite3PExpr(pParse, TK_DOT, - sqlite3ExprAlloc(db, TK_ID, &t_new, 0), - sqlite3ExprAlloc(db, TK_ID, &t_to_col, - 0)); - eq = sqlite3PExpr(pParse, TK_EQ, l, r); - when = sqlite3ExprAnd(db, when, eq); + Expr *old_val = sqlite3PExpr( + pParse, TK_DOT, + sqlite3ExprAlloc(db, TK_ID, &t_old, 0), + sqlite3ExprAlloc(db, TK_ID, &t_to_col, + 0)); + Expr *newVal = sqlite3PExpr( + pParse, TK_DOT, + sqlite3ExprAlloc(db, TK_ID, &t_new, 0), + sqlite3ExprAlloc(db, TK_ID, &t_to_col, + 0)); + Expr *old_is_null = sqlite3PExpr( + pParse, TK_ISNULL, + sqlite3ExprDup(db, old_val, 0), NULL); + eq = sqlite3PExpr( + pParse, TK_EQ, + sqlite3ExprDup(db, old_val, 0), + sqlite3ExprDup(db, newVal, 0)); + Expr *new_non_null = sqlite3PExpr( + pParse, TK_NOTNULL, newVal, NULL); + Expr *non_null_eq = sqlite3PExpr( + pParse, TK_AND, new_non_null, eq); + Expr *no_action_needed = sqlite3PExpr( + pParse, TK_OR, old_is_null, + non_null_eq); + when = sqlite3ExprAnd(db, when, no_action_needed); } if (action != FKEY_ACTION_RESTRICT && diff --git a/test/sql-tap/gh3645-set-null.test.lua b/test/sql-tap/gh3645-set-null.test.lua new file mode 100755 index 000000000..9067c52f1 --- /dev/null +++ b/test/sql-tap/gh3645-set-null.test.lua @@ -0,0 +1,84 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(11) + +test:do_catchsql_test( + "set-null-1.0", + [[ + CREATE TABLE T1 (a INTEGER PRIMARY KEY, + b char(5) unique); + CREATE TABLE T2 (a INTEGER PRIMARY KEY, + b char(5) unique, + foreign key (b) references t1 (b) on update set null); + INSERT INTO T1 VALUES (1,'a'); + INSERT INTO T2 VALUES (1,'a'); + ]], {0}); + +test:do_catchsql_test( + "set-null-1.1", + [[ + UPDATE T1 SET B = NULL; + ]], {0}); + +test:do_catchsql_test( + "set-null-1.2", + [[ + SELECT * FROM T1; + ]], {0, {1, ""}}); + +test:do_catchsql_test( + "set-null-1.3", + [[ + SELECT * FROM T2; + ]], {0, {1, ""}}); + +test:do_catchsql_test( + "set-null-1.4", + [[ + UPDATE T1 SET B = 'a'; + ]], {0}); + +test:do_catchsql_test( + "set-null-1.5", + [[ + SELECT * FROM T1; + ]], {0, {1, "a"}}); + +test:do_catchsql_test( + "set-null-1.6", + [[ + SELECT * FROM T2; + ]], {0, {1, ""}}); + + +test:do_catchsql_test( + "set-null-2.0", + [[ + CREATE TABLE T3 (a INTEGER PRIMARY KEY, + b char(5) unique); + CREATE TABLE T4 (a INTEGER PRIMARY KEY, + b char(5) unique, + foreign key (b) references t3 (b) on update cascade); + INSERT INTO T3 VALUES (1,'a'); + INSERT INTO T4 VALUES (1,'a'); + ]], {0}); + +test:do_catchsql_test( + "set-null-2.1", + [[ + UPDATE T3 SET B = 'b'; + ]], {0}); + +test:do_catchsql_test( + "set-null-2.2", + [[ + SELECT * FROM T3; + ]], {0, {1, "b"}}); + +test:do_catchsql_test( + "set-null-2.3", + [[ + SELECT * FROM T4; + ]], {0, {1, "b"}}); + +test:finish_test() -- 2.14.1