[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5633) AbstractEmptinessExpression producing incorrect sql for innerselect

[minh huon (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38622#action_38622 ] 

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira