[jira] [Comment Edited] (IGNITE-3999) Support case insensitive search in SQL

["Vladimir Ozerov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-3999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16466975#comment-16466975 ] 

Vladimir Ozerov edited comment on IGNITE-3999 at 5/8/18 7:09 AM:
-----------------------------------------------------------------

[~vkulichenko], [~dpavlov], [~NIzhikov], [~aakhmedov], [~al.psc],
In general this is not correct approach and I would recommend to stop working on this ticket. Defining separate type to control case sensitivity is not industry-wide practice. Instead, problems like this are solved through the following techniques:
1) Functional indexes - we do not have them, but this would be very nice addition to the product
2) Specifying collations on per-database, per-session or per-statement levels

We do not consider H2 as reference implementation for us, and do not want to support all it's features. Instead, our ultimate goal is to drop H2 and implement new features using industrial experience of major vendors. 

In any case, my comments to the ticket:
1) Indentation changes should be reverted, looks like your IDE doing this automatically. Patch should contain only real code changes.

2) {{QueryEntity}} is saved to disk to metadata store. Please make sure that binary compatibility is preserved between previous version and master. It should be possible to start a persistent cache with {{QueryEntity}} on the previous version, put some data to is, and then restart with master - node should start successfully and data should be available.

3) Solution is incomplete. You applied case insensitivity only to SQL. But we have other access path - cache. And cache operates on case-sensitive {{java.lang.Strings}}. Consider the following SQL snippet:
{code}
CREATE my_table (code VARCHAR_INSENSITIVE PRIMARY KEY, ...)
INSERT INTO (code) VALUES ('Test');
{code}

This call will remove a row:
{code}
DELETE FROM my_table WHERE code='test';
{code}

But this will not work:
{code}
ignite.cache("myTable").remove(new Key("test")); // "test" != "Test"
{code}

We strive to maintain consistent behavior irrespective of access path, so this should be fixed. But please note that this might be a huge thing, because all collation features should be respected. Provided complexity and lack of serious demand from users, I doubt this feature worth implementation at all. 


was (Author: vozerov):
[~vkulichenko], [~dpavlov], [~NIzhikov], [~aakhmedov], [~al.psc],
In general this is not correct approach and I would recommend to stop working on this ticket. Defining separate type to control sensitivity is not industry-wide practice. Instead, problems like this are solved through the following techniques:
1) Functional indexes - we do not have them, but this would be very nice addition to the product
2) Specifying collations on per-database, per-session or per-statement levels

We do not consider H2 as reference implementation for us, and do not want to support all it's features. Instead, our ultimate goal is to drop H2 and implement new features using industrial experience of major vendors. 

In any case, my comments to the ticket:
1) Indentation changes should be reverted, looks like your IDE doing this automatically. Patch should contain only real code changes.

2) {{QueryEntity}} is saved to disk to metadata store. Please make sure that binary compatibility is preserved between previous version and master. It should be possible to start a persistent cache with {{QueryEntity}} on the previous version, put some data to is, and then restart with master - node should start successfully and data should be available.

3) Solution is incomplete. You applied case insensitivity only to SQL. But we have other access path - cache. And cache operates on case-sensitive {{java.lang.Strings}}. Consider the following SQL snippet:
{code}
CREATE my_table (code VARCHAR_INSENSITIVE PRIMARY KEY, ...)
INSERT INTO (code) VALUES ('Test');
{code}

This call will remove a row:
{code}
DELETE FROM my_table WHERE code='test';
{code}

But this will not work:
{code}
ignite.cache("myTable").remove(new Key("test")); // "test" != "Test"
{code}

We strive to maintain consistent behavior irrespective of access path, so this should be fixed. But please note that this might be a huge thing, because all collation features should be respected. Provided complexity and lack of serious demand from users, I doubt this feature worth implementation at all. 

> Support case insensitive search in SQL
> --------------------------------------
>
>                 Key: IGNITE-3999
>                 URL: https://issues.apache.org/jira/browse/IGNITE-3999
>             Project: Ignite
>          Issue Type: Improvement
>          Components: sql
>    Affects Versions: 1.7
>            Reporter: Valentin Kulichenko
>            Assignee: Amir Akhmedov
>            Priority: Critical
>              Labels: sql-engine
>             Fix For: 2.6
>
>
> Currently case insensitive search is possible only with the help of {{lower()}} function:
> {code}
> select name from MyValue where lower(name) = 'abc_5'
> {code}
> But this will always be a full scan, even if {{name}} field is indexed.
> We need to correctly support {{VARCHAR_IGNORECASE}} H2 type in Ignite and add a respective property to {{@QuerySqlField}} annotation.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)