This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new fe1d6e6a606 Support federated query mysql bin function (#32373)
fe1d6e6a606 is described below
commit fe1d6e6a6060f41ec3d10d7c7aa1d1b1f6b2420f
Author: niu niu <[email protected]>
AuthorDate: Mon Aug 5 14:39:06 2024 +0800
Support federated query mysql bin function (#32373)
* Support federated query mysql bin function
* Format code
* Format code
* Fix
---
.../optimizer/function/mysql/MySQLBinFunction.java | 69 ++++++++++++++++++++++
.../planner/util/SQLFederationFunctionUtils.java | 2 +
.../test/resources/cases/dql/e2e-dql-select.xml | 23 ++++++++
3 files changed, 94 insertions(+)
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLBinFunction.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLBinFunction.java
new file mode 100644
index 00000000000..70ed9d99f57
--- /dev/null
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLBinFunction.java
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sqlfederation.optimizer.function.mysql;
+
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+
+import java.math.BigInteger;
+
+/**
+ * MySQL bin function.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class MySQLBinFunction {
+
+ /**
+ * Bin.
+ *
+ * @param value value
+ * @return binary string
+ */
+ public static String bin(final Object value) {
+ if (null == value) {
+ return null;
+ }
+ if (value instanceof Number && !(value instanceof BigInteger)) {
+ return Long.toBinaryString(((Number) value).longValue());
+ }
+ BigInteger bigIntegerValue = value instanceof BigInteger ?
(BigInteger) value : new BigInteger(getFirstNumbers(String.valueOf(value)));
+ return -1 != bigIntegerValue.signum() ? bigIntegerValue.toString(2) :
bigIntegerValue.add(BigInteger.ONE.shiftLeft(Long.SIZE).subtract(BigInteger.ONE)).add(BigInteger.ONE).toString(2);
+ }
+
+ private static String getFirstNumbers(final String value) {
+ if (value.isEmpty()) {
+ return "0";
+ }
+ boolean isNegative = '-' == value.charAt(0);
+ StringBuilder result = new StringBuilder();
+ if (isNegative) {
+ result.append('-');
+ }
+ for (int i = isNegative ? 1 : 0; i < value.length(); i++) {
+ if (Character.isDigit(value.charAt(i))) {
+ result.append(value.charAt(i));
+ } else {
+ break;
+ }
+ }
+ if (0 == result.length() || isNegative && 1 == result.length()) {
+ return "0";
+ }
+ return result.toString();
+ }
+}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationFunctionUtils.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationFunctionUtils.java
index d7aa09479f2..0228352f728 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationFunctionUtils.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationFunctionUtils.java
@@ -23,6 +23,7 @@ import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.ScalarFunctionImpl;
import org.apache.shardingsphere.infra.autogen.version.ShardingSphereVersion;
+import
org.apache.shardingsphere.sqlfederation.optimizer.function.mysql.MySQLBinFunction;
import
org.apache.shardingsphere.sqlfederation.optimizer.function.mysql.MySQLBitCountFunction;
/**
@@ -53,6 +54,7 @@ public final class SQLFederationFunctionUtils {
schemaPlus.add("bit_count",
ScalarFunctionImpl.create(MySQLBitCountFunction.class, "bitCount"));
schemaPlus.add("atan", ScalarFunctionImpl.create(SqlFunctions.class,
"atan2"));
schemaPlus.add("atan2", ScalarFunctionImpl.create(SqlFunctions.class,
"atan"));
+ schemaPlus.add("bin",
ScalarFunctionImpl.create(MySQLBinFunction.class, "bin"));
if ("pg_catalog".equalsIgnoreCase(schemaName)) {
schemaPlus.add("pg_catalog.pg_table_is_visible",
ScalarFunctionImpl.create(SQLFederationFunctionUtils.class,
"pgTableIsVisible"));
schemaPlus.add("pg_catalog.pg_get_userbyid",
ScalarFunctionImpl.create(SQLFederationFunctionUtils.class, "pgGetUserById"));
diff --git a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
index 1d2c7bfac7a..ccd33ebc19c 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
@@ -347,4 +347,27 @@
INNER JOIN (SELECT item_id, order_id FROM t_order_item
limit 10) res2 ON res1.order_id = res2.order_id" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT bin(2), bin(-2), BIN(2.5), BIN(-2.5), BIN('2.5'),
BIN('-2.5'), BIN('123hello world'), BIN('-123hello world')" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT BIN('18446744073709551615'),
BIN('-9223372036854775808'), BIN('-18446744073709551615')" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT t4.order_id, t3.item_id, bin(t4.order_id),
bin(t3.item_id), t2.* FROM t_product t1
+ INNER JOIN (
+ SELECT product_id, bin(type_int),
bin(type_smallint), bin(type_decimal), bin(type_float),
+ bin(type_double), bin(type_bit),
bin(type_tinyint), bin(type_mediumint), bin(type_bigint),
+ bin(type_date), bin(type_time),
bin(type_datetime), bin(type_timestamp), bin(type_year),
+ bin(type_char), bin(type_text),
bin(type_varchar), bin(type_longtext), bin(type_longblob),
+ bin(type_mediumtext), bin(type_mediumblob),
bin(type_binary), bin(type_varbinary),
+ bin(type_blob), bin(type_enum), bin(type_set),
bin(type_json), bin(type_unsigned_int),
+ bin(type_unsigned_bigint),
bin(type_unsigned_tinyint), bin(type_unsigned_smallint),
bin(type_unsigned_float),
+ bin(type_unsigned_double),
bin(type_unsigned_decimal) FROM t_product_extend
+ ) t2 ON t1.product_id = t2.product_id
+ INNER JOIN t_order_item t3 ON t2.product_id =
t3.product_id INNER JOIN t_order t4 ON t4.order_id = t3.order_id order by
t1.product_id" db-types="MySQL" scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</e2e-test-cases>
