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

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

     [ https://issues.apache.org/jira/browse/DERBY-6051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Vlasov Igor closed DERBY-6051.
------------------------------

    Resolution: Not A Problem
    
> 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