Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

    Question Unanswered: self outer join and or condition

    Folks!

    Consider:

    create table LORD (
    ORD_NUMBER int,
    XETRA_FLAG char(1),
    BOOK char(1)
    )

    create table EXCS (
    ORD_NUMBER int,
    EX_NUMBER int
    )

    insert into LORD values(1, 'Y', 'N')
    insert into LORD values(1, 'N', 'N')

    insert into EXCS values(1, 1)

    select * from LORD l, EXCS e1, EXCS e2
    where
    e1.ORD_NUMBER = l.ORD_NUMBER
    and e2.ORD_NUMBER =* e1.ORD_NUMBER
    and (
    (
    ( e1.EX_NUMBER in (0,1)
    and e2.EX_NUMBER = 2
    )
    and
    XETRA_FLAG != 'Y'
    )
    )

    Result:

    ORD_NUMBER XETRA_FLAG BOOK ORD_NUMBER EX_NUMBER ORD_NUMBER EX_NUMBER
    ----------- ---------- ---- ----------- ----------- ----------- -----------
    1 N N 1 1 NULL NULL


    But if I add an or condition, the result is empty:

    select * from LORD l, EXCS e1, EXCS e2
    where
    e1.ORD_NUMBER = l.ORD_NUMBER
    and e2.ORD_NUMBER =* e1.ORD_NUMBER
    and (
    (
    ( e1.EX_NUMBER in (0,1)
    and e2.EX_NUMBER = 2
    )
    and
    XETRA_FLAG != 'Y'
    )
    or
    BOOK = 'Y'
    )

    Tested on 11.9.2.3, 12.0.0.4, 12.5.0.3
    It works correct on 11.0.3.3


    Has anyone encountered this bug/strange behaviour before?

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Seems to me that it's caused by a enw behavior to follow the ANSI SQL normalization regarding the outer join and the condition in the outer tables.

    On ASE 12.5.0.3, did you try the syntax

    Code:
    select * 
    from LORD 
    inner join EXCS e1 on e1.ORD_NUMBER = l.ORD_NUMBER)
    outer right join EXCS e2 on e2.ORD_NUMBER =* e1.ORD_NUMBER
    where
    (
      (e1.EX_NUMBER in (0,1) 
       and e2.EX_NUMBER = 2
      )
      and
      XETRA_FLAG != 'Y'
    )
    or BOOK = 'Y'
    Please, check... I've some touble with your parenthesis

  3. #3
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120
    Using the explicit join syntax solves this problem.
    It seems that the optimizer has problems with separating the join-clauses (e.g. e1.EX_NUMBER in (0,1) and e2.EX_NUMBER = 2) from the filtering clauses (e.g. XETRA_FLAG != 'Y').

    So I have to get accustomed to the join syntax, shouldn't be so hard.

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Wink

    Please, note that it's not a bug, but a ANSI SQL compliance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •