This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new ec498ddc66c IGNITE-23658 SQL Calcite: Add support for bitwise
operations (BITAND, BITOR, BITXOR) - Fixes #11655.
ec498ddc66c is described below
commit ec498ddc66c99334fa15fd98d37d83d62d1dcacd
Author: Vladimir Steshin <[email protected]>
AuthorDate: Wed Nov 20 18:24:02 2024 +0300
IGNITE-23658 SQL Calcite: Add support for bitwise operations (BITAND,
BITOR, BITXOR) - Fixes #11655.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_docs/SQL/sql-calcite.adoc | 2 +-
.../query/calcite/exec/exp/RexImpTable.java | 7 +
.../calcite/sql/fun/IgniteOwnSqlOperatorTable.java | 32 +++++
.../query/calcite/integration/FunctionsTest.java | 150 +++++++++++++++++++++
4 files changed, 190 insertions(+), 1 deletion(-)
diff --git a/docs/_docs/SQL/sql-calcite.adoc b/docs/_docs/SQL/sql-calcite.adoc
index 547d1192faa..afb7dc5a92e 100644
--- a/docs/_docs/SQL/sql-calcite.adoc
+++ b/docs/_docs/SQL/sql-calcite.adoc
@@ -150,7 +150,7 @@ The Calcite-based SQL engine currently supports:
|`UPPER`, `LOWER`, `INITCAP`, `TO_BASE64`, `FROM_BASE64`, `MD5`, `SHA1`,
`SUBSTRING`, `LEFT`, `RIGHT`, `REPLACE`, `TRANSLATE`, `CHR`, `CHAR_LENGTH`,
`CHARACTER_LENGTH`, `LENGTH`, `CONCAT`, `OVERLAY`, `POSITION`, `ASCII`,
`REPEAT`, `SPACE`, `STRCMP`, `SOUNDEX`, `DIFFERENCE`, `REVERSE`, `TRIM`,
`LTRIM`, `RTRIM`, `REGEXP_REPLACE`
|Math functions
-|`MOD`, `EXP`, `POWER`, `LN`, `LOG10`, `ABS`, `RAND`, `RAND_INTEGER`, `ACOS`,
`ACOSH`, `ASIN`, `ASINH`, `ATAN`, `ATANH`, `ATAN2`, `SQRT`, `CBRT`, `COS`,
`COSH`, `COT`, `COTH`, `DEGREES`, `RADIANS`, `ROUND`, `SIGN`, `SIN`, `SINH`,
`TAN`, `TANH`, `SEC`, `SECH`, `CSC`, `CSCH`, `TRUNCATE`, `PI`
+|`MOD`, `EXP`, `POWER`, `LN`, `LOG10`, `ABS`, `RAND`, `RAND_INTEGER`, `ACOS`,
`ACOSH`, `ASIN`, `ASINH`, `ATAN`, `ATANH`, `ATAN2`, `SQRT`, `CBRT`, `COS`,
`COSH`, `COT`, `COTH`, `DEGREES`, `RADIANS`, `ROUND`, `SIGN`, `SIN`, `SINH`,
`TAN`, `TANH`, `SEC`, `SECH`, `CSC`, `CSCH`, `TRUNCATE`, `PI`, `BITAND`,
`BITOR`, `BITXOR`
|Date and time functions
|`EXTRACT`, `FLOOR`, `CEIL`, `TIMESTAMPADD`, `TIMESTAMPDIFF`, `LAST_DATE`,
`DAYNAME`, `MONTHNAME`, `DAYOFMONTH`, `DAYOFWEEK`, `DAYOFYEAR`, `YEAR`,
`QUARTER`, `MONTH`, `WEEK`, `HOUR`, `MINUTE`, `SECOND`, `TIMESTAMP_SECONDS`,
`TIMESTAMP_MILLIS`, `TIMESTAMP_MICROS`, `UNIX_SECONDS`, `UNIX_MILLIS`,
`UNIX_MICROS`, `UNIX_DATE`, `DATE_FROM_UNIX_DATE`, `DATE`, `TIME`, `DATETIME`,
`CURRENT_TIME`, `CURRENT_TIMESTAMP`, `CURRENT_DATE`, `LOCALTIME`,
`LOCALTIMESTAMP`
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 e2f587882f8..c66eae98219 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
@@ -242,6 +242,9 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_MINUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UPPER;
import static org.apache.calcite.util.ReflectUtil.isStatic;
+import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.BITAND;
+import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.BITOR;
+import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.BITXOR;
import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.GREATEST2;
import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.LEAST2;
import static
org.apache.ignite.internal.processors.query.calcite.sql.fun.IgniteOwnSqlOperatorTable.NULL_BOUND;
@@ -564,6 +567,10 @@ public class RexImpTable {
// Operator IS_NOT_DISTINCT_FROM is removed by RexSimplify, but still
possible in join conditions, so
// implementation required.
defineMethod(IS_NOT_DISTINCT_FROM,
IgniteMethod.IS_NOT_DISTINCT_FROM.method(), NullPolicy.NONE);
+
+ defineMethod(BITAND, BuiltInMethod.BIT_AND.method, NullPolicy.ANY);
+ defineMethod(BITOR, BuiltInMethod.BIT_OR.method, NullPolicy.ANY);
+ defineMethod(BITXOR, BuiltInMethod.BIT_XOR.method, NullPolicy.ANY);
}
/** */
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
index b9f994c26fc..b0a43abd2be 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/sql/fun/IgniteOwnSqlOperatorTable.java
@@ -19,8 +19,10 @@ package
org.apache.ignite.internal.processors.query.calcite.sql.fun;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeTransforms;
import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable;
@@ -123,6 +125,36 @@ public class IgniteOwnSqlOperatorTable extends
ReflectiveSqlOperatorTable {
OperandTypes.SAME_SAME,
SqlFunctionCategory.SYSTEM);
+ /** Bitwise '&' of two values. */
+ public static final SqlFunction BITAND =
+ new SqlFunction(
+ "BITAND",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.LEAST_RESTRICTIVE,
+ InferTypes.RETURN_TYPE,
+ OperandTypes.family(SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER),
+ SqlFunctionCategory.NUMERIC);
+
+ /** Bitwise '|' of two values. */
+ public static final SqlFunction BITOR =
+ new SqlFunction(
+ "BITOR",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.LEAST_RESTRICTIVE,
+ InferTypes.RETURN_TYPE,
+ OperandTypes.family(SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER),
+ SqlFunctionCategory.NUMERIC);
+
+ /** Bitwise '^' of two values. */
+ public static final SqlFunction BITXOR =
+ new SqlFunction(
+ "BITXOR",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.LEAST_RESTRICTIVE,
+ InferTypes.RETURN_TYPE,
+ OperandTypes.family(SqlTypeFamily.INTEGER, SqlTypeFamily.INTEGER),
+ SqlFunctionCategory.NUMERIC);
+
/**
* Returns the Ignite operator table, creating it if necessary.
*/
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
index f0bf58b661a..0f7ffe89ecf 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
@@ -21,6 +21,7 @@ import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
+import java.util.ArrayList;
import java.util.List;
import java.util.function.LongFunction;
import org.apache.calcite.sql.validate.SqlValidatorException;
@@ -28,6 +29,7 @@ import org.apache.ignite.IgniteCache;
import org.apache.ignite.calcite.CalciteQueryEngineConfiguration;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.internal.processors.query.IgniteSQLException;
+import org.apache.ignite.internal.util.typedef.F;
import org.apache.ignite.internal.util.typedef.internal.U;
import org.junit.Test;
@@ -58,6 +60,154 @@ public class FunctionsTest extends
AbstractBasicIntegrationTest {
assertQuery("SELECT
QUERY_ENGINE()").returns(CalciteQueryEngineConfiguration.ENGINE_NAME).check();
}
+ /** */
+ @Test
+ public void testBitwiseOperationsWithTable() {
+ try {
+ sql("CREATE TABLE TBL(i INT PRIMARY KEY, s SMALLINT, l BIGINT)");
+
+ for (int i = 0; i < 100; ++i)
+ sql("INSERT INTO TBL values (?, ?, ?)", i, i, i);
+
+ sql("INSERT INTO TBL values (?, ?, ?)", Short.MAX_VALUE + 1,
Short.MAX_VALUE, Short.MAX_VALUE);
+
+ assertQuery("SELECT BITAND(i + 1, i + 1) FROM TBL WHERE
i=0").returns(1).check();
+ assertQuery("SELECT BITAND(s, s) FROM TBL WHERE
i=1").returns((short)1).check();
+ assertQuery("SELECT BITAND((SELECT l FROM TBL WHERE i=3), (SELECT
l FROM TBL WHERE i=1))").returns(1L).check();
+ assertQuery("SELECT BITOR((SELECT s FROM TBL WHERE i=?), (SELECT i
FROM TBL WHERE i=?))").withParams(14, 1)
+ .returns(15).check();
+
+ assertQuery("SELECT BITAND((SELECT s FROM TBL WHERE s=3), (SELECT
l FROM TBL WHERE i=1))").returns(1L).check();
+
+ assertQuery("SELECT BITXOR(1000::BIGINT, i) FROM TBL WHERE
i=93").returns(949L).check();
+ assertQuery("SELECT BITAND(?, i) FROM TBL WHERE
i=73").withParams(NULL_RESULT).returns(NULL_RESULT).check();
+ assertQuery("SELECT BITAND(l, ?) FROM TBL WHERE
l=45").withParams(NULL_RESULT).returns(NULL_RESULT).check();
+ assertQuery("SELECT BITAND(?, s) FROM TBL WHERE
s=40").withParams(NULL_RESULT).returns(NULL_RESULT).check();
+
+ assertQuery("SELECT BITAND(i, s) FROM TBL WHERE
l=?").withParams(Short.MAX_VALUE).returns(0).check();
+ assertQuery("SELECT BITOR(i, s) FROM TBL WHERE
l=?").withParams(Short.MAX_VALUE).returns(65535).check();
+ assertQuery("SELECT BITXOR(i, s) FROM TBL WHERE
l=?").withParams(Short.MAX_VALUE).returns(65535).check();
+ }
+ finally {
+ sql("DROP TABLE if EXISTS TBL");
+ }
+ }
+
+ /** */
+ @Test
+ public void testBitwiseOperations() {
+ doTestBitwiseOperations(false);
+
+ doTestBitwiseOperations(true);
+ }
+
+ /** */
+ private void doTestBitwiseOperations(boolean dynamic) {
+ for (List<Object> paramSet : bitwiseParams(dynamic)) {
+ assert paramSet.size() == 6;
+
+ int idx = 0;
+
+ String op = paramSet.get(idx++).toString();
+ Object p1 = paramSet.get(idx++);
+ String cast1 = (String)paramSet.get(idx++);
+ Object p2 = paramSet.get(idx++);
+ String cast2 = (String)paramSet.get(idx++);
+ Object res = paramSet.get(idx);
+
+ cast1 = cast1 == null ? "" : "::" + cast1;
+ cast2 = cast2 == null ? "" : "::" + cast2;
+
+ log.info("Op: " + op + ", dynamic=" + dynamic + ", p1=" + p1 + ",
p2=" + p2 + ", expected=" + res);
+
+ if (dynamic) {
+ String sql = "SELECT BIT" + op + "(?" + cast1 + ", ?" + cast2
+ ')';
+
+ if (res instanceof Exception)
+ assertThrows(sql, (Class<? extends
Exception>)res.getClass(), ((Throwable)res).getMessage(), p1, p2);
+ else
+ assertQuery(sql).withParams(p1, p2).returns(res).check();
+ }
+ else {
+ String sql = "SELECT BIT" + op + '(' + p1 + cast1 + ", " + p2
+ cast2 + ')';
+
+ if (res instanceof Exception)
+ assertThrows(sql, (Class<? extends
Exception>)res.getClass(), ((Throwable)res).getMessage());
+ else
+ assertQuery(sql).returns(res).check();
+ }
+ }
+ }
+
+ /** Bitwise operation params: operation, param1, cast1, param2, cast2,
result. */
+ private Iterable<List<Object>> bitwiseParams(boolean dynamic) {
+ List<List<Object>> res = new ArrayList<>(100);
+
+ SqlValidatorException andErr = new SqlValidatorException("Cannot apply
'BITAND' to arguments of type", null);
+ SqlValidatorException orErr = new SqlValidatorException("Cannot apply
'BITOR' to arguments of type", null);
+ SqlValidatorException xorErr = new SqlValidatorException("Cannot apply
'BITXOR' to arguments of type", null);
+
+ // BITAND
+ res.add(F.asList("AND", 1, null, 1.0, null, andErr));
+ res.add(F.asList("AND", 1.0, null, 1, null, andErr));
+ res.add(F.asList("AND", 1, null, 1.0f, null, andErr));
+ res.add(F.asList("AND", 1.0f, null, 1, null, andErr));
+ res.add(F.asList("AND", null, null, null, null, null));
+ res.add(F.asList("AND", 1, null, 1, null, 1));
+ res.add(F.asList("AND", 1, null, 0, null, 0));
+ res.add(F.asList("AND", 0, null, 1, null, 0));
+ res.add(F.asList("AND", 1, null, 1, "BIGINT", 1L));
+ res.add(F.asList("AND", 1, "TINYINT", 1, "INT", 1));
+ res.add(F.asList("AND", 1, "TINYINT", 1, "SMALLINT", (short)1));
+ res.add(F.asList("AND", 0, "TINYINT", 0, "TINYINT", (byte)0));
+ res.add(F.asList("AND", 1, "TINYINT", 1, "SMALLINT", (short)1));
+ res.add(F.asList("AND", 15, null, 7, null, 7));
+ res.add(F.asList("AND", -1, null, 1, null, 1));
+ res.add(F.asList("AND", (short)32767, null, 65535, null, 32767));
+ res.add(F.asList("AND", null, null, 1, null, null));
+ res.add(F.asList("AND", 1, "SMALLINT", null, null, null));
+ // BITOR
+ res.add(F.asList("OR", 1, null, 1.0, null, orErr));
+ res.add(F.asList("OR", 1.0, null, 1, null, orErr));
+ res.add(F.asList("OR", 1, null, 1.0f, null, orErr));
+ res.add(F.asList("OR", 1.0f, null, 1, null, orErr));
+ res.add(F.asList("OR", 1, null, 1, null, 1));
+ res.add(F.asList("OR", 1, null, 0, null, 1));
+ res.add(F.asList("OR", 0, null, 1, null, 1));
+ res.add(F.asList("OR", 1, null, 1, "BIGINT", 1L));
+ res.add(F.asList("OR", 1, "TINYINT", 1, "INT", 1));
+ res.add(F.asList("OR", 1, "TINYINT", 1, "SMALLINT", (short)1));
+ res.add(F.asList("OR", 0, "TINYINT", 0, "TINYINT", (byte)0));
+ res.add(F.asList("OR", 1, "TINYINT", 1, "SMALLINT", (short)1));
+ res.add(F.asList("OR", 8, null, 7, null, 15));
+ res.add(F.asList("OR", -1, null, 1, null, -1));
+ res.add(F.asList("OR", (short)32767, null, 65535, null, 65535));
+ res.add(F.asList("OR", (short)32767, null, 65536, null, 98303));
+ res.add(F.asList("OR", null, null, 1, null, null));
+ res.add(F.asList("OR", 1, null, null, null, null));
+ // BITXOR
+ res.add(F.asList("XOR", 1, null, 1.0, null, xorErr));
+ res.add(F.asList("XOR", 1.0, null, 1, null, xorErr));
+ res.add(F.asList("XOR", 1, null, 1.0f, null, xorErr));
+ res.add(F.asList("XOR", 1.0f, null, 1, null, xorErr));
+ res.add(F.asList("XOR", 1, null, 1, null, 0));
+ res.add(F.asList("XOR", 1, null, 0, null, 1));
+ res.add(F.asList("XOR", 0, null, 1, null, 1));
+ res.add(F.asList("XOR", 1, null, 1, "BIGINT", 0L));
+ res.add(F.asList("XOR", 1, "TINYINT", 1, "INT", 0));
+ res.add(F.asList("XOR", 1, "TINYINT", 1, "SMALLINT", (short)0));
+ res.add(F.asList("XOR", 0, "TINYINT", 0, "TINYINT", (byte)0));
+ res.add(F.asList("XOR", 1, "TINYINT", 1, "SMALLINT", (short)0));
+ res.add(F.asList("XOR", 8, null, 7, null, 15));
+ res.add(F.asList("XOR", -1, null, 1, null, -2));
+ res.add(F.asList("XOR", (short)32767, null, 65535, null, 32768));
+ res.add(F.asList("XOR", (short)32767, null, 65536, null, 98303));
+ res.add(F.asList("XOR", null, null, 1, "TINYINT", null));
+ res.add(F.asList("XOR", 1, null, null, null, null));
+
+ return res;
+ }
+
/** */
@Test
public void testCurrentDateTimeTimeStamp() {
