julianhyde/optiq — pull request #182 by bdumon (2014-03-13 11:09:37+00:00)
Title: jdbc: do not add varchar-to-varchar casts

When using Optiq's jdbc support with a query condition on a varchar column, this sometimes fails because Optiq introduces varchar-to-varchar casts.

For example, using the foodmart sample with hsqldb, performing this query:

```
select "lname" from "customer"
where "lname" = 'this string is longer than 30 characters'
```

results in this query on hsqldb:

```
SELECT "lname"
FROM "foodmart"."customer"
WHERE CAST("lname" AS VARCHAR(40) CHARACTER SET "ISO-8859-1") = 'this string is longer than 30 characters'
```

which hsqldb does not understand:

```
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: CHARACTER required: ) : line: 3
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at net.hydromatic.optiq.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:139)
... 39 more
```

I actually experienced this problem in the context of HBase Phoenix, which has a similar problems with not understanding cast syntax.

Optiq introduces casts to the least restrictive type (= the longer string), therefore this issue only occurs when comparing with a string longer than the declared type of lname, which is varchar(30).

I guess that these casts have no additional value, especially since they cast to the least restrictive type, and they also obfuscate the generated sql, therefore I would propose to remove them.

The change proposed here fixes the problem and adds a test. It removes the cast in JdbcImplementor, thus when going from RexNodes to SqlNodes, but the cast will still be visible when requesting "explain plan".