This discussion is archived
8 Replies Latest reply: Sep 9, 2013 7:50 AM by 89477557-c20c-4e78-9ca0-1792fbbee003 RSS

Query Bug

981070 Newbie
Currently Being Moderated
The following query does not execute correctly in XE 11g R2.

select
     "Join1".C1--,
     --case when "Join1".C1 is null then 'A' else 'B' end as C2
FROM "Parents" "Extent1"
     LEFT OUTER JOIN (SELECT
          "Extent2"."Id" AS "Id1",
          "Project1"."Id" AS "Id2",
          "Project1".C1
          FROM "Children" "Extent2"
          LEFT OUTER JOIN (SELECT
               "Extent3"."Id",
               1 AS C1
               FROM "SubChildren" "Extent3" ) "Project1" ON "Extent2"."Id" = "Project1"."Id" ) "Join1" ON "Extent1"."ChildId" = "Join1"."Id1"
     WHERE ("Extent1"."Id" = 1)

I'm executing this query on a database where the "Parents" table contains 1 record with an "Id" of 1 and a "ChildId" of 1, the "Children" table contains 1 record with an "Id" of 1, and the "SubChildren" table is empty.

If I execute the query as-is, it returns a single row where C1 is null. If I un-comment the case statement, the query returns a single row where C1 is 1 and C2 is 'B'.

Is there any easy way to work around this without having to re-write the query? I'm using a tool that is auto-generating my SQL statements, so I don't think it will be possible to change it.

Thanks,
Nathan
  • 1. Re: Query Bug
    clcarter Expert
    Currently Being Moderated
    What tool is this that double quotes entities and attributes? They can introduce errors by making the object names case sensitive.

    "Emp" != "EMP" != "EMp"

    But EMP == emp == Emp much easier to be clear about which object is referenced.

    The ... FROM "Parents" "Extent1" ... clause creates an alias "Extent1" for "Parents", also confusing, usually an alias is shorthand to make it easier to fully qualify an object, i.e.:
    select p.id, p.fname [, ...] from parent p
    Some sample data will be helpful. Either the table DDL and inserts, or a 'with' query to make it clear what the data and relation(s) are supposed to be, like this:
    with parent as ( select 1 id, 1 childid from dual
                     union
                     select 2,    NULL from dual
                     union
                     select 3,    NULL from dual ),
         child as ( select 1 id, 0 subchildid from dual )
    select p.id, p.childid, c.id  from parent p
     left outer join child c
        on p.childid = c.id
    If your subchildren table is empty ... how is that relevant to whether or not the query works?
  • 2. Re: Query Bug
    981070 Newbie
    Currently Being Moderated
    Here's the DDL to generate the schema:

    CREATE TABLE "Parents" (
    "Id" NUMBER(10) NOT NULL,
    "ChildId" NUMBER(10) NOT NULL,
    PRIMARY KEY ("Id")
    )

    CREATE TABLE "Children" (
    "Id" NUMBER(10) NOT NULL,
    PRIMARY KEY ("Id")
    )

    CREATE TABLE "SubChildren" (
    "Id" NUMBER(10) NOT NULL,
    PRIMARY KEY ("Id")
    )
    ALTER TABLE "Parents"
    ADD CONSTRAINT "FK_Parents_Children_ChildId" FOREIGN KEY ("ChildId") REFERENCES "Children" ("Id")
    ON DELETE CASCADE
    ALTER TABLE "SubChildren"
    ADD CONSTRAINT "FK_SubChildren_Children_Id" FOREIGN KEY ("Id") REFERENCES "Children" ("Id")

    Here's the SQL to add the data:
    insert into "Children" values (1);
    insert into "Parents" values (1,1);

    I know the SQL is ugly, but like I said, its generated by a tool and I don't have much control over it. The fact is that this query seems to do something totally wrong in Oracle XE while it works as expected in Oracle Standard
  • 3. Re: Query Bug
    clcarter Expert
    Currently Being Moderated
    its generated by a tool and I don't have much control over it.
    I'd suggest using a different tool. Tools are supposed to aid, not hinder or add ambiguity to tasks.

    And as far as using outer joins, if the entity relationships require that a model refinement is usually called for.
    works as expected in Oracle Standard
    Which version? 11g? 11gR2? or R1? 10g?
  • 4. Re: Query Bug
    981070 Newbie
    Currently Being Moderated
    I'm testing this in Oracle Standard 11G R2 and Oracle XE 11G R2. The query gives the correct result in Standard, and gives an incorrect result in XE.
  • 5. Re: Query Bug
    clcarter Expert
    Currently Being Moderated
    That query shows the same result set for me on linux-x64 XE 11gR2 as it does on an AIX-x64 11gR2, 11.2.0.3 EE.

    Don't have any SE versions to try it on.

    Try a different query writing tool. Even MSAccess, if you must, it does a fine job for what it was intended. I frequently have trouble remembering the ANSI join syntax(es) and Access can GUI it for you.

    Or SQLDeveloper is a free tool as well, see http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
  • 6. Re: Query Bug
    981070 Newbie
    Currently Being Moderated
    I should have specified that I'm running Oracle XE 11G R2 in Windows 7. I'll try it in Linux and see if it works there.
  • 7. Re: Query Bug
    981070 Newbie
    Currently Being Moderated
    I just confirmed that the exact same problem occurs in Oracle 11G XE on Linux also. This does not appear to be a problem that is unique to Windows.
  • 8. Re: Query Bug
    89477557-c20c-4e78-9ca0-1792fbbee003 Newbie
    Currently Being Moderated

    Hello,

    I'd like to confirm what 981070 state about oracle 11g xe. I get the same problem with query generated by EntityFramework.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points