From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: From: Stanislav Zudin Subject: Re: [tarantool-patches] Re: [PATCH v2] sql: Duplicate key error for a non-unique index References: <20190220100610.29771-1-szudin@tarantool.org> <20190226132625.4bsl5p27m4rgoizi@esperanza> Message-ID: <3e9f77a3-62d7-1619-fc43-7d6d9b300031@tarantool.org> Date: Thu, 28 Feb 2019 17:17:13 +0300 MIME-Version: 1.0 In-Reply-To: <20190226132625.4bsl5p27m4rgoizi@esperanza> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Transfer-Encoding: 8bit Content-Language: en-US To: tarantool-patches@freelists.org Cc: Vladimir Davydov List-ID: 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