[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-1283) ScrollableResults JoinFetch don't set child collection correctly after the second parent object
Strong Liu (JIRA)
noreply at atlassian.com
Wed Mar 30 06:19:11 EDT 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1283?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=41892#action_41892 ]
Strong Liu edited comment on HHH-1283 at 3/30/11 5:18 AM:
----------------------------------------------------------
Unfortunately, this patch will not work reliably unless the data is ordered by parent.
For the HQL in the test case, "select p from Parent p join fetch p.children c", Hibernate generates:
select
parent0_.name as name4_0_,
children1_.name as name5_1_,
children1_.parent_id as parent2_4_0__,
children1_.name as name0__
from
Parents parent0_
inner join
chlidren children1_
on parent0_.name=children1_.parent_id
Notice that there is no ORDER BY clause.
Unless the DB returns sorted data (e.g., due to an index or primary key), the resultset probably won't have rows for a each parent in a contiguous block.
The scrolling logic reads sequentially; when it reaches a new parent, it assumes it is finished with all rows for the current parent. If there are actually more rows for the current parent later in the resultset then that parent will be returned with fewer elements than it should contain. Later, when more rows for that parent are found, Hibernate will (again) return that same parent with the same incomplete set.
The testScroll() test case passes using the patch because the DB happens to return it in the right order.
It fails as described above using H2, if the data generation method is changed to:
{code}
private void insertTestData() {
Session s = openSession();
Transaction t = s.beginTransaction();
Child child_1_1 = new Child( "achild1-1");
Child child_1_2 = new Child( "ychild1-2");
Child child_1_3 = new Child( "dchild1-3");
Child child_2_1 = new Child( "bchild2-1");
Child child_2_2 = new Child( "cchild2-2");
Child child_2_3 = new Child( "zchild2-3");
s.save( child_1_1 );
s.save( child_2_1 );
s.save( child_1_2 );
s.save( child_2_2 );
s.save( child_1_3 );
s.save( child_2_3 );
s.flush();
Parent p1 = new Parent( "parent1" );
p1.addChild( child_1_1 );
p1.addChild( child_1_2 );
p1.addChild( child_1_3 );
s.save( p1 );
Parent p2 = new Parent( "parent2" );
p2.addChild( child_2_1 );
p2.addChild( child_2_2 );
p2.addChild( child_2_3 );
s.save( p2 );
t.commit();
s.close();
}
{code}
was (Author: stliu):
Unfortunately, this patch will not work reliably unless the data is ordered by parent.
For the HQL in the test case, "select p from Parent p join fetch p.children c", Hibernate generates:
select
parent0_.name as name4_0_,
children1_.name as name5_1_,
children1_.parent_id as parent2_4_0__,
children1_.name as name0__
from
Parents parent0_
inner join
chlidren children1_
on parent0_.name=children1_.parent_id
Notice that there is no ORDER BY clause.
Unless the DB returns sorted data (e.g., due to an index or primary key), the resultset probably won't have rows for a each parent in a contiguous block.
The scrolling logic reads sequentially; when it reaches a new parent, it assumes it is finished with all rows for the current parent. If there are actually more rows for the current parent later in the resultset then that parent will be returned with fewer elements than it should contain. Later, when more rows for that parent are found, Hibernate will (again) return that same parent with the same incomplete set.
The testScroll() test case passes using the patch because the DB happens to return it in the right order.
It fails as described above using H2, if the data generation method is changed to:
private void insertTestData() {
Session s = openSession();
Transaction t = s.beginTransaction();
Child child_1_1 = new Child( "achild1-1");
Child child_1_2 = new Child( "ychild1-2");
Child child_1_3 = new Child( "dchild1-3");
Child child_2_1 = new Child( "bchild2-1");
Child child_2_2 = new Child( "cchild2-2");
Child child_2_3 = new Child( "zchild2-3");
s.save( child_1_1 );
s.save( child_2_1 );
s.save( child_1_2 );
s.save( child_2_2 );
s.save( child_1_3 );
s.save( child_2_3 );
s.flush();
Parent p1 = new Parent( "parent1" );
p1.addChild( child_1_1 );
p1.addChild( child_1_2 );
p1.addChild( child_1_3 );
s.save( p1 );
Parent p2 = new Parent( "parent2" );
p2.addChild( child_2_1 );
p2.addChild( child_2_2 );
p2.addChild( child_2_3 );
s.save( p2 );
t.commit();
s.close();
}
> ScrollableResults JoinFetch don't set child collection correctly after the second parent object
> -----------------------------------------------------------------------------------------------
>
> Key: HHH-1283
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1283
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.1
> Environment: Hibernate 3.1
> Oracle10gR2
> Oracle JDBC Driver 10.2.0.1.0
> Sun JDK 1.5.0_06
> Spring 1.2.6
> Reporter: Masahiro Hirashima
> Attachments: fetch-scroll-collection-3.5.4.patch, ScrollableCollectionFetchingTest.java, ScrollableResults.zip
>
>
> ScrollableResults JoinFetch set child collection correctly at first parent object.
> but after the second parent object, It set only the first element of a child collection.
> I made the following tables.
> CREATE TABLE owners (
> id NUMBER(36, 0) NOT NULL PRIMARY KEY,
> first_name VARCHAR(30),
> last_name VARCHAR(30),
> address VARCHAR(255),
> city VARCHAR(80),
> telephone VARCHAR(20),
> version NUMBER(36, 0) DEFAULT 0
> );
> CREATE TABLE types (
> id NUMBER(36, 0) NOT NULL PRIMARY KEY,
> name VARCHAR(80),
> version NUMBER(36, 0) DEFAULT 0
> );
> CREATE TABLE pets (
> id NUMBER(36, 0) NOT NULL PRIMARY KEY,
> name VARCHAR(30),
> birth_date DATE,
> type_id NUMBER(36, 0),
> owner_id NUMBER(36, 0),
> version NUMBER(36, 0) DEFAULT 0
> );
> and inserted the following data.
> INSERT INTO owners VALUES (1, 'Betty', 'Davis', '638 Cardinal Ave.', 'Sun Prairie', '6085551749', 0);
> INSERT INTO owners VALUES (2, 'Eduardo', 'Rodriquez', '2693 Commerce St.', 'McFarland', '6085558763', 0);
> INSERT INTO pets VALUES (1, 'Leo', '2000-09-07', 1, 1, 0);
> INSERT INTO pets VALUES (2, 'Basil', '2002-08-06', 2, 1, 0);
> INSERT INTO pets VALUES (3, 'Rosy', '2001-04-17', 3, 2, 0);
> INSERT INTO pets VALUES (4, 'Jewel', '2000-03-07', 4, 2, 0);
> INSERT INTO pets VALUES (5, 'Iggy', '2000-11-30', 5, 2, 0);
> INSERT INTO types VALUES (1, 'cat', 0);
> INSERT INTO types VALUES (2, 'dog', 0);
> INSERT INTO types VALUES (3, 'lizard', 0);
> INSERT INTO types VALUES (4, 'snake', 0);
> INSERT INTO types VALUES (5, 'bird', 0);
> and I execute following code.
> String hqlJoinFetchTest =
> "from Owner owner " +
> "left outer join fetch owner.pets as pets " +
> "left outer join fetch pets.type " +
> "order by owner.firstName, owner.lastName";
> Query query = session.createQuery(hqlJoinFetchTest);
> ScrollableResults cursor = query.scroll();
> while ( cursor.next() ) {
> Owner owner = (Owner)cursor.get(0);
> System.out.println(owner);
> }
> result of this code is following.
> petclinic.domain.Owner at 15d616e[id=1,version=0,firstName=Betty,lastName=Davis,
> petclinic.domain.Pet at 136d9d8[id=2,version=0,name=Basil,birthDate=2002-08-06,
> petclinic.domain.PetType at 1be2893[id=2,version=0,name=dog]
> petclinic.domain.Pet at 14a75bb[id=1,version=0,name=Leo,birthDate=2000-09-07,
> petclinic.domain.PetType at 17779e3[id=1,version=0,name=cat]
> petclinic.domain.Owner at e3570c[id=2,version=0,firstName=Eduardo,lastName=Rodriquez
> petclinic.domain.Pet at 167e3a5[id=4,version=0,name=Jewel,birthDate=2000-03-07,
> petclinic.domain.PetType at 1926e90[id=4,version=0,name=snake]
> First owner object set collection collectly.
> but second owner object don't set second pet object(id=5) and third pet object(id=6).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list