| Bug #27921 | View ignores precision for CAST() | ||
|---|---|---|---|
| Submitted: | 18 Apr 2007 11:45 | Modified: | 15 May 2007 4:53 |
| Reporter: | Daniel Jaenecke | ||
| Status: | Closed | ||
| Category: | Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0.37, 5.1 | OS: | Linux |
| Assigned to: | Alexey Botchkov | Target Version: | |
| Tags: | precision, cast, VIEW | ||
[18 Apr 2007 12:18]
Sveta Smirnova
Thank you for the report. Verified as described.
[18 Apr 2007 12:18]
Sveta Smirnova
test case
Attachment: bug27921.test (application/octet-stream, text), 174 bytes.
[18 Apr 2007 21:32]
Martin Friebe
proposal for a patch
Attachment: cast_print.patch (text/x-patch), 613 bytes.
[18 Apr 2007 21: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 15: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 13:52]
Sergey Gluhov
replace (cs->cset->longlong10_to_str)() with int10_to_str(). ok to push.
[9 May 2007 15: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 22: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 8:17]
Bugs System
Pushed into 5.1.19-beta
[13 May 2007 8:19]
Bugs System
Pushed into 5.0.42
[15 May 2007 4:53]
Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.
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` | +------+------------------------------------------------------------------------------------------------------------------------------+