dynamobi/dynamobi — Issue #217: [LDB-104] join on 2 foreign tables in oracle gives error (and does not use hash join) 
Author: dynamobi-build
Created: 2011-12-30 02:22:32+00:00
State: open
Labels: LDB, LHX

[reporter="schoi", created="Fri, 2 Jun 2006 15:30:54 -0500 (GMT-05:00)"]
repro steps:

-------------------

create server orcl_server2

foreign data wrapper orcl_jdbc

options(

url 'jdbc:oracle:thin:@akela.lucidera.com:1521:XE',

user_name 'lucidwh',

password 'lucidwh',

driver_class 'oracle.jdbc.driver.OracleDriver'

);

select * from orcl_server2."HR"."EMPLOYEES" as e, orcl_server2."HR"."DEPARTMENTS" as d where d."DEPARTMENT_ID" = e."DEPARTMENT_ID";

=> output:

Error: Invalid operation for forward only resultset : first (state=,code=17075)

java.sql.SQLException: Invalid operation for forward only resultset : first

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)

at oracle.jdbc.driver.BaseResultSet.first(BaseResultSet.java:85)

at org.eigenbase.runtime.ResultSetTupleIter.restart(ResultSetTupleIter.java:73)

at net.sf.farrago.runtime.FarragoTransformImpl.restart(FarragoTransformImpl.java:117)

at net.sf.farrago.fennel.FennelStorage.tupleStreamFetch(Native Method)

at net.sf.farrago.fennel.FennelStreamGraph.fetch(FennelStreamGraph.java:175)

at net.sf.farrago.runtime.FennelTupleIter.populateBuffer(FennelTupleIter.java:110)

at net.sf.farrago.runtime.FennelAbstractTupleIter.fetchNext(FennelAbstractTupleIter.java:99)

at net.sf.farrago.dynamic.stmt13849.ExecutableStmt$2.fetchNext(Unknown Source)

at org.eigenbase.runtime.TupleIterResultSet.next(TupleIterResultSet.java:90)

at net.sf.farrago.runtime.FarragoTupleIterResultSet.next(FarragoTupleIterResultSet.java:97)

at sqlline.SqlLine$BufferedRows.(Unknown Source)

explain plan for select * from orcl_server2."HR"."EMPLOYEES" as e, orcl_server2."HR"."DEPARTMENTS" as d where d."DEPARTMENT_ID" = e."DEPARTMENT_ID";

+-------------------------------------------------------------------------------------------------------------+

| column0 |

+-------------------------------------------------------------------------------------------------------------+

| IterCalcRel(expr#0..14=[{inputs}], expr#15=[Reinterpret($t11)], expr#16=[Reinterpret($t10)], expr#17=[=($t1 |

| FennelToIteratorConverter |

| FennelCartesianProductRel(leftouterjoin=[false])|

| IteratorToFennelConverter |

| ResultSetToFarragoIteratorConverter |

| MedJdbcQueryRel(foreignSql=[SELECT * |

| FROM "HR"."EMPLOYEES"]) |

| IteratorToFennelConverter |

| ResultSetToFarragoIteratorConverter |

| MedJdbcQueryRel(foreignSql=[SELECT * |

| FROM "HR"."DEPARTMENTS"]) |

+-------------------------------------------------------------------------------------------------------------+