Forum Stats

  • 3,839,017 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

Categories

Single column foreign key and left join - result depends from select column list

FilipFry
FilipFry Member Posts: 60
edited Oct 20, 2013 9:41AM in SQL & PL/SQL

create table test_fk(c1 date primary key deferrable);

create table test_fk1(b1 date references test_fk(c1) deferrable);

alter session set constraints=deferred;

insert into test_fk1 values(sysdate);

insert into test_fk1 values(sysdate);

insert into test_fk1 values(sysdate);

SELECT t1.C1, t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

C1                     B1                  

---------------------- ----------------------

                       18-OCT-2013 16.38.43  

                       18-OCT-2013 16.38.48  

                       18-OCT-2013 16.38.46 

SELECT t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

no rows selected

Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • 34MCA2K2
    34MCA2K2 Member Posts: 442

    Looks like a bug of deferred constraints

    Regards,

    · Share on TwitterShare on Facebook
  • Greg Spall
    Greg Spall Member Posts: 972
    edited Oct 18, 2013 11:08AM

    Your test case is incomplete.

    Those 2 queries are identical.

    When I run it, I get the "now rows selected" result both times.

    Also note ... it's probably due to your deferred constraints, and the fact that you do not have a parent. So it's going to really confuse your result sets.

    Try issuing a commit after those inserts, and you'll see the "real" problem

    [edit]

    correction - nm, looked over those 2 statements closely, and still missed the select portion

    got it!

    Still think you should issue a commit before running those queries, can't expect serious results when the data is "incomplete"

    [/edit]

    · Share on TwitterShare on Facebook
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    edited Oct 18, 2013 11:05AM

    This looks like a bug to me:

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

    PL/SQL Release 11.2.0.1.0 - Production

    "CORE 11.2.0.1.0 Production"

    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

    {code}

    explain plan for

    SELECT t.b1     FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

    {code}

    result

    {code}

    Plan hash value: 2082422233

    -------------------------------------------------------------------------------

    | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

    -------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT   |          |     1 |     9 |     0   (0)|          |

    |*  1 |  FILTER            |          |       |       |            |          |

    |   2 |   TABLE ACCESS FULL| TEST_FK1 |     3 |    27 |     3   (0)| 00:00:01 |

    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter(NULL IS NOT NULL)

    Note

    -----

       - dynamic sampling used for this statement (level=2)

    {code}

    and we can clearly see that filter null is not null is never true.

    Keith Jamieson
    · Share on TwitterShare on Facebook
  • DrabJay
    DrabJay Member Posts: 15

    An example of the "mutating database" feature of Oracle. You are querying from a database that is in an invalid state - according to the constraints - and therefore any query on the database in this state has an indeterminate result set.


    Perhaps Oracle should introduce a multiple assignment operator as detailed in Darwen and Date's Third Manifesto so we would no longer need deferrable constraints and you could no longer issue queries against a mutating database. While this operator is not available this kind of issue will always be a problem, querying the database in the middle of a "transaction".

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond

    You don't mention a version - but I've reproduced your result in 11.2.0.4, but find it fixed in 12.1.0.1.

    I'd guess that It's likely to appear in the bug list under "table elimination", which probably shouldn't be allowed for deferrable referential integrity constraints.

    Regards

    Jonathan Lewis


    Jonathan Lewis
    · Share on TwitterShare on Facebook
  • FilipFry
    FilipFry Member Posts: 60

    In order to improve performances we defer constraints, issue 50 000+ DML commands and then we are looking for faulty data before trying to commit. We need to see faulty data because unsuccessful commit will rollback every thing and we will not know what went wrong. DMLs are generated by some graphic editor which makes mistakes when generating deltas......I know that this can be achieved with alter table validate constraint exceptions into ......but that disables constraints and that crepes me out.....

    · Share on TwitterShare on Facebook
  • FilipFry
    FilipFry Member Posts: 60

    Interesting thing is that it works fine with composite foreign constraints ....

    · Share on TwitterShare on Facebook
  • Priyasagi
    Priyasagi Member Posts: 429

    9i results like this,

    SELECT t1.C1, t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

    C1        B1

    --------- ---------

              19-OCT-13

              19-OCT-13

              19-OCT-13

    SELECT t.B1 FROM TEST_FK1 t LEFT JOIN TEST_FK t1 ON (t1.C1 = t.B1) WHERE t1.C1 IS NULL AND t.B1 IS NOT NULL;

    B1

    ---------

    19-OCT-13

    19-OCT-13

    19-OCT-13

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond

    Table elimination is only applicable for single column foreign key constraints.

    Regards

    Jonathan Lewis


    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Oct 20, 2013 9:39AM

    Table elimination not implemented in that version of Oracle. (I think it might not have appeared until 10.2).

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
This discussion has been closed.