Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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".
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/pull/182
Created by: bdumon
Labels:
Created at: Thu Mar 13 12:09:37 CET 2014
State: closed