Bug #29306 Truncated data in MS Access with decimal (3,1) columns in a VIEW
Submitted: 22 Jun 2007 17:04 Modified: 25 Aug 2007 16:12
Reporter: Bogdan Degtyariov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.37, 5.0.45 OS:Microsoft Windows
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: access, bfsm_2007_07_19, VIEW, windows

[22 Jun 2007 17:04] Bogdan Degtyariov
Description:
The problem involves a decimal(3,1) in a view. In MS Access, if the digit to the right of the deciaml is non-zero, we receive truncation errors and in table view mode in Access, all you see for those rows with this problem is #Error.

How to repeat:
create table issue17557 ( id int primary key auto_increment, dd decimal(3,1));
INSERT INTO issue17557 (id, dd) VALUES (NULL, 33.1);
INSERT INTO issue17557 (id, dd) VALUES (NULL, 69.2);
INSERT INTO issue17557 (id, dd) VALUES (NULL, 85.7);
create view viev17557 as select * from issue17557 where dd>15 and dd<60;

Open MS Access and try to create links to the table and the view.
Table is OK whereas the link to the view displays the truncation error.
[5 Jul 2007 14:32] Bogdan Degtyariov
This is the server-side bug.
See the attached C test case.
[5 Jul 2007 14:33] Bogdan Degtyariov
Test case that shows zero decimals from view with decimal(3,1) column

Attachment: mysql-list-columns-test.c (text/plain), 1.33 KiB.

[25 Jul 2007 18:26] Bogdan Degtyariov
Verified against MySQL Server 5.0.37 and 5.0.45
[6 Aug 2007 10:22] 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/32135

ChangeSet@1.2488, 2007-08-06 14:22:24+04:00, kostja@bodhi.(none) +2 -0
  A fix and a test case for Bug#29306 "Truncated data in MS Access with decimal (3,1) columns in a 
  VIEW".
  
  mysql_list_fields() C API function would incorrectly set MYSQL_FIELD::decimals
  member for some view columns.
  
  The problem was in an incomplete implementation of 
  Item_ident_for_show::make_field(), which is responsible for view
  columns metadata.
[6 Aug 2007 10:31] Konstantin Osipov
Queued in 5.0-runtime
[9 Aug 2007 13:17] Bugs System
Pushed into 5.0.48
[9 Aug 2007 13:18] Bugs System
Pushed into 5.1.22-beta
[25 Aug 2007 16:12] Paul Dubois
Noted in 5.0.48, 5.1.22 changelogs.

The mysql_list_fields() C API function incorrectly set
MYSQL_FIELD::decimals for some view columns.