InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

Philip

Thank you for reporting this bug, and thank you for taking the trouble to
put a script together to illustrate the problem - it makes my job a lot
easier.

This bug will be fixed in 3.20 beta 1 which will be available shortly. If
this is causing you serious problems then let me know and I'll see if I can
get a patch to you faster.

Regards

Peter Hearty
Lutris Technologies UK Ltd.
-----Original Message-----
From: Phillip Baird <phillip.baird@clear.net.nz>
To: instantdb@enhydra.org <instantdb@enhydra.org>
Date: 16 July 2000 06:01
Subject: InstantDB: Exception with LEFT OUTER JOIN


Hi,

I've just started using InstantDB (3.14) and have come across a problem with
the following query...

SELECT country.id, country.name, city.id, city.name FROM country LEFT OUTER
JOIN city ON country.id=city.country_id;

This results in the following exception...

java.sql.SQLException: Error fetching row in joined table:
java.sql.SQLException: IO problem: java.io.IOException: Negative seek
offsetNegative seek offset
        at org.enhydra.instantdb.db.SQLProg.execute(SQLProg.java:276)
        at
org.enhydra.instantdb.jdbc.idbStatement.execute(idbStatement.java:235)
        at
org.enhydra.instantdb.jdbc.idbStatement.executeQuery(idbStatement.java:91)
        at org.enhydra.instantdb.SampleThread.run(ScriptTool.java:542)
        at java.lang.Thread.run(Unknown Source)

If I add a WHERE clause everything works as expected e.g. SELECT country.id,
country.name, city.id, city.name FROM country LEFT OUTER JOIN city ON
country.id=city.country_id WHERE country.id = 1;

However other WHERE conditions fail, eg country.id >= 1

Here is a script that reproduces the problem...

; First load the JDBC driver and open a database.
d org.enhydra.instantdb.jdbc.idbDriver;
o jdbc:idb=sample.prp;

; dump SQL and results to "trace.log"
e SET EXPORT "trace.log" TRACE 2 CONSOLE;

; Create the tables
e DROP TABLE country;
e CREATE TABLE country (
  id     int not null,
  name   varchar(200) not null );
e CREATE UNIQUE INDEX country_pk ON country ( id );

e DROP TABLE city;
e CREATE TABLE city (
  id         int not null,
  country_id int not null,
  name       varchar(200) not null );
e CREATE UNIQUE INDEX city_pk ON city ( id );

; Create a country with no cities.
e INSERT INTO country (id,name) VALUES (1,"USA");

; Returns single country with nulls for city.id and city.name - as expected.
q SELECT country.id, country.name, city.id, city.name FROM country LEFT
OUTER JOIN city ON country.id=city.country_id WHERE country.id = 1;

; Same as above but without the WHERE clause.  Result should be the same.
Creates java.io exception!!
q SELECT country.id, country.name, city.id, city.name FROM country LEFT
OUTER JOIN city ON country.id=city.country_id;

c close;


Hope someone can help.

Kind regards,

Phillip Baird


-----------------------------------------------------------------------------
To unsubscribe from this mailing list, send email to majordomo@enhydra.org
with the text "unsubscribe instantdb" in the body of the email.
If you have other questions regarding this mailing list, send email to
the list admin at owner-instantdb@enhydra.org.