Bug #11760 subtime in view
Submitted: 6 Jul 2005 10:30 Modified: 20 Jul 2005 1:10
Reporter: Varoqui Stephane
Status: Closed
Category:Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.7 OS:Any (any)
Assigned to: Evgeny Potemkin Target Version:

[6 Jul 2005 10: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 11: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)
[20 Jul 2005 0:25] Evgeny Potemkin 
Item_func_add_time::print() were printing arg[0] instead of arg[1] which
results in wrongly created view.
[20 Jul 2005 0: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
[20 Jul 2005 1:03] Evgeny Potemkin 
Fixed in 5.0.10, cset 1.1948.3.1
[20 Jul 2005 1:10] Paul DuBois 
Noted in 5.0.10 changelogs.