This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new 7b71443 IGNITE-16113 Support for BINARY/VARBINARY data types - Fixes
#9655.
7b71443 is described below
commit 7b71443172699f3c72eab649ff660dc00b045f00
Author: Aleksey Plekhanov <[email protected]>
AuthorDate: Mon Dec 20 09:59:40 2021 +0300
IGNITE-16113 Support for BINARY/VARBINARY data types - Fixes #9655.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
.../calcite/exec/exp/IgniteBuiltInMethod.java | 9 +-
.../query/calcite/exec/exp/IgniteSqlFunctions.java | 12 +++
.../query/calcite/exec/exp/RexImpTable.java | 2 +
.../query/calcite/exec/exp/RexToLixTranslator.java | 14 +++
.../query/calcite/exec/exp/agg/Accumulators.java | 74 ++++++++++++++
.../query/calcite/externalize/RelJson.java | 18 ++++
.../calcite/sql/fun/IgniteStdSqlOperatorTable.java | 1 +
.../processors/query/calcite/util/TypeUtils.java | 8 +-
.../processors/query/calcite/DataTypesTest.java | 104 ++++++++++++++++++++
.../query/calcite/StdSqlOperatorsTest.java | 4 +-
.../calcite/exec/exp/IgniteSqlFunctionsTest.java | 2 +-
.../query/calcite/logical/SqlScriptRunner.java | 24 +++--
.../calcite/src/test/sql/types/blob/test_blob.test | 108 +++++++++++++++++++++
.../src/test/sql/types/blob/test_blob.test_ignore | 76 ++++++++-------
...t_blob_cast.test_ignore => test_blob_cast.test} | 39 ++++----
.../test/sql/types/blob/test_blob_function.test | 84 ++++++++++++++++
.../sql/types/blob/test_blob_function.test_ignore | 85 ----------------
.../test/sql/types/blob/test_blob_operator.test | 92 ++++++++++++++++++
.../sql/types/blob/test_blob_operator.test_ignore | 38 ++++----
...ob_string.test_ignore => test_blob_string.test} | 9 +-
20 files changed, 630 insertions(+), 173 deletions(-)
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
index 13fe07b..5187386 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteBuiltInMethod.java
@@ -18,6 +18,7 @@ package
org.apache.ignite.internal.processors.query.calcite.exec.exp;
import java.lang.reflect.Method;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.linq4j.tree.Types;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.parser.SqlParserUtil;
@@ -36,7 +37,13 @@ public enum IgniteBuiltInMethod {
PARSE_INTERVAL_YEAR_MONTH(SqlParserUtil.class, "intervalToMonths",
String.class, SqlIntervalQualifier.class),
/** */
- PARSE_INTERVAL_DAY_TIME(SqlParserUtil.class, "intervalToMillis",
String.class, SqlIntervalQualifier.class);
+ PARSE_INTERVAL_DAY_TIME(SqlParserUtil.class, "intervalToMillis",
String.class, SqlIntervalQualifier.class),
+
+ /** */
+ BYTESTRING_TO_STRING(IgniteSqlFunctions.class, "toString",
ByteString.class),
+
+ /** */
+ STRING_TO_BYTESTRING(IgniteSqlFunctions.class, "toByteString",
String.class);
/** */
public final Method method;
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
index f7c4b8b..4238dec 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
@@ -20,6 +20,7 @@ import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import org.apache.calcite.DataContext;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.linq4j.AbstractEnumerable;
import org.apache.calcite.linq4j.Enumerable;
@@ -34,6 +35,7 @@ import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.type.SqlTypeName;
import
org.apache.ignite.internal.processors.query.calcite.type.IgniteTypeSystem;
+import org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.checkerframework.checker.nullness.qual.Nullable;
/**
@@ -142,6 +144,16 @@ public class IgniteSqlFunctions {
: toBigDecimal(o.toString(), precision, scale);
}
+ /** CAST(VARCHAR AS VARBINARY). */
+ public static ByteString toByteString(String s) {
+ return s == null ? null : new
ByteString(s.getBytes(Commons.typeFactory().getDefaultCharset()));
+ }
+
+ /** CAST(VARBINARY AS VARCHAR). */
+ public static String toString(ByteString b) {
+ return b == null ? null : new String(b.getBytes(),
Commons.typeFactory().getDefaultCharset());
+ }
+
/** */
private static class RangeTable implements ScannableTable {
/** Start of the range. */
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
index ca9da6b..9b8a77d 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexImpTable.java
@@ -201,6 +201,7 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_EQUALS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_LIKE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_SIMILAR_TO;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OCTET_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OR;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OVERLAY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PI;
@@ -269,6 +270,7 @@ public class RexImpTable {
defineMethod(TRANSLATE3, BuiltInMethod.TRANSLATE3.method,
NullPolicy.STRICT);
defineMethod(CHR, "chr", NullPolicy.STRICT);
defineMethod(CHAR_LENGTH, BuiltInMethod.CHAR_LENGTH.method,
NullPolicy.STRICT);
+ defineMethod(OCTET_LENGTH, BuiltInMethod.OCTET_LENGTH.method,
NullPolicy.STRICT);
defineMethod(CONCAT, BuiltInMethod.STRING_CONCAT.method,
NullPolicy.STRICT);
defineMethod(CONCAT_FUNCTION,
BuiltInMethod.MULTI_STRING_CONCAT.method, NullPolicy.STRICT);
defineMethod(OVERLAY, BuiltInMethod.OVERLAY.method, NullPolicy.STRICT);
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
index 7439e34..9962507 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/RexToLixTranslator.java
@@ -502,6 +502,12 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
BuiltInMethod.BOOLEAN_TO_STRING.method,
operand));
break;
+ case BINARY:
+ case VARBINARY:
+ convert = RexImpTable.optimize2(
+ operand,
+
Expressions.call(IgniteBuiltInMethod.BYTESTRING_TO_STRING.method, operand));
+ break;
}
break;
case INTERVAL_YEAR:
@@ -537,6 +543,14 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
)
);
}
+ break;
+ case BINARY:
+ case VARBINARY:
+ switch (sourceType.getSqlTypeName().getFamily()) {
+ case CHARACTER:
+ convert =
Expressions.call(IgniteBuiltInMethod.STRING_TO_BYTESTRING.method, operand);
+ }
+ break;
}
if (targetType.getSqlTypeName() == SqlTypeName.DECIMAL)
convert = ConverterUtils.convertToDecimal(operand, targetType);
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/agg/Accumulators.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/agg/Accumulators.java
index 8ee774c..5f698a5 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/agg/Accumulators.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/agg/Accumulators.java
@@ -24,6 +24,7 @@ import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.function.Supplier;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.type.RelDataType;
import
org.apache.ignite.internal.processors.query.calcite.type.IgniteTypeFactory;
@@ -34,6 +35,7 @@ import static org.apache.calcite.sql.type.SqlTypeName.BIGINT;
import static org.apache.calcite.sql.type.SqlTypeName.DECIMAL;
import static org.apache.calcite.sql.type.SqlTypeName.DOUBLE;
import static org.apache.calcite.sql.type.SqlTypeName.INTEGER;
+import static org.apache.calcite.sql.type.SqlTypeName.VARBINARY;
import static org.apache.calcite.sql.type.SqlTypeName.VARCHAR;
/**
@@ -143,6 +145,9 @@ public class Accumulators {
case CHAR:
case VARCHAR:
return VarCharMinMax.MIN_FACTORY;
+ case BINARY:
+ case VARBINARY:
+ return VarBinaryMinMax.MIN_FACTORY;
case BIGINT:
default:
return LongMinMax.MIN_FACTORY;
@@ -163,6 +168,9 @@ public class Accumulators {
case CHAR:
case VARCHAR:
return VarCharMinMax.MAX_FACTORY;
+ case BINARY:
+ case VARBINARY:
+ return VarBinaryMinMax.MAX_FACTORY;
case BIGINT:
default:
return LongMinMax.MAX_FACTORY;
@@ -865,6 +873,72 @@ public class Accumulators {
}
/** */
+ private static class VarBinaryMinMax implements Accumulator {
+ /** */
+ public static final Supplier<Accumulator> MIN_FACTORY = () -> new
VarBinaryMinMax(true);
+
+ /** */
+ public static final Supplier<Accumulator> MAX_FACTORY = () -> new
VarBinaryMinMax(false);
+
+ /** */
+ private final boolean min;
+
+ /** */
+ private ByteString val;
+
+ /** */
+ private boolean empty = true;
+
+ /** */
+ private VarBinaryMinMax(boolean min) {
+ this.min = min;
+ }
+
+ /** {@inheritDoc} */
+ @Override public void add(Object... args) {
+ ByteString in = (ByteString)args[0];
+
+ if (in == null)
+ return;
+
+ val = empty ? in : min ?
+ (val.compareTo(in) < 0 ? val : in) :
+ (val.compareTo(in) < 0 ? in : val);
+
+ empty = false;
+ }
+
+ /** {@inheritDoc} */
+ @Override public void apply(Accumulator other) {
+ VarBinaryMinMax other0 = (VarBinaryMinMax)other;
+
+ if (other0.empty)
+ return;
+
+ val = empty ? other0.val : min ?
+ (val.compareTo(other0.val) < 0 ? val : other0.val) :
+ (val.compareTo(other0.val) < 0 ? other0.val : val);
+
+ empty = false;
+ }
+
+ /** {@inheritDoc} */
+ @Override public Object end() {
+ return empty ? null : val;
+ }
+
+ /** {@inheritDoc} */
+ @Override public List<RelDataType> argumentTypes(IgniteTypeFactory
typeFactory) {
+ return
F.asList(typeFactory.createTypeWithNullability(typeFactory.createSqlType(VARBINARY),
true));
+ }
+
+ /** {@inheritDoc} */
+ @Override public RelDataType returnType(IgniteTypeFactory typeFactory)
{
+ return
typeFactory.createTypeWithNullability(typeFactory.createSqlType(VARBINARY),
true);
+ }
+ }
+
+ /** */
private static class DistinctAccumulator implements Accumulator {
/** */
private final Accumulator acc;
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
index 45e55da..ed39cb2 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/externalize/RelJson.java
@@ -34,6 +34,7 @@ import com.google.common.cache.LoadingCache;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import org.apache.calcite.avatica.AvaticaUtils;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.linq4j.tree.BlockBuilder;
@@ -91,6 +92,7 @@ import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlNameMatchers;
import org.apache.calcite.util.ImmutableBitSet;
@@ -297,6 +299,8 @@ class RelJson {
return toJson((RelDataType)value);
else if (value instanceof RelDataTypeField)
return toJson((RelDataTypeField)value);
+ else if (value instanceof ByteString)
+ return toJson((ByteString)value);
else
throw new UnsupportedOperationException("type not serializable: "
+ value + " (type " + value.getClass().getCanonicalName() +
")");
@@ -502,6 +506,8 @@ class RelJson {
if (type.getSqlTypeName() == SqlTypeName.SYMBOL)
literal = toEnum(literal);
+ else if (type.getSqlTypeName().getFamily() ==
SqlTypeFamily.BINARY)
+ literal = toByteString(literal);
return rexBuilder.makeLiteral(literal, type, false);
}
@@ -581,6 +587,13 @@ class RelJson {
}
/** */
+ private ByteString toByteString(Object o) {
+ assert o instanceof String;
+
+ return ByteString.of((String)o, 16);
+ }
+
+ /** */
private RelFieldCollation toFieldCollation(Map<String, Object> map) {
Integer field = (Integer)map.get("field");
Direction direction = toEnum(map.get("direction"));
@@ -912,4 +925,9 @@ class RelJson {
map.put("syntax", toJson(operator.getSyntax()));
return map;
}
+
+ /** */
+ private Object toJson(ByteString val) {
+ return val.toString();
+ }
}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
index 3e5dcaa..0f4e7dc 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteStdSqlOperatorTable.java
@@ -249,6 +249,7 @@ public class IgniteStdSqlOperatorTable extends
ReflectiveSqlOperatorTable {
register(SqlLibraryOperators.LEAST);
register(SqlLibraryOperators.GREATEST);
register(SqlLibraryOperators.COMPRESS);
+ register(SqlStdOperatorTable.OCTET_LENGTH);
// XML Operators.
register(SqlLibraryOperators.EXTRACT_VALUE);
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
index 8c7c33e..a6e0376 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/TypeUtils.java
@@ -32,6 +32,7 @@ import java.util.stream.Collectors;
import java.util.stream.IntStream;
import com.google.common.collect.ImmutableSet;
import org.apache.calcite.DataContext;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.plan.RelOptSchema;
import org.apache.calcite.plan.RelOptTable;
@@ -64,7 +65,8 @@ public class TypeUtils {
java.sql.Time.class,
java.sql.Timestamp.class,
Duration.class,
- Period.class
+ Period.class,
+ byte[].class
);
/** */
@@ -280,6 +282,8 @@ public class TypeUtils {
}
else if (storageType == Period.class)
return (int)((Period)val).toTotalMonths();
+ else if (storageType == byte[].class)
+ return new ByteString((byte[])val);
else
return val;
}
@@ -300,6 +304,8 @@ public class TypeUtils {
return Duration.ofMillis((Long)val);
else if (storageType == Period.class && val instanceof Integer)
return Period.of((Integer)val / 12, (Integer)val % 12, 0);
+ else if (storageType == byte[].class && val instanceof ByteString)
+ return ((ByteString)val).getBytes();
else
return val;
}
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/DataTypesTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/DataTypesTest.java
index 00e1072..9c8319f 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/DataTypesTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/DataTypesTest.java
@@ -18,8 +18,10 @@
package org.apache.ignite.internal.processors.query.calcite;
import java.util.List;
+import java.util.Objects;
import java.util.stream.Collectors;
import com.google.common.collect.ImmutableSet;
+import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.QueryEntity;
import org.apache.ignite.configuration.CacheConfiguration;
import
org.apache.ignite.internal.processors.query.calcite.integration.AbstractBasicIntegrationTest;
@@ -137,4 +139,106 @@ public class DataTypesTest extends
AbstractBasicIntegrationTest {
assertEquals(val.length(), rows.get(0).get(0));
}
}
+
+ /** */
+ @Test
+ public void testBinarySql() {
+ try {
+ executeSql("CREATE TABLE tbl(b BINARY(3), v VARBINARY)");
+
+ byte[] val = new byte[] {1, 2, 3};
+
+ // From parameters to internal, from internal to store, from store
to internal and from internal to user.
+ executeSql("INSERT INTO tbl VALUES (?, ?)", val, val);
+
+ List<List<?>> res = executeSql("SELECT b, v FROM tbl");
+
+ assertEquals(1, res.size());
+ assertEquals(2, res.get(0).size());
+ assertTrue(Objects.deepEquals(val, res.get(0).get(0)));
+ assertTrue(Objects.deepEquals(val, res.get(0).get(1)));
+
+ executeSql("DELETE FROM tbl");
+
+ // From literal to internal, from internal to store, from store to
internal and from internal to user.
+ executeSql("INSERT INTO tbl VALUES (x'1A2B3C', x'AABBCC')");
+
+ res = executeSql("SELECT b, v FROM tbl");
+
+ assertEquals(1, res.size());
+ assertEquals(2, res.get(0).size());
+ assertTrue(Objects.deepEquals(new byte[] {0x1A, 0x2B, 0x3C},
res.get(0).get(0)));
+ assertTrue(Objects.deepEquals(new byte[] {(byte)0xAA, (byte)0xBB,
(byte)0xCC}, res.get(0).get(1)));
+ }
+ finally {
+ executeSql("DROP TABLE IF EXISTS tbl");
+ }
+ }
+
+ /** Cache API - SQL API cross check. */
+ @Test
+ public void testBinaryCache() {
+ try {
+ IgniteCache<Integer, byte[]> cache = client.getOrCreateCache(new
CacheConfiguration<Integer, byte[]>()
+ .setName("binary_cache")
+ .setSqlSchema("PUBLIC")
+ .setQueryEntities(F.asList(new QueryEntity(Integer.class,
byte[].class).setTableName("binary_table")))
+ );
+
+ byte[] val = new byte[] {1, 2, 3};
+
+ // From cache to internal, from internal to user.
+ cache.put(1, val);
+
+ List<List<?>> res = executeSql("SELECT _val FROM binary_table");
+ assertEquals(1, res.size());
+ assertEquals(1, res.get(0).size());
+ assertTrue(Objects.deepEquals(val, res.get(0).get(0)));
+
+ // From literal to internal, from internal to cache.
+ executeSql("INSERT INTO binary_table (_KEY, _VAL) VALUES (2,
x'010203')");
+ byte[] resVal = cache.get(2);
+ assertTrue(Objects.deepEquals(val, resVal));
+ }
+ finally {
+ client.destroyCache("binary_cache");
+ }
+ }
+
+ /** */
+ @Test
+ public void testBinaryAggregation() {
+ try {
+ executeSql("CREATE TABLE tbl(b varbinary)");
+ executeSql("INSERT INTO tbl VALUES (NULL)");
+ executeSql("INSERT INTO tbl VALUES (x'010203')");
+ executeSql("INSERT INTO tbl VALUES (x'040506')");
+ List<List<?>> res = executeSql("SELECT MIN(b), MAX(b) FROM tbl");
+
+ assertEquals(1, res.size());
+ assertEquals(2, res.get(0).size());
+ assertTrue(Objects.deepEquals(new byte[] {1, 2, 3},
res.get(0).get(0)));
+ assertTrue(Objects.deepEquals(new byte[] {4, 5, 6},
res.get(0).get(1)));
+ }
+ finally {
+ executeSql("DROP TABLE IF EXISTS tbl");
+ }
+ }
+
+ /** */
+ @Test
+ public void testBinaryConcat() {
+ try {
+ executeSql("CREATE TABLE tbl(b varbinary)");
+ executeSql("INSERT INTO tbl VALUES (x'010203')");
+ List<List<?>> res = executeSql("SELECT b || x'040506' FROM tbl");
+
+ assertEquals(1, res.size());
+ assertEquals(1, res.get(0).size());
+ assertTrue(Objects.deepEquals(new byte[] {1, 2, 3, 4, 5, 6},
res.get(0).get(0)));
+ }
+ finally {
+ executeSql("DROP TABLE IF EXISTS tbl");
+ }
+ }
}
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/StdSqlOperatorsTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/StdSqlOperatorsTest.java
index 467a75e..f6db6b5 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/StdSqlOperatorsTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/StdSqlOperatorsTest.java
@@ -23,7 +23,6 @@ import java.sql.Timestamp;
import java.time.Period;
import java.util.Arrays;
import java.util.Collections;
-import org.apache.calcite.avatica.util.ByteString;
import
org.apache.ignite.internal.processors.query.calcite.integration.AbstractBasicIntegrationTest;
import
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteStdSqlOperatorTable;
import org.apache.ignite.internal.util.typedef.F;
@@ -138,7 +137,7 @@ public class StdSqlOperatorsTest extends
AbstractBasicIntegrationTest {
assertQuery("SELECT LOWER('aA')").returns("aa").check();
assertQuery("SELECT INITCAP('aA')").returns("Aa").check();
assertQuery("SELECT TO_BASE64('aA')").returns("YUE=").check();
- assertQuery("SELECT FROM_BASE64('YUE=')").returns(new ByteString(new
byte[] {'a', 'A'})).check();
+ assertQuery("SELECT
FROM_BASE64('YUE=')::VARCHAR").returns("aA").check();
assertQuery("SELECT
MD5('aa')").returns("4124bc0a9335c27f086f24ba207a4912").check();
assertQuery("SELECT
SHA1('aa')").returns("e0c9035898dd52fc65c41454cec9c4d2611bfb37").check();
assertQuery("SELECT SUBSTRING('aAaA', 2, 2)").returns("Aa").check();
@@ -274,6 +273,7 @@ public class StdSqlOperatorsTest extends
AbstractBasicIntegrationTest {
assertQuery("SELECT LEAST('a', 'b')").returns("a").check();
assertQuery("SELECT GREATEST('a', 'b')").returns("b").check();
assertQuery("SELECT COMPRESS('')::VARCHAR").returns("").check();
+ assertQuery("SELECT OCTET_LENGTH(x'01')").returns(1).check();
}
/** */
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctionsTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctionsTest.java
index 90cf6f9..b6c1e4a 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctionsTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctionsTest.java
@@ -28,7 +28,7 @@ public class IgniteSqlFunctionsTest {
/** */
@Test
public void testBigDecimalToString() {
- Assert.assertNull(IgniteSqlFunctions.toString(null));
+ Assert.assertNull(IgniteSqlFunctions.toString((BigDecimal)null));
Assert.assertEquals(
"10",
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
index 9ff891d..af8105b 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
@@ -39,6 +39,7 @@ import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.google.common.collect.ImmutableSet;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.ignite.IgniteException;
import org.apache.ignite.IgniteLogger;
import org.apache.ignite.cache.query.FieldsQueryCursor;
@@ -73,8 +74,8 @@ public class SqlScriptRunner {
int rows = r1.size();
for (int i = 0; i < rows; ++i) {
- String s1 = String.valueOf(r1.get(i));
- String s2 = String.valueOf(r2.get(i));
+ String s1 = toString(r1.get(i));
+ String s2 = toString(r2.get(i));
if (!s1.equals(s2))
return s1.compareTo(s2);
@@ -156,6 +157,14 @@ public class SqlScriptRunner {
}
/** */
+ private static String toString(Object res) {
+ if (res instanceof byte[])
+ return ByteString.toString((byte[])res, 16);
+ else
+ return String.valueOf(res);
+ }
+
+ /** */
private class Script implements Iterable<Command>, AutoCloseable {
/** Reader. */
private final String fileName;
@@ -611,7 +620,10 @@ public class SqlScriptRunner {
for (int j = 0; j < expectedRow.size(); ++j) {
checkEquals("Not expected result at: " + posDesc +
". [row=" + i + ", col=" + j +
- ", expected=" + expectedRow.get(j) + ", actual=" +
row.get(j) + ']', expectedRow.get(j), row.get(j));
+ ", expected=" + expectedRow.get(j) + ", actual=" +
SqlScriptRunner.toString(row.get(j)) + ']',
+ expectedRow.get(j),
+ row.get(j)
+ );
}
}
}
@@ -638,8 +650,8 @@ public class SqlScriptRunner {
}
}
else {
- if
(!String.valueOf(expectedStr).equals(String.valueOf(actual)) &&
- !("(empty)".equals(expectedStr) &&
String.valueOf(actual).isEmpty()))
+ if
(!String.valueOf(expectedStr).equals(SqlScriptRunner.toString(actual)) &&
+ !("(empty)".equals(expectedStr) &&
SqlScriptRunner.toString(actual).isEmpty()))
throw new AssertionError(msg);
}
}
@@ -652,7 +664,7 @@ public class SqlScriptRunner {
for (List<?> row : res) {
for (Object col : row) {
- messageDigest.update(String.valueOf(col).getBytes());
+
messageDigest.update(SqlScriptRunner.toString(col).getBytes());
messageDigest.update(NL_BYTES);
}
}
diff --git a/modules/calcite/src/test/sql/types/blob/test_blob.test
b/modules/calcite/src/test/sql/types/blob/test_blob.test
new file mode 100644
index 0000000..be0647a
--- /dev/null
+++ b/modules/calcite/src/test/sql/types/blob/test_blob.test
@@ -0,0 +1,108 @@
+# name: test/sql/types/blob/test_blob.test
+# description: BLOB tests
+# group: [blob]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE blobs (b varbinary);
+
+# Insert valid hex strings
+statement ok
+INSERT INTO blobs VALUES(x'aaffaa'), (x'AAFFAAAAFFAA'), (x'AAFFAAAAFFAAAAFFAA')
+
+query T rowsort
+SELECT * FROM blobs
+----
+aaffaa
+aaffaaaaffaa
+aaffaaaaffaaaaffaa
+
+# Insert valid hex strings, lower case
+statement ok
+DELETE FROM blobs
+
+statement ok
+INSERT INTO blobs VALUES(x'aaffaa'), (x'aaffaaaaffaa'), (x'aaffaaaaffaaaaffaa')
+
+query T rowsort
+SELECT * FROM blobs
+----
+aaffaa
+aaffaaaaffaa
+aaffaaaaffaaaaffaa
+
+# Insert valid hex strings with number and letters
+statement ok
+DELETE FROM blobs
+
+statement ok
+INSERT INTO blobs VALUES(x'aa1199'), (x'aa1199aa1199'), (x'aa1199aa1199aa1199')
+
+query T rowsort
+SELECT * FROM blobs
+----
+aa1199
+aa1199aa1199
+aa1199aa1199aa1199
+
+# Insert invalid hex strings (invalid hex chars: G, H, I)
+statement error
+INSERT INTO blobs VALUES(x'GAFFAA')
+
+# Insert invalid hex strings (odd # of chars)
+statement error
+INSERT INTO blobs VALUES(x'A')
+
+statement error
+INSERT INTO blobs VALUES(x'AAA')
+
+statement ok
+DELETE FROM blobs
+
+# Implicit cast
+statement ok
+INSERT INTO blobs VALUES('blablabla')
+
+# BINARY with “non-printable” octets
+statement ok
+INSERT INTO blobs VALUES('abc �'::VARBINARY)
+
+query T
+SELECT b::varchar FROM blobs ORDER BY b
+----
+abc �
+blablabla
+
+# BINARY null and empty values
+query T
+SELECT ''::VARBINARY
+----
+(empty)
+
+query T
+SELECT NULL::VARBINARY
+----
+NULL
+
+statement ok
+CREATE TABLE blob_empty (b binary);
+
+statement ok
+INSERT INTO blob_empty VALUES('')
+
+statement ok
+INSERT INTO blob_empty VALUES(''::VARBINARY)
+
+statement ok
+INSERT INTO blob_empty VALUES(NULL), (NULL::VARBINARY)
+
+query T rowsort
+SELECT * FROM blob_empty
+----
+NULL
+NULL
+(empty)
+(empty)
+
diff --git a/modules/calcite/src/test/sql/types/blob/test_blob.test_ignore
b/modules/calcite/src/test/sql/types/blob/test_blob.test_ignore
index d0769d8..f0fca68 100644
--- a/modules/calcite/src/test/sql/types/blob/test_blob.test_ignore
+++ b/modules/calcite/src/test/sql/types/blob/test_blob.test_ignore
@@ -1,79 +1,89 @@
# name: test/sql/types/blob/test_blob.test
# description: BLOB tests
# group: [blob]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15618
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15123
statement ok
PRAGMA enable_verification
statement ok
-CREATE TABLE blobs (b binary);
+CREATE TABLE blobs (b varbinary);
# Insert valid hex strings
statement ok
-INSERT INTO blobs VALUES('\xaa\xff\xaa'), ('\xAA\xFF\xAA\xAA\xFF\xAA'),
('\xAA\xFF\xAA\xAA\xFF\xAA\xAA\xFF\xAA')
+INSERT INTO blobs VALUES(x'aaffaa'), (x'AAFFAAAAFFAA'), (x'AAFFAAAAFFAAAAFFAA')
-query T
+query T rowsort
SELECT * FROM blobs
----
-\xAA\xFF\xAA
-\xAA\xFF\xAA\xAA\xFF\xAA
-\xAA\xFF\xAA\xAA\xFF\xAA\xAA\xFF\xAA
+aaffaa
+aaffaaaaffaa
+aaffaaaaffaaaaffaa
# Insert valid hex strings, lower case
statement ok
DELETE FROM blobs
statement ok
-INSERT INTO blobs VALUES('\xaa\xff\xaa'), ('\xaa\xff\xaa\xaa\xff\xaa'),
('\xaa\xff\xaa\xaa\xff\xaa\xaa\xff\xaa')
+INSERT INTO blobs VALUES(x'aaffaa'), (x'aaffaaaaffaa'), (x'aaffaaaaffaaaaffaa')
-query T
+query T rowsort
SELECT * FROM blobs
----
-\xAA\xFF\xAA
-\xAA\xFF\xAA\xAA\xFF\xAA
-\xAA\xFF\xAA\xAA\xFF\xAA\xAA\xFF\xAA
+aaffaa
+aaffaaaaffaa
+aaffaaaaffaaaaffaa
# Insert valid hex strings with number and letters
statement ok
DELETE FROM blobs
statement ok
-INSERT INTO blobs VALUES('\xaa1199'), ('\xaa1199aa1199'),
('\xaa1199aa1199aa1199')
+INSERT INTO blobs VALUES(x'aa1199'), (x'aa1199aa1199'), (x'aa1199aa1199aa1199')
-query T
+query T rowsort
SELECT * FROM blobs
----
-\xAA1199
-\xAA1199aa1199
-\xAA1199aa1199aa1199
+aa1199
+aa1199aa1199
+aa1199aa1199aa1199
# Insert invalid hex strings (invalid hex chars: G, H, I)
statement error
-INSERT INTO blobs VALUES('\xGA\xFF\xAA')
+INSERT INTO blobs VALUES(x'GAFFAA')
# Insert invalid hex strings (odd # of chars)
statement error
-INSERT INTO blobs VALUES('\xA')
+INSERT INTO blobs VALUES(x'A')
statement error
-INSERT INTO blobs VALUES('\xAA\xA')
+INSERT INTO blobs VALUES(x'AAA')
-statement error
-INSERT INTO blobs VALUES('blablabla\x')
+statement ok
+DELETE FROM blobs
-# BLOB with “non-printable” octets
-statement error
-SELECT 'abc �'::BYTEA;
+# Implicit cast
+statement ok
+INSERT INTO blobs VALUES('blablabla')
+
+# BINARY with “non-printable” octets
+statement ok
+INSERT INTO blobs VALUES('abc �'::VARBINARY)
-# BLOB null and empty values
query T
-SELECT ''::BLOB
+SELECT b::varchar FROM blobs ORDER BY b
+----
+abc �
+blablabla
+
+# BINARY null and empty values
+query T
+SELECT ''::VARBINARY
----
(empty)
query T
-SELECT NULL::BLOB
+SELECT NULL::VARBINARY
----
NULL
@@ -81,16 +91,16 @@ statement ok
CREATE TABLE blob_empty (b binary);
statement ok
-INSERT INTO blob_empty VALUES(''), (''::BLOB)
+INSERT INTO blob_empty VALUES(''), (''::VARBINARY)
statement ok
-INSERT INTO blob_empty VALUES(NULL), (NULL::BLOB)
+INSERT INTO blob_empty VALUES(NULL), (NULL::VARBINARY)
-query T
+query T rowsort
SELECT * FROM blob_empty
----
-(empty)
-(empty)
NULL
NULL
+(empty)
+(empty)
diff --git a/modules/calcite/src/test/sql/types/blob/test_blob_cast.test_ignore
b/modules/calcite/src/test/sql/types/blob/test_blob_cast.test
similarity index 58%
rename from modules/calcite/src/test/sql/types/blob/test_blob_cast.test_ignore
rename to modules/calcite/src/test/sql/types/blob/test_blob_cast.test
index 03eaf09..17fc157 100644
--- a/modules/calcite/src/test/sql/types/blob/test_blob_cast.test_ignore
+++ b/modules/calcite/src/test/sql/types/blob/test_blob_cast.test
@@ -1,7 +1,6 @@
# name: test/sql/types/blob/test_blob_cast.test
# description: Cast BLOB values
# group: [blob]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15618
statement ok
PRAGMA enable_verification
@@ -16,57 +15,57 @@ a
query T
SELECT 'a'::VARCHAR::binary
----
-a
+61
# Hex string with BLOB
query T
-SELECT '\x20\x00\xFF'::binary
+SELECT x'2000FF'::varbinary
----
- \x00\xFF
+2000ff
# CastFromBlob with hex string
query T
-SELECT '\x20\x00\xFF'::BLOB::VARCHAR
+SELECT x'612061'::VARBINARY::VARCHAR
----
- \x00\xFF
+a a
# CastFromBlob and after CastToBlob with hex string
query T
-SELECT '\x20\x00\xFF'::BLOB::VARCHAR::BLOB
+SELECT x'612061'::VARBINARY::VARCHAR::VARBINARY
----
- \x00\xFF
+612061
# CastFromBlob -> CastToBlob -> CastFromBlob with hex string
query T
-SELECT '\x20\x00\xFF'::BLOB::VARCHAR::BLOB::VARCHAR
+SELECT x'612061'::VARBINARY::VARCHAR::VARBINARY::VARCHAR
----
- \x00\xFF
+a a
# CastToBlob -> CastFromBlob -> CastToBlob with hex string
query T
-SELECT '\x20\x00\xFF'::VARCHAR::BLOB::VARCHAR::BLOB
+SELECT x'612061'::VARCHAR::VARBINARY::VARCHAR::VARBINARY
----
- \x00\xFF
+612061
statement error
-SELECT 1::BYTEA
+SELECT 1::VARBINARY
statement error
-SELECT 1.0::BYTEA
+SELECT 1.0::VARBINARY
-# numeric -> bytea, not valid/implemented casts
+# numeric -> varbinary, not valid/implemented casts
statement error
-SELECT 1::tinyint::BYTEA
+SELECT 1::tinyint::VARBINARY
statement error
-SELECT 1::smallint::BYTEA
+SELECT 1::smallint::VARBINARY
statement error
-SELECT 1::integer::BYTEA
+SELECT 1::integer::VARBINARY
statement error
-SELECT 1::bigint::BYTEA
+SELECT 1::bigint::VARBINARY
statement error
-SELECT 1::decimal::BYTEA
+SELECT 1::decimal::VARBINARY
diff --git a/modules/calcite/src/test/sql/types/blob/test_blob_function.test
b/modules/calcite/src/test/sql/types/blob/test_blob_function.test
new file mode 100644
index 0000000..1633df7
--- /dev/null
+++ b/modules/calcite/src/test/sql/types/blob/test_blob_function.test
@@ -0,0 +1,84 @@
+# name: test/sql/types/blob/test_blob_function.test
+# description: BLOB with Functions
+# group: [blob]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE blobs (b varbinary);
+
+statement ok
+INSERT INTO blobs VALUES ('a'::binary)
+
+# conventional concat
+query T
+SELECT (b || 'ZZ'::varbinary)::varchar FROM blobs
+----
+aZZ
+
+query T
+SELECT 'abc '::varbinary || 'klm *'::varbinary || x'EFBFBD'::varbinary ||
'T'::varbinary
+----
+616263206b6c6d202aefbfbd54
+
+statement ok
+INSERT INTO blobs VALUES ('abc '::varbinary || 'klm *'::varbinary ||
x'EFBFBD'::varbinary || 'T'::varbinary)
+
+query I
+SELECT COUNT(*) FROM blobs
+----
+2
+
+# octet_length
+query I
+SELECT OCTET_LENGTH(b) FROM blobs ORDER BY 1
+----
+1
+13
+
+# HEX strings
+statement ok
+DELETE FROM blobs
+
+statement ok
+INSERT INTO blobs VALUES (x'FF'::binary)
+
+query T
+SELECT b || 'ZZ'::varbinary FROM blobs
+----
+ff5a5a
+
+query T
+SELECT b || x'5A5A'::varbinary FROM blobs
+----
+ff5a5a
+
+# BLOB || VARCHAR is not allowed, should fail
+statement error
+SELECT b || '5A5A'::VARCHAR FROM blobs
+
+# Octet Length tests
+statement ok
+DELETE FROM blobs
+
+statement ok
+INSERT INTO blobs VALUES (x'FF'::binary)
+
+statement ok
+INSERT INTO blobs VALUES ('FF'::varbinary)
+
+statement ok
+INSERT INTO blobs VALUES (x'55AAFF55AAFF55AAFF01'::varbinary)
+
+statement ok
+INSERT INTO blobs VALUES ('55AAFF55AAFF55AAFF01'::varbinary)
+
+query I
+SELECT OCTET_LENGTH(b) FROM blobs ORDER BY 1
+----
+1
+2
+10
+20
+
diff --git
a/modules/calcite/src/test/sql/types/blob/test_blob_function.test_ignore
b/modules/calcite/src/test/sql/types/blob/test_blob_function.test_ignore
deleted file mode 100644
index 5040b6d..0000000
--- a/modules/calcite/src/test/sql/types/blob/test_blob_function.test_ignore
+++ /dev/null
@@ -1,85 +0,0 @@
-# name: test/sql/types/blob/test_blob_function.test
-# description: BLOB with Functions
-# group: [blob]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15618
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE blobs (b binary);
-
-statement ok
-INSERT INTO blobs VALUES ('a'::binary)
-
-# conventional concat
-query T
-SELECT b || 'ZZ'::binary FROM blobs
-----
-aZZ
-
-query T
-SELECT 'abc '::BYTEA || 'klm *\xEF\xBF\xBDT'::binary
-----
-abc klm *\xEF\xBF\xBDT
-
-statement ok
-INSERT INTO blobs VALUES ('abc klm *\xEF\xBF\xBDT'::binary)
-
-query I
-SELECT COUNT(*) FROM blobs
-----
-2
-
-# octet_length
-query I
-SELECT OCTET_LENGTH(b) FROM blobs
-----
-1
-13
-
-# HEX strings
-statement ok
-DELETE FROM blobs
-
-statement ok
-INSERT INTO blobs VALUES ('\xFF'::binary)
-
-query T
-SELECT b || 'ZZ'::binary FROM blobs
-----
-\xFFZZ
-
-query T
-SELECT b || '\x5A\x5A'::binary FROM blobs
-----
-\xFFZZ
-
-# BLOB || VARCHAR is not allowed, should fail
-statement error
-SELECT b || '5A5A'::VARCHAR FROM blobs
-
-# Octet Length tests
-statement ok
-DELETE FROM blobs
-
-statement ok
-INSERT INTO blobs VALUES ('\xFF'::binary)
-
-statement ok
-INSERT INTO blobs VALUES ('FF'::binary)
-
-statement ok
-INSERT INTO blobs VALUES ('\x55\xAA\xFF\x55\xAA\xFF\x55\xAA\xFF\x01'::binary)
-
-statement ok
-INSERT INTO blobs VALUES ('55AAFF55AAFF55AAFF01'::binary)
-
-query I
-SELECT OCTET_LENGTH(b) FROM blobs
-----
-1
-2
-10
-20
-
diff --git a/modules/calcite/src/test/sql/types/blob/test_blob_operator.test
b/modules/calcite/src/test/sql/types/blob/test_blob_operator.test
new file mode 100644
index 0000000..d992760
--- /dev/null
+++ b/modules/calcite/src/test/sql/types/blob/test_blob_operator.test
@@ -0,0 +1,92 @@
+# name: test/sql/types/blob/test_blob_operator.test
+# description: Test BLOBs with various SQL operators
+# group: [blob]
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15123
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE blobs (b varbinary, g INTEGER);
+
+# strings: hello -> \x68656C6C6F, r -> \x72
+statement ok
+INSERT INTO blobs VALUES ('hello', 0)
+
+statement ok
+INSERT INTO blobs VALUES (x'00' || 'whatisgoingon'::varbinary, 1)
+
+statement ok
+INSERT INTO blobs VALUES (NULL, 0)
+
+statement ok
+INSERT INTO blobs VALUES (x'FFFEFB', 1)
+
+# simple aggregates only
+query IITT
+SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM blobs
+----
+4 3 00776861746973676f696e676f6e fffefb
+
+# ORDER BY
+query TI
+SELECT * FROM blobs ORDER BY b
+----
+NULL 0
+00776861746973676f696e676f6e 1
+68656c6c6f 0
+fffefb 1
+
+# GROUP BY
+statement ok
+INSERT INTO blobs VALUES ('hello', 3)
+
+statement ok
+INSERT INTO blobs VALUES (x'00' || 'whatisgoingon'::varbinary, 9)
+
+statement ok
+INSERT INTO blobs VALUES (NULL, 0)
+
+statement ok
+INSERT INTO blobs VALUES (x'FFFEFB', 19)
+
+query II
+SELECT b, SUM(g) FROM blobs GROUP BY b ORDER BY b
+----
+NULL 0
+00776861746973676f696e676f6e 10
+68656c6c6f 3
+fffefb 20
+
+# JOIN
+statement ok
+CREATE TABLE blobs2 (b VARBINARY, g INTEGER);
+
+statement ok
+INSERT INTO blobs2 VALUES ('hello', 0)
+
+statement ok
+INSERT INTO blobs2 VALUES (x'00' || 'whatisgoingon'::varbinary, 100)
+
+statement ok
+INSERT INTO blobs2 VALUES (NULL, 0)
+
+statement ok
+INSERT INTO blobs2 VALUES (x'FFFEFB', 200)
+
+# group by blobs.b, explicit JOIN
+query TR
+SELECT L.b, SUM(L.g) FROM blobs as L JOIN blobs2 AS R ON L.b=R.b GROUP BY L.b
ORDER BY L.b
+----
+00776861746973676f696e676f6e 10.000000
+68656c6c6f 3.000000
+fffefb 20.000000
+
+# group by blobs2.b, implicit JOIN
+query TR
+SELECT R.b, SUM(R.g) FROM blobs as L, blobs2 AS R WHERE L.b=R.b GROUP BY R.b
ORDER BY R.b
+----
+00776861746973676f696e676f6e 200.000000
+68656c6c6f 0.000000
+fffefb 400.000000
+
diff --git
a/modules/calcite/src/test/sql/types/blob/test_blob_operator.test_ignore
b/modules/calcite/src/test/sql/types/blob/test_blob_operator.test_ignore
index 0473de7..1fc938f 100644
--- a/modules/calcite/src/test/sql/types/blob/test_blob_operator.test_ignore
+++ b/modules/calcite/src/test/sql/types/blob/test_blob_operator.test_ignore
@@ -1,65 +1,65 @@
# name: test/sql/types/blob/test_blob_operator.test
# description: Test BLOBs with various SQL operators
# group: [blob]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15618
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15123
statement ok
PRAGMA enable_verification
statement ok
-CREATE TABLE blobs (b binary, g INTEGER);
+CREATE TABLE blobs (b varbinary, g INTEGER);
# strings: hello -> \x68656C6C6F, r -> \x72
statement ok
-INSERT INTO blobs VALUES ('hello', 0), ('\x00whatisgoingon', 1), (NULL, 0),
('\xFF\xFE\xFB', 1)
+INSERT INTO blobs VALUES ('hello', 0), (x'00' || 'whatisgoingon'::varbinary,
1), (NULL, 0), (x'FFFEFB', 1)
# simple aggregates only
query IITT
SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM blobs
----
-4 3 \x00whatisgoingon \xFF\xFE\xFB
+4 3 00776861746973676f696e676f6e fffefb
# ORDER BY
query TI
SELECT * FROM blobs ORDER BY b
----
NULL 0
-\x00whatisgoingon 1
-hello 0
-\xFF\xFE\xFB 1
+00776861746973676f696e676f6e 1
+68656c6c6f 0
+fffefb 1
# GROUP BY
statement ok
-INSERT INTO blobs VALUES ('hello', 3), ('\x00whatisgoingon', 9), (NULL, 0),
('\xFF\xFE\xFB', 19)
+INSERT INTO blobs VALUES ('hello', 3), (x'00' || 'whatisgoingon'::varbinary,
9), (NULL, 0), (x'FFFEFB', 19)
query II
SELECT b, SUM(g) FROM blobs GROUP BY b ORDER BY b
----
NULL 0
-\x00whatisgoingon 10
-hello 3
-\xFF\xFE\xFB 20
+00776861746973676f696e676f6e 10
+68656c6c6f 3
+fffefb 20
# JOIN
statement ok
-CREATE TABLE blobs2 (b BYTEA, g INTEGER);
+CREATE TABLE blobs2 (b VARBINARY, g INTEGER);
statement ok
-INSERT INTO blobs2 VALUES ('hello', 0), ('\x00whatisgoingon', 100), (NULL, 0),
('\xFF\xFE\xFB', 200)
+INSERT INTO blobs2 VALUES ('hello', 0), (x'00' || 'whatisgoingon'::varbinary,
100), (NULL, 0), (x'FFFEFB', 200)
# group by blobs.b, explicit JOIN
query TR
SELECT L.b, SUM(L.g) FROM blobs as L JOIN blobs2 AS R ON L.b=R.b GROUP BY L.b
ORDER BY L.b
----
-\x00whatisgoingon 10.000000
-hello 3.000000
-\xFF\xFE\xFB 20.000000
+00776861746973676f696e676f6e 10.000000
+68656c6c6f 3.000000
+fffefb 20.000000
# group by blobs2.b, implicit JOIN
query TR
SELECT R.b, SUM(R.g) FROM blobs as L, blobs2 AS R WHERE L.b=R.b GROUP BY R.b
ORDER BY R.b
----
-\x00whatisgoingon 200.000000
-hello 0.000000
-\xFF\xFE\xFB 400.000000
+00776861746973676f696e676f6e 200.000000
+68656c6c6f 0.000000
+fffefb 400.000000
diff --git
a/modules/calcite/src/test/sql/types/blob/test_blob_string.test_ignore
b/modules/calcite/src/test/sql/types/blob/test_blob_string.test
similarity index 80%
rename from modules/calcite/src/test/sql/types/blob/test_blob_string.test_ignore
rename to modules/calcite/src/test/sql/types/blob/test_blob_string.test
index 0214477..2dce6ea 100644
--- a/modules/calcite/src/test/sql/types/blob/test_blob_string.test_ignore
+++ b/modules/calcite/src/test/sql/types/blob/test_blob_string.test
@@ -1,20 +1,19 @@
# name: test/sql/types/blob/test_blob_string.test
# description: Insert BLOB values from normal strings
# group: [blob]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15618
statement ok
PRAGMA enable_verification
statement ok
-CREATE TABLE blobs (b binary);
+CREATE TABLE blobs (b varbinary);
-# insert BLOB from string
+# insert BINARY from string
statement ok
-INSERT INTO blobs VALUES ('aaaaaaaaaa')
+INSERT INTO blobs VALUES ('aaaaaaaaaa'::varbinary)
# sizes: 10, 100, 1000, 10000 -> double plus two due to hexadecimal
representation
-# The concat function casts BLOB to VARCHAR,resulting in a hex string
+# The concat function casts BINARY to VARCHAR, resulting in a hex string
statement ok
INSERT INTO blobs SELECT b||b||b||b||b||b||b||b||b||b FROM blobs WHERE
OCTET_LENGTH(b)=(SELECT MAX(OCTET_LENGTH(b)) FROM blobs)
