[tarantool-patches] Re: [PATCH 2/2] sql: remove GLOB from Tarantool

[Alex Khatskevich <avkhatskevich@tarantool.org>]

[-- Attachment #1: Type: text/plain, Size: 29225 bytes --]

1. See some comments below.
2. Please, send the full patch diff when done (not just an incremental 
diff as you did).
Patch seems ok in overall.

On 09.09.2018 17:57, Nikita Tatunov wrote:
> Hello, please consider corrected version of the patch.
> Diff with the previous version at the end.
>
>> On 17 Aug 2018, at 11:25, Alex Khatskevich 
>> <avkhatskevich@tarantool.org <mailto:avkhatskevich@tarantool.org>> wrote:
>>
>>
>>
>> On 16.08.2018 20:00, N.Tatunov wrote:
>>> GLOB is a legacy extension for LIKE from SQLite. As we want our SQL to
>>> be close to ANSI SQL & LIKE to depend on collations, we do not want to
>>> support it. This patch totally removes it from Tarantool along with any
>>> mentions of it.
>> 1.We delete it because it is not working properly, and instead of 
>> fixing it we
>> want to replace it with more general regexp. Delete other unnecessary 
>> thoughts
>> from this message.
>> 2. Do not use "we", "our" in commit messages.
>
> As discussed above it’s not going to be changed.
Ok.
>
>>>  static int
>>>  sql_utf8_pattern_compare(const char *pattern,
>>> const char *string,
>>> -const struct compareInfo *pInfo,
>>> -UChar32 matchOther)
>>> +const int *is_like_ci,
>> Pass this parameter by value.
>
> Fixed.
>
>>> +UChar32 match_other)
>>>  {
>>> /* Next pattern and input string chars */
>>> UChar32 c, c2;
>>> -/* "?" or "_" */
>>> -UChar32 matchOne = pInfo->matchOne;
>>> -/* "*" or "%" */
>>> -UChar32 matchAll = pInfo->matchAll;
>>> -/* True if uppercase==lowercase */
>>> -UChar32 noCase = pInfo->noCase;
>>> +/* "_" */
>>> +UChar32 match_one = '_';
>>> +/* "%" */
>>> +UChar32 match_all = '%';
>> This variables consumes stack. Can they be moved to defines?
>> If it will break smth, make them const.
>
> moved them to defines.
>
>>>
>>>  int
>>> -sqlite3_strlike(const char *zPattern, const char *zStr, unsigned 
>>> int esc)
>>> +sql_strlike_ci(const char *zPattern, const char *zStr, unsigned int 
>>> esc)
>>>  {
>>> -return sql_utf8_pattern_compare(zPattern, zStr, &likeInfoNorm, esc);
>>> +return sql_utf8_pattern_compare(zPattern, zStr, 
>>> &case_insensitive_like, esc);
>> Hardcode `case_insensitive_like` value here.
>
> Done.
>
>>> +/**
>>> +* Limit the length of the LIKE pattern to avoid problems
>>> +* of deep recursion and N*N behavior in
>> I thought that only globe could require N*N time. Check delete the 
>> comment.
>
> The reason is the recursion in sql_utf8_pattern_compare() which is still
> there.
>
>>>
>>> -    "ESCAPE expression must be a single character",
>>> +    "ESCAPE expression must be a"
>>> +    " single character",
>> Do not split error messages at the middle of a sentence. It makes 
>> errors ungreppable.
>> Make it <80 somehow different.
>>
>
> Have already been discussed in this thread.
I suppose that we concluded to try to fit into 80 and split the string only
in case it is impossible.
>>> -sqlite3_result_error(context, "LIKE or GLOB pattern can only"
>>> -    " contain UTF-8 characters", -1);
>>> +sqlite3_result_error(context, "LIKE pattern can only contain"
>>> +    " UTF-8 characters", -1);
>> Do not split error messages at the middle of a sentence. Make it <80 
>> somehow different.
>
> Have already been discussed in this thread.
same
>
>>> +int *is_like_ci;
>>> +if (is_case_sensitive)
>>> +is_like_ci = (int *)&case_sensitive_like;
>> pass this var by value.
>
> We need (void *) in sqlite3CreateFunc(), i don’t think it’s relevant 
> to pass
> variable by value.
Yes, sorry.
>
>>> diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua
>>> index cfe2801..773bdeb 100755
>>> --- a/test/sql-tap/alter.test.lua
>>> +++ b/test/sql-tap/alter.test.lua
>>> @@ -230,9 +230,10 @@ test:do_execsql_test(
>>>  test:do_execsql_test(
>>>      "alter-5.1",
>>>      [[
>>> +        PRAGMA case_sensitive_like = true;
>>>          CREATE TABLE xyz(x PRIMARY KEY);
>>>          ALTER TABLE xyz RENAME TO "xyz1234abc";
>>> -        SELECT "name" FROM "_space" WHERE "name" GLOB 'xyz*';
>>> +        SELECT "name" FROM "_space" WHERE "name" LIKE 'xyz%';
>> This test become unreasonably complex.
>> Do just "select where name = 'xyz1234abc'
>> Or at least delete case_sensitive…
>
> Done.
>
>>>      ]], {
>>>          -- <alter-5.1>
>>>          "xyz1234abc"
>>> @@ -243,7 +244,8 @@ test:do_execsql_test(
>>>      "alter-5.2",
>>>      [[
>>>          ALTER TABLE "xyz1234abc" RENAME TO xyzabc;
>>> -        SELECT "name" FROM "_space" WHERE "name" GLOB 'XYZ*';
>>> +        SELECT "name" FROM "_space" WHERE "name" LIKE 'XYZ%';
>>> +        PRAGMA case_sensitive_like = false;
>> This test become unreasonably complex.
>> Do just "select where name = ‘xyz1234abc'
>
> Done.
>
>>> --- NOTE: This test needs refactoring after deletion of GLOB &
>>> ---type restrictions for LIKE. (See #3572)
>>> ---    {"LIKE", "like"},
>>> ---    {"GLOB", "glob"},
>>> +    {"LIKE", "like"},
>>>      {"AND", "and"},
>>>      {"OR", "or"},
>>>      {"MATCH", "match"},
>>> @@ -98,12 +95,9 @@ operations = {
>>>      {"+", "-"},
>>>      {"<<", ">>", "&", "|"},
>>>      {"<", "<=", ">", ">="},
>>> --- NOTE: This test needs refactoring after deletion of GLOB &
>>> ---type restrictions for LIKE. (See #3572)
>>>  -- Another NOTE: MATCH & REGEXP aren't supported in Tarantool &
>>> ---are waiting for their hour, don't confuse them
>>> ---being commented with ticket above.
>>> -    {"=", "==", "!=", "<>"}, --"LIKE", "GLOB"}, --"MATCH", "REGEXP"},
>>> +--are waiting for their hour.
>>> +    {"=", "==", "!=", "<>", "LIKE"}, --"MATCH", "REGEXP"},
>>>      {"AND"},
>>>      {"OR"},
>>>  }
>>> @@ -128,7 +122,7 @@ end
>>>  -- EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
>>>  -- operators, in order from highest to lowest precedence: || * / % + -
>>>  -- << >> & | < <= > >= = == != <> IS IS
>>> --- NOT IN LIKE GLOB MATCH REGEXP AND OR
>>> +-- NOT IN LIKE MATCH REGEXP AND OR
>>>  --
>>>  -- EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
>>>  -- precedence as =.
>>> @@ -467,18 +461,63 @@ literals = {
>>>  for _, op in ipairs(oplist) do
>>>      for n1, rhs in ipairs(literals) do
>>>          for n2, lhs in ipairs(literals) do
>>> -            local t = test:execsql(string.format(" SELECT typeof(%s 
>>> %s %s) ", lhs, op, rhs))[1]
>>> -            test:do_test(
>>> -                string.format("e_expr-7.%s.%s.%s", opname[op], n1, n2),
>>> -                function()
>>> -                    --print("\n op "..op.." t "..t)
>>> -                    return (((op == "||") and ((t == "text") or
>>> -                            (t == "null"))) or
>>> -                            ((op ~= "||") and (((t == "integer") or
>>> -                                    (t == "real")) or
>>> -                                    (t == "null")))) and 1 or 0
>>> -                end, 1)
>>> +            if op ~= "LIKE" then
>> 1. Why do not just delete like from `oplist`?
>> 2. We were discussing this place with you and Georgy, and decided 
>> that you do
>> not touch this for loop at all.
>
> Ok. Commented LIKE in `oplist` (it’s still a binary operator who 
> knows, maybe we will revive it).
>
>>> -local function glob(args)
>>> -    return 1
>>> -end
>>>  -box.internal.sql_create_function("GLOB", glob)
>>> -box.internal.sql_create_function("MATCH", glob)
>>> -box.internal.sql_create_function("REGEXP", glob)
>>> +-- NOTE: GLOB is removed from Tarantool, thus it'll be needed to
>>> +--       refactor these calls. They don't work right now since
>>> +--          we don't support MATHC & REGEXP.
>>> +-- local function glob(args)
>>> +--     return 1
>>> +-- end
>> This test do not test the glob function. Delete this comment.
>
> Done.
>
>>> +
>>> +-- box.internal.sql_create_function("MATCH", glob)
>>> +-- box.internal.sql_create_function("REGEXP", glob)
>> You was lucky that commenting those lines do not break the tests. 
>> (because there is a similar
>> code above)
>> Return it back.
>
> Done.
>
>>> @@ -2274,15 +2312,23 @@ test:do_execsql_test(
>>>          -- </e_expr-16.1.7>
>>>      })
>>>  --- EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
>>> --- uses the Unix file globbing syntax for its wildcards.
>>> ---
>>> --- EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike 
>>> LIKE.
>>> +-- EVIDENCE-OF: R-52087-12043 LIKE doesn't use Unix file globbing
>>> +-- syntax for its wildcards.
>> Those test was designed especially for the glob function.
>> There are similar tests for like above.
>> You should delete it instead of renaming.
>
> Ok, Deleted.
>
>>>  --- EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded 
>>> by the
>>> +-- EVIDENCE-OF: R-39616-20555 LIKE may be preceded by the
>>>  -- NOT keyword to invert the sense of the test.
>>>  --
>>>  test:do_execsql_test(
>>> +    "e_expr-17.2.0",
>>> +    [[
>>> +        SELECT 'abcxyz' NOT LIKE 'ABC%'
>>> +    ]], {
>>> +        -- <e_expr-17.2.0>
>>> +        1
>>> +        -- </e_expr-17.2.0>
>>> +    })
>>> +
>>> +test:do_execsql_test(
>>>      "e_expr-17.2.1",
>>>      [[
>>> -        SELECT 'abcxyz' NOT GLOB 'ABC*'
>>> +        SELECT 'abcxyz' NOT LIKE 'abc%'
>>>      ]], {
>>>          -- <e_expr-17.2.1>
>>> -        1
>>> +        0
>>>          -- </e_expr-17.2.1>
>>>      })
>>>    test:do_execsql_test(
>>>      "e_expr-17.2.2",
>>>      [[
>>> -        SELECT 'abcxyz' NOT GLOB 'abc*'
>>> +        PRAGMA case_sensitive_like = 0
>>>      ]], {
>>>          -- <e_expr-17.2.2>
>>> -        0
>>> -        -- </e_expr-17.2.2>
>>> +
>>> +        -- <e_expr-17.2.2>
>>>      })
>>>    test:do_execsql_test(
>>> @@ -2405,10 +2461,11 @@ test:do_execsql_test(
>>>  -- MUST_WORK_TEST uses access to nullvalue... (sql parameters) and 
>>> built in functions
>>>  if 0>0 then
>>>      db("nullvalue", "null")
>> do not change tests which are not working.
>> There is a chance chat you do it wrong and you do not know about it.
> Made it back.
>
>>> diff --git a/test/sql-tap/like3.test.lua b/test/sql-tap/like3.test.lua
>>> index 505d2fa..0bc71a0 100755
>>> --- a/test/sql-tap/like3.test.lua
>>> +++ b/test/sql-tap/like3.test.lua
>>> @@ -12,13 +12,13 @@ test:plan(7)
>>>  --    May you find forgiveness for yourself and forgive others.
>>>  --    May you share freely, never taking more than you give.
>>>  --
>>> --------------------------------------------------------------------------
>>> +-----------------------------------------------------------------
>>>  --
>>> --- This file implements regression tests for SQLite library.  The
>>> --- focus of this file is testing the LIKE and GLOB operators and
>>> --- in particular the optimizations that occur to help those operators
>>> --- run faster and that those optimizations work correctly when there
>>> --- are both strings and blobs being tested.
>>> +-- This file implements regression tests for SQLite library. The
>>> +-- focus of this file is testing the LIKE operator and
>>> +-- in particular the optimizations that occur to help this
>>> +-- operator run faster and that those optimizations work
>>> +-- correctly when there are both strings and blobs being tested.
>>>  --
>>>  -- Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the 
>>> following
>>>  -- SQL was not working correctly:
>>> @@ -70,10 +70,11 @@ test:do_execsql_test(
>>>  test:do_execsql_test(
>>>      "like3-2.0",
>>>      [[
>>> +        PRAGMA case_sensitive_like = 1;
>>>          CREATE TABLE t2(a PRIMARY KEY, b TEXT);
>>>          INSERT INTO t2 SELECT a, b FROM t1;
>>>          CREATE INDEX t2ba ON t2(b,a);
>>> -        SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
>>> +        SELECT a, b FROM t2 WHERE b LIKE 'ab%' ORDER BY +a;
>> Those tests were especially created for glob. Delete it instead of 
>> renaming.
>
> Done.
>
> Diff with the prev version of the patch:
>
> diff --git a/src/box/sql/func.c b/src/box/sql/func.c
> index 177193e94..28b435ae3 100644
> --- a/src/box/sql/func.c
> +++ b/src/box/sql/func.c
> @@ -631,6 +631,12 @@ static const int case_insensitive_like = 1;
>   */
>  static const int case_sensitive_like = 0;
> +/**
> + * Wildcards.
> + */
> +#define match_one '_'
> +#define match_all '%'
> +
>  /**
>   * Possible error returns from sql_utf8_pattern_compare().
>   */
> @@ -672,15 +678,11 @@ static const int case_sensitive_like = 0;
>  static int
>  sql_utf8_pattern_compare(const char *pattern,
> const char *string,
> -const int *is_like_ci,
> +const int is_like_ci,
> UChar32 match_other)
>  {
> /* Next pattern and input string chars */
> UChar32 c, c2;
> -/* "_" */
> -UChar32 match_one = '_';
> -/* "%" */
> -UChar32 match_all = '%';
> /* One past the last escaped input char */
> const char *zEscaped = 0;
> const char *pattern_end = pattern + strlen(pattern);
> @@ -741,7 +743,7 @@ sql_utf8_pattern_compare(const char *pattern,
> */
> int bMatch;
> -if (*is_like_ci)
> +if (is_like_ci)
> c = u_tolower(c);
> while (string < string_end){
> /**
> @@ -757,7 +759,7 @@ sql_utf8_pattern_compare(const char *pattern,
> c2 = Utf8Read(string, string_end);
> if (c2 == SQL_INVALID_UTF8_SYMBOL)
> return SQL_NOMATCH;
> -if (!(*is_like_ci)) {
> +if (!is_like_ci) {
> if (c2 != c)
> continue;
> } else {
> @@ -786,7 +788,7 @@ sql_utf8_pattern_compare(const char *pattern,
> return SQL_NOMATCH;
> if (c == c2)
> continue;
> -if (*is_like_ci) {
> +if (is_like_ci) {
> /**
> * Small optimisation. Reduce number of
> * calls to u_tolower function. SQL
> @@ -814,7 +816,7 @@ sql_utf8_pattern_compare(const char *pattern,
>  int
>  sql_strlike_cs(const char *zPattern, const char *zStr, unsigned int esc)
>  {
> -return sql_utf8_pattern_compare(zPattern, zStr, &case_sensitive_like, 
> esc);
> +return sql_utf8_pattern_compare(zPattern, zStr, case_sensitive_like, 
> esc);
>  }
>  /**
> @@ -824,7 +826,7 @@ sql_strlike_cs(const char *zPattern, const char 
> *zStr, unsigned int esc)
>  int
>  sql_strlike_ci(const char *zPattern, const char *zStr, unsigned int esc)
>  {
> -return sql_utf8_pattern_compare(zPattern, zStr, 
> &case_insensitive_like, esc);
> +return sql_utf8_pattern_compare(zPattern, zStr, 
> case_insensitive_like, esc);
>  }
>  /**
> @@ -907,7 +909,7 @@ likeFunc(sqlite3_context *context, int argc, 
> sqlite3_value **argv)
> sqlite3_like_count++;
>  #endif
> int res;
> -res = sql_utf8_pattern_compare(zB, zA, is_like_ci, escape);
> +res = sql_utf8_pattern_compare(zB, zA, *is_like_ci, escape);
> if (res == SQL_INVALID_PATTERN) {
> sqlite3_result_error(context, "LIKE pattern can only contain"
>     " UTF-8 characters", -1);
> diff --git a/src/box/sql/whereexpr.c b/src/box/sql/whereexpr.c
> index 947bd5d94..2d9fb6453 100644
> --- a/src/box/sql/whereexpr.c
> +++ b/src/box/sql/whereexpr.c
> @@ -218,6 +218,12 @@ operatorMask(int op)
> return c;
>  }
> +/**
> + * Wildcard characters.
> + */
> +#define match_one '_'
> +#define match_all '%'
> +
>  #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
>  /**
>   * Check to see if the given expression is a LIKE operator that
> @@ -258,9 +264,6 @@ is_like(Parse *pParse,
> int c;
> /* Number of non-wildcard prefix characters */
> int cnt;
> -/* Wildcard characters */
> -char match_all = '%';
> -char match_one = '_';
> /* Database connection */
> sqlite3 *db = pParse->db;
> sqlite3_value *pVal = 0;
> diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua
> index 773bdebdb..98338c493 100755
> --- a/test/sql-tap/alter.test.lua
> +++ b/test/sql-tap/alter.test.lua
> @@ -230,10 +230,9 @@ test:do_execsql_test(
>  test:do_execsql_test(
>      "alter-5.1",
>      [[
> -        PRAGMA case_sensitive_like = true;
>          CREATE TABLE xyz(x PRIMARY KEY);
>          ALTER TABLE xyz RENAME TO "xyz1234abc";
> -        SELECT "name" FROM "_space" WHERE "name" LIKE 'xyz%';
> +        SELECT "name" FROM "_space" WHERE "name" = 'xyz1234abc';
>      ]], {
>          -- <alter-5.1>
>          "xyz1234abc"
> @@ -244,8 +243,7 @@ test:do_execsql_test(
>      "alter-5.2",
>      [[
>          ALTER TABLE "xyz1234abc" RENAME TO xyzabc;
> -        SELECT "name" FROM "_space" WHERE "name" LIKE 'XYZ%';
> -        PRAGMA case_sensitive_like = false;
> +        SELECT "name" FROM "_space" WHERE "name" = 'XYZABC';
>      ]], {
>          -- <alter-5.2>
>          "XYZABC"
> diff --git a/test/sql-tap/e_expr.test.lua b/test/sql-tap/e_expr.test.lua
> index 162026845..0d69e8535 100755
> --- a/test/sql-tap/e_expr.test.lua
> +++ b/test/sql-tap/e_expr.test.lua
> @@ -1,6 +1,6 @@
>  #!/usr/bin/env tarantool
>  test = require("sqltester")
> -test:plan(11521)
> +test:plan(10647)
>  --!./tcltestrunner.lua
>  -- 2010 July 16
> @@ -77,7 +77,7 @@ local operations = {
>      {"<>", "ne1"},
>      {"!=", "ne2"},
>      {"IS", "is"},
> -    {"LIKE", "like"},
> +--    {"LIKE", "like"},
>      {"AND", "and"},
>      {"OR", "or"},
>      {"MATCH", "match"},
> @@ -96,8 +96,9 @@ operations = {
>      {"<<", ">>", "&", "|"},
>      {"<", "<=", ">", ">="},
>  -- Another NOTE: MATCH & REGEXP aren't supported in Tarantool &
> --- are waiting for their hour.
> -    {"=", "==", "!=", "<>", "LIKE"}, --"MATCH", "REGEXP"},
> +--               are waiting for their hour, don't confuse them
> +--               being commented with commenting of "LIKE".
> +    {"=", "==", "!=", "<>"}, --"LIKE"}, --"MATCH", "REGEXP"},
Delete Like. No one returns here.
>      {"AND"},
>      {"OR"},
>  }
> @@ -461,67 +462,21 @@ literals = {
>  for _, op in ipairs(oplist) do
>      for n1, rhs in ipairs(literals) do
>          for n2, lhs in ipairs(literals) do
> -            if op ~= "LIKE" then
> -                local t = test:execsql(string.format(" SELECT 
> typeof(%s %s %s) ", lhs, op, rhs))[1]
> -                test:do_test(
> -  string.format("e_expr-7.%s.%s.%s", opname[op], n1, n2),
> -                    function()
> -                        return (((op == "||") and ((t == "text") or
> -                                (t == "null"))) or
> -                                ((op ~= "||") and (((t == "integer") or
> -                                 (t == "real")) or
> -                                 (t == "null")))) and 1 or 0
> -                    end, 1)
> -            end
> -        end
> -    end
> -end
> -
> -local valid_patterns =
> -    {"'abc'", "'hexadecimal'", "''", 123, -123, 0,
> -    123.4, 0.0, -123.4, "X''", "X'0000'", "NULL"}
> -
> -local invalid_patterns = {"X'ABCDEF'"}
> -
> -for n1, rhs in ipairs(valid_patterns) do
> -    for n2, lhs in ipairs(literals) do
> -        local t = test:execsql(string.format(" SELECT typeof(%s LIKE 
> %s) ", lhs, rhs))[1]
> -        test:do_test(
> -            string.format("e_expr-7.%s.LIKE.%s", n1, n2),
> -            function()
> -                return (t == "integer" or
> -                        t == "real" or
> -                        t == "null") and 1 or 0
> -            end, 1)
> -    end
> -end
> +            local t = test:execsql(string.format(" SELECT typeof(%s 
> %s %s) ", lhs, op, rhs))[1]
> +            test:do_test(
> +  string.format("e_expr-7.%s.%s.%s", opname[op], n1, n2),
> +                function()
> +                    --print("\n op "..op.." t "..t)
> +                    return (((op == "||") and ((t == "text") or
> +                            (t == "null"))) or
> +                            ((op ~= "||") and (((t == "integer") or
> +                                    (t == "real")) or
> +                                    (t == "null")))) and 1 or 0
> +                end, 1)
> -for n1, rhs in ipairs(invalid_patterns) do
> -    for n2, lhs in ipairs(literals) do
> -        local t = string.format(" SELECT typeof(%s LIKE %s) ", lhs, rhs)
> -        local test_name = string.format("e_expr-7.%s.LIKE.%s", n1 + 
> 12, n2)
> -        if n2 ~= 13 then
> -            test:do_catchsql_test(
> -                test_name,
> -                t,
> -                {
> -                    -- <test_name>
> -                    1, "LIKE pattern can only contain UTF-8 characters"
> -                    -- <test_name>
> -                })
> -        else
> -            test:do_catchsql_test(
> -                test_name,
> -                t,
> -                {
> -                    -- <test_name>
> -                    0, {"null"}
> -                    -- <test_name>
> -                })
>          end
>      end
>  end
> -
>  ---------------------------------------------------------------------------
>  -- Test the IS and IS NOT operators.
>  --
> @@ -1343,15 +1298,12 @@ test:execsql [[
>      CREATE TABLE tblname(cname PRIMARY KEY);
>  ]]
> --- NOTE: GLOB is removed from Tarantool, thus it'll be needed to
> ---       refactor these calls. They don't work right now since
> ---          we don't support MATHC & REGEXP.
> --- local function glob(args)
> ---     return 1
> --- end
> +local function glob(args)
> +    return 1
> +end
> --- box.internal.sql_create_function("MATCH", glob)
> --- box.internal.sql_create_function("REGEXP", glob)
> +box.internal.sql_create_function("MATCH", glob)
> +box.internal.sql_create_function("REGEXP", glob)
>  local test_cases12 ={
>      {1, 123},
>      {2, 123.4e05},
> @@ -2312,96 +2264,14 @@ test:do_execsql_test(
>          -- </e_expr-16.1.7>
>      })
> --- EVIDENCE-OF: R-52087-12043 LIKE doesn't use Unix file globbing
> --- syntax for its wildcards.
> ---
> -test:do_execsql_test(
> -    "e_expr-17.1.0",
> -    [[
> -        PRAGMA case_sensitive_like = 1
> -    ]], {
> -        -- <e_expr-17.1.0>
> -
> -        -- <e_expr-17.1.0>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.1",
> -    [[
> -        SELECT 'abcxyz' LIKE 'abc*'
> -    ]], {
> -        -- <e_expr-17.1.1>
> -        0
> -        -- </e_expr-17.1.1>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.2",
> -    [[
> -        SELECT 'abcxyz' LIKE 'abc%'
> -    ]], {
> -        -- <e_expr-17.1.2>
> -        1
> -        -- </e_expr-17.1.2>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.3",
> -    [[
> -        SELECT 'abcxyz' LIKE 'abc???'
> -    ]], {
> -        -- <e_expr-17.1.3>
> -        0
> -        -- </e_expr-17.1.3>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.4",
> -    [[
> -        SELECT 'abcxyz' LIKE 'abc___'
> -    ]], {
> -        -- <e_expr-17.1.4>
> -        1
> -        -- </e_expr-17.1.4>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.5",
> -    [[
> -        SELECT 'abcxyz' LIKE 'abc%'
> -    ]], {
> -        -- <e_expr-17.1.5>
> -        1
> -        -- </e_expr-17.1.5>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.6",
> -    [[
> -        SELECT 'ABCxyz' LIKE 'abc%'
> -    ]], {
> -        -- <e_expr-17.1.6>
> -        0
> -        -- </e_expr-17.1.6>
> -    })
> -
> -test:do_execsql_test(
> -    "e_expr-17.1.7",
> -    [[
> -        SELECT 'abcxyz' LIKE 'ABC%'
> -    ]], {
> -        -- <e_expr-17.1.7>
> -        0
> -        -- </e_expr-17.1.7>
> -    })
> -
>  -- EVIDENCE-OF: R-39616-20555 LIKE may be preceded by the
>  -- NOT keyword to invert the sense of the test.
>  --
>  test:do_execsql_test(
>      "e_expr-17.2.0",
>      [[
> -        SELECT 'abcxyz' NOT LIKE 'ABC%'
> +        PRAGMA case_sensitive_like = 1;
> +        SELECT 'abcxyz' NOT LIKE 'ABC%';
>      ]], {
>          -- <e_expr-17.2.0>
>          1
> @@ -2461,11 +2331,10 @@ test:do_execsql_test(
>  -- MUST_WORK_TEST uses access to nullvalue... (sql parameters) and 
> built in functions
>  if 0>0 then
>      db("nullvalue", "null")
> -
>      test:do_execsql_test(
>          "e_expr-17.2.6",
>          [[
> -            SELECT 'abcxyz' NOT LIKE NULL
> +            SELECT 'abcxyz' NOT GLOB NULL
>          ]], {
>              -- <e_expr-17.2.6>
>              "null"
> @@ -2475,13 +2344,33 @@ if 0>0 then
>      test:do_execsql_test(
>          "e_expr-17.2.7",
>          [[
> -            SELECT NULL NOT LIKE 'ABC%'
> +            SELECT 'abcxyz' NOT LIKE NULL
>          ]], {
>              -- <e_expr-17.2.7>
>              "null"
>              -- </e_expr-17.2.7>
>          })
> +    test:do_execsql_test(
> +        "e_expr-17.2.8",
> +        [[
> +            SELECT NULL NOT GLOB 'abc*'
> +        ]], {
> +            -- <e_expr-17.2.8>
> +            "null"
> +            -- </e_expr-17.2.8>
> +        })
> +
> +    test:do_execsql_test(
> +        "e_expr-17.2.9",
> +        [[
> +            SELECT NULL NOT LIKE 'ABC%'
> +        ]], {
> +            -- <e_expr-17.2.9>
> +            "null"
> +            -- </e_expr-17.2.9>
> +        })
> +
>      db("nullvalue", "")
>  end
> diff --git a/test/sql-tap/gh-3251-string-pattern-comparison.test.lua 
> b/test/sql-tap/gh-3251-string-pattern-comparison.test.lua
> index 55943345f..a6d822ccd 100755
> --- a/test/sql-tap/gh-3251-string-pattern-comparison.test.lua
> +++ b/test/sql-tap/gh-3251-string-pattern-comparison.test.lua
> @@ -185,7 +185,7 @@ local valid_testcases = {
>  -- Valid testcases.
>  for i, tested_string in ipairs(valid_testcases) do
> -    test_name = prefix .. "8." .. tostring(i)
> +    local test_name = prefix .. "8." .. tostring(i)
>      local test_itself = "SELECT 'abc' LIKE 'ab" .. tested_string .. "';"
>      test:do_execsql_test(test_name, test_itself, {0})
> diff --git a/test/sql-tap/like3.test.lua b/test/sql-tap/like3.test.lua
> index 0bc71a09c..8f4f79422 100755
> --- a/test/sql-tap/like3.test.lua
> +++ b/test/sql-tap/like3.test.lua
> @@ -67,70 +67,6 @@ test:do_execsql_test(
>          -- </like3-1.2>
>      })
> -test:do_execsql_test(
> -    "like3-2.0",
> -    [[
> -        PRAGMA case_sensitive_like = 1;
> -        CREATE TABLE t2(a PRIMARY KEY, b TEXT);
> -        INSERT INTO t2 SELECT a, b FROM t1;
> -        CREATE INDEX t2ba ON t2(b,a);
> -        SELECT a, b FROM t2 WHERE b LIKE 'ab%' ORDER BY +a;
> -    ]], {
> -        -- <like3-2.0>
> -        1, "abc", 4, "abc"
> -        -- </like3-2.0>
> -    })
> -
> -test:do_execsql_test(
> -    "like3-2.1",
> -    [[
> -        SELECT a, b FROM t2 WHERE +b LIKE 'ab%' ORDER BY +a;
> -    ]], {
> -        -- <like3-2.1>
> -        1, "abc", 4, "abc"
> -        -- </like3-2.1>
> -    })
> -
> -test:do_execsql_test(
> -    "like3-2.2",
> -    [[
> -        SELECT a, b FROM t2 WHERE b>=x'6162' AND b LIKE 'ab%'
> -    ]], {
> -        -- <like3-2.2>
> -        4, "abc"
> -        -- </like3-2.2>
> -    })
> -
> -test:do_execsql_test(
> -    "like3-2.3",
> -    [[
> -        SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b LIKE 'ab%'
> -    ]], {
> -        -- <like3-2.3>
> -        4, "abc"
> -        -- </like3-2.3>
> -    })
> -
> -test:do_execsql_test(
> -    "like3-2.4",
> -    [[
> -        SELECT a, b FROM t2 WHERE b LIKE 'ab%' AND b>=x'6162'
> -    ]], {
> -        -- <like3-2.4>
> -        4, "abc"
> -        -- </like3-2.4>
> -    })
> -
> -test:do_execsql_test(
> -    "like3-2.5",
> -    [[
> -        SELECT a, b FROM t2 WHERE +b LIKE 'ab%' AND +b>=x'6162';
> -        PRAGMA case_sensitive_like = 0;
> -    ]], {
> -        -- <like3-2.5>
> -        4, "abc"
> -        -- </like3-2.5>
> -    })
>  test:execsql([[
>      CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE "unicode_ci");
>      INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
>
>
> --
> WBR, Nikita Tatunov.
> n.tatunov@tarantool.org <mailto:n.tatunov@tarantool.org>
>


[-- Attachment #2: Type: text/html, Size: 179721 bytes --]