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);
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();
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