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

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13561556#comment-13561556 ] 

Knut Anders Hatlen commented on DERBY-6051:
-------------------------------------------

Hi Igor,

The second query has (mfs1_.sys_end is null) in its WHERE clause, so it sounds correct to me that it returns 0 rows if all records have non-null values in sys_end. The predicates in the WHERE clause are used to filter the rows after the outer join has been performed, and since it doesn't match any of the rows returned by the outer join, no rows should be returned.

In the first query, it's part of the join condition and used in the actual outer join operation, not to filter the rows returned by it, so in that case the special outer join semantics come into play, and more rows can be returned.
                
> 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