Bug #11760 subtime in view
Submitted: 6 Jul 2005 8:30 Modified: 19 Jul 2005 23:10
Reporter: Varoqui Stephane Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.7 OS:Any (any)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[6 Jul 2005 8:30] Varoqui Stephane
Description:
subtime in view cause dispaly of null values instead of the same select instruction 

How to repeat:
create view as select subtime(f1), .... from T1;

Suggested fix:
?? use date_sub
[6 Jul 2005 9:32] Victoria Reznichenko
mysql> create table dt1(dt datetime);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into dt1 values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into dt1 values(now());
Query OK, 1 row affected (0.00 sec)

mysql> select dt, subtime(dt, '1:1:1') as sb from dt1;
+---------------------+---------------------+
| dt                  | sb                  |
+---------------------+---------------------+
| 2005-07-06 13:27:13 | 2005-07-06 12:26:12 |
| 2005-07-06 13:27:14 | 2005-07-06 12:26:13 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> create view vdt as select dt, subtime(dt, '1:1:1') as sb from dt1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vdt;
+---------------------+------+
| dt                  | sb   |
+---------------------+------+
| 2005-07-06 13:27:13 | NULL |
| 2005-07-06 13:27:14 | NULL |
+---------------------+------+
2 rows in set (0.00 sec)
[19 Jul 2005 22:25] Evgeny Potemkin
Item_func_add_time::print() were printing arg[0] instead of arg[1] which
results in wrongly created view.
[19 Jul 2005 22:54] 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/internals/27328
[19 Jul 2005 23:03] Evgeny Potemkin
Fixed in 5.0.10, cset 1.1948.3.1
[19 Jul 2005 23:10] Paul Dubois
Noted in 5.0.10 changelogs.