[jira] [Created] (DERBY-6051) SQL left outer join Error

["Vlasov Igor (JIRA)" <ji...@apache.org>]

Vlasov Igor created DERBY-6051:
----------------------------------

             Summary: SQL left outer join Error
                 Key: DERBY-6051
                 URL: https://issues.apache.org/jira/browse/DERBY-6051
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.9.1.0
         Environment: Win7 jdbc
            Reporter: Vlasov Igor


Hello. I have 2 tables: DOMAIN -1:n- morefld.

Then I run two queries:
I)select  *    from
        DOMAIN domain0_ 
    left outer join
        morefld mfs1_ 
       on (domain0_.dm_id=mfs1_.dm_id and mfs1_.sys_end is null)
    where
        domain0_.dm_id=13       
    order by
        mfs1_.mf_order asc
and 
II) select
       *
    from
        DOMAIN domain0_ 
    left outer join
        morefld mfs1_ 
            on domain0_.dm_id=mfs1_.dm_id 
    where
        domain0_.dm_id=13 
        and (
            mfs1_.sys_end is null
        ) 
    order by
        mfs1_.mf_order asc 


The first query works as expected and alway return valid result.

The second query is generated by ORM and it has strange behaviour.

1) if I have 2 records in MOREFLD with NULL .sys_end it returns 2 rows  -  GOOD
2) if I have 2 records in MOREFLD with one NULL  in sys_end and anoter NOT NULL sys_end it returns 1 row  -  GOOD
3) if  have 2 records in MOREFLD with NOT NULL in sys_end it returns 0 rows  -  ERROR

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira