Bug with Take() and Contains()

Bug with Take() and Contains()

Postby 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.
a.m.sidorenko
 
Posts: 9
Joined: Fri 21 Apr 2017 05:23

Re: Bug with Take() and Contains()

Postby Shalex » Mon 24 Apr 2017 17:40

We have reproduced the issue and are investigating it. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44

Re: Bug with Take() and Contains()

Postby 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: http://forums.devart.com/viewtopic.php?f=1&t=35436.
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle