You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by 何海洋 <ha...@tendcloud.com> on 2017/03/30 03:45:11 UTC

Support for basic sql function in Elasticsearch adapter

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

The same problem appears in MongoDB Adapter

Posted by 何海洋 <hh...@gmail.com>.

The same problem appears in MongoDB Adapter.

0: jdbc:calcite:model=mongodb/target/test-cla> select city from zips limit 5;
+----------------------+
|         CITY         |
+----------------------+
| AGAWAM               |
| CUSHMAN              |
| BARRE                |
| BELCHERTOWN          |
| BLANDFORD            |
+----------------------+
5 rows selected (0.184 seconds)

0: jdbc:calcite:model=mongodb/target/test-cla> select char_length(city) from zips limit 5;
Error: Error while executing SQL "select char_length(city) from zips limit 5": 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 MongoProject (state=,code=0)

0: jdbc:calcite:model=mongodb/target/test-cla> select upper(city) from zips limit 5;
Error: Error while executing SQL "select upper(city) from zips limit 5": Translation of UPPER(CAST(ITEM($0, 'city')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary") is not supported by MongoProject (state=,code=0)