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 987BC25251 for ; Wed, 24 Jul 2019 17:02:50 -0400 (EDT) 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 IVunoE2FYi5S for ; Wed, 24 Jul 2019 17:02:50 -0400 (EDT) Received: from smtp58.i.mail.ru (smtp58.i.mail.ru [217.69.128.38]) (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 2A2472521F for ; Wed, 24 Jul 2019 17:02:50 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\)) Subject: [tarantool-patches] Re: [PATCH 1/2] sql: remove "PRAGMA case_sensitive_like" From: Roman Khabibov In-Reply-To: <7BB8A4C9-226B-46F8-B330-12335CFE974F@tarantool.org> Date: Thu, 25 Jul 2019 00:02:48 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <1549C0DA-B805-4608-8C61-4E9E6A55EC01@tarantool.org> References: <1f34c8f1038ae55865745d041701c2193039c258.1563057282.git.roman.habibov@tarantool.org> <7BB8A4C9-226B-46F8-B330-12335CFE974F@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: "n. pettik" > On Jul 17, 2019, at 19:18, n.pettik wrote: >=20 >=20 >=20 >> On 14 Jul 2019, at 01:51, Roman Khabibov = wrote: >>=20 >> According to ANSI, LIKE should match characters taking into >> account passed collation, so this pragma is no longer needed. >=20 > Can=E2=80=99t build this particular patch: >=20 > /Users/n.pettik/tarantool/src/box/sql/func.c:1795:1: error: = conflicting types for 'sql_is_like_func' > sql_is_like_func(struct sql *db, struct Expr *expr, int *is_like_ci) > ^ > /Users/n.pettik/tarantool/src/box/sql/sqlInt.h:4277:1: note: previous = declaration is here > sql_is_like_func(struct sql *db, struct Expr *expr); > ^ > /Users/n.pettik/tarantool/src/box/sql/func.c:1805:35: error: use of = undeclared identifier 'SQL_FUNC_CASE' > *is_like_ci =3D (func->funcFlags & SQL_FUNC_CASE) =3D=3D 0; > ^ > 2 errors generated. > make[2]: *** [src/box/sql/CMakeFiles/sql.dir/func.c.o] Error 1 > make[2]: *** Waiting for unfinished jobs.... > make[1]: *** [src/box/sql/CMakeFiles/sql.dir/all] Error 2 > make: *** [all] Error 2 >=20 > Please, fix compilation errors. >=20 >> diff --git a/test/sql-tap/pragma.test.lua = b/test/sql-tap/pragma.test.lua >> index 8221d36e6..d8fb550d0 100755 >> --- a/test/sql-tap/pragma.test.lua >> +++ b/test/sql-tap/pragma.test.lua >> @@ -81,21 +81,16 @@ test:do_execsql_test( >> -- >> }) >>=20 >> --- Check that "PRAGMA case_sensitive_like" returns its status >> --- (0 or 1) if called without parameter. >> -test:do_test( >> +-- Check that "PRAGMA case_sensitive_like" does not exist. >> +test:do_catchsql_test( >> "pragma-3.3", >> - function() >> - old_value =3D box.execute('PRAGMA = case_sensitive_like').rows >> - box.execute('PRAGMA case_sensitive_like =3D 1') >> - new_value =3D box.execute('PRAGMA = case_sensitive_like').rows >> - box.execute('PRAGMA case_sensitive_like =3D '.. = old_value[1][1]) >> - return new_value[1][1] >> - end, >> + [[ >> + PRAGMA case_sensitive_like >> + ]], { >> -- >> - 1 >> + 1, "Pragma 'CASE_SENSITIVE_LIKE' does not exist" >> -- >> - ) >> +}) >=20 > Sorry, this test looks weird. Just remove it. Done. commit ceed475fc0b497074ce122121a729fee85ad9adb Author: Roman Khabibov Date: Tue Apr 16 15:51:55 2019 +0300 sql: remove "PRAGMA case_sensitive_like" =20 According to ANSI, LIKE should match characters taking into account passed collation, so this pragma is no longer needed. =20 Part of #3589 diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 98cad51fd..4ac11cb77 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1791,42 +1791,8 @@ sqlRegisterPerConnectionBuiltinFunctions(sql * = db) sqlOomFault(db); } =20 -/* - * Set the LIKEOPT flag on the 2-argument function with the given name. - */ -static void -setLikeOptFlag(sql * db, const char *zName, u8 flagVal) -{ - FuncDef *pDef; - pDef =3D sqlFindFunction(db, zName, 2, 0); - if (ALWAYS(pDef)) { - pDef->funcFlags |=3D flagVal; - } -} - -/** - * Register the built-in LIKE function. - */ -void -sqlRegisterLikeFunctions(sql *db, int is_case_insensitive) -{ - /* - * FIXME: after introducing type LIKE must - * return that type: TRUE if the string matches the - * supplied pattern and FALSE otherwise. - */ - int *is_like_ci =3D SQL_INT_TO_PTR(is_case_insensitive); - sqlCreateFunc(db, "LIKE", FIELD_TYPE_BOOLEAN, 2, 0, - is_like_ci, likeFunc, 0, 0, 0); - sqlCreateFunc(db, "LIKE", FIELD_TYPE_BOOLEAN, 3, 0, - is_like_ci, likeFunc, 0, 0, 0); - setLikeOptFlag(db, "LIKE", - !(is_case_insensitive) ? (SQL_FUNC_LIKE | - SQL_FUNC_CASE) : SQL_FUNC_LIKE); -} - int -sql_is_like_func(struct sql *db, struct Expr *expr, int *is_like_ci) +sql_is_like_func(struct sql *db, struct Expr *expr) { if (expr->op !=3D TK_FUNCTION || !expr->x.pList || expr->x.pList->nExpr !=3D 2) @@ -1836,7 +1802,6 @@ sql_is_like_func(struct sql *db, struct Expr = *expr, int *is_like_ci) assert(func !=3D NULL); if ((func->funcFlags & SQL_FUNC_LIKE) =3D=3D 0) return 0; - *is_like_ci =3D (func->funcFlags & SQL_FUNC_CASE) =3D=3D 0; return 1; } =20 diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c index 53524b617..293c2cd2e 100644 --- a/src/box/sql/pragma.c +++ b/src/box/sql/pragma.c @@ -484,13 +484,6 @@ sqlPragma(Parse * pParse, Token * pId, /* First = part of [schema.]id field */ sqlParserTrace(0, 0); } #endif - /* - * Reinstall the LIKE and functions. The - * variant of LIKE * used will be case - * sensitive or not depending on the RHS. - */ - if (mask =3D=3D LIKE_CASE_SENS_FLAG) - sqlRegisterLikeFunctions(db, = !is_pragma_set); } break; } diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h index aa7e7cd96..02895b0ea 100644 --- a/src/box/sql/pragma.h +++ b/src/box/sql/pragma.h @@ -93,60 +93,57 @@ static const char *const pragCName[] =3D { /* 55 */ "TEXT", /* 56 */ "match", /* 57 */ "TEXT", - /* Used by: case_sensitive_like */ - /* 58 */ "case_sensitive_like", - /* 59 */ "INTEGER", /* Used by: count_changes */ - /* 60 */ "count_changes", - /* 61 */ "INTEGER", + /* 58 */ "count_changes", + /* 59 */ "INTEGER", /* Used by: defer_foreign_keys */ - /* 62 */ "defer_foreign_keys", - /* 63 */ "INTEGER", + /* 60 */ "defer_foreign_keys", + /* 61 */ "INTEGER", /* Used by: full_column_names */ - /* 64 */ "full_column_names", - /* 65 */ "INTEGER", + /* 62 */ "full_column_names", + /* 63 */ "INTEGER", /* Used by: parser_trace */ - /* 66 */ "parser_trace", - /* 67 */ "INTEGER", + /* 64 */ "parser_trace", + /* 65 */ "INTEGER", /* Used by: recursive_triggers */ - /* 68 */ "recursive_triggers", - /* 69 */ "INTEGER", + /* 66 */ "recursive_triggers", + /* 67 */ "INTEGER", /* Used by: reverse_unordered_selects */ - /* 70 */ "reverse_unordered_selects", - /* 71 */ "INTEGER", + /* 68 */ "reverse_unordered_selects", + /* 69 */ "INTEGER", /* Used by: select_trace */ - /* 72 */ "select_trace", - /* 73 */ "INTEGER", + /* 70 */ "select_trace", + /* 71 */ "INTEGER", /* Used by: short_column_names */ - /* 74 */ "short_column_names", - /* 75 */ "INTEGER", + /* 72 */ "short_column_names", + /* 73 */ "INTEGER", /* Used by: sql_compound_select_limit */ - /* 76 */ "sql_compound_select_limit", - /* 77 */ "INTEGER", + /* 74 */ "sql_compound_select_limit", + /* 75 */ "INTEGER", /* Used by: sql_default_engine */ - /* 78 */ "sql_default_engine", - /* 79 */ "TEXT", + /* 76 */ "sql_default_engine", + /* 77 */ "TEXT", /* Used by: sql_trace */ - /* 80 */ "sql_trace", - /* 81 */ "INTEGER", + /* 78 */ "sql_trace", + /* 79 */ "INTEGER", /* Used by: vdbe_addoptrace */ - /* 82 */ "vdbe_addoptrace", - /* 83 */ "INTEGER", + /* 80 */ "vdbe_addoptrace", + /* 81 */ "INTEGER", /* Used by: vdbe_debug */ - /* 84 */ "vdbe_debug", - /* 85 */ "INTEGER", + /* 82 */ "vdbe_debug", + /* 83 */ "INTEGER", /* Used by: vdbe_eqp */ - /* 86 */ "vdbe_eqp", - /* 87 */ "INTEGER", + /* 84 */ "vdbe_eqp", + /* 85 */ "INTEGER", /* Used by: vdbe_listing */ - /* 88 */ "vdbe_listing", - /* 89 */ "INTEGER", + /* 86 */ "vdbe_listing", + /* 87 */ "INTEGER", /* Used by: vdbe_trace */ - /* 90 */ "vdbe_trace", - /* 91 */ "INTEGER", + /* 88 */ "vdbe_trace", + /* 89 */ "INTEGER", /* Used by: where_trace */ - /* 92 */ "where_trace", - /* 93 */ "INTEGER", + /* 90 */ "where_trace", + /* 91 */ "INTEGER", }; =20 /* Definitions of all built-in pragmas */ @@ -163,11 +160,6 @@ typedef struct PragmaName { * to be sorted. For more info see pragma_locate function. */ static const PragmaName aPragmaName[] =3D { - { /* zName: */ "case_sensitive_like", - /* ePragTyp: */ PragTyp_FLAG, - /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 58, 1, - /* iArg: */ LIKE_CASE_SENS_FLAG}, { /* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, @@ -210,46 +202,46 @@ static const PragmaName aPragmaName[] =3D { { /* zName: */ "parser_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 66, 1, + /* ColNames: */ 64, 1, /* iArg: */ PARSER_TRACE_FLAG}, #endif { /* zName: */ "recursive_triggers", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 68, 1, + /* ColNames: */ 66, 1, /* iArg: */ SQL_RecTriggers}, { /* zName: */ "reverse_unordered_selects", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 70, 1, + /* ColNames: */ 68, 1, /* iArg: */ SQL_ReverseOrder}, #if defined(SQL_DEBUG) { /* zName: */ "select_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 72, 1, + /* ColNames: */ 70, 1, /* iArg: */ SQL_SelectTrace}, #endif { /* zName: */ "short_column_names", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 73, 1, + /* ColNames: */ 72, 1, /* iArg: */ SQL_ShortColNames}, { /* zName: */ "sql_compound_select_limit", /* ePragTyp: */ PragTyp_COMPOUND_SELECT_LIMIT, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 76, 1, + /* ColNames: */ 74, 1, /* iArg: */ 0}, { /* zName: */ "sql_default_engine", /* ePragTyp: */ PragTyp_DEFAULT_ENGINE, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 78, 1, + /* ColNames: */ 76, 1, /* iArg: */ 0}, #if defined(SQL_DEBUG) { /* zName: */ "sql_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 80, 1, + /* ColNames: */ 78, 1, /* iArg: */ SQL_SqlTrace}, #endif { /* zName: */ "stats", @@ -268,33 +260,33 @@ static const PragmaName aPragmaName[] =3D { { /* zName: */ "vdbe_addoptrace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 82, 1, + /* ColNames: */ 80, 1, /* iArg: */ SQL_VdbeAddopTrace}, { /* zName: */ "vdbe_debug", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 84, 1, + /* ColNames: */ 82, 1, /* iArg: */ SQL_SqlTrace | SQL_VdbeListing | SQL_VdbeTrace}, { /* zName: */ "vdbe_eqp", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 86, 1, + /* ColNames: */ 84, 1, /* iArg: */ SQL_VdbeEQP}, { /* zName: */ "vdbe_listing", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 88, 1, + /* ColNames: */ 86, 1, /* iArg: */ SQL_VdbeListing}, { /* zName: */ "vdbe_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 90, 1, + /* ColNames: */ 88, 1, /* iArg: */ SQL_VdbeTrace}, { /* zName: */ "where_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1, - /* ColNames: */ 92, 1, + /* ColNames: */ 90, 1, /* iArg: */ SQL_WhereTrace}, #endif }; diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 73dc6e4d7..57da8ec5d 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -1143,8 +1143,6 @@ struct sql { /* Debug print info about SQL query as it parsed */ #define PARSER_TRACE_FLAG 0x00000002 #define SQL_FullColNames 0x00000004 /* Show full column names on = SELECT */ -/* True if LIKE is case sensitive. */ -#define LIKE_CASE_SENS_FLAG 0x00000008 #define SQL_ShortColNames 0x00000040 /* Show short columns names */ #define SQL_CountRows 0x00000080 /* Count rows changed by INSERT, = */ /* DELETE, or UPDATE and = return */ @@ -1271,7 +1269,6 @@ struct FuncDestructor { * SQL_FUNC_CONSTANT =3D=3D sql_DETERMINISTIC from the API */ #define SQL_FUNC_LIKE 0x0004 /* Candidate for the LIKE = optimization */ -#define SQL_FUNC_CASE 0x0008 /* Case-sensitive LIKE-type = function */ #define SQL_FUNC_EPHEM 0x0010 /* Ephemeral. Delete with VDBE = */ #define SQL_FUNC_NEEDCOLL 0x0020 /* sqlGetFuncCollSeq() might be = called. * The flag is set when the = collation @@ -4266,8 +4263,6 @@ sql_key_info_unref(struct sql_key_info *key_info); struct key_def * sql_key_info_to_key_def(struct sql_key_info *key_info); =20 -void sqlRegisterLikeFunctions(sql *, int); - /** * Check if the function implements LIKE-style comparison & if it * is appropriate to apply a LIKE query optimization. @@ -4279,7 +4274,7 @@ void sqlRegisterLikeFunctions(sql *, int); * @retval 1 if LIKE optimization can be used, 0 otherwise. */ int -sql_is_like_func(struct sql *db, struct Expr *expr, int *is_like_ci); +sql_is_like_func(struct sql *db, struct Expr *expr); =20 int sqlCreateFunc(sql *, const char *, enum field_type, int, int, void *, diff --git a/src/box/sql/whereexpr.c b/src/box/sql/whereexpr.c index a88f96407..0a0e900bd 100644 --- a/src/box/sql/whereexpr.c +++ b/src/box/sql/whereexpr.c @@ -237,14 +237,12 @@ operatorMask(int op) * pattern prefix. * @param pisComplete True if the only wildcard is '%' in the * last character. - * @param pnoCase True if case insensitive. - * * @retval True if the given expr is a LIKE operator & is * optimizable using inequality constraints. */ static int like_optimization_is_valid(Parse *pParse, Expr *pExpr, Expr **ppPrefix, - int *pisComplete, int *pnoCase) + int *pisComplete) { /* String on RHS of LIKE operator. */ const char *z =3D 0; @@ -264,7 +262,7 @@ like_optimization_is_valid(Parse *pParse, Expr = *pExpr, Expr **ppPrefix, /* Result code to return. */ int rc; =20 - if (!sql_is_like_func(db, pExpr, pnoCase)) { + if (!sql_is_like_func(db, pExpr)) { return 0; } pList =3D pExpr->x.pList; @@ -1156,7 +1154,7 @@ exprAnalyze(SrcList * pSrc, /* the FROM = clause */ */ if (pWC->op =3D=3D TK_AND && like_optimization_is_valid(pParse, pExpr, &pStr1, - &isComplete, &noCase)) { + &isComplete)) { Expr *pLeft; /* Copy of pStr1 - RHS of LIKE operator. */ Expr *pStr2; diff --git a/test/sql-tap/collation.test.lua = b/test/sql-tap/collation.test.lua index 79547361c..e1df8750b 100755 --- a/test/sql-tap/collation.test.lua +++ b/test/sql-tap/collation.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test =3D require("sqltester") -test:plan(174) +test:plan(172) =20 local prefix =3D "collation-" =20 @@ -483,7 +483,6 @@ local like_testcases =3D {"2.0", [[ CREATE TABLE tx1 (s1 VARCHAR(5) PRIMARY KEY); - CREATE INDEX I1 on tx1(s1 collate "unicode_ci"); INSERT INTO tx1 VALUES('aaa'); INSERT INTO tx1 VALUES('Aab'); INSERT INTO tx1 VALUES('=C4=B0ac'); @@ -491,35 +490,29 @@ local like_testcases =3D ]], {0}}, {"2.1.1", "SELECT * FROM tx1 WHERE s1 LIKE 'A%' order by s1;", - {0, {"Aab", "aaa"}} }, + {0, {"Aab"}} }, {"2.1.2", "EXPLAIN QUERY PLAN SELECT * FROM tx1 WHERE s1 LIKE 'A%';", - {0, {0, 0, 0, "SEARCH TABLE TX1 USING COVERING INDEX I1 (S1>? = AND S1? AND = S1 - 1, "abc", 2, "ABX", 4, "abc", 5, "ABX" + 1, "abc", 4, "abc" -- }) test:do_execsql_test( diff --git a/test/sql-tap/pragma.test.lua b/test/sql-tap/pragma.test.lua index 8221d36e6..a89353aea 100755 --- a/test/sql-tap/pragma.test.lua +++ b/test/sql-tap/pragma.test.lua @@ -1,7 +1,7 @@ #!/usr/bin/env tarantool test =3D require("sqltester") =20 -test:plan(24) +test:plan(23) =20 test:do_catchsql_test( "pragma-1.3", @@ -81,22 +81,6 @@ test:do_execsql_test( -- }) =20 --- Check that "PRAGMA case_sensitive_like" returns its status --- (0 or 1) if called without parameter. -test:do_test( - "pragma-3.3", - function() - old_value =3D box.execute('PRAGMA = case_sensitive_like').rows - box.execute('PRAGMA case_sensitive_like =3D 1') - new_value =3D box.execute('PRAGMA = case_sensitive_like').rows - box.execute('PRAGMA case_sensitive_like =3D '.. = old_value[1][1]) - return new_value[1][1] - end, - -- - 1 - -- - ) - -- -- gh-3733: remove useless or obsolete pragmas -- diff --git a/test/sql/sql-debug.result b/test/sql/sql-debug.result index 32c65cc81..2dba6844a 100644 --- a/test/sql/sql-debug.result +++ b/test/sql/sql-debug.result @@ -38,7 +38,6 @@ box.execute('PRAGMA') - name: pragma_value type: INTEGER rows: - - ['case_sensitive_like', 0] - ['count_changes', 0] - ['defer_foreign_keys', 0] - ['full_column_names', 0]