Max

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Max

Post by PatrikAhlquist » Thu 08 May 2014 10:13

Using latest version of dotConnect for Oracle (8.3.146) and Entity Framework 6.1.

Image

maxHylsplanBana should be (and was in earlier versions) 3, not 1.
Hylsplan contains 4 collections of 3 Hylsplan_Bana.

hylsplan.Max generates this SQL:

Code: Select all

SELECT 
"GroupBy2".A1 AS C1
FROM ( SELECT Max((SELECT Count(1) AS A1
		FROM PL.HYLSPLAN_BANA "Extent2"
		WHERE ("Extent1".ID = "Extent2".HYLSPLAN_ID) AND ROWNUM <= 1)) AS A1
	FROM PL.HYLSPLAN "Extent1"
	WHERE ("Extent1".STATUS = 'N') AND ("Extent1".MASKIN_NR = 1)
)  "GroupBy2";
Which gives the result
C1
1

hylsplan.ToList generates this:

Code: Select all

SELECT 
"Project2".C2 AS C1,
"Project2".ID,
"Project2".STATUS,
"Project2".ORDNINGS_NR,
"Project2".KORORDER_NR,
"Project2".KNIVSTALLNING_NR,
"Project2".HYLSKOD,
"Project2".PLANERAT_ANTAL_SET,
"Project2".PRODUCERAT_ANTAL_SET,
"Project2".C3 AS C2,
"Project2".TOLERANS,
"Project2".LANGDJUSTERING,
"Project2".UPD_DATUM,
"Project2".C5 AS C3,
"Project2".MASKIN_NR,
"Project2".HYLSPLAN_NR,
"Project2".BESTALLARE,
"Project2".UTGANGSHYLSA,
"Project2".SKP_DATUM,
"Project2".HYLSKAP_STATUS,
"Project2".C4,
"Project2".HYLSPLAN_ID,
"Project2".BANA_NR,
"Project2".LANGD,
"Project2".PLUGG,
"Project2".SKP_DATUM1,
"Project2".UPD_DATUM1
FROM ( SELECT 
	CASE WHEN "Project1".STATUS = 'P' THEN 1 ELSE 2 END AS C1,
	"Project1".ID,
	"Project1".MASKIN_NR,
	"Project1".HYLSPLAN_NR,
	"Project1".STATUS,
	"Project1".BESTALLARE,
	"Project1".KORORDER_NR,
	"Project1".KNIVSTALLNING_NR,
	"Project1".HYLSKOD,
	"Project1".UTGANGSHYLSA,
	"Project1".TOLERANS,
	"Project1".LANGDJUSTERING,
	"Project1".PLANERAT_ANTAL_SET,
	"Project1".PRODUCERAT_ANTAL_SET,
	"Project1".SKP_DATUM,
	"Project1".UPD_DATUM,
	"Project1".ORDNINGS_NR,
	"Project1".HYLSKAP_STATUS,
	1 AS C2,
	"Project1".PLANERAT_ANTAL_SET - "Project1".PRODUCERAT_ANTAL_SET AS C3,
	"Extent3".HYLSPLAN_ID,
	"Extent3".BANA_NR,
	"Extent3".LANGD,
	"Extent3".PLUGG,
	"Extent3".SKP_DATUM AS SKP_DATUM1,
	"Extent3".UPD_DATUM AS UPD_DATUM1,
	CASE WHEN "Extent3".HYLSPLAN_ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C4,
	"Project1".C1 AS C5
	FROM   (SELECT 
		"Extent1".ID,
		"Extent1".MASKIN_NR,
		"Extent1".HYLSPLAN_NR,
		"Extent1".STATUS,
		"Extent1".BESTALLARE,
		"Extent1".KORORDER_NR,
		"Extent1".KNIVSTALLNING_NR,
		"Extent1".HYLSKOD,
		"Extent1".UTGANGSHYLSA,
		"Extent1".TOLERANS,
		"Extent1".LANGDJUSTERING,
		"Extent1".PLANERAT_ANTAL_SET,
		"Extent1".PRODUCERAT_ANTAL_SET,
		"Extent1".SKP_DATUM,
		"Extent1".UPD_DATUM,
		"Extent1".ORDNINGS_NR,
		"Extent1".HYLSKAP_STATUS,
		(SELECT Sum("Extent2".LANGD) AS A1
FROM PL.HYLSPLAN_BANA "Extent2"
WHERE ("Extent1".ID = "Extent2".HYLSPLAN_ID) AND ROWNUM <= 1) AS C1
		FROM PL.HYLSPLAN "Extent1"
		WHERE ("Extent1".STATUS = 'N') AND ("Extent1".MASKIN_NR = 1) ) "Project1"
	LEFT OUTER JOIN PL.HYLSPLAN_BANA "Extent3" ON "Project1".ID = "Extent3".HYLSPLAN_ID
)  "Project2"
ORDER BY "Project2".C1 ASC, "Project2".ORDNINGS_NR ASC, "Project2".ID ASC, "Project2".C4 ASC;
Whick gives the result
Image
Where I can see that there is 4 groups of hylsplan with 3 hylsplan_bana

Regards,
Patrik

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

Re: Max

Post by Shalex » Fri 09 May 2014 13:29

1. Please send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.
2. Specify the exact previous build (x.x.x) of dotConnect for Oracle which set maxHylsplanBana to 3.

PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Post by PatrikAhlquist » Fri 16 May 2014 13:10

Hi!

I will do.
Here's the result of my test.
Previously we used version 7.3.132 in which this worked.

Image

Image

Regards,
Patrik

Kjell-Åke
Posts: 1
Joined: Mon 19 May 2014 12:22

Re: Max

Post by Kjell-Åke » Mon 19 May 2014 12:27

Same behaviour in 8.3.161. The produced SQL contains "...WHERE ROWNUM <= 1" which is causing the strange results.

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

Re: Max

Post by Shalex » Mon 19 May 2014 15:11

Please send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.

PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Post by PatrikAhlquist » Mon 19 May 2014 15:41

Hi!

I did so on friday last week.

Regards,
Patrik

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

Re: Max

Post by Shalex » Tue 20 May 2014 11:03

Sorry, but we did not receive your test project. We have sent additional questions to the email specified in your forum profile.

PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Re: Max

Post by PatrikAhlquist » Tue 20 May 2014 11:19

Now I've uploaded it again, attached it in a reply to your email and put it here: http://www.sendspace.com/file/cozl3u

That should do it! :)

Regards,
Patrik

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

Re: Max

Post by Shalex » Tue 20 May 2014 14:46

Thank you for your report. We have reproduced the issue and are investigating it.

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

Re: Max

Post by Shalex » Thu 29 May 2014 14:23

The bug with generating extra condition for limiting subselect by one record in generated subqueries with grouping is fixed in the new (8.4) version of dotConnect for Oracle.

It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.

Post Reply