[Hibernate-JIRA] Created: (HHH-5633) AbstractEmptinessExpression producing incorrect sql for innerselect
6:53 p.m.
AbstractEmptinessExpression producing incorrect sql for innerselect
-------------------------------------------------------------------
Key: HHH-5633
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.5.0-Final
Environment: eclipse gallileo windows 7 32 bit mysql
Reporter: minh huon
Attachments: hibernate bug.zip
Attached are the domain objects that are used in question for the query. I cannot see a
way to explicitly give an alias to the Restrictions.isNotEmpty when the query produce on
the inside of the isNotEmpty is a derived query table.
Looking at the code for version 3.5.0 AbstractEmptinessExpression like number 68:
{code}
String innerSelect = "(select 1 from " + collectionPersister.getTableName()
+ " where "
+ new ConditionFragment().setTableAlias( sqlAlias ).setCondition( ownerKeys,
collectionKeys ).toFragmentString()
+ ")";
{code}
The string creation seems to need an alias at the end of the creation to be correct my
mysql syntax.
The test case "shipmentQuery" is the one failing to produce the right query.
I'd create a patch but I'm not sure if that is exactly the problem or an I doing
something wrong.
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:44 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5633) AbstractEmptinessExpression producing incorrect sql for innerselect
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633?page=c...
]
minh huon commented on HHH-5633:
--------------------------------
Further examination of the produced query lends me to believe the construction is wrong or
I'm creating an incorrect Criteria object. In my test I want a list of Shipment
objects where its dnas list is not empty. In reality, you'd never have a Shipment
object without an associated DNA type object but I still think the query production is
incorrect and even with adding an alias to the inner query, the query produced doesn't
make sense because its not filtering on the Shipment id of the outer query so in essence,
if it was possible to have a Shipment object without any dnas, then the query would return
false positives because of the *{color:red}exists (select 1 ...){color:red}* doesn't
check for a match but only checks for existence if that makes any sense.
AbstractEmptinessExpression producing incorrect sql for innerselect
-------------------------------------------------------------------
Key: HHH-5633
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.5.0-Final
Environment: eclipse gallileo windows 7 32 bit mysql
Reporter: minh huon
Attachments: hibernate bug.zip
Attached are the domain objects that are used in question for the query. I cannot see a
way to explicitly give an alias to the Restrictions.isNotEmpty when the query produce on
the inside of the isNotEmpty is a derived query table.
Looking at the code for version 3.5.0 AbstractEmptinessExpression like number 68:
{code}
String innerSelect = "(select 1 from " + collectionPersister.getTableName()
+ " where "
+ new ConditionFragment().setTableAlias( sqlAlias ).setCondition( ownerKeys,
collectionKeys ).toFragmentString()
+ ")";
{code}
The string creation seems to need an alias at the end of the creation to be correct my
mysql syntax.
The test case "shipmentQuery" is the one failing to produce the right query.
I'd create a patch but I'm not sure if that is exactly the problem or an I doing
something wrong.
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:05 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5633) AbstractEmptinessExpression producing incorrect sql for innerselect
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633?page=c...
]
minh huon commented on HHH-5633:
--------------------------------
I was able to get the correct query to be produced with the following test:
{code}
@SuppressWarnings("unchecked")
@Test
public void shipmentQuery() {
Criteria crit = getSession().createCriteria(Shipment.class, "shipment");
crit.createAlias("shipment.dnas", "dna")
.add(Restrictions.isNotNull("dna.shipment"));
List<Shipment> shipments = crit.list();
if(!CollectionUtils.isEmpty(shipments)) {
Assert.assertTrue(shipments.size() > 0);
for(Shipment shipment : shipments) {
Assert.assertTrue(!CollectionUtils.isEmpty(shipment.getDnas()));
}
} else {
Assert.fail();
}
}
{code}
This produces the query:
{code}
select
this_.id as id4_1_,
this_.dateCreated as dateCrea2_4_1_,
this_.trackingData as tracking3_4_1_,
this_.trackingDataCreationDate as tracking4_4_1_,
dna1_.id as id0_0_,
dna1_.sequence as sequence0_0_,
dna1_.shipment_id as shipment3_0_0_,
dna1_.oligo_id as oligo1_1_0_,
dna1_.template_id as template2_1_0_,
dna1_.clazz_ as clazz_0_
from
dnatest.Shipment this_
inner join
(
select
id,
sequence,
shipment_id,
oligo_id,
template_id,
1 as clazz_
from
dnatest.SequencingRead
union
select
id,
sequence,
shipment_id,
null as oligo_id,
null as template_id,
2 as clazz_
from
dnatest.Oligo
union
select
id,
sequence,
shipment_id,
null as oligo_id,
null as template_id,
3 as clazz_
from
dnatest.Template
) dna1_
on this_.id=dna1_.shipment_id
where
dna1_.shipment_id is not null
{code}
Which is correct because now the filtering is happening on Shipment.
I still believe my original bug post is still valid as the inner select needs to be alias
to be correct for mysql, but how I wrote the original Criteria was incorrect due what was
outputted in sql.
AbstractEmptinessExpression producing incorrect sql for innerselect
-------------------------------------------------------------------
Key: HHH-5633
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.5.0-Final
Environment: eclipse gallileo windows 7 32 bit mysql
Reporter: minh huon
Attachments: hibernate bug.zip
Attached are the domain objects that are used in question for the query. I cannot see a
way to explicitly give an alias to the Restrictions.isNotEmpty when the query produce on
the inside of the isNotEmpty is a derived query table.
Looking at the code for version 3.5.0 AbstractEmptinessExpression like number 68:
{code}
String innerSelect = "(select 1 from " + collectionPersister.getTableName()
+ " where "
+ new ConditionFragment().setTableAlias( sqlAlias ).setCondition( ownerKeys,
collectionKeys ).toFragmentString()
+ ")";
{code}
The string creation seems to need an alias at the end of the creation to be correct my
mysql syntax.
The test case "shipmentQuery" is the one failing to produce the right query.
I'd create a patch but I'm not sure if that is exactly the problem or an I doing
something wrong.
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5010
days inactive
5010
days old
hibernate-issues@lists.jboss.org
2 comments
1 participants
participants (1)
-
minh huon (JIRA)