Tarantool development patches archive
 help / color / mirror / Atom feed
From: AKhatskevich <avkhatskevich@tarantool.org>
To: korablev@tarantool.org, tarantool-patches@freelists.org
Subject: [tarantool-patches] [PATCH] sql: fix fk set null clause
Date: Tue,  9 Oct 2018 15:37:50 +0300	[thread overview]
Message-ID: <20181009123750.3330-1-avkhatskevich@tarantool.org> (raw)

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

             reply	other threads:[~2018-10-09 12:38 UTC|newest]

Thread overview: 5+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-10-09 12:37 AKhatskevich [this message]
2018-10-09 19:14 ` [tarantool-patches] " n.pettik
2018-10-11 13:20   ` Alex Khatskevich
2018-10-17 15:41     ` n.pettik
2018-10-26  5:47 ` Kirill Yukhin

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20181009123750.3330-1-avkhatskevich@tarantool.org \
    --to=avkhatskevich@tarantool.org \
    --cc=korablev@tarantool.org \
    --cc=tarantool-patches@freelists.org \
    --subject='Re: [tarantool-patches] [PATCH] sql: fix fk set null clause' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox