| Bug #50918 | Date columns treated differently in Views than in Base Tables | ||
|---|---|---|---|
| Submitted: | 4 Feb 2010 15:31 | Modified: | 17 Jun 2010 22:40 |
| Reporter: | Chris DiMartino | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0.91, 5.1.41-log, 5.1.44-bzr | OS: | Linux (2.6.18-92.1.22.el5) |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | addtime, date, datetime, VIEW | ||
[4 Feb 2010 17:41]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.44 from bzr:
77-52-24-143:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.44-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table a (the_date date, the_time time);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into a values ('2010-01-01', '01:01:01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from a a1 join a a2 on addtime(a1.the_date,
-> a1.the_time) = addtime(a2.the_date, a2.the_time);
+------------+----------+------------+----------+
| the_date | the_time | the_date | the_time |
+------------+----------+------------+----------+
| 2010-01-01 | 01:01:01 | 2010-01-01 | 01:01:01 |
+------------+----------+------------+----------+
1 row in set (0.00 sec)
mysql> create view a_v as select * from a;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from a a1 join a_v a2 on addtime(a1.the_date,
-> a1.the_time) = addtime(a2.the_date, a2.the_time );
Empty set, 2 warnings (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect time value: '2010-01-01'
*************************** 2. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect datetime value: '01:21:11'
2 rows in set (0.00 sec)
mysql> desc a_v;
+----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| the_date | date | YES | | NULL | |
| the_time | time | YES | | NULL | |
+----------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from a a1 join a_v a2 on addtime(a1.the_date,
-> a1.the_time) = addtime(cast(a2.the_date as datetime), a2.the_time);
+------------+----------+------------+----------+
| the_date | the_time | the_date | the_time |
+------------+----------+------------+----------+
| 2010-01-01 | 01:01:01 | 2010-01-01 | 01:01:01 |
+------------+----------+------------+----------+
1 row in set (0.00 sec)
[10 Mar 2010 16:38]
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/102919 3384 Martin Hansson 2010-03-10 Bug#50918: Date columns treated differently in Views than in Base Tables The type inferrence of a view column caused the result to be interpreted as the wrong type: DATE colums were interpreted as TIME and TIME as DATETIME. This happened because view columns are represented by Item_ref objects as opposed to Item_field's. Item_ref had no method for retrieving a TIME value and thus was forced to depend on the default implementation for any expression, which caused the expression to be evaluated as a string and then parsed into a TIME/DATETIME value. Fixed by letting Item_ref classes forward the request for a TIME value to the referred Item - which is a field in this case - this reads the TIME value directly without conversion.
[16 Mar 2010 9:20]
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/103371 3401 Martin Hansson 2010-03-16 Bug#50918: Date columns treated differently in Views than in Base Tables The type inferrence of a view column caused the result to be interpreted as the wrong type: DATE colums were interpreted as TIME and TIME as DATETIME. This happened because view columns are represented by Item_ref objects as opposed to Item_field's. Item_ref had no method for retrieving a TIME value and thus was forced to depend on the default implementation for any expression, which caused the expression to be evaluated as a string and then parsed into a TIME/DATETIME value. Fixed by letting Item_ref classes forward the request for a TIME value to the referred Item - which is a field in this case - this reads the TIME value directly without conversion.
[16 Mar 2010 9:45]
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/103392 3972 Martin Hansson 2010-03-16 [merge] Merge of fix for Bug#50918
[26 Mar 2010 8:23]
Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:27]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:31]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 8:00]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:martin.hansson@sun.com-20100316162138-u9724fhm54cj3or0) (merge vers: 5.1.46) (pib:16)
[6 Apr 2010 13:11]
Jon Stephens
Documented bugfix in the 5.1.46, 5.5.4, and 6.0.124 changelogs, as follows:
The type inference used for view columns caused some columns in views to
be handled as the wrong type, as compared to the same columns in base
tables. DATE columns in base tables were treated as TIME columns in views,
and base table TIME columns as view DATETIME columns.
Closed.
[7 Apr 2010 15:58]
Paul DuBois
5.5.4 changelog entry was moved to 5.5.5.
[17 Jun 2010 11:59]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:39]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:25]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: Date values appear to be implicitly cast to datetime values when a function requiring a datetime operates directly on the column in a base table. When the same column is defined as part of a view (pass-thru), the implicit cast does not happen, resulting in warnings. This is unexpected behavior. The column in the view (that appears to have the identical data type as the base table) should be treated the same as that in the base table. How to repeat: (root@caff) [international]> create table a (the_date date, the_time time); Query OK, 0 rows affected (0.13 sec) (root@caff) [international]> insert into a values ('2010-01-01', '01:01:01'); Query OK, 1 row affected (0.01 sec) (root@caff) [international]> sselect * from a a1 join a a2 on addtime(a1.the_date, a1.the_time) = addtime(a2.the_date, a2.the_time) +------------+----------+------------+----------+ | the_date | the_time | the_date | the_time | +------------+----------+------------+----------+ | 2010-01-01 | 01:01:01 | 2010-01-01 | 01:01:01 | +------------+----------+------------+----------+ 1 row in set (0.03 sec) (root@caff) [international]> create view a_v as select * from a; Query OK, 0 rows affected (0.22 sec) (root@caff) [international]> select * from a a1 join a_v a2 on addtime(a1.the_date, a1.the_time) = addtime(a2.the_date, a2.the_time ); Empty set, 2 warnings (0.01 sec) (root@chai) [international]> select * from a a1 join a_v a2 on addtime(a1.the_date, a1.the_time) = addtime(cast(a2.the_date as datetime), a2.the_time); +------------+----------+------------+----------+ | the_date | the_time | the_date | the_time | +------------+----------+------------+----------+ | 2010-01-01 | 01:01:01 | 2010-01-01 | 01:01:01 | +------------+----------+------------+----------+ 1 row in set (0.02 sec) Suggested fix: Make the columns be treated