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:
None 
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
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[4 Feb 2010 15:31] Chris DiMartino
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
[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)