[tarantool-patches] Re: [PATCH v2] sql: Duplicate key error for a non-unique index
Stanislav Zudin
szudin at tarantool.org
Thu Feb 28 17:17:13 MSK 2019
The recent patch includes the suggested changes, see the comments inline.
On 26.02.2019 16:26, Vladimir Davydov wrote:
> On Wed, Feb 20, 2019 at 01:06:10PM +0300, Stanislav Zudin wrote:
>> 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
> Whenever you resubmit a patch, please write a brief change log here.
> For more details see
>
> https://tarantool.io/en/doc/1.10/dev_guide/developer_guidelines/#how-to-submit-a-patch-for-review
>
>> 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)
> Please don't use secondary/primary in argument names/comments,
> because key_def_merge doesn't. This creates inconsistency leading
> to confusion. Let's just assume that this function checks whether
> an arbitrary key part can be merged into an arbitrary key def.
fixed.
>> +{
>> + const struct key_part* end = sec_key_def->parts +
> Nit: we always put * closer to the variable name, not type. Please fix.
>
>> + 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 function name sounds like it should return true if the given key
> part needs to be merged into the given key def, but actually it return
> false in this case. Also, IMO key_def_can_merge would be a better name.
ok
>> +
>> + /* The duplicate key_part is found,
>> + * compare collation */
> Nit: we always format multline comments like
>
> /*
> * This is a very long
> * comment.
> */
>
> Please fix.
>
>> + 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
> 'sec' doesn't look like a common abbreviation for secondary.
>
>> + * is binary then the sec. key
>> + * doesn't require a composite key.
>> + * */
> This comment is pretty much useless, because it follows directly from
> the code surrounding it. Please explain *why* you're doing it rather
> than *what* you're doing.
i've removed this comment.
> Perhaps, it's worth encapsulating this logic in a helper function
> defined in coll.c. Something like coll_is_compatible.
This case is not a general one, so it doesn't deserve to be implemented
as a generic helper function.
>> + } 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);
> Why did you factor key_def_find_next out of key_def_find? AFAIU you
> could as well use key_def_find directly in key_def_need_merge, no?
The current functions are result of several consecutive// improvements
included previous review.
Fixed.
>> +}
>> +
>> 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;
> Do we really need it to add strengh here? Can't we extract it from the
> collator?
implemented a function to retrieve the collation strength.
>> /** ICU collation specific data. */
>> struct UCollator *collator;
>> /** String comparator. */
The updated patch is below:
---
Branch:
https://github.com/tarantool/tarantool/tree/stanztt/gh-3537-nonunique-index-dup-key-error
Issue: https://github.com/tarantool/tarantool/issues/3537
src/CMakeLists.txt | 3 +-
src/box/key_def.c | 29 +++-
src/coll.c | 38 +++++
src/coll.h | 4 +
test/sql/collation.result | 330 ++++++++++++++++++++++++++++++++++++
test/sql/collation.test.lua | 123 ++++++++++++++
6 files changed, 524 insertions(+), 3 deletions(-)
diff --git a/src/CMakeLists.txt b/src/CMakeLists.txt
index 04de5ad04..7346e7ba7 100644
--- a/src/CMakeLists.txt
+++ b/src/CMakeLists.txt
@@ -125,7 +125,8 @@ target_link_libraries(core
${LIBEIO_LIBRARIES}
${LIBCORO_LIBRARIES}
${MSGPUCK_LIBRARIES}
- ${generic_libraries})
+ ${generic_libraries}
+ ${ICU_LIBRARIES})
add_library(stat STATIC rmean.c latency.c histogram.c)
target_link_libraries(stat core)
diff --git a/src/box/key_def.c b/src/box/key_def.c
index 9411ade39..61b0258b2 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" };
@@ -611,6 +612,30 @@ key_def_find(const struct key_def *key_def, const
struct key_part *to_find)
return NULL;
}
+static bool
+key_def_can_merge(const struct key_def *first_key_def,
+ const struct key_part *second_key_part)
+{
+ const struct key_part *part = key_def_find(first_key_def,
+ second_key_part);
+ if (part == NULL)
+ return true;
+
+ /*
+ * The duplicate key_part is found,
+ * compare collation
+ */
+ if (part->coll == second_key_part->coll)
+ return false;
+
+ if (part->coll == NULL ||
+ /*part->coll->strength == COLL_ICU_STRENGTH_DEFAULT)*/
+ coll_get_strength(part->coll) == COLL_ICU_STRENGTH_DEFAULT)
+ return false;
+ else
+ return true;
+}
+
bool
key_def_contains(const struct key_def *first, const struct key_def
*second)
{
@@ -639,7 +664,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_can_merge(first, part))
--new_part_count;
else
sz += part->path_len;
@@ -677,7 +702,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_can_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..7d7267d8a 100644
--- a/src/coll.c
+++ b/src/coll.c
@@ -295,6 +295,44 @@ coll_def_snfingerprint(char *buffer, int size,
const struct coll_def *def)
return total;
}
+static enum coll_icu_strength
+cast_coll_strength(UCollationStrength s)
+{
+ enum coll_icu_strength res = COLL_ICU_STRENGTH_DEFAULT;
+
+ switch(s) {
+ case UCOL_PRIMARY:
+ res = COLL_ICU_STRENGTH_PRIMARY;
+ break;
+ case UCOL_SECONDARY:
+ res = COLL_ICU_STRENGTH_SECONDARY;
+ break;
+ case UCOL_TERTIARY:
+ res = COLL_ICU_STRENGTH_TERTIARY;
+ break;
+ case UCOL_QUATERNARY:
+ res = COLL_ICU_STRENGTH_QUATERNARY;
+ break;
+ case UCOL_IDENTICAL:
+ res = COLL_ICU_STRENGTH_IDENTICAL;
+ break;
+ default:
+ break;
+ }
+
+ return res;
+}
+
+/*enum coll_icu_strength*/
+enum coll_icu_strength
+coll_get_strength(const struct coll *coll)
+{
+ if (coll->collator == NULL)
+ return COLL_ICU_STRENGTH_DEFAULT;
+ else
+ return cast_coll_strength(ucol_getStrength(coll->collator));
+}
+
struct coll *
coll_new(const struct coll_def *def)
{
diff --git a/src/coll.h b/src/coll.h
index 9c725712a..8f65b73dd 100644
--- a/src/coll.h
+++ b/src/coll.h
@@ -70,6 +70,10 @@ struct coll {
const char fingerprint[0];
};
+/** Retrieve strength of the given collation */
+enum coll_icu_strength
+coll_get_strength(const struct coll *coll);
+
/**
* Create a collation by definition. Can return an existing
* collation object, if a one with the same fingerprint was
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
More information about the Tarantool-patches
mailing list