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 7459e0d4923 IGNITE-24358 SQL Calcite: Restrict system functions
overriding - Fixes #11851.
7459e0d4923 is described below
commit 7459e0d492360523446572b2c4698f108da8ce6f
Author: Vladimir Steshin <[email protected]>
AuthorDate: Fri Feb 7 23:39:05 2025 +0300
IGNITE-24358 SQL Calcite: Restrict system functions overriding - Fixes
#11851.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_docs/SQL/custom-sql-func.adoc | 6 +-
.../query/calcite/schema/SchemaHolderImpl.java | 36 ++++++++
.../UserDefinedFunctionsIntegrationTest.java | 99 +++++++++++++++++++++-
3 files changed, 138 insertions(+), 3 deletions(-)
diff --git a/docs/_docs/SQL/custom-sql-func.adoc
b/docs/_docs/SQL/custom-sql-func.adoc
index 7c9540c87ca..36d28947728 100644
--- a/docs/_docs/SQL/custom-sql-func.adoc
+++ b/docs/_docs/SQL/custom-sql-func.adoc
@@ -18,7 +18,7 @@
The SQL Engine can extend the SQL functions' set, defined by the ANSI-99
specification, via the addition of custom SQL functions written in Java.
-A custom SQL function is just a public static method marked by the
`@QuerySqlFunction` annotation.
+A custom SQL function is just a `public` or `public static` method marked by
the `@QuerySqlFunction` annotation.
////
TODO looks like it's unsupported in C#
@@ -48,7 +48,7 @@ include::{javaFile}[tags=sql-function-query, indent=0]
Custom SQL function can be a table function. Result of table function is
treated as a row set (a table) and can be used
-by other SQL operators. Custom SQL function is also a `public` method marked
by annotation `@QuerySqlTableFunction`.
+by other SQL operators. Custom SQL function is also a `public` or `public
static` method marked by annotation `@QuerySqlTableFunction`.
Table function must return an `Iterable` as a row set. Each row can be
represented by an `Object[]` or by a `Collection`.
Row length must match the defined number of column types. Row value types must
match the defined column types or be able
assigned to them.
@@ -61,6 +61,8 @@ include::{javaFile}[tags=sql-table-function-example, indent=0]
----
include::{javaFile}[tags=sql-table-function-config-query, indent=0]
----
+NOTE: Creating custom functions in the system schema is forbidden. Creating a
custom function in schema 'PUBLIC' is forbidden if the function name interferes
with a standard function like 'TYPEOF' or 'SUBSTRING'.
+
NOTE: The table functions also are available currently only with
link:SQL/sql-calcite[Calcite, window=_blank].
NOTE: Classes registered with `CacheConfiguration.setSqlFunctionClasses(...)`
must be added to the classpath of all the nodes where the defined custom
functions might be executed. Otherwise, you will get a `ClassNotFoundException`
error when trying to execute the custom function.
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
index 2542228e654..d853a538be9 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
@@ -27,6 +27,11 @@ import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.schema.SchemaPlus;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlNameMatchers;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.util.ImmutableBitSet;
@@ -51,6 +56,7 @@ import
org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.apache.ignite.internal.processors.query.schema.SchemaChangeListener;
import
org.apache.ignite.internal.processors.query.schema.management.IndexDescriptor;
import
org.apache.ignite.internal.processors.subscription.GridInternalSubscriptionProcessor;
+import org.apache.ignite.internal.util.typedef.F;
import org.apache.ignite.internal.util.typedef.internal.S;
import org.apache.ignite.lang.IgnitePredicate;
import org.apache.ignite.spi.systemview.view.SystemView;
@@ -356,6 +362,9 @@ public class SchemaHolderImpl extends AbstractService
implements SchemaHolder, S
/** {@inheritDoc} */
@Override public void onFunctionCreated(String schemaName, String name,
boolean deterministic, Method method) {
+ if (!checkNewUserDefinedFunction(schemaName, name))
+ return;
+
IgniteSchema schema = igniteSchemas.computeIfAbsent(schemaName,
IgniteSchema::new);
schema.addFunction(name.toUpperCase(),
IgniteScalarFunction.create(method));
@@ -371,6 +380,9 @@ public class SchemaHolderImpl extends AbstractService
implements SchemaHolder, S
Class<?>[] colTypes,
String[] colNames
) {
+ if (!checkNewUserDefinedFunction(schemaName, name))
+ return;
+
IgniteSchema schema = igniteSchemas.computeIfAbsent(schemaName,
IgniteSchema::new);
schema.addFunction(name.toUpperCase(),
IgniteTableFunction.create(method, colTypes, colNames));
@@ -378,6 +390,30 @@ public class SchemaHolderImpl extends AbstractService
implements SchemaHolder, S
rebuild();
}
+ /** */
+ private boolean checkNewUserDefinedFunction(String schName, String
funName) {
+ if (F.eq(schName, QueryUtils.DFLT_SCHEMA)) {
+ List<SqlOperator> operators = new ArrayList<>();
+
+ frameworkCfg.getOperatorTable().lookupOperatorOverloads(
+ new SqlIdentifier(funName, SqlParserPos.ZERO),
+ null,
+ SqlSyntax.FUNCTION,
+ operators,
+ SqlNameMatchers.withCaseSensitive(false)
+ );
+
+ if (!operators.isEmpty()) {
+ log.error("Unable to add user-defined SQL function '" +
funName + "'. Default schema '"
+ + QueryUtils.DFLT_SCHEMA + "' already has a standard
function with the same name.");
+
+ return false;
+ }
+ }
+
+ return true;
+ }
+
/** {@inheritDoc} */
@Override public void onSystemViewCreated(String schemaName, SystemView<?>
sysView) {
IgniteSchema schema = igniteSchemas.computeIfAbsent(schemaName,
IgniteSchema::new);
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/UserDefinedFunctionsIntegrationTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/UserDefinedFunctionsIntegrationTest.java
index 422d3a8c2db..f46b0bb2f96 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/UserDefinedFunctionsIntegrationTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/UserDefinedFunctionsIntegrationTest.java
@@ -17,9 +17,11 @@
package org.apache.ignite.internal.processors.query.calcite.integration;
+import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
+import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.validate.SqlValidatorException;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteCheckedException;
@@ -32,6 +34,7 @@ import
org.apache.ignite.calcite.CalciteQueryEngineConfiguration;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.configuration.IgniteConfiguration;
import org.apache.ignite.internal.processors.query.IgniteSQLException;
+import org.apache.ignite.internal.processors.query.QueryUtils;
import org.apache.ignite.internal.util.typedef.F;
import org.apache.ignite.testframework.GridTestUtils;
import org.apache.ignite.testframework.ListeningTestLogger;
@@ -61,6 +64,67 @@ public class UserDefinedFunctionsIntegrationTest extends
AbstractBasicIntegratio
return cfg;
}
+ /** */
+ @Test
+ public void testSystemFunctionOverriding() throws Exception {
+ // To a custom schema.
+ client.getOrCreateCache(new CacheConfiguration<Integer,
Employer>("TEST_CACHE_OWN")
+ .setSqlSchema("OWN_SCHEMA")
+ .setSqlFunctionClasses(OverrideSystemFunctionLibrary.class)
+ .setQueryEntities(F.asList(new QueryEntity(Integer.class,
Employer.class).setTableName("emp_own")))
+ );
+
+ // Make sure that the new functions didn't affect schema 'PUBLIC'.
+ assertQuery("SELECT
UPPER(?)").withParams("abc").returns("ABC").check();
+ assertQuery("select UNIX_SECONDS(TIMESTAMP '2021-01-01
00:00:00')").returns(1609459200L).check();
+ assertQuery("select * from table(SYSTEM_RANGE(1,
2))").returns(1L).returns(2L).check();
+ assertQuery("select
TYPEOF(?)").withParams(1L).returns("BIGINT").check();
+ assertQuery("select ? + ?").withParams(1, 2).returns(3).check();
+ assertThrows("select PLUS(?, ?)", SqlValidatorException.class, "No
match found for function signature", 1, 2);
+
+ // Ensure that new functions are successfully created in a custom
schema.
+ assertQuery("SELECT
\"OWN_SCHEMA\".UPPER(?)").withParams("abc").returns(3).check();
+ assertQuery("select \"OWN_SCHEMA\".UNIX_SECONDS(TIMESTAMP '2021-01-01
00:00:00')").returns(1).check();
+ assertQuery("select * from table(\"OWN_SCHEMA\".SYSTEM_RANGE(1,
2))").returns(100L).check();
+ assertQuery("select \"OWN_SCHEMA\".TYPEOF('ABC')").returns(1).check();
+ assertQuery("select \"OWN_SCHEMA\".PLUS(?, ?)").withParams(1,
2).returns(100).check();
+
+ LogListener logChecker0 = LogListener.matches("Unable to add
user-defined SQL function 'upper'")
+ .andMatches("Unable to add user-defined SQL function
'unix_seconds'")
+ .andMatches("Unable to add user-defined SQL function
'system_range'")
+ .andMatches("Unable to add user-defined SQL function 'typeof'")
+ .andMatches("Unable to add user-defined SQL function
'plus'").times(0)
+ .build();
+
+ listeningLog.registerListener(logChecker0);
+
+ // Try to add the functions into the default schema.
+ client.getOrCreateCache(new CacheConfiguration<Integer,
Employer>("TEST_CACHE_PUB")
+ .setSqlFunctionClasses(OverrideSystemFunctionLibrary.class)
+ .setSqlSchema(QueryUtils.DFLT_SCHEMA)
+ .setQueryEntities(F.asList(new QueryEntity(Integer.class,
Employer.class).setTableName("emp_pub"))));
+
+ assertTrue(logChecker0.check(getTestTimeout()));
+
+ // Make sure that the standard functions work once again.
+ assertQuery("SELECT
UPPER(?)").withParams("abc").returns("ABC").check();
+ assertQuery("select UNIX_SECONDS(TIMESTAMP '2021-01-01
00:00:00')").returns(1609459200L).check();
+ assertQuery("select * from table(SYSTEM_RANGE(1,
2))").returns(1L).returns(2L).check();
+ assertQuery("select
TYPEOF(?)").withParams(1L).returns("BIGINT").check();
+
+ // Make sure that operator '+' works and new function 'PLUS' also
registered in the default schema.
+ assertQuery("select ? + ?").withParams(1, 2).returns(3).check();
+ assertQuery("select PLUS(?, ?)").withParams(1, 2).returns(100);
+
+ SchemaPlus dfltSchema =
queryProcessor(client).schemaHolder().schema(QueryUtils.DFLT_SCHEMA);
+
+ assertEquals(0, dfltSchema.getFunctions("UPPER").size());
+ assertEquals(0, dfltSchema.getFunctions("UNIX_SECONDS").size());
+ assertEquals(0, dfltSchema.getFunctions("SYSTEM_RANGE").size());
+ assertEquals(0, dfltSchema.getFunctions("TYPEOF").size());
+ assertEquals(1, dfltSchema.getFunctions("PLUS").size());
+ }
+
/** */
@Test
public void testFunctions() throws Exception {
@@ -260,7 +324,7 @@ public class UserDefinedFunctionsIntegrationTest extends
AbstractBasicIntegratio
assertThrows("SELECT * FROM wrongRowType(1)", IgniteSQLException.class,
"row type is neither Collection or Object[]");
- logChecker0.check(getTestTimeout());
+ assertTrue(logChecker0.check(getTestTimeout()));
String errTxt = "No match found for function signature";
@@ -556,6 +620,39 @@ public class UserDefinedFunctionsIntegrationTest extends
AbstractBasicIntegratio
}
}
+ /** */
+ public static class OverrideSystemFunctionLibrary {
+ /** Overwrites standard 'UPPER(VARCHAR)'. */
+ @QuerySqlFunction
+ public static int upper(String s) {
+ return F.isEmpty(s) ? 0 : s.length();
+ }
+
+ /** Overwrites standard 'UNIX_SECONDS(Timestamp)'. */
+ @QuerySqlFunction
+ public static int unix_seconds(Timestamp ts) {
+ return 1;
+ }
+
+ /** Overwrites Ignite's 'SYSTEM_RANGE(...)'. */
+ @QuerySqlTableFunction(columnTypes = {long.class}, columnNames =
{"RESULT"})
+ public static Collection<Object> system_range(long x, long y) {
+ return F.asList(F.asList(100L));
+ }
+
+ /** Overwrites Ignite's 'TYPEOF(Object)'. */
+ @QuerySqlFunction
+ public static int typeof(Object o) {
+ return 1;
+ }
+
+ /** Same name as of operator '+' which is not a function. */
+ @QuerySqlFunction
+ public static int plus(int x, int y) {
+ return 100;
+ }
+ }
+
/** */
public static class InnerSqlFunctionsLibrary {
/** */
