Bug #47872 View maps unsigned bigint to signed bigint
Submitted: 6 Oct 2009 18:45 Modified: 5 Nov 2009 16:35
Reporter: Lukas Botsch Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.1.2 OS:Windows
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: MAPPING, VIEW

[6 Oct 2009 18:45] Lukas Botsch
Description:
Tested on MySQL 5.1.39-community and MySQL Connector/NET 6.1.2

I created a table 'property' with an unsigned bigint primary key 'Id'.
Then I created a view 'countryproperties' on this table.
I generated an ADO.NET entity model from the database and mapped an entity to the view. The Id column of the view is of the type 'bigint' rather than 'ubigint'.

How to repeat:
I used the following query to create the table:

CREATE TABLE `property` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Name` text NOT NULL,
  `Value` text NOT NULL,
  `Type` varchar(45) NOT NULL,
  `ValueType` varchar(45) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And this one to create the view:

CREATE VIEW `countryproperties` AS
  select `p`.`Id` AS `Id`,
         `p`.`Name` AS `Name`,
         `p`.`Value` AS `Value`,
         `p`.`ValueType` AS `ValueType`
  from `property` `p`
  where (`p`.`Type` = 'COUNTRY');

Then created a new ADO.NET entity model from the database in Visual Studio 2008 with MySQL Connector/NET 6.1.2.

Created a new entity named 'CountryProperty' and set it to map to the view 'countryproperties'.

In the column mappings, the column 'Id' is of type 'bigint'. But it should be of type 'ubigint'.
[12 Oct 2009 10:17] Tonci Grgin
Hi Lukas and thanks for your report.

For now, I bet the problem lies in fact that MSSQL does not have unsigned types (although .NET does). Checking.
[12 Oct 2009 10:32] Tonci Grgin
VS snapshot

Attachment: Bug47872-all.jpg (image/jpeg, text), 102.73 KiB.

[12 Oct 2009 10:35] Tonci Grgin
Verified as described.

Importing table "property" into model shows field Id to of type "ubigint" while same column from view "countryproperties" shows bigint.
Imo, table or view should not matter to end-user, they should behave the same.

Lukas, I see nothing serious (S2) in this problem, could you please lower the severity to, at least, S3?
[12 Oct 2009 14:40] Lukas Botsch
Sorry. Lowered severity to S3.
[28 Oct 2009 15:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88499
[28 Oct 2009 16:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88500
[28 Oct 2009 16:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88501
[28 Oct 2009 16:07] Reggie Burnett
Fixed in 6.0.5, 6.1.3, and 6.2.1+
[28 Oct 2009 16:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88506
[28 Oct 2009 16:11] Reggie Burnett
Fixed in 6.0.5, 6.1.3, and 6.2.1+
[5 Nov 2009 16:35] Tony Bedford
An entry has been added to the 6.0.5, 6.1.3, and 6.2.1 changelogs:

An entity model created from a schema containing a table with a column of type UNSIGNED BIGINT and a view of the table did not behave correctly. When an entity was created and mapped to the view, the column that was of type UNSIGNED BIGINT was displayed as BIGINT.