Search code examples
sqldata-managementapache-calcite

Apache Calcite query parser - Unexpected character double quotes

I am using Apache Calcite to execute queries on different data sources. The model file that I am using is 

inline: {
  version: '1.0',
  defaultSchema: 'sakila',
  schemas: [
    {
      name: 'sakila',
      type: 'custom',
      factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory',
      operand: {
        jdbcDriver: 'org.postgresql.Driver',
        jdbcUrl: 'jdbc:postgresql://localhost:5432/sakila',
        jdbcUser: 'postgres',
        jdbcPassword: 'postgres'
      }
    }
  ]
}

And the query is

select 
    "sakila"."actor"."first_name" as "actor_first_name" 
from
    "sakila"."actor"

The above query is not working due to the double quotes applied for tables and columns. So, I had to remove the quotes and the following query works fine.

select 
    sakila.actor.first_name as actor_first_name 
from
    sakila.actor

Here, the question is the query parser is not allowing some queries if they don't have double quotes. And in some cases like above it is not requiring quotes to execute properly. Can anyone throw some insight on why exactly it is so?

Solution

  • I suspect that the cause is case-sensitivity. Assuming that Calcite is in its default lexical mode, if you remove the quotes around identifiers, Calcite will convert them to upper case before trying to find tables and columns with those names. You say that it works without quotes, so I presume that your schema, table and column are upper case (SAKILA, ACTOR, FIRST_NAME).

    You can ask Calcite to be non-case-sensitive by passing caseSensitive=false as part of the Calcite JDBC connect string.