[tarantool-patches] Re: [PATCH 1/2] sql: derive collation for built-in functions
n.pettik
korablev at tarantool.org
Mon Feb 25 21:32:02 MSK 2019
> On 25 Feb 2019, at 15:58, Vladislav Shpilevoy <v.shpilevoy at tarantool.org> wrote:
> Hi! Thanks for the patch!
> On 21/02/2019 21:01, Nikita Pettik wrote:
>> Functions such as trim(), substr() etc should return result with
>> collation derived from their arguments. So, lets add flag indicating
>> that collation of first argument must be applied to function's result to
>> SQL function definition. Using this flag, we can derive appropriate
>> collation in sql_expr_coll().
>> Part of #3932
>> ---
>> src/box/sql/analyze.c | 6 +++---
>> src/box/sql/expr.c | 23 +++++++++++++++++++++++
>> src/box/sql/func.c | 22 +++++++++++-----------
>> src/box/sql/sqlInt.h | 31 +++++++++++++++++++++++--------
>> test/sql/collation.result | 28 ++++++++++++++++++++++++++++
>> test/sql/collation.test.lua | 11 +++++++++++
>> 6 files changed, 99 insertions(+), 22 deletions(-)
>> diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
>> index 2830ab639..5fb7285d8 100644
>> --- a/src/box/sql/sqlInt.h
>> +++ b/src/box/sql/sqlInt.h
>> @@ -1633,6 +1633,13 @@ struct FuncDef {
>> } u;
>> /* Return type. */
>> enum field_type ret_type;
>> + /**
>> + * If function returns string, it may require collation
>> + * to be applied on its result. For instance, result of
>> + * substr() built-in function must have the same collation
>> + * as its first argument.
>> + */
>> + bool is_coll_derived;
>> };
>
> This way works only for builtin functions taking not a
> bind parameter ('?’).
AFAIK, we can’t pass binding value with collation,
we just have no means to do things like this:
cn:execute('select trim(?)', { ‘ABCD’, collation = “unicode_ci" })
On the other hand, we can do this:
cn:execute('select trim(? COLLATE “unicode_ci")', { ‘ABCD’})
> For user-defined functions and for
> bind parameters it does not fit. How can you determine
> a function's result collation, if it is not builtin, and
> does not depend on arguments?
In no way. We can extend signature of sql_create_function
and allow to pass collation to be applied to returning value.
But I am not sure that we should do this. Anyway, it wouldn't
help us with the initial issue: in our case collation is dependent
on one of arguments, so it *dynamically* changes. Hence, I
guess these problems are barely related.
Also, inlining comment from P.Gulutzan:
(https://github.com/tarantool/tarantool/issues/3932)
‘’'
It is true that user-defined functions will not know some things about
what an SQL caller is passing. We don't promise that they will, so I
think it is okay that it is the caller's responsibility to make sure
relevant information is passed explicitly. A possible issue is that the
function cannot use the utf8 module for all possible collations, but
that is not an SQL issue.
‘''
> Does SQL standard allow to define user functions without
> a runtime defined collation? If SQL standard does not define
> SQL functions at all, then what other vendors do with that
> problem?
There’s no such opportunity in ANSI, if I’m not mistaking.
Generally speaking, other vendors have procedural SQL.
And since PSQL is a part of SQL, there are no such problems:
collation is a part of string-like types.
More information about the Tarantool-patches
mailing list