Bug with Take() and Contains()

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
a.m.sidorenko
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Bug with Take() and Contains()

Post by a.m.sidorenko » Fri 21 Apr 2017 06:32

Hi!

Today we found a bug in dotConnect for Oracle (9.2.220.0, 9.3.230).
It appears in wrong positioning of "FETCH FIRST" clause in a query when using Take() in EF.

Steps to reproduce.
Create table and fill it with data

Code: Select all

CREATE TABLE SAMPLE_REPORT
(
    QUEUE_NAME NVARCHAR2(50) NOT NULL,
    CREATE_DATE DATE NOT NULL,
    VALUE INT NOT NULL
);
ALTER TABLE SAMPLE_REPORT ADD CONSTRAINT SAMPLE_REPORT_pk PRIMARY KEY (QUEUE_NAME, CREATE_DATE);

INSERT INTO SAMPLE_REPORT VALUES ('Q1', to_date('2017.04.19 10:00:00', 'YYYY.MM.DD HH24:MI:SS'), 1);
INSERT INTO SAMPLE_REPORT VALUES ('Q1', to_date('2017.04.19 10:05:00', 'YYYY.MM.DD HH24:MI:SS'), 2);
INSERT INTO SAMPLE_REPORT VALUES ('Q1', to_date('2017.04.19 10:10:00', 'YYYY.MM.DD HH24:MI:SS'), 3);
INSERT INTO SAMPLE_REPORT VALUES ('Q1', to_date('2017.04.19 10:15:00', 'YYYY.MM.DD HH24:MI:SS'), 4);
INSERT INTO SAMPLE_REPORT VALUES ('Q1', to_date('2017.04.19 10:20:00', 'YYYY.MM.DD HH24:MI:SS'), 5);

INSERT INTO SAMPLE_REPORT VALUES ('Q2', to_date('2017.04.19 10:00:00', 'YYYY.MM.DD HH24:MI:SS'), 1);
INSERT INTO SAMPLE_REPORT VALUES ('Q2', to_date('2017.04.19 10:05:00', 'YYYY.MM.DD HH24:MI:SS'), 2);
INSERT INTO SAMPLE_REPORT VALUES ('Q2', to_date('2017.04.19 10:10:00', 'YYYY.MM.DD HH24:MI:SS'), 3);
INSERT INTO SAMPLE_REPORT VALUES ('Q2', to_date('2017.04.19 10:15:00', 'YYYY.MM.DD HH24:MI:SS'), 4);
INSERT INTO SAMPLE_REPORT VALUES ('Q2', to_date('2017.04.19 10:20:00', 'YYYY.MM.DD HH24:MI:SS'), 5);
Now we want to get last 3 records from our table for each QUEUE_NAME.

Code: Select all

var reports = db.SampleReports; // db is DbContext
var timesForReport = reports.Select(x => x.CreateDate)
    .Distinct()
    .OrderByDescending(x => x)
    .Take(3);
var result = reports.Where(x => timesForReport.Contains(x.CreateDate))
    .OrderByDescending(x => x.CreateDate)
    .ThenBy(x => x.QueueName)
    .GroupBy(x => x.QueueName)
    .ToList();
In result, we have groups of 5 rows instead of 3 rows expected.

Resulting query with DevArt provider is:

Code: Select all

SELECT
"Project7".C1,
"Project7".QUEUE_NAME,
"Project7".C2,
"Project7".QUEUE_NAME1,
"Project7".CREATE_DATE,
"Project7".VALUE
FROM ( SELECT
 "Project4".QUEUE_NAME,
 "Project4".C1,
 "Extent3".QUEUE_NAME AS QUEUE_NAME1,
 "Extent3".CREATE_DATE,
 "Extent3".VALUE,
 CASE WHEN "Extent3".VALUE IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C2
 FROM   (SELECT 
  "Distinct2".QUEUE_NAME,
  1 AS C1
  FROM ( SELECT DISTINCT 
   "Extent1".QUEUE_NAME
   FROM SAMPLE_REPORT "Extent1"
   WHERE  EXISTS (SELECT 
    1 AS C1
    FROM ( SELECT DISTINCT 
     "Extent2".CREATE_DATE
     FROM SAMPLE_REPORT "Extent2"
    )  "Distinct1"
    WHERE "Distinct1".CREATE_DATE = "Extent1".CREATE_DATE
    ORDER BY "Distinct1".CREATE_DATE DESC
    FETCH FIRST 3 ROWS ONLY
   )
  )  "Distinct2" ) "Project4"
 LEFT OUTER JOIN SAMPLE_REPORT "Extent3" ON ( EXISTS (SELECT
  1 AS C1
  FROM ( SELECT DISTINCT 
   "Extent4".CREATE_DATE
   FROM SAMPLE_REPORT "Extent4"
  )  "Distinct3"
  WHERE "Distinct3".CREATE_DATE = "Extent3".CREATE_DATE
  ORDER BY "Distinct3".CREATE_DATE DESC
  FETCH FIRST 3 ROWS ONLY
 )) AND ("Project4".QUEUE_NAME = "Extent3".QUEUE_NAME)
)  "Project7"
ORDER BY "Project7".QUEUE_NAME ASC, "Project7".C2 ASC

When using Oracle managed provider the query is:

Code: Select all

SELECT
"Project7"."C1" AS "C1",
"Project7"."QUEUE_NAME" AS "QUEUE_NAME", "Project7"."C2" AS "C2", "Project7"."QUEUE_NAME1" AS "QUEUE_NAME1", "Project7"."CREATE_DATE" AS "CREATE_DATE", "Project7"."VALUE" AS "VALUE"
FROM ( SELECT
 "Project4"."QUEUE_NAME" AS "QUEUE_NAME",  "Project4"."C1" AS "C1",  "Extent3"."QUEUE_NAME" AS "QUEUE_NAME1",  "Extent3"."CREATE_DATE" AS "CREATE_DATE",  "Extent3"."VALUE" AS "VALUE",  CASE WHEN ("Extent3"."VALUE" IS NULL) THEN NULL ELSE 1 END AS "C2"
 FROM   (SELECT 
  "Distinct2"."QUEUE_NAME" AS "QUEUE_NAME",
  1 AS "C1"
  FROM ( SELECT DISTINCT 
   "Extent1"."QUEUE_NAME" AS "QUEUE_NAME"
   FROM "SAMPLE_REPORT" "Extent1"
   WHERE ( EXISTS (SELECT 
    1 AS "C1"
    FROM ( SELECT * 
     FROM ( 
     SELECT "Distinct1"."CREATE_DATE" AS "CREATE_DATE"
     FROM ( SELECT DISTINCT 
      "Extent2"."CREATE_DATE" AS "CREATE_DATE"
      FROM "SAMPLE_REPORT" "Extent2"
     )  "Distinct1"
     ORDER BY "Distinct1"."CREATE_DATE" DESC
     )
     WHERE (ROWNUM <= (3) )
    )  "Limit1"
    WHERE ("Limit1"."CREATE_DATE" = "Extent1"."CREATE_DATE")
   ))
  )  "Distinct2" ) "Project4"
 LEFT OUTER JOIN "SAMPLE_REPORT" "Extent3" ON ( EXISTS (SELECT
  1 AS "C1"
  FROM ( SELECT * 
   FROM ( 
   SELECT "Distinct3"."CREATE_DATE" AS "CREATE_DATE"
   FROM ( SELECT DISTINCT 
    "Extent4"."CREATE_DATE" AS "CREATE_DATE"
    FROM "SAMPLE_REPORT" "Extent4"
   )  "Distinct3"
   ORDER BY "Distinct3"."CREATE_DATE" DESC
   )
   WHERE (ROWNUM <= (3) )
  )  "Limit2"
  WHERE ("Limit2"."CREATE_DATE" = "Extent3"."CREATE_DATE")
 )) AND ("Project4"."QUEUE_NAME" = "Extent3"."QUEUE_NAME")
)  "Project7"
ORDER BY "Project7"."QUEUE_NAME" ASC, "Project7"."C2" ASC
Note, that with oracle's provider uses a separate SELECT subquery for a limit condition, and DevArt provider put them in one query.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Bug with Take() and Contains()

Post by Shalex » Mon 24 Apr 2017 17:40

We have reproduced the issue and are investigating it. We will notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Bug with Take() and Contains()

Post by Shalex » Fri 26 May 2017 17:04

The bug with paging in subselect within EXISTS generated for Oracle 12c using EF1/EF4/EF5/EF6 is fixed: viewtopic.php?f=1&t=35436.

Post Reply