[
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)
