MySQL Bugs: #27921: View ignores precision for CAST()

Bug #27921	View ignores precision for CAST()
Submitted:	18 Apr 2007 9:45	Modified:	15 May 2007 2:53
Reporter:	Daniel Jaenecke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.1	OS:	Linux
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	cast, precision, VIEW

Description:
Using a precision when casting works fine in a SELECT statement. However when I create a VIEW from the very same statement the precision information is discarded / replaced by the default. 

How to repeat:
mysql> SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) );
+--------------------------------------+
| CAST( 1.23456789 AS DECIMAL( 7,5 ) ) |
+--------------------------------------+
|                              1.23457 | 
+--------------------------------------+

mysql> CREATE VIEW foo AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col;

mysql> SELECT * FROM foo;
+------+
| col  |
+------+
| 1.23 | 
+------+

mysql> DESC foo;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| col   | decimal(10,2) | NO   |     | 0.00    |       | 
+-------+---------------+------+-----+---------+-------+

mysql> SHOW CREATE VIEW foo;
+------+------------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                                  |
+------+------------------------------------------------------------------------------------------------------------------------------+
| foo  | CREATE ALGORITHM=UNDEFINED DEFINER=`[snip]` SQL SECURITY DEFINER VIEW `foo` AS select cast(1.23456789 as decimal) AS `col` | 
+------+------------------------------------------------------------------------------------------------------------------------------+

Thank you for the report.

Verified as described.

test case

Attachment: bug27921.test (application/octet-stream, text), 174 bytes.

proposal for a patch

Attachment: cast_print.patch (text/x-patch), 613 bytes.

a patch proposal has been attached.

The pronblem is/was that Item_decimal_typecast::print did not include the values for precission.

This means the view was stored as
  select cast(1.23456789 as decimal) AS `col`
instead of
  select cast(1.23456789 as decimal(7,5)) AS `col`

The patch will (and can) only work for newly created views. Existing views have lost the information and it can not be recovered.

The patch highlights a new problem (or maybe not a problem?)

Item_decimal_typecast does not know, if it has default or user specified values for precission. Therefore
CAST(x as DECIMAL)
CAST(x as DECIMAL(7))
CAST(x as DECIMAL(7,5))

will now ALL print as DECIMAL(m,n)

This can be a problem, if for example the defaults change in future versions of mysql. 
Views ould then still use the old default.

On the other hand, that may be desireable, as it will mean views will not depend on that changes. Well, I don't know if that is better.

In any way the patch fixes the described issue, the rest may go in its own bug report, if it is decided to be an issue.

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/26186

ChangeSet@1.2474, 2007-05-06 17:36:07+05:00, holyfoot@mysql.com +3 -0
  bug #27921 (View ignores precision for CAST)
  
  Item_decimal_typecast::print method wasn't properly implemented,
  so VIEW internal implementation didn't get proper precision/scale

replace (cs->cset->longlong10_to_str)() with int10_to_str().
ok to push.

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/26377

ChangeSet@1.2474, 2007-05-09 17:13:18+05:00, holyfoot@mysql.com +7 -0
  Bug #27921 View ignores precision for CAST()
  missing overflow checks added to
  Item_decimal_typecast::val_decimal

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/26411

ChangeSet@1.2474, 2007-05-10 00:17:21+05:00, holyfoot@mysql.com +9 -0
  Bug #27921 View ignores precision for CAST()
  Item_decimal_typecast::print properly implemented

Pushed into 5.1.19-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.19 changelogs.