[patches] [constraints 1/2] sql: ban multiple ON CONFLICT REPLACE

[Bulat Niatshin]

Ban an opportunity for user to create table with multiple constraints
with ON CONFLICT REPLACE option.

For #2963

Signed-off-by: Bulat Niatshin <niatshin at tarantool.org>
---
 src/box/sql/build.c                            | 36 +++++++++++++
 test/sql-tap/gh-2963-multiple-replace.test.lua | 70 ++++++++++++++++++++++++++
 test/sql/gh2963-multiple-replace.result        | 21 ++++++++
 test/sql/gh2963-multiple-replace.test.lua      | 13 +++++
 4 files changed, 140 insertions(+)
 create mode 100755 test/sql-tap/gh-2963-multiple-replace.test.lua
 create mode 100644 test/sql/gh2963-multiple-replace.result
 create mode 100644 test/sql/gh2963-multiple-replace.test.lua

diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index eb51a9863..12b234272 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -1822,6 +1822,35 @@ emitNewSysSpaceSequenceRecord(Parse *pParse, int space_id, const char reg_seq_id
 	return first_col;
 }
 
+static bool
+checkMultipleReplaceEntries(Table * pTab)
+{
+	bool onReplaceUsed = false;
+	Index * pIdx;
+	int i;
+
+	for (pIdx = pTab->pIndex; pIdx; pIdx = pIdx->pNext) {
+		if (pIdx->onError == OE_Replace) {
+	       		if (onReplaceUsed == true) {
+				return true;
+			}
+			onReplaceUsed = true;
+		}
+	}
+
+	for (i = 0; i < pTab->nCol; i++) {
+		u8 onError = pTab->aCol[i].notNull;
+		if (onError == OE_Replace) {
+	       		if (onReplaceUsed == true) {
+				return true;
+			}
+			onReplaceUsed = true;
+		}
+	}
+
+	return false;
+}
+
 /*
  * This routine is called to report the final ")" that terminates
  * a CREATE TABLE statement.
@@ -1884,6 +1913,13 @@ sqlite3EndTable(Parse * pParse,	/* Parse context */
 		}
 	}
 
+	if (checkMultipleReplaceEntries(p)) {
+		sqlite3ErrorMsg(pParse,
+				"in table %s - only one ON CONFLICT REPLACE is allowed",
+				p->zName);
+		return;
+	}
+
 #ifndef SQLITE_OMIT_CHECK
 	/* Resolve names in all CHECK constraint expressions.
 	 */
diff --git a/test/sql-tap/gh-2963-multiple-replace.test.lua b/test/sql-tap/gh-2963-multiple-replace.test.lua
new file mode 100755
index 000000000..30012ad88
--- /dev/null
+++ b/test/sql-tap/gh-2963-multiple-replace.test.lua
@@ -0,0 +1,70 @@
+#!/usr/bin/env tarantool
+-- In SQLite multiple ON CONFLICT REPLACE were allowed in CREATE TABLE statement.
+-- However, we decided that we should have only one constraint with ON CONFLICT
+-- REPLACE action. This tests check that proper error message will be raised
+-- if user makes multiple columns with ON CONFLICT REPLACE.
+
+test = require("sqltester")
+test:plan(5)
+
+test:do_execsql_test(
+	"replace-1.1",
+	[[
+		CREATE TABLE t1(a INT PRIMARY KEY,
+						b INT UNIQUE ON CONFLICT REPLACE,
+						c INT UNIQUE
+						);
+	]], {
+
+	})
+
+test:do_catchsql_test(
+	"replace-1.2",
+	[[
+		DROP TABLE IF EXISTS t1;
+		CREATE TABLE t1(a INT PRIMARY KEY,
+						b INT UNIQUE ON CONFLICT REPLACE,
+						c INT UNIQUE ON CONFLICT REPLACE
+						);
+	]], {
+		1, "in table T1 - only one ON CONFLICT REPLACE is allowed"
+	})
+
+test:do_catchsql_test(
+	"replace-1.3",
+	[[
+		DROP TABLE IF EXISTS t1;
+		CREATE TABLE t1(a INT PRIMARY KEY,
+						b INT UNIQUE ON CONFLICT REPLACE,
+						c INT NOT NULL ON CONFLICT REPLACE
+						);
+	]], {
+		1, "in table T1 - only one ON CONFLICT REPLACE is allowed"
+	})
+
+test:do_catchsql_test(
+	"replace-1.4",
+	[[
+		DROP TABLE IF EXISTS t1;
+		CREATE TABLE t1(a INT PRIMARY KEY,
+						b INT NOT NULL ON CONFLICT REPLACE,
+						c INT UNIQUE ON CONFLICT REPLACE
+						);
+	]], {
+		1, "in table T1 - only one ON CONFLICT REPLACE is allowed"
+	})
+
+test:do_catchsql_test(
+	"replace-1.5",
+	[[
+		DROP TABLE IF EXISTS t1;
+		CREATE TABLE t1(a INT PRIMARY KEY,
+						b INT NOT NULL ON CONFLICT REPLACE,
+						c INT NOT NULL ON CONFLICT REPLACE
+						);
+	]], {
+		1, "in table T1 - only one ON CONFLICT REPLACE is allowed"
+	})
+
+test:finish_test()
+
diff --git a/test/sql/gh2963-multiple-replace.result b/test/sql/gh2963-multiple-replace.result
new file mode 100644
index 000000000..867155022
--- /dev/null
+++ b/test/sql/gh2963-multiple-replace.result
@@ -0,0 +1,21 @@
+-- gh-2963 - this test is a part of that ticket
+-- The purpose is to ban an opportunity for user to create table with multiple
+-- ON CONFLICT clauses for columns, user should be able to point it only to one
+-- column. This test checks it.
+test_run = require('test_run').new()
+---
+...
+box.sql.execute('CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE ON CONFLICT REPLACE, c UNIQUE)')
+---
+...
+box.sql.execute('CREATE TABLE t2(a INT PRIMARY KEY, b INT UNIQUE ON CONFLICT REPLACE, c UNIQUE ON CONFLICT REPLACE)')
+---
+- error: in table T2 - only one ON CONFLICT REPLACE is allowed
+...
+box.sql.execute('DROP TABLE t1')
+---
+...
+box.sql.execute('DROP TABLE t2')
+---
+- error: 'no such table: T2'
+...
diff --git a/test/sql/gh2963-multiple-replace.test.lua b/test/sql/gh2963-multiple-replace.test.lua
new file mode 100644
index 000000000..76f8f3dc7
--- /dev/null
+++ b/test/sql/gh2963-multiple-replace.test.lua
@@ -0,0 +1,13 @@
+-- gh-2963 - this test is a part of that ticket
+-- The purpose is to ban an opportunity for user to create table with multiple
+-- ON CONFLICT clauses for columns, user should be able to point it only to one
+-- column. This test checks it.
+
+test_run = require('test_run').new()
+
+box.sql.execute('CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE ON CONFLICT REPLACE, c UNIQUE)')
+
+box.sql.execute('CREATE TABLE t2(a INT PRIMARY KEY, b INT UNIQUE ON CONFLICT REPLACE, c UNIQUE ON CONFLICT REPLACE)')
+
+box.sql.execute('DROP TABLE t1')
+box.sql.execute('DROP TABLE t2')
-- 
2.14.1