[tarantool-patches] Re: [PATCH 1/3] sql: remove SQL_LIKE_DOESNT_MATCH_BLOBS

[n.pettik]

> On 11 Mar 2019, at 11:07, Konstantin Osipov <kostja.osipov at gmail.com> wrote:
> 
> * Nikita Pettik <korablev at tarantool.org> [19/03/09 22:50]:
>> We are going to always throw an error if value of BLOB type gets to LIKE
>> arguments, so code under is macro is not needed anymore.
> 
> What's the decision about LIKE for MP_STR + collaction binary or
> no collation at all? Shouldn't we need this code as an optimized
> implementation of LIKE for binary?

Firstly, this optimisation has been turned off for quite a while.
I’m not sure that it can be enabled without significant patching.
Secondly, MP_STR + binary (or none) collation is still stored as
a string (MEM_Str in terms of VDBE), meanwhile deleted code
was operating on real blob values (MEM_Blob):

‘''

- * The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
- * expression: "x>='ABC' AND x<'abd'".  But this requires that the range
- * scan loop run twice, once for strings and a second time for BLOBs.
- * The OP_String opcodes on the second pass convert the upper and lower
- * bound string constants to blobs.  This routine makes the necessary changes
- * to the OP_String opcodes for that to happen.

‘’'

Note that now LIKE doesn’t use implicitly set collation:

tarantool> create table t1(id int primary key, a text collate "binary")
---
...

tarantool> insert into t1 values (1, 'ABC')
---
...

tarantool> select * from t1 where a like 'abc'
---
- - [1, 'ABC']
…

It is going to be fixed in https://github.com/tarantool/tarantool/issues/3589

> 
> -- 
> Konstantin Osipov, Moscow, Russia, +7 903 626 22 32
> http://tarantool.io - www.twitter.com/kostja_osipov