[FRG-4] join across a foreign table and local table results in an assertion

[dynamobi-build]

[reporter="boris", created="Wed, 7 Dec 2005 18:01:49 -0500 (GMT-05:00)"]
(this assumes you have access to machine akela, with a sql server with existing data already loaded.)



create schema s;

set schema 's';



create table LOCATION(

LOCID char(2) primary key,

STREET varchar(50),

CITY varchar(20),

STATE char(2),

ZIP integer)

server sys_ftrs_data_server;



create foreign data wrapper test_jdbc library '../farrago/plugin/FarragoMedJdbc.jar' language java;



create server csv_server

foreign data wrapper test_jdbc

options(

    driver_class 'org.relique.jdbc.csv.CsvDriver',

    url 'jdbc:relique:csv:test/sql/shortreg',

    schema_name 'TESTDATA');



create schema csv_schema;

CREATE foreign table csv_schema.SHORTREG_LOCATION_SRC (

LOCID VARCHAR(2),

STREET VARCHAR(50),

CITY VARCHAR(20),

STATE VARCHAR(2),

ZIP INTEGER

)

server csv_server

options (table_name 'location');



INSERT INTO s.LOCATION

SELECT LOCID,STREET,CITY,STATE,ZIP

FROM csv_schema.SHORTREG_LOCATION_SRC;



import foreign schema "dbo" limit to ("bench1M") from server sql_server into s;



create server sql_server foreign data wrapper test_jdbc options(driver_class 'net.sourceforge.jtds.jdbc.Driver', url 'jdbc:jtds:sqlserver://AKELA.lucidera.com:1433;user=XX;password=XX', QUALIFYING_CATALOG_NAME 'BENCHMARK');



 import foreign schema "dbo" limit to ("bench1M") from server sql_server into s;



select "bench1M"."kseq",location.LOCID from "bench1M",location where "bench1M"."k100k"=location.zip;





OUTPUT is the following:

0: jdbc:luciddb:> select "bench1M"."kseq",location.LOCID from "bench1M",location where "bench1M"."k100k"=location.zip;

select "bench1M"."kseq",location.LOCID from "bench1M",location where "bench1M"."k100k"=location.zip;

Error: java.lang.AssertionError: Internal error: node could not be implemented: Subset#4.ITERATOR (state=,code=0)

[dynamobi-build]

[author="jvs", created="Thu, 8 Dec 2005 01:01:04 -0500 (GMT-05:00)"]
Fixed in eigenchange 4617. Wow, now we have awesome EII capabilities! I strongly recommend that you don't actually run that query unless you want to see smoke come out of your machine. (I didn't run it myself, I just used EXPLAIN PLAN to verify that the optimizer fix worked.)



Could you add a test before closing this? I don't know what the protocol for adding sqlserver-dependent tests is.

[dynamobi-build]

[author="boris", created="Thu, 8 Dec 2005 10:35:15 -0500 (GMT-05:00)"]
I think what I'll do is repro the problem with two csv_jdbc sources. that should have the same problem i would think.



will that be acceptable?

[dynamobi-build]

[author="boris", created="Thu, 8 Dec 2005 11:11:50 -0500 (GMT-05:00)"]
I tried with two foreign tables from csv_jdbc...



0: jdbc:luciddb:> select shortreg_emp_src.empno, shortreg_dept_src.deptno from shortreg_emp_src, shortreg_dept_src where shortreg_emp_src.deptno = shortreg_dept_src.deptno;

select shortreg_emp_src.empno, shortreg_dept_src.deptno from shortreg_emp_src, shortreg_dept_src where shortreg_emp_src.deptno = shortreg_dept_src.deptno;

Error: java.lang.AssertionError: Internal error: node could not be implemented: Subset#3.ITERATOR (state=,code=0)





I'll rebuild farrago with your changes, and see if it goes away.

[dynamobi-build]

[author="jvs", created="Fri, 9 Dec 2005 01:12:44 -0500 (GMT-05:00)"]
In eigenchange 4628, I fixed the bug that was causing a NullPointerException when attempting to restart the inner foreign table scan.



However, I haven't fixed the case where the JDBC driver doesn't support ResultSet.first(), which is what we currently use for restart. The fix would require us to know that first is not supported, and then either



(a) buffer the results and restart from the buffer



(b) restart by re-executing the query



Both fixes are a bit of work, and this functionality is not very important, so low priority. Workaround is to load the data in first and then query from a real table.

[dynamobi-build]

[author="jvs", created="Fri, 9 Dec 2005 01:13:19 -0500 (GMT-05:00)"]
Setting correct component.