[IGNITE-18166] SQL: LIKE predicate is not supported for BINARY data

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: calcite calcite2-required calcite3-required ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Andrey Khitrin
• Created: 16/Nov/22 05:09
• Updated: 05/Feb/24 15:15

Description

A 'T022' feature in SQL standard allows to use LIKE predicate for BINARY data. Unfortunately, it is not supported in AI3 beta1:

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'

Instead of returning data, all queries show the following error:

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