Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Single column foreign key and left join - result depends from select column list

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
Answers
-
Looks like a bug of deferred constraints
Regards,
-
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]
-
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.
-
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". -
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
-
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.....
-
Interesting thing is that it works fine with composite foreign constraints ....
-
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
-
Table elimination is only applicable for single column foreign key constraints.
Regards
Jonathan Lewis
-
Table elimination not implemented in that version of Oracle. (I think it might not have appeared until 10.2).
Regards
Jonathan Lewis