Tarantool development patches archive
 help / color / mirror / Atom feed
* [tarantool-patches] [PATCH v2] sql: Duplicate key error for a non-unique index
@ 2019-02-20 10:06 Stanislav Zudin
  2019-02-26 13:26 ` Vladimir Davydov
  0 siblings, 1 reply; 6+ messages in thread
From: Stanislav Zudin @ 2019-02-20 10:06 UTC (permalink / raw)
  To: tarantool-patches, kostja; +Cc: Stanislav Zudin

Adds collation analysis into creating of a composite key for
index tuples.
The keys of secondary index consist of parts defined for index itself
combined with parts defined for primary key.
The duplicate parts are ignored. But the search of duplicates didn't
take the collation into consideration.
If non-unique secondary index contained primary key columns their
parts from the primary key were omitted. This fact caused an issue.

Closes #3537
---
Branch: https://github.com/tarantool/tarantool/tree/stanztt/gh-3537-nonunique-index-dup-key-error
Issue: https://github.com/tarantool/tarantool/issues/3537

 src/box/key_def.c           |  51 +++++-
 src/coll.c                  |   1 +
 src/coll.h                  |   2 +
 test/sql/collation.result   | 330 ++++++++++++++++++++++++++++++++++++
 test/sql/collation.test.lua | 123 ++++++++++++++
 5 files changed, 500 insertions(+), 7 deletions(-)

diff --git a/src/box/key_def.c b/src/box/key_def.c
index 9411ade39..9814d6df0 100644
--- a/src/box/key_def.c
+++ b/src/box/key_def.c
@@ -37,6 +37,7 @@
 #include "schema_def.h"
 #include "coll_id_cache.h"
 #include "small/region.h"
+#include "coll.h"
 
 const char *sort_order_strs[] = { "asc", "desc", "undef" };
 
@@ -596,12 +597,11 @@ key_def_find_by_fieldno(const struct key_def *key_def, uint32_t fieldno)
 	return key_def_find(key_def, &part);
 }
 
-const struct key_part *
-key_def_find(const struct key_def *key_def, const struct key_part *to_find)
+static const struct key_part *
+key_def_find_next(const struct key_part *part, const struct key_part *end,
+		  const struct key_part *to_find)
 {
-	const struct key_part *part = key_def->parts;
-	const struct key_part *end = part + key_def->part_count;
-	for (; part != end; part++) {
+	for(; part != end; part++) {
 		if (part->fieldno == to_find->fieldno &&
 		    json_path_cmp(part->path, part->path_len,
 				  to_find->path, to_find->path_len,
@@ -611,6 +611,43 @@ key_def_find(const struct key_def *key_def, const struct key_part *to_find)
 	return NULL;
 }
 
+static bool
+key_def_need_merge(const struct key_def *sec_key_def,
+		   const struct key_part *pk_key_part)
+{
+	const struct key_part* end = sec_key_def->parts +
+				     sec_key_def->part_count;
+	const struct key_part* part = key_def_find_next(sec_key_def->parts,
+							end,
+							pk_key_part);
+	if (part == NULL)
+		return true;
+
+	/* The duplicate key_part is found,
+	 * compare collation */
+	if (part->coll == pk_key_part->coll)
+		return false;
+
+	if (part->coll == NULL ||
+	    part->coll->strength == COLL_ICU_STRENGTH_DEFAULT) {
+		return false;
+		/* If collation of the sec. key part
+		 * is binary then the sec. key
+		 * doesn't require a composite key.
+		 * */
+	} else
+		return true;
+}
+
+const struct key_part *
+key_def_find(const struct key_def *key_def, const struct key_part *to_find)
+{
+	/* find the first match */
+	return key_def_find_next(key_def->parts,
+				 key_def->parts + key_def->part_count,
+				 to_find);
+}
+
 bool
 key_def_contains(const struct key_def *first, const struct key_def *second)
 {
@@ -639,7 +676,7 @@ key_def_merge(const struct key_def *first, const struct key_def *second)
 	part = second->parts;
 	end = part + second->part_count;
 	for (; part != end; part++) {
-		if (key_def_find(first, part) != NULL)
+		if (!key_def_need_merge(first, part))
 			--new_part_count;
 		else
 			sz += part->path_len;
@@ -677,7 +714,7 @@ key_def_merge(const struct key_def *first, const struct key_def *second)
 	part = second->parts;
 	end = part + second->part_count;
 	for (; part != end; part++) {
-		if (key_def_find(first, part) != NULL)
+		if (!key_def_need_merge(first, part))
 			continue;
 		key_def_set_part(new_def, pos++, part->fieldno, part->type,
 				 part->nullable_action, part->coll,
diff --git a/src/coll.c b/src/coll.c
index 6d9c44dbf..8f9dcbac2 100644
--- a/src/coll.c
+++ b/src/coll.c
@@ -320,6 +320,7 @@ coll_new(const struct coll_def *def)
 	}
 	memcpy((char *) coll->fingerprint, fingerprint, fingerprint_len + 1);
 	coll->refs = 1;
+	coll->strength = def->icu.strength;
 	coll->type = def->type;
 	switch (coll->type) {
 	case COLL_TYPE_ICU:
diff --git a/src/coll.h b/src/coll.h
index 9c725712a..2412f8032 100644
--- a/src/coll.h
+++ b/src/coll.h
@@ -56,6 +56,8 @@ struct UCollator;
 struct coll {
 	/** Collation type. */
 	enum coll_type type;
+	/** Strength ICU settings */
+	enum coll_icu_strength strength;
 	/** ICU collation specific data. */
 	struct UCollator *collator;
 	/** String comparator. */
diff --git a/test/sql/collation.result b/test/sql/collation.result
index 5721ef854..6cccbc84b 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -325,3 +325,333 @@ box.sql.execute("DROP TABLE t1;")
 box.sql.execute("DROP TABLE t0;")
 ---
 ...
+-- gh-3537 Duplicate key error for an index that is not unique
+-- pk - default, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3 (s1 CHAR(5) PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3 ON t3 (s1 collate "unicode_ci");')
+---
+...
+box.sql.execute("INSERT INTO t3 VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3 VALUES ('A');")
+---
+...
+box.sql.execute("SELECT * FROM t3;")
+---
+- - ['A']
+  - ['a']
+...
+box.sql.execute("DROP TABLE t3;")
+---
+...
+-- pk - binary, sc - unicode
+box.sql.execute('CREATE TABLE t3b (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3b ON t3b (s1 collate "unicode");')
+---
+...
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3b VALUES ('A');")
+---
+...
+box.sql.execute("SELECT * FROM t3b;")
+---
+- - ['A']
+  - ['a']
+...
+box.sql.execute("DROP TABLE t3b;")
+---
+...
+-- pk - binary, sc - unicode (make dup)
+box.sql.execute('CREATE TABLE t3b (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3b ON t3b (s1 collate "unicode");')
+---
+...
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3b VALUES ('A');")
+---
+...
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3B_1' in space 'T3B'
+...
+box.sql.execute("SELECT * FROM t3b;")
+---
+- - ['A']
+  - ['a']
+...
+box.sql.execute("DROP TABLE t3b;")
+---
+...
+-- pk - unicode, sc - binary
+box.sql.execute('CREATE TABLE t3c (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3c ON t3c (s1 collate "binary");')
+---
+...
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3c VALUES ('A');")
+---
+...
+box.sql.execute("SELECT * FROM t3c;")
+---
+- - ['a']
+  - ['A']
+...
+box.sql.execute("DROP TABLE t3c;")
+---
+...
+-- pk - unicode, sc - binary (make dup)
+box.sql.execute('CREATE TABLE t3c (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3c ON t3c (s1 collate "binary");')
+---
+...
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3c VALUES ('A');")
+---
+...
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3C_1' in space 'T3C'
+...
+box.sql.execute("SELECT * FROM t3c;")
+---
+- - ['a']
+  - ['A']
+...
+box.sql.execute("DROP TABLE t3c;")
+---
+...
+-- pk - binary, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3d (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3d ON t3d (s1 collate "unicode_ci");')
+---
+...
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3d VALUES ('A');")
+---
+...
+box.sql.execute("SELECT * FROM t3d;")
+---
+- - ['A']
+  - ['a']
+...
+box.sql.execute("DROP TABLE t3d;")
+---
+...
+-- pk - binary, sc - unicode_ci (make dup)
+box.sql.execute('CREATE TABLE t3d (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3d ON t3d (s1 collate "unicode_ci");')
+---
+...
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3d VALUES ('A');")
+---
+...
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3D_1' in space 'T3D'
+...
+box.sql.execute("SELECT * FROM t3d;")
+---
+- - ['A']
+  - ['a']
+...
+box.sql.execute("DROP TABLE t3d;")
+---
+...
+-- pk - unicode_ci, sc - binary (should fail)
+box.sql.execute('CREATE TABLE t3e (s1 CHAR(5) collate "unicode_ci" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3e ON t3e (s1 collate "binary");')
+---
+...
+box.sql.execute("INSERT INTO t3e VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3e VALUES ('A');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3E_1' in space 'T3E'
+...
+box.sql.execute("SELECT * FROM t3e;")
+---
+- - ['a']
+...
+box.sql.execute("DROP TABLE t3e;")
+---
+...
+-- pk - unicode, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3f (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3f ON t3f (s1 collate "unicode_ci");')
+---
+...
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3f VALUES ('A');")
+---
+...
+box.sql.execute("SELECT * FROM t3f;")
+---
+- - ['a']
+  - ['A']
+...
+box.sql.execute("DROP TABLE t3f;")
+---
+...
+-- pk - unicode, sc - unicode_ci (make dup)
+box.sql.execute('CREATE TABLE t3f (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3f ON t3f (s1 collate "unicode_ci");')
+---
+...
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3f VALUES ('A');")
+---
+...
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3F_1' in space 'T3F'
+...
+box.sql.execute("SELECT * FROM t3f;")
+---
+- - ['a']
+  - ['A']
+...
+box.sql.execute("DROP TABLE t3f;")
+---
+...
+-- pk - unicode_ci, sc - unicode (should fail)
+box.sql.execute('CREATE TABLE t3g (s1 CHAR(5) collate "unicode_ci" PRIMARY KEY);')
+---
+...
+box.sql.execute('CREATE INDEX i3g ON t3g (s1 collate "unicode");')
+---
+...
+box.sql.execute("INSERT INTO t3g VALUES ('a');")
+---
+...
+box.sql.execute("INSERT INTO t3g VALUES ('A');")
+---
+- error: Duplicate key exists in unique index 'pk_unnamed_T3G_1' in space 'T3G'
+...
+box.sql.execute("SELECT * FROM t3g;")
+---
+- - ['a']
+...
+box.sql.execute("DROP TABLE t3g;")
+---
+...
+-- pk - default, sc - multipart
+box.sql.execute('CREATE TABLE qms1 (w CHAR(5) PRIMARY KEY, n CHAR(5), q CHAR(5), s INTEGER);')
+---
+...
+box.sql.execute('CREATE INDEX iqms1 ON qms1 (w collate "unicode_ci", n);')
+---
+...
+box.sql.execute("INSERT INTO qms1 VALUES ('www', 'nnn', 'qqq', 1);")
+---
+...
+box.sql.execute("INSERT INTO qms1 VALUES ('WWW', 'nnn', 'qqq', 2);")
+---
+...
+box.sql.execute("SELECT * FROM qms1;")
+---
+- - ['WWW', 'nnn', 'qqq', 2]
+  - ['www', 'nnn', 'qqq', 1]
+...
+box.sql.execute("DROP TABLE qms1;")
+---
+...
+box.sql.execute('CREATE TABLE qms2 (w CHAR(5) PRIMARY KEY, n CHAR(5), q CHAR(5), s INTEGER);')
+---
+...
+box.sql.execute('CREATE INDEX iqms2 ON qms2 (w collate "unicode", n);')
+---
+...
+box.sql.execute("INSERT INTO qms2 VALUES ('www', 'nnn', 'qqq', 1);")
+---
+...
+box.sql.execute("INSERT INTO qms2 VALUES ('WWW', 'nnn', 'qqq', 2);")
+---
+...
+box.sql.execute("SELECT * FROM qms2;")
+---
+- - ['WWW', 'nnn', 'qqq', 2]
+  - ['www', 'nnn', 'qqq', 1]
+...
+box.sql.execute("DROP TABLE qms2;")
+---
+...
+-- pk - multipart, sc overlaps with pk
+box.sql.execute('CREATE TABLE qms3 (w CHAR(5), n CHAR(5), q CHAR(5), s INTEGER, CONSTRAINT pk_qms3 PRIMARY KEY(w, n, q));')
+---
+...
+box.sql.execute('CREATE INDEX iqms3 ON qms3 (w collate "unicode_ci", s);')
+---
+...
+box.sql.execute("INSERT INTO qms3 VALUES ('www', 'nnn', 'qqq', 1);")
+---
+...
+box.sql.execute("INSERT INTO qms3 VALUES ('WWW', 'nnn', 'qqq', 2);")
+---
+...
+box.sql.execute("SELECT * FROM qms3;")
+---
+- - ['WWW', 'nnn', 'qqq', 2]
+  - ['www', 'nnn', 'qqq', 1]
+...
+box.sql.execute("DROP TABLE qms3;")
+---
+...
+box.sql.execute('CREATE TABLE qms4 (w CHAR(5), n CHAR(5), q CHAR(5), s INTEGER, CONSTRAINT pk_qms4 PRIMARY KEY(w, n, q));')
+---
+...
+box.sql.execute('CREATE INDEX iqms4 ON qms4 (w collate "unicode", s);')
+---
+...
+box.sql.execute("INSERT INTO qms4 VALUES ('www', 'nnn', 'qqq', 1);")
+---
+...
+box.sql.execute("INSERT INTO qms4 VALUES ('WWW', 'nnn', 'qqq', 2);")
+---
+...
+box.sql.execute("SELECT * FROM qms4;")
+---
+- - ['WWW', 'nnn', 'qqq', 2]
+  - ['www', 'nnn', 'qqq', 1]
+...
+box.sql.execute("DROP TABLE qms4;")
+---
+...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
index 4c649a444..075ec7ee4 100644
--- a/test/sql/collation.test.lua
+++ b/test/sql/collation.test.lua
@@ -126,3 +126,126 @@ box.sql.execute("SELECT * FROM t1;")
 box.sql.execute("SELECT s1 FROM t0;")
 box.sql.execute("DROP TABLE t1;")
 box.sql.execute("DROP TABLE t0;")
+
+-- gh-3537 Duplicate key error for an index that is not unique
+-- pk - default, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3 (s1 CHAR(5) PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3 ON t3 (s1 collate "unicode_ci");')
+box.sql.execute("INSERT INTO t3 VALUES ('a');")
+box.sql.execute("INSERT INTO t3 VALUES ('A');")
+box.sql.execute("SELECT * FROM t3;")
+box.sql.execute("DROP TABLE t3;")
+
+-- pk - binary, sc - unicode
+box.sql.execute('CREATE TABLE t3b (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3b ON t3b (s1 collate "unicode");')
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+box.sql.execute("INSERT INTO t3b VALUES ('A');")
+box.sql.execute("SELECT * FROM t3b;")
+box.sql.execute("DROP TABLE t3b;")
+
+-- pk - binary, sc - unicode (make dup)
+box.sql.execute('CREATE TABLE t3b (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3b ON t3b (s1 collate "unicode");')
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+box.sql.execute("INSERT INTO t3b VALUES ('A');")
+box.sql.execute("INSERT INTO t3b VALUES ('a');")
+box.sql.execute("SELECT * FROM t3b;")
+box.sql.execute("DROP TABLE t3b;")
+
+-- pk - unicode, sc - binary
+box.sql.execute('CREATE TABLE t3c (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3c ON t3c (s1 collate "binary");')
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+box.sql.execute("INSERT INTO t3c VALUES ('A');")
+box.sql.execute("SELECT * FROM t3c;")
+box.sql.execute("DROP TABLE t3c;")
+
+-- pk - unicode, sc - binary (make dup)
+box.sql.execute('CREATE TABLE t3c (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3c ON t3c (s1 collate "binary");')
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+box.sql.execute("INSERT INTO t3c VALUES ('A');")
+box.sql.execute("INSERT INTO t3c VALUES ('a');")
+box.sql.execute("SELECT * FROM t3c;")
+box.sql.execute("DROP TABLE t3c;")
+
+-- pk - binary, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3d (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3d ON t3d (s1 collate "unicode_ci");')
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+box.sql.execute("INSERT INTO t3d VALUES ('A');")
+box.sql.execute("SELECT * FROM t3d;")
+box.sql.execute("DROP TABLE t3d;")
+
+-- pk - binary, sc - unicode_ci (make dup)
+box.sql.execute('CREATE TABLE t3d (s1 CHAR(5) collate "binary" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3d ON t3d (s1 collate "unicode_ci");')
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+box.sql.execute("INSERT INTO t3d VALUES ('A');")
+box.sql.execute("INSERT INTO t3d VALUES ('a');")
+box.sql.execute("SELECT * FROM t3d;")
+box.sql.execute("DROP TABLE t3d;")
+
+-- pk - unicode_ci, sc - binary (should fail)
+box.sql.execute('CREATE TABLE t3e (s1 CHAR(5) collate "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3e ON t3e (s1 collate "binary");')
+box.sql.execute("INSERT INTO t3e VALUES ('a');")
+box.sql.execute("INSERT INTO t3e VALUES ('A');")
+box.sql.execute("SELECT * FROM t3e;")
+box.sql.execute("DROP TABLE t3e;")
+
+-- pk - unicode, sc - unicode_ci
+box.sql.execute('CREATE TABLE t3f (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3f ON t3f (s1 collate "unicode_ci");')
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+box.sql.execute("INSERT INTO t3f VALUES ('A');")
+box.sql.execute("SELECT * FROM t3f;")
+box.sql.execute("DROP TABLE t3f;")
+
+-- pk - unicode, sc - unicode_ci (make dup)
+box.sql.execute('CREATE TABLE t3f (s1 CHAR(5) collate "unicode" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3f ON t3f (s1 collate "unicode_ci");')
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+box.sql.execute("INSERT INTO t3f VALUES ('A');")
+box.sql.execute("INSERT INTO t3f VALUES ('a');")
+box.sql.execute("SELECT * FROM t3f;")
+box.sql.execute("DROP TABLE t3f;")
+
+-- pk - unicode_ci, sc - unicode (should fail)
+box.sql.execute('CREATE TABLE t3g (s1 CHAR(5) collate "unicode_ci" PRIMARY KEY);')
+box.sql.execute('CREATE INDEX i3g ON t3g (s1 collate "unicode");')
+box.sql.execute("INSERT INTO t3g VALUES ('a');")
+box.sql.execute("INSERT INTO t3g VALUES ('A');")
+box.sql.execute("SELECT * FROM t3g;")
+box.sql.execute("DROP TABLE t3g;")
+
+-- pk - default, sc - multipart
+box.sql.execute('CREATE TABLE qms1 (w CHAR(5) PRIMARY KEY, n CHAR(5), q CHAR(5), s INTEGER);')
+box.sql.execute('CREATE INDEX iqms1 ON qms1 (w collate "unicode_ci", n);')
+box.sql.execute("INSERT INTO qms1 VALUES ('www', 'nnn', 'qqq', 1);")
+box.sql.execute("INSERT INTO qms1 VALUES ('WWW', 'nnn', 'qqq', 2);")
+box.sql.execute("SELECT * FROM qms1;")
+box.sql.execute("DROP TABLE qms1;")
+
+box.sql.execute('CREATE TABLE qms2 (w CHAR(5) PRIMARY KEY, n CHAR(5), q CHAR(5), s INTEGER);')
+box.sql.execute('CREATE INDEX iqms2 ON qms2 (w collate "unicode", n);')
+box.sql.execute("INSERT INTO qms2 VALUES ('www', 'nnn', 'qqq', 1);")
+box.sql.execute("INSERT INTO qms2 VALUES ('WWW', 'nnn', 'qqq', 2);")
+box.sql.execute("SELECT * FROM qms2;")
+box.sql.execute("DROP TABLE qms2;")
+
+-- pk - multipart, sc overlaps with pk
+box.sql.execute('CREATE TABLE qms3 (w CHAR(5), n CHAR(5), q CHAR(5), s INTEGER, CONSTRAINT pk_qms3 PRIMARY KEY(w, n, q));')
+box.sql.execute('CREATE INDEX iqms3 ON qms3 (w collate "unicode_ci", s);')
+box.sql.execute("INSERT INTO qms3 VALUES ('www', 'nnn', 'qqq', 1);")
+box.sql.execute("INSERT INTO qms3 VALUES ('WWW', 'nnn', 'qqq', 2);")
+box.sql.execute("SELECT * FROM qms3;")
+box.sql.execute("DROP TABLE qms3;")
+
+box.sql.execute('CREATE TABLE qms4 (w CHAR(5), n CHAR(5), q CHAR(5), s INTEGER, CONSTRAINT pk_qms4 PRIMARY KEY(w, n, q));')
+box.sql.execute('CREATE INDEX iqms4 ON qms4 (w collate "unicode", s);')
+box.sql.execute("INSERT INTO qms4 VALUES ('www', 'nnn', 'qqq', 1);")
+box.sql.execute("INSERT INTO qms4 VALUES ('WWW', 'nnn', 'qqq', 2);")
+box.sql.execute("SELECT * FROM qms4;")
+box.sql.execute("DROP TABLE qms4;")
-- 
2.17.1

^ permalink raw reply	[flat|nested] 6+ messages in thread

end of thread, other threads:[~2019-03-04 17:53 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-02-20 10:06 [tarantool-patches] [PATCH v2] sql: Duplicate key error for a non-unique index Stanislav Zudin
2019-02-26 13:26 ` Vladimir Davydov
2019-02-28 14:17   ` [tarantool-patches] " Stanislav Zudin
2019-02-28 16:19     ` Vladimir Davydov
2019-03-01 11:46       ` Stanislav Zudin
2019-03-04 17:53         ` Vladimir Davydov

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