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
Tags: cast, precision, VIEW

[18 Apr 2007 9:45] Daniel Jaenecke
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` | 
+------+------------------------------------------------------------------------------------------------------------------------------+
[18 Apr 2007 10:18] Sveta Smirnova
Thank you for the report.

Verified as described.
[18 Apr 2007 10:18] Sveta Smirnova
test case

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

[18 Apr 2007 19:32] Martin Friebe
proposal for a patch

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

[18 Apr 2007 19:41] Martin Friebe
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.
[6 May 2007 13:36] 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/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
[7 May 2007 11:52] Sergey Glukhov
replace (cs->cset->longlong10_to_str)() with int10_to_str().
ok to push.
[9 May 2007 13:13] 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/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
[9 May 2007 20:17] 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/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
[13 May 2007 6:17] Bugs System
Pushed into 5.1.19-beta
[13 May 2007 6:19] Bugs System
Pushed into 5.0.42
[15 May 2007 2:53] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.