In the list of functions and operators supported by Calcite there is only one CONTAINS keyword which indeed indicates a predicate on periods: https://calcite.apache.org/docs/reference.html#period-predicates
I don't see a CONTAINS function in the elastic search SQL documentation: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions.html That being said, it's relatively easy to add new functions to Calcite. Most pull requests these days are about adding new functions, so there are lots of examples. Mihai ________________________________ From: 侯宾宾 <houbinbin.e...@gmail.com> Sent: Monday, February 19, 2024 12:00 AM To: dev@calcite.apache.org <dev@calcite.apache.org> Subject: Calcite-elasticsearch v1.36.0，How to use keyword LIKE/CONTAINS/Regex for match query in sql? Hello, everyone. In calcite-elasticsearch v1.36.0，I wanna to query sth in es with match query or wildcard query. I try sql like 【SELECT * FROM booksmapping WHERE title like '%Java%'】 But failed: ``` Suppressed: java.lang.UnsupportedOperationException: LIKE not yet supported at org.apache.calcite.adapter.elasticsearch.PredicateAnalyzer$Visitor.binary(PredicateAnalyzer.java:377) ``` By the way, it seems CONTAINS also not support varchar type by default. ```java private static void showContains(CalciteConnection calciteConnection) { try { String sql = "SELECT * FROM booksmapping WHERE title contains 'Java'"; Statement statement = calciteConnection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); System.out.println(ResultSetUtil.resultString(resultSet)); } catch (SQLException e) { throw new RuntimeException(e); } } ``` error logs ``` Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply 'CONTAINS' to arguments of type '<VARCHAR> CONTAINS <CHAR(4)>'. Supported form(s): '<DATETIME> CONTAINS <DATETIME>' '<DATETIME> CONTAINS <DATETIME>' '<DATETIME> CONTAINS <INTERVAL>' '<DATETIME> CONTAINS <INTERVAL>' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601) ... 37 more ``` How to fix it? thx
