[tarantool-patches] Re: [PATCH 1/2] sql: remove "PRAGMA case_sensitive_like"
Roman Khabibov
roman.habibov at tarantool.org
Thu Jul 25 00:02:48 MSK 2019
> On Jul 17, 2019, at 19:18, n.pettik <korablev at tarantool.org> wrote:
>
>
>
>> On 14 Jul 2019, at 01:51, Roman Khabibov <roman.habibov at tarantool.org> wrote:
>>
>> According to ANSI, LIKE should match characters taking into
>> account passed collation, so this pragma is no longer needed.
>
> Can’t build this particular patch:
>
> /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 = (func->funcFlags & SQL_FUNC_CASE) == 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
>
> Please, fix compilation errors.
>
>> 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(
>> -- </pragma-3.2>
>> })
>>
>> --- 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 = box.execute('PRAGMA case_sensitive_like').rows
>> - box.execute('PRAGMA case_sensitive_like = 1')
>> - new_value = box.execute('PRAGMA case_sensitive_like').rows
>> - box.execute('PRAGMA case_sensitive_like = '.. old_value[1][1])
>> - return new_value[1][1]
>> - end,
>> + [[
>> + PRAGMA case_sensitive_like
>> + ]], {
>> -- <pragma-3.3>
>> - 1
>> + 1, "Pragma 'CASE_SENSITIVE_LIKE' does not exist"
>> -- </pragma-3.3>
>> - )
>> +})
>
> Sorry, this test looks weird. Just remove it.
Done.
commit ceed475fc0b497074ce122121a729fee85ad9adb
Author: Roman Khabibov <roman.habibov at tarantool.org>
Date: Tue Apr 16 15:51:55 2019 +0300
sql: remove "PRAGMA case_sensitive_like"
According to ANSI, LIKE should match characters taking into
account passed collation, so this pragma is no longer needed.
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);
}
-/*
- * 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 = sqlFindFunction(db, zName, 2, 0);
- if (ALWAYS(pDef)) {
- pDef->funcFlags |= flagVal;
- }
-}
-
-/**
- * Register the built-in LIKE function.
- */
-void
-sqlRegisterLikeFunctions(sql *db, int is_case_insensitive)
-{
- /*
- * FIXME: after introducing type <BOOLEAN> LIKE must
- * return that type: TRUE if the string matches the
- * supplied pattern and FALSE otherwise.
- */
- int *is_like_ci = 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 != TK_FUNCTION || !expr->x.pList ||
expr->x.pList->nExpr != 2)
@@ -1836,7 +1802,6 @@ sql_is_like_func(struct sql *db, struct Expr *expr, int *is_like_ci)
assert(func != NULL);
if ((func->funcFlags & SQL_FUNC_LIKE) == 0)
return 0;
- *is_like_ci = (func->funcFlags & SQL_FUNC_CASE) == 0;
return 1;
}
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 == 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[] = {
/* 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",
};
/* 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[] = {
- { /* 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[] = {
{ /* 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[] = {
{ /* 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 == 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);
-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);
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 = 0;
@@ -264,7 +262,7 @@ like_optimization_is_valid(Parse *pParse, Expr *pExpr, Expr **ppPrefix,
/* Result code to return. */
int rc;
- if (!sql_is_like_func(db, pExpr, pnoCase)) {
+ if (!sql_is_like_func(db, pExpr)) {
return 0;
}
pList = pExpr->x.pList;
@@ -1156,7 +1154,7 @@ exprAnalyze(SrcList * pSrc, /* the FROM clause */
*/
if (pWC->op == 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 = require("sqltester")
-test:plan(174)
+test:plan(172)
local prefix = "collation-"
@@ -483,7 +483,6 @@ local like_testcases =
{"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('İac');
@@ -491,35 +490,29 @@ local like_testcases =
]], {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<?) (~16384 rows)"}}},
+ {0, {0, 0, 0, "SEARCH TABLE TX1 USING PRIMARY KEY (S1>? AND S1<?) (~16384 rows)"}}},
{"2.2.0",
- "PRAGMA case_sensitive_like = true;",
- {0}},
- {"2.2.1",
"SELECT * FROM tx1 WHERE s1 LIKE 'A%' order by s1;",
{0, {"Aab"}} },
- {"2.2.2",
+ {"2.2.1",
"EXPLAIN QUERY PLAN SELECT * FROM tx1 WHERE s1 LIKE 'A%';",
{0, {0, 0, 0, "/USING PRIMARY KEY/"}} },
{"2.3.0",
- "PRAGMA case_sensitive_like = false;",
- {0}},
- {"2.3.1",
"SELECT * FROM tx1 WHERE s1 LIKE 'i%' order by s1;",
- {0, {"iad", "İac"}}},
- {"2.3.2",
+ {0, {"iad"}}},
+ {"2.3.1",
"SELECT * FROM tx1 WHERE s1 LIKE 'İ%'order by s1;",
- {0, {"iad", "İac"}} },
+ {0, {"İac"}} },
{"2.4.0",
[[
INSERT INTO tx1 VALUES('ЯЁЮ');
]], {0} },
{"2.4.1",
"SELECT * FROM tx1 WHERE s1 LIKE 'яёю';",
- {0, {"ЯЁЮ"}} },
+ {0, {}} },
}
test:do_catchsql_set_test(like_testcases, prefix)
diff --git a/test/sql-tap/like3.test.lua b/test/sql-tap/like3.test.lua
index 0eee25129..b5f52be52 100755
--- a/test/sql-tap/like3.test.lua
+++ b/test/sql-tap/like3.test.lua
@@ -76,7 +76,7 @@ test:do_execsql_test(
SELECT a, b FROM t2 WHERE b LIKE 'ab%' ORDER BY +a;
]], {
-- <like3-2.0>
- 1, "abc", 2, "ABX", 4, "abc", 5, "ABX"
+ 1, "abc", 4, "abc"
-- </like3-2.0>
})
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 = require("sqltester")
-test:plan(24)
+test:plan(23)
test:do_catchsql_test(
"pragma-1.3",
@@ -81,22 +81,6 @@ test:do_execsql_test(
-- </pragma-3.2>
})
--- 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 = box.execute('PRAGMA case_sensitive_like').rows
- box.execute('PRAGMA case_sensitive_like = 1')
- new_value = box.execute('PRAGMA case_sensitive_like').rows
- box.execute('PRAGMA case_sensitive_like = '.. old_value[1][1])
- return new_value[1][1]
- end,
- -- <pragma-3.3>
- 1
- -- </pragma-3.3>
- )
-
--
-- 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]
More information about the Tarantool-patches
mailing list