From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EBF6D2FBDD for ; Wed, 14 Nov 2018 16:00:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id YzSf60gmqV8K for ; Wed, 14 Nov 2018 16:00:09 -0500 (EST) Received: from smtp51.i.mail.ru (smtp51.i.mail.ru [94.100.177.111]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 09F272FA09 for ; Wed, 14 Nov 2018 16:00:08 -0500 (EST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.0 \(3445.100.39\)) Subject: [tarantool-patches] Re: [PATCH v2 2/3] Introduce "none" and "binary" collations From: "n.pettik" In-Reply-To: Date: Wed, 14 Nov 2018 23:59:59 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <9DBC0637-55A0-47E7-815F-CEA5FA1189AA@tarantool.org> References: <8e9bca5d0f620a5cbd3bdffe521cecdfd1ad0ffd.1542066357.git.korablev@tarantool.org> <03A13ECD-6BA4-448F-8952-EE8E5C753130@tarantool.org> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: Vladislav Shpilevoy > On 14 Nov 2018, at 14:52, Vladislav Shpilevoy = wrote: >=20 >>>>=20 >>>> + /* >>>> + * In SQL all identifiers should be uppercased, so >>>> + * to avoid mess lets simple search binary (since it is >>>> + * sort of "special" collation) ignoring case at all. >>>> + */ >>>=20 >>> 2. I am not sure if it should be so special. I think, we should >>> treat it just like any other collation. If tests have BINARY >>> uppercased, then they should be fixed, I guess. >> Thing is in SQLite binary can be used without quotes, like: >> =E2=80=A6 COLLATE BiNaRy =E2=80=A6 >> And since ids are uppercased, this thing comes as =E2=80=9CBINARY". >> If I forced only lower case =E2=80=9Cbinary=E2=80=9D, a lot (>80 = separate sub-tests, >> 12 separate file tests) of tests would fail. Moreover, I see that = similar >> things take place at Lua-land (schema.cc): >> local function update_index_parts(format, parts) >> ... >> elseif k =3D=3D 'collation' then >> -- find ID by name >> local coll =3D box.space._collation.index.name:get{v} >> if not coll then >> coll =3D box.space._collation.index.name:get{v:lower()} >> end >> If you still think that we should allow only =E2=80=9Cbinary=E2=80=9D = format, >> I will do it alongside with tests in a separate commit. >=20 > I still think that since 'binary' is even in _collation space > now, it should be just a regular collation. If you do not want > to change tests, then you can try to search any collation firstly > as is, and if not found, then lowerify and search again, regardless > of is it binary or not. We can=E2=80=99t lower each collation=E2=80=99s name - I=E2=80=99m sure = that Peter will be strictly against this change (to be honest I am too), because it contradicts our rules concerning identifications=E2=80=99 names. Moreover, what if user create two collations with names like =E2=80=9Ccustom_collation=E2=80=9D and =E2=80=9CCuStom_COLLATION=E2=80=9D = and execute query like SELECT =E2=80=A6 COLLATE =E2=80=9Ccustom_COLLATION" Which collation will be used in this case? Without lowering no one is found, but after lowering both would be available. I would better fix tests to make them use =E2=80=9Cbinary=E2=80=9D = instead of BINARY. I guess this is absolutely right solution. Commit at the end of letter. > My point is that we should not treat binary differently from > others. Yep, now I fully agree with you. I am going to add one more commit to patchset. > By the way, what if I write SQL like this? : >=20 > SELECT ... WHERE ... COLLATE NONE =E2=80=A6 >=20 > Looks like this code: >=20 >> + if (strcasecmp(name, "binary") =3D=3D 0) >> + p =3D coll_by_name("binary", strlen("binary")); >> + else >> + p =3D coll_by_name(name, strlen(name)); >=20 > will find it ok, but maybe should not, is it? Why? It exists in _collation and uses memcmp, so what is wrong with it?=20= I guess we simply should document its behaviour. Also, in your example parser would fail to find it. Correct usage is = like: > SELECT ... WHERE ... COLLATE =E2=80=9Cnone=E2=80=9D =E2=80=A6 :) On the contrary, I've added tests on this case: diff --git a/test/sql-tap/identifier_case.test.lua = b/test/sql-tap/identifier_case.test.lua index 4878ced65..9e8107aef 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test =3D require("sqltester") -test:plan(67) +test:plan(71) =20 local test_prefix =3D "identifier_case-" =20 @@ -207,7 +207,9 @@ data =3D { { 4, [["bInaRy"]], {1, "Collation 'bInaRy' does not exist"}}, { 5, [["unicode"]], {0}}, { 6, [[ unicode ]], {1,"Collation 'UNICODE' does not exist"}}, - { 7, [["UNICODE"]], {1,"Collation 'UNICODE' does not exist"}} + { 7, [["UNICODE"]], {1,"Collation 'UNICODE' does not exist"}}, + { 8, [[NONE]], {1,"Collation 'NONE' does not exist"}}, + { 9, [["none"]], {0}}, } =20 test:do_catchsql_test( @@ -241,6 +243,8 @@ data =3D { { 5, [[ 5 < 'b' collate "unicode" ]], {0, {1}}}, { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does = not exist"}}, { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {1}}}, + { 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not = exist"}}, + { 9, [[ 5 < 'b' collate "none" ]], {0, {1}}}, >> In other words, each field with type of STRING/SCALAR and mb ANY >> would have real pointer to collation. However, in turn such change >> would break too many tests, without significant refactoring of SQL >> query planner (at least I failed to do it in several hours). >> On the other hand, in this case we can remove COLL_NONE >> and unify collation interface: each field or key part MUST feature >> collation. And I mean not only valid ID (it is how it works now), >> but also valid (i.e. !=3D NULL) pointer to collation struct. So, it = seems >> that we would be able to remove any checks like: >> if (coll !=3D NULL) >> *use collation* >> else >> *process without involving collations* >> Would become: >> If (type_is_string_like) >> *always use collation* >> To be honest I was sure that you would notice this pickle=E2=80=A6 >> Or current approach is not dat bad? >=20 > Yeah, I understand your aspiration, but I have some remarks > about the draft patch above and about the concept as it is: >=20 > * you said that you want to get rid of COLL_NONE, but in the draft > above you still use it. Even if you had removed COLL_NONE > usage from key_def builders, it would have stayed in alter.cc > as a check that you can not drop 'none' collation. I think that > COLL_NONE as a constant equal to 0 should not be removed from > source code until it is not used at all. But it is a minor > comment rather about coding style: I prefer code like > 'id !=3D UNDERSTANDABLE_NAME' to 'id !=3D '. And still we use iid !=3D 0 to check whether index primary or not=E2=80=A6= But I like your argument. > * the main flaw in storing a collation for each string is that it > will dramatically slow down comparators. The awful problem is in > virtual functions calling - it is much slower than regular > functions. Yep, I understand this point and I told the same to Kostja, but he insisted on this =E2=80=9Cnone=E2=80=9D collation. To be honest, = I thought that he wanted to implement second variant, where pointers to =E2=80=9Cnone=E2=80=9D collation !=3D NULL. > Now we have two levels of avoiding coll->cmp() calling. > First in *_compare_create functions. If a key has no collations, > we can use highly specified template recursive comparators > (struct TupleCompare, struct TupleCompareWithKey) which call > memcmp. Second, we are trying to do not call coll->cmp() in other > comparators (see mp_compare_str). >=20 > Yes, we still can follow you, store a collation object in each > key_part, tuple_field, field_def, but in tuple_compare.cc we > still will need a way how to check that a collation comparator > can be inlined into memcmp. This way is check coll->id =3D=3D = COLL_NONE. > Now it is like >=20 > use_fast_cmp =3D coll =3D=3D NULL >=20 > Will be like >=20 > use_fast_cmp =3D coll->id =3D=3D COLL_NONE >=20 > As a summary: we can store a collation in each *_field/part object > to simplify SQL source code, but it does not affect necessity of > a special COLL_NONE constant. Thanks for your comments. > Also, I see now a new minor problem: >=20 >> diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua >> index 64f74f9d3..a9525058b 100644 >> --- a/src/box/lua/upgrade.lua >> +++ b/src/box/lua/upgrade.lua >> @@ -396,8 +396,10 @@ local function create_collation_space() >> box.space._index:insert{_collation.id, 1, 'name', 'tree', {unique = =3D true}, {{1, 'string'}}} >> log.info("create predefined collations") >> + box.space._collation:replace{0, "none", ADMIN, "BINARY", "", = setmap{}} >> box.space._collation:replace{1, "unicode", ADMIN, "ICU", "", = setmap{}} >> box.space._collation:replace{2, "unicode_ci", ADMIN, "ICU", "", = {strength=3D'primary'}} >> + box.space._collation:replace{3, "binary", ADMIN, "BINARY", "", = setmap{}} >> local _priv =3D box.space[box.schema.PRIV_ID] >> _priv:insert{ADMIN, PUBLIC, 'space', _collation.id, box.priv.W} >=20 > Unfortunately, we can not change old update() functions. So the only = way to add > these new collations is to add a new code to upgrade_to_2_1_0() with = these two > new lines. Ops, indeed. Moved new collations to upgrade function and updated bootstrap file: diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua index a9525058b..4b0fd0345 100644 --- a/src/box/lua/upgrade.lua +++ b/src/box/lua/upgrade.lua @@ -396,10 +396,8 @@ local function create_collation_space() box.space._index:insert{_collation.id, 1, 'name', 'tree', {unique =3D= true}, {{1, 'string'}}} =20 log.info("create predefined collations") - box.space._collation:replace{0, "none", ADMIN, "BINARY", "", = setmap{}} box.space._collation:replace{1, "unicode", ADMIN, "ICU", "", = setmap{}} box.space._collation:replace{2, "unicode_ci", ADMIN, "ICU", "", = {strength=3D'primary'}} - box.space._collation:replace{3, "binary", ADMIN, "BINARY", "", = setmap{}} =20 local _priv =3D box.space[box.schema.PRIV_ID] _priv:insert{ADMIN, PUBLIC, 'space', _collation.id, box.priv.W} @@ -608,6 +606,9 @@ local function upgrade_to_2_1_0() format[2] =3D {type=3D'any', name=3D'value', is_nullable=3Dtrue} box.space._schema:format(format) =20 + box.space._collation:replace{0, "none", ADMIN, "BINARY", "", = setmap{}} + box.space._collation:replace{3, "binary", ADMIN, "BINARY", "", = setmap{}} + upgrade_priv_to_2_1_0() end New commit in patch-set: commit 854792f46d4ec87c3c2218c7645fe398e007c888 Author: Nikita Pettik Date: Wed Nov 14 21:07:58 2018 +0300 sql: don't uppercase name of binary collation =20 Since now we don't have real binary collation, it was allowed to use = its name in different cases. For instance: BiNaRY, "bInArY", "BINARY" = etc. All these names were valid and accounted for binary collation. However, we are going to introduce real entry in _collation space to represent binary collation. Thus, for now we allow using only = standard "binary" name. =20 Needed for #3185 diff --git a/src/box/sql/build.c b/src/box/sql/build.c index 17c20480e..191eb0308 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -2400,11 +2400,8 @@ index_fill_def(struct Parse *parse, struct index = *index, if (expr->op =3D=3D TK_COLLATE) { sql_get_coll_seq(parse, expr->u.zToken, = &coll_id); if (coll_id =3D=3D COLL_NONE && - strcmp(expr->u.zToken, "binary") !=3D 0) { - diag_set(ClientError, = ER_NO_SUCH_COLLATION, - expr->u.zToken); + strcmp(expr->u.zToken, "binary") !=3D 0) goto tnt_error; - } diff --git a/src/box/sql/callback.c b/src/box/sql/callback.c index 3cf3a835d..62904fd20 100644 --- a/src/box/sql/callback.c +++ b/src/box/sql/callback.c @@ -42,15 +42,16 @@ struct coll * sql_get_coll_seq(Parse *parser, const char *name, uint32_t *coll_id) { - if (name =3D=3D NULL || strcasecmp(name, "binary") =3D=3D 0) { + if (name =3D=3D NULL || strcmp(name, "binary") =3D=3D 0) { *coll_id =3D COLL_NONE; return NULL; } struct coll_id *p =3D coll_by_name(name, strlen(name)); if (p =3D=3D NULL) { *coll_id =3D COLL_NONE; - sqlite3ErrorMsg(parser, "no such collation sequence: = %s", - name); + diag_set(ClientError, ER_NO_SUCH_COLLATION, name); + parser->rc =3D SQL_TARANTOOL_ERROR; + parser->nErr++; return NULL; } else { *coll_id =3D p->id; diff --git a/test/sql-tap/collation.test.lua = b/test/sql-tap/collation.test.lua index eb4f43a90..9483632b2 100755 --- a/test/sql-tap/collation.test.lua +++ b/test/sql-tap/collation.test.lua @@ -155,7 +155,7 @@ local data_test_unicode_ci =3D { local data_collations =3D { -- default collation =3D binary {"/*COLLATE DEFAULT*/", data_test_binary_1}, - {"COLLATE BINARY", data_test_binary_1}, + {"COLLATE \"binary\"", data_test_binary_1}, {"COLLATE \"unicode\"", data_test_unicode}, {"COLLATE \"unicode_ci\"", data_test_unicode_ci}, } diff --git a/test/sql-tap/distinct.test.lua = b/test/sql-tap/distinct.test.lua index 26a4aace2..8f6ca8ed9 100755 --- a/test/sql-tap/distinct.test.lua +++ b/test/sql-tap/distinct.test.lua @@ -124,9 +124,9 @@ local data =3D { {"13.2", 0, "SELECT DISTINCT a, b, c COLLATE \"unicode_ci\" FROM = t4"}, {"14.1", 0, "SELECT DISTINCT a, d COLLATE \"unicode_ci\" FROM t1"}, {"14.2", 1, "SELECT DISTINCT a, d COLLATE \"unicode_ci\" FROM t4"}, - {"15 ", 0, "SELECT DISTINCT a, d COLLATE binary FROM t1"}, - {"16.1", 0, "SELECT DISTINCT a, b, c COLLATE binary FROM t1"}, - {"16.2", 1, "SELECT DISTINCT a, b, c COLLATE binary FROM t4"}, + {"15 ", 0, "SELECT DISTINCT a, d COLLATE \"binary\" FROM t1"}, + {"16.1", 0, "SELECT DISTINCT a, b, c COLLATE \"binary\" FROM t1"}, + {"16.2", 1, "SELECT DISTINCT a, b, c COLLATE \"binary\" FROM t4"}, {"17", 0, --{ \/* Technically, it would be possible to detect = that DISTINCT\n ** is a no-op in cases like the following. = But SQLite does not\n ** do so. *\/\n "SELECT DISTINCT t1.id FROM t1, t2 WHERE t1.id=3Dt2.x" }, {"18 ", 1, "SELECT DISTINCT c1, c2 FROM t3"}, @@ -173,7 +173,7 @@ data =3D { {"a, b, c FROM t1", {"btree"}, {"A", "B", "C", "a", "b", "c"}}, {"a, b, c FROM t1 ORDER BY a, b, c", {"btree"}, {"A", "B", "C", = "a", "b", "c"}}, {"b FROM t1 WHERE a =3D 'a'", {}, {"b"}}, - {"b FROM t1 ORDER BY +b COLLATE binary", {"btree", "btree"}, {"B", = "b"}}, + {"b FROM t1 ORDER BY +b COLLATE \"binary\"", {"btree", "btree"}, = {"B", "b"}}, {"a FROM t1", {}, {"A", "a"}}, {"b COLLATE \"unicode_ci\" FROM t1", {}, {"b"}}, {"b COLLATE \"unicode_ci\" FROM t1 ORDER BY b COLLATE = \"unicode_ci\"", {}, {"b"}}, diff --git a/test/sql-tap/e_select1.test.lua = b/test/sql-tap/e_select1.test.lua index 47fb7a809..1e8f096e1 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -508,11 +508,11 @@ if (0 > 0) test:do_execsql_test( "e_select-1.6.0", [[ - CREATE TABLE t5(a TEXT COLLATE "unicode_ci", b TEXT = COLLATE binary); + CREATE TABLE t5(a TEXT COLLATE "unicode_ci", b TEXT = COLLATE "binary"); INSERT INTO t5 VALUES('AA', 'cc'); INSERT INTO t5 VALUES('BB', 'dd'); INSERT INTO t5 VALUES(NULL, NULL); - CREATE TABLE t6(a TEXT COLLATE binary, b TEXT COLLATE = "unicode_ci"); + CREATE TABLE t6(a TEXT COLLATE "c", b TEXT COLLATE = "unicode_ci"); INSERT INTO t6 VALUES('aa', 'cc'); INSERT INTO t6 VALUES('bb', 'DD'); INSERT INTO t6 VALUES(NULL, NULL); @@ -997,7 +997,7 @@ test:do_execsql_test( INSERT INTO b2 VALUES('abc', 3); INSERT INTO b2 VALUES('xyz', 4); =20 - CREATE TABLE b3(id INT PRIMARY KEY, a TEXT COLLATE = "unicode_ci", b TEXT COLLATE binary); + CREATE TABLE b3(id INT PRIMARY KEY, a TEXT COLLATE = "unicode_ci", b TEXT COLLATE "binary"); INSERT INTO b3 VALUES(1, 'abc', 'abc'); INSERT INTO b3 VALUES(2, 'aBC', 'aBC'); INSERT INTO b3 VALUES(3, 'Def', 'Def'); @@ -1289,7 +1289,7 @@ test:do_select_tests( {"1", "SELECT DISTINCT b FROM h1", {"one", "I", "i", "four", = "IV", "iv"}}, {"2", "SELECT DISTINCT b COLLATE \"unicode_ci\" FROM h1", = {"one", "I", "four", "IV"}}, {"3", "SELECT DISTINCT x FROM h2", {"One", "Two", "Three", = "Four"}}, - {"4", "SELECT DISTINCT x COLLATE binary FROM h2", { + {"4", "SELECT DISTINCT x COLLATE \"binary\" FROM h2", { "One", "Two", "Three", "Four", "one", "two", "three", = "four" }}, }) @@ -1574,7 +1574,7 @@ test:drop_all_tables() test:do_execsql_test( "e_select-7.10.0", [[ - CREATE TABLE y1(a TEXT COLLATE "unicode_ci" PRIMARY KEY, b = TEXT COLLATE binary, c TEXT ); + CREATE TABLE y1(a TEXT COLLATE "unicode_ci" PRIMARY KEY, b = TEXT COLLATE "binary", c TEXT ); INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); ]], { -- @@ -1588,12 +1588,12 @@ test:do_select_tests( {"1", "SELECT 'abc' UNION SELECT 'ABC'", {"ABC", = "abc"}}, {"2", "SELECT 'abc' COLLATE \"unicode_ci\" UNION SELECT 'ABC'", = {"ABC" }}, {"3", "SELECT 'abc' UNION SELECT 'ABC' COLLATE = \"unicode_ci\"", {"ABC" }}, - {"4", "SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE = \"unicode_ci\"", {"ABC", "abc"}}, - {"5", "SELECT 'abc' COLLATE \"unicode_ci\" UNION SELECT 'ABC' = COLLATE binary", {"ABC" }}, + {"4", "SELECT 'abc' COLLATE \"binary\" UNION SELECT 'ABC' = COLLATE \"unicode_ci\"", {"ABC", "abc"}}, + {"5", "SELECT 'abc' COLLATE \"unicode_ci\" UNION SELECT 'ABC' = COLLATE \"binary\"", {"ABC" }}, {"6", "SELECT a FROM y1 UNION SELECT b FROM y1", {"abc" }}, {"7", "SELECT b FROM y1 UNION SELECT a FROM y1", {"Abc", = "abc"}}, {"8", "SELECT a FROM y1 UNION SELECT c FROM y1", {"aBC" }}, - {"9", "SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1", = {"aBC" }}, + {"9", "SELECT a FROM y1 UNION SELECT c COLLATE \"binary\" FROM = y1", {"aBC" }}, }) =20 -- EVIDENCE-OF: R-32706-07403 No affinity transformations are applied = to @@ -1932,7 +1932,7 @@ test:do_execsql_test( test:do_execsql_test( "e_select-8.9.1", [[ - SELECT x FROM d4 ORDER BY 1 COLLATE binary + SELECT x FROM d4 ORDER BY 1 COLLATE "binary" ]], { -- "DEF", "JKL", "abc", "ghi" @@ -1942,7 +1942,7 @@ test:do_execsql_test( test:do_execsql_test( "e_select-8.9.2", [[ - SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE "unicode_ci" + SELECT x COLLATE "binary" FROM d4 ORDER BY 1 COLLATE = "unicode_ci" ]], { -- "abc", "DEF", "ghi", "JKL" @@ -1963,7 +1963,7 @@ test:do_execsql_test( test:do_execsql_test( "e_select-8.10.1", [[ - SELECT x COLLATE binary FROM d4 ORDER BY 1 + SELECT x COLLATE "binary" FROM d4 ORDER BY 1 ]], { -- "DEF", "JKL", "abc", "ghi" @@ -1973,7 +1973,7 @@ test:do_execsql_test( test:do_execsql_test( "e_select-8.10.2", [[ - SELECT x COLLATE binary FROM d4 ORDER BY x + SELECT x COLLATE "binary" FROM d4 ORDER BY x ]], { -- "abc", "DEF", "ghi", "JKL" @@ -1983,7 +1983,7 @@ test:do_execsql_test( test:do_execsql_test( "e_select-8.10.3", [[ - SELECT x COLLATE binary AS x FROM d4 ORDER BY x + SELECT x COLLATE "binary" AS x FROM d4 ORDER BY x ]], { -- "DEF", "JKL", "abc", "ghi" diff --git a/test/sql-tap/identifier_case.test.lua = b/test/sql-tap/identifier_case.test.lua index 096130a52..4878ced65 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -201,10 +201,10 @@ end =20 -- Check that collaiton names work as identifiers data =3D { - { 1, [[ binary ]], {0}}, - { 2, [[ BINARY ]], {0}}, + { 1, [[ binary ]], {1, "Collation 'BINARY' does not exist"}}, + { 2, [[ BINARY ]], {1, "Collation 'BINARY' does not exist"}}, { 3, [["binary"]], {0}}, - { 4, [["bInaRy"]], {0}}, + { 4, [["bInaRy"]], {1, "Collation 'bInaRy' does not exist"}}, { 5, [["unicode"]], {0}}, { 6, [[ unicode ]], {1,"Collation 'UNICODE' does not exist"}}, { 7, [["UNICODE"]], {1,"Collation 'UNICODE' does not exist"}} @@ -234,12 +234,12 @@ test:do_catchsql_test( {0}) =20 data =3D { - { 1, [[ 'a' < 'b' collate binary ]], {0, {1}}}, + { 1, [[ 'a' < 'b' collate binary ]], {1, "Collation 'BINARY' does = not exist"}}, { 2, [[ 'a' < 'b' collate "binary" ]], {0, {1}}}, { 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[near "'binary'": = syntax error]]}}, { 4, [[ 'a' < 'b' collate "unicode" ]], {0, {1}}}, { 5, [[ 5 < 'b' collate "unicode" ]], {0, {1}}}, - { 6, [[ 5 < 'b' collate unicode ]], {1,"no such collation = sequence: UNICODE"}}, + { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does = not exist"}}, { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {1}}}, } =20 diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua index 83139a3e4..e6f0d415d 100755 --- a/test/sql-tap/in3.test.lua +++ b/test/sql-tap/in3.test.lua @@ -206,7 +206,7 @@ test:do_test( test:do_test( "in3-1.15", function() - return exec_neph(" SELECT a FROM t1 WHERE a COLLATE binary IN = (SELECT a FROM t1) ") + return exec_neph(" SELECT a FROM t1 WHERE a COLLATE \"binary\" = IN (SELECT a FROM t1) ") end, { -- 0, 1, 3, 5 diff --git a/test/sql-tap/in5.test.lua b/test/sql-tap/in5.test.lua index 4e2cdcd24..ba21170ee 100755 --- a/test/sql-tap/in5.test.lua +++ b/test/sql-tap/in5.test.lua @@ -277,7 +277,7 @@ test:do_execsql_test( test:do_execsql_test( "6.1.2", [[ - SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT = DISTINCT a FROM t1) + SELECT count(*) FROM t1 WHERE a COLLATE "binary" IN (SELECT = DISTINCT a FROM t1) ]], { -- <6.1.2> 1 diff --git a/test/sql-tap/index3.test.lua b/test/sql-tap/index3.test.lua index 4f950be09..222b895aa 100755 --- a/test/sql-tap/index3.test.lua +++ b/test/sql-tap/index3.test.lua @@ -59,7 +59,7 @@ test:do_execsql_test( CREATE TABLE t1(a INT , b TEXT , c INT , d INT , e INT , PRIMARY KEY(a), UNIQUE(b COLLATE "unicode_ci" = DESC)); CREATE INDEX t1c ON t1(c); - CREATE INDEX t1d ON t1(d COLLATE binary ASC); + CREATE INDEX t1d ON t1(d COLLATE "binary" ASC); WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE = x<30) INSERT INTO t1(a,b,c,d,e)=20 SELECT x, printf('ab%03xxy',x), x, x, x FROM c; diff --git a/test/sql-tap/minmax3.test.lua = b/test/sql-tap/minmax3.test.lua index 1ddf39ff5..348275ffa 100755 --- a/test/sql-tap/minmax3.test.lua +++ b/test/sql-tap/minmax3.test.lua @@ -502,7 +502,7 @@ test:do_execsql_test( test:do_execsql_test( "minmax3-4.4", [[ - SELECT max(x COLLATE binary), max(x COLLATE "unicode_ci") FROM = t4; + SELECT max(x COLLATE "binary"), max(x COLLATE "unicode_ci") = FROM t4; ]], { -- "abc", "BCD" @@ -564,7 +564,7 @@ test:do_execsql_test( test:do_execsql_test( "minmax3-4.13", [[ - SELECT min(x COLLATE binary), min(x COLLATE "unicode_ci") FROM = t4; + SELECT min(x COLLATE "binary"), min(x COLLATE "unicode_ci") = FROM t4; ]], { -- "BCD", "abc" diff --git a/test/sql-tap/resolver01.test.lua = b/test/sql-tap/resolver01.test.lua index d08f95bf6..7be2184e5 100755 --- a/test/sql-tap/resolver01.test.lua +++ b/test/sql-tap/resolver01.test.lua @@ -236,7 +236,7 @@ test:do_test( ]]) local r =3D {} table.insert(r, test:execsql("SELECT '1', substr(m,2) AS m FROM = t4 ORDER BY m;")) - table.insert(r, test:execsql("SELECT '2', substr(m,2) AS m FROM = t4 ORDER BY m COLLATE binary;")) + table.insert(r, test:execsql("SELECT '2', substr(m,2) AS m FROM = t4 ORDER BY m COLLATE \"binary\";")) table.insert(r, test:execsql("SELECT '3', substr(m,2) AS m FROM = t4 ORDER BY lower(m);")) return r end, { diff --git a/test/sql-tap/selectE.test.lua = b/test/sql-tap/selectE.test.lua index 11b84711e..b379f5aa8 100755 --- a/test/sql-tap/selectE.test.lua +++ b/test/sql-tap/selectE.test.lua @@ -84,7 +84,7 @@ test:do_test( function() return test:execsql [[ SELECT a FROM t2 EXCEPT SELECT a FROM t3 - ORDER BY a COLLATE binary; + ORDER BY a COLLATE "binary"; ]] end, { -- @@ -127,7 +127,7 @@ test:do_test( function() return test:execsql [[ SELECT a COLLATE "unicode_ci" FROM t2 EXCEPT SELECT a FROM = t3 - ORDER BY 1 COLLATE binary + ORDER BY 1 COLLATE "binary" ]] end, { -- diff --git a/test/sql-tap/tkt2391.test.lua = b/test/sql-tap/tkt2391.test.lua index 7fa5e1634..b48998c53 100755 --- a/test/sql-tap/tkt2391.test.lua +++ b/test/sql-tap/tkt2391.test.lua @@ -20,7 +20,7 @@ test:plan(4) test:do_execsql_test( "tkt2391.1", [[ - CREATE TABLE folders(folderid INT , parentid INT , foldername = TEXT COLLATE binary primary key); + CREATE TABLE folders(folderid INT , parentid INT , foldername = TEXT COLLATE "binary" primary key); INSERT INTO folders VALUES(1, 3, 'FolderA'); INSERT INTO folders VALUES(1, 3, 'folderB'); INSERT INTO folders VALUES(4, 0, 'FolderC'); diff --git a/test/sql-tap/tkt3493.test.lua = b/test/sql-tap/tkt3493.test.lua index 26ca2271b..7e093b217 100755 --- a/test/sql-tap/tkt3493.test.lua +++ b/test/sql-tap/tkt3493.test.lua @@ -246,7 +246,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-3.1", [[ - CREATE TABLE t2(a TEXT COLLATE "unicode_ci" PRIMARY KEY, b = TEXT COLLATE BINARY); + CREATE TABLE t2(a TEXT COLLATE "unicode_ci" PRIMARY KEY, b = TEXT COLLATE "binary"); INSERT INTO t2 VALUES('aBc', 'DeF'); ]], { -- @@ -291,7 +291,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-3.3.2", [[ - SELECT a>b COLLATE BINARY FROM t2 GROUP BY a, b + SELECT a>b COLLATE "binary" FROM t2 GROUP BY a, b ]], { -- 1 diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua index faa99811c..8200d25f8 100755 --- a/test/sql-tap/with1.test.lua +++ b/test/sql-tap/with1.test.lua @@ -1056,7 +1056,7 @@ test:do_catchsql_test(13.3, [[ -- 2015-04-12 -- test:do_execsql_test(14.1, [[ - WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 = COLLATE binary; + WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 = COLLATE "binary"; ]], { -- <14.1>