Using the * operation on time gives unexpected results

[Daniël van Eeden]

drizzle> insert into t1(t) values ('00:05:10');
Query OK, 1 row affected (0.001717 sec)

drizzle> select t,t*1.5 from t1;
+----------+-------+
| t | t*1.5 |
+----------+-------+
| 00:05:10 | 465 |
+----------+-------+
1 row in set (0.000691 sec)

This differs from the SQL standard and behaviour from MySQL, PostgreSQL and SQLite.

If I understand it correctly the standards compliant behaviour should be to throw an error. The expected behaviour (PostgreSQL does this) is to return 00:07:45.

[Daniël van Eeden (dveeden)]

See also: http://databaseblog.myname.nl/2011/11/fixed-in-drizzle-or-just-different.html

[Brian Aker (brianaker)]

That is the column type for t?

[Daniël van Eeden (dveeden)]

The t column is defined as TIME.

[Brian Aker (brianaker)]

Going through the standard I don't see a reference as to what the behavior for multiplication against time is. What PG is doing is giving you back min:sec, what we are doing is giving you back the actual number of seconds.

We are returning the result as a REAL though, and it is an open question as to what should be returned (or if what a user should expect is an error).