MySQL Bugs: #29306: Truncated data in MS Access with decimal (3,1) columns in a VIEW

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:	Windows
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	access, bfsm_2007_07_19, VIEW, windows

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.

This is the server-side bug.
See the attached C test case.

Test case that shows zero decimals from view with decimal(3,1) column

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

Verified against MySQL Server 5.0.37 and 5.0.45

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.

Queued in 5.0-runtime

Pushed into 5.0.48

Pushed into 5.1.22-beta

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.