Bug #11760 subtime in view

• Submitted: 6 Jul 2005 8:30
• Modified: 19 Jul 2005 23:10
• Reporter: Varoqui Stephane
• Status: Closed
• 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] MySQL Verification Team

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.