[
https://issues.apache.org/jira/browse/IGNITE-18166?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Dmitrienko updated IGNITE-18166:
-----------------------------------------
Description:
A 'T022' feature in SQL standard allows to use LIKE predicate for BINARY and
VARBINARY data. Unfortunately, it is not supported in AI3 beta1:
{code:sql}
create table tmp_table_binary (key_field INT PRIMARY KEY,field1 VARBINARY);
INSERT INTO tmp_table_binary (key_field, field1) values(1, x'00ffaa');
INSERT INTO tmp_table_binary (key_field, field1) values(2, x'ffcc');
select * from tmp_table_binary where field1 like '%ff%'; --
expected to return both rows
select * from tmp_table_binary where field1 like '_f%'; --
expected to return 'ffcc'
select * from tmp_table_binary where field1 like '_fc_'; --
expected to return 'ffcc'
{code}
Instead of returning data, all queries show the following error:
{code:java}
Error: Exception while executing query [query=select * from tmp_table_binary
where field1 like '%ff%']. Error message:From line 1, column 38 to line 1,
column 55: Cannot apply 'LIKE' to arguments of type '<VARBINARY(65536)>,
<CHAR(4)>'. Supported form(s): 'LIKE(<STRING>, <STRING>, <STRING>)'
(state=50000,code=0)
java.sql.SQLException: Exception while executing query [query=select * from
tmp_table_binary where field1 like '%ff%']. Error message:From line 1, column
38 to line 1, column 55: Cannot apply 'LIKE' to arguments of type
'<VARBINARY(65536)>, <CHAR(4)>)'. Supported form(s): 'LIKE(<STRING>, <STRING>,
<STRING>)'
at
org.apache.ignite.internal.jdbc.proto.IgniteQueryErrorCode.createJdbcSqlException(IgniteQueryErrorCode.java:57)
at
org.apache.ignite.internal.jdbc.JdbcStatement.execute0(JdbcStatement.java:148)
at
org.apache.ignite.internal.jdbc.JdbcStatement.execute(JdbcStatement.java:341)
...
{code}
was:
A 'T022' feature in SQL standard allows to use LIKE predicate for BINARY and
VARBINARY data. Unfortunately, it is not supported in AI3 beta1:
{code:sql}
create table tmp_table_binary (key_field INT PRIMARY KEY,field1 BINARY);
INSERT INTO tmp_table_binary (key_field, field1) values(1, x'00ffaa');
INSERT INTO tmp_table_binary (key_field, field1) values(2, x'ffcc');
select * from tmp_table_binary where field1 like '%ff%'; --
expected to return both rows
select * from tmp_table_binary where field1 like '_f%'; --
expected to return 'ffcc'
select * from tmp_table_binary where field1 like '_fc_'; --
expected to return 'ffcc'
{code}
Instead of returning data, all queries show the following error:
{code:java}
Error: Exception while executing query [query=select * from tmp_table_binary
where field1 like '%ff%']. Error message:From line 1, column 38 to line 1,
column 55: Cannot apply 'LIKE' to arguments of type 'LIKE(<BINARY(1)>,
<CHAR(4)>)'. Supported form(s): 'LIKE(<STRING>, <STRING>, <STRING>)'
(state=50000,code=0)
java.sql.SQLException: Exception while executing query [query=select * from
tmp_table_binary where field1 like '%ff%']. Error message:From line 1, column
38 to line 1, column 55: Cannot apply 'LIKE' to arguments of type
'LIKE(<BINARY(1)>, <CHAR(4)>)'. Supported form(s): 'LIKE(<STRING>, <STRING>,
<STRING>)'
at
org.apache.ignite.internal.jdbc.proto.IgniteQueryErrorCode.createJdbcSqlException(IgniteQueryErrorCode.java:57)
at
org.apache.ignite.internal.jdbc.JdbcStatement.execute0(JdbcStatement.java:148)
at
org.apache.ignite.internal.jdbc.JdbcStatement.execute(JdbcStatement.java:341)
...
{code}
> SQL: LIKE predicate is not supported for VARBINARY data
> -------------------------------------------------------
>
> Key: IGNITE-18166
> URL: https://issues.apache.org/jira/browse/IGNITE-18166
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0, 3.0.0-beta1
> Reporter: Andrey Khitrin
> Priority: Major
> Labels: calcite, calcite2-required, calcite3-required, ignite-3
>
> A 'T022' feature in SQL standard allows to use LIKE predicate for BINARY and
> VARBINARY data. Unfortunately, it is not supported in AI3 beta1:
> {code:sql}
> create table tmp_table_binary (key_field INT PRIMARY KEY,field1 VARBINARY);
> INSERT INTO tmp_table_binary (key_field, field1) values(1, x'00ffaa');
> INSERT INTO tmp_table_binary (key_field, field1) values(2, x'ffcc');
> select * from tmp_table_binary where field1 like '%ff%'; --
> expected to return both rows
> select * from tmp_table_binary where field1 like '_f%'; --
> expected to return 'ffcc'
> select * from tmp_table_binary where field1 like '_fc_'; --
> expected to return 'ffcc'
> {code}
> Instead of returning data, all queries show the following error:
> {code:java}
> Error: Exception while executing query [query=select * from tmp_table_binary
> where field1 like '%ff%']. Error message:From line 1, column 38 to line 1,
> column 55: Cannot apply 'LIKE' to arguments of type '<VARBINARY(65536)>,
> <CHAR(4)>'. Supported form(s): 'LIKE(<STRING>, <STRING>, <STRING>)'
> (state=50000,code=0)
> java.sql.SQLException: Exception while executing query [query=select * from
> tmp_table_binary where field1 like '%ff%']. Error message:From line 1, column
> 38 to line 1, column 55: Cannot apply 'LIKE' to arguments of type
> '<VARBINARY(65536)>, <CHAR(4)>)'. Supported form(s): 'LIKE(<STRING>,
> <STRING>, <STRING>)'
> at
> org.apache.ignite.internal.jdbc.proto.IgniteQueryErrorCode.createJdbcSqlException(IgniteQueryErrorCode.java:57)
> at
> org.apache.ignite.internal.jdbc.JdbcStatement.execute0(JdbcStatement.java:148)
> at
> org.apache.ignite.internal.jdbc.JdbcStatement.execute(JdbcStatement.java:341)
> ...
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
