dotConnect 8.3.146: Count in subquery breaking

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
thakkarmayank
Posts: 13
Joined: Fri 23 Apr 2010 21:16

dotConnect 8.3.146: Count in subquery breaking

Post by thakkarmayank » Tue 13 May 2014 17:24

Hi,
I just upgraded to dotConnect 8.3.146(Trial version) from 6.80. (I had to upgrade in order to fix the issue mentioned at http://forums.devart.com/viewtopic.php?f=1&t=29443.)
I am using VS 2010, EF 4.1 and .Net Framework 4

I ran into this breaking change:

The following query, which was working fine in 6.80, is now showing incorrect results.

Code: Select all

var lstUsers = (from cu in UserEntities
 				select new
				{
				user = cu,
				StateLicenses1 = (from sl in StateLicenseHistoryEntities
												where cu.UID == sl.UserUID
											select sl).Count() 
				}).ToList();
The "StateLicenses1" property returns "1" for all non-zero counts. It should return the actual count of child rows in the "StateLicenseHistoryEntities" table.

I read the release history but did not find anything regarding this change.

Am I missing something or have I stumbled into another ugly bug?

~Mayank

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect 8.3.146: Count in subquery breaking

Post by Shalex » Wed 14 May 2014 16:59

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

thakkarmayank
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: dotConnect 8.3.146: Count in subquery breaking

Post by thakkarmayank » Wed 14 May 2014 23:10

Shalex,
This is a simple parent-child table relationship.
Parent table can a User table with USerUID as PK and some other attributes
Child table can be any table with ParentTable.UserUID as a FK.

Let me know if this doesnt work and I can send you some scripts.

~Mayank

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect 8.3.146: Count in subquery breaking

Post by Shalex » Thu 15 May 2014 10:44

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

thakkarmayank
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: dotConnect 8.3.146: Count in subquery breaking

Post by thakkarmayank » Thu 15 May 2014 15:41

Here are the scripts:

Code: Select all

CREATE TABLE "DBO"."USER" 
   (	"USER_UID" NUMBER(10,0) NOT NULL ENABLE, 
	"USER_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_PSW" VARCHAR2(100 BYTE), 
	"USER_DEFAULT_ROLE_TYPE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
  "USER_FIRST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
	"USER_LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
		 PRIMARY KEY ("USER_UID") ENABLE
     ) ;

  CREATE TABLE "DBO"."USOL_USR_STATE_LICENSE_HIST"
  (
    "USOL_UID"      NUMBER(10,0),
    "USOL_USER_UID" NUMBER(10,0) NOT NULL ENABLE,
    "USOL_SOL"      VARCHAR2(6 BYTE) NOT NULL ENABLE,
    "USOL_BEGIN_DATE" DATE NOT NULL ENABLE,
    "USOL_END_DATE" DATE NOT NULL ENABLE
    PRIMARY KEY ("USOL_UID")  ENABLE,
    FOREIGN KEY ("USOL_USER_UID") REFERENCES "DBO"."USER" ("USER_UID") ENABLE
  );
  
  
  
  
  
INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    1,
    'Test1',
    'test123',
    'EMP',
    'Test',
    '1'  
  );
  
  INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    2,
    'Test2',
    'test123',
    'EMP',
    'Test',
    '2'  
  );
  
  INSERT
INTO DBO.USER
  (
    USER_UID,
    USER_NAME,
    USER_PSW,
    USER_DEFAULT_ROLE_TYPE,
    USER_FIRST_NAME,
    USER_LAST_NAME  
  )
  VALUES
  (
    3,
    'Test3',
    'test123',
    'EMP',
    'Test',
    '3'  
  );
  
  
INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    1,
    1,
    'CT',
    '01-JAN-2013',
    '31-DEC-2099'
  );


INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    2,
    1,
    'NY',
    '01-JAN-2013',
    '31-DEC-2099'
  );


INSERT
INTO USOL_USR_STATE_LICENSE_HIST
  (
    USOL_UID,
    USOL_USER_UID,
    USOL_SOL,
    USOL_BEGIN_DATE,
    USOL_END_DATE
    
  )
  VALUES
  (
    3,
    3,
    'CT',
    '01-JAN-2013',
    '31-DEC-2099'
  );

thakkarmayank
Posts: 13
Joined: Fri 23 Apr 2010 21:16

Re: dotConnect 8.3.146: Count in subquery breaking

Post by thakkarmayank » Mon 19 May 2014 17:20

Shalex,
I have uploaded a test project on the link provided. The DB scripts, referenced in the above post, are also included. The DB scripts and edmx file in the project may be out of sync, as I wont be able to provide the actual DB table scripts, due to some restrictions here.

You can tailor the edmx file to match the scripts and you should be able to reproduce the issue.

Please let me know.

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect 8.3.146: Count in subquery breaking

Post by Shalex » Tue 20 May 2014 14:28

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

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect 8.3.146: Count in subquery breaking

Post by Shalex » Wed 28 May 2014 14:51

The bug with generating extra condition for limiting subselect by one record in generated subqueries with grouping is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect 8.3.146: Count in subquery breaking

Post by Shalex » Thu 29 May 2014 14:14

New version of dotConnect for Oracle 8.4 is released!
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