Dear Calcite dev team, Our team is using Calcite to query Elasticsearch, but found that the Elasticsearch Adapter doesn’t support basic functions like UPPER, CHAR_LENGTH, TRIM, etc.
For example, we use the sql "select char_length(city) from zips”, it will throw an exception like below: java.sql.SQLException: Error while executing SQL "select char_length(city) from zips": Translation of CHAR_LENGTH(CAST(ITEM($0, 'city')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")is not supported by ElasticsearchProject …... Caused by: java.lang.IllegalArgumentException: Translation of CHAR_LENGTH(CAST(ITEM($0, 'city')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary")is not supported by ElasticsearchProject at org.apache.calcite.adapter.elasticsearch.ElasticsearchRules$RexToElasticsearchTranslator.visitCall(ElasticsearchRules.java:158) at org.apache.calcite.adapter.elasticsearch.ElasticsearchRules$RexToElasticsearchTranslator.visitCall(ElasticsearchRules.java:107) at org.apache.calcite.rex.RexCall.accept(RexCall.java:107) at org.apache.calcite.adapter.elasticsearch.ElasticsearchProject.implement(ElasticsearchProject.java:68) at org.apache.calcite.adapter.elasticsearch.ElasticsearchRel$Implementor.visitChild(ElasticsearchRel.java:53) at org.apache.calcite.adapter.elasticsearch.ElasticsearchToEnumerableConverter.implement(ElasticsearchToEnumerableConverter.java:71) The same sql can work in calcite core module. We are not very familiar with the framework of calcite and wondering how we can use these basic sql function in ES adapter, could you give us some advise? Look forward to your reply. Best regards, Haiyang He TalkingData.com