[tarantool-patches] Re: [PATCH 1/2] sql: derive collation for built-in functions

["n.pettik" <korablev@tarantool.org>]

> On 25 Feb 2019, at 15:58, Vladislav Shpilevoy <v.shpilevoy@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.