Wednesday, October 12, 2011

Same query, 3 databases, 3 different results

The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences.

SQLite  3.7.4
sqlite> create table t1 (id serial, t time);
sqlite> insert into t1(t) values ('00:05:10');
sqlite> select t,t*1.5 from t1;

MySQL 5.6.4-m5
mysql> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select t,t*1.5 from t1;
| t        | t*1.5 |
| 00:05:10 |   765 |
1 row in set (0.00 sec)

PostgreSQL 9.0.3
test=# create table t1 (id serial, t time);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column ""
test=# insert into t1(t) values ('00:05:10');
test=# select t,t*1.5 from t1;
    t     | ?column?
 00:05:10 | 00:07:45
(1 row)

I think that the behaviour from PostgreSQL is the correct one. MySQL will just remove the :'s to get the string 000510 and then multiplies that value. The behaviour from SQLite is even more strange.

Of course for MySQL you could use the time_to_sec and sec_to_time functions.

mysql> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
| t        | sec_to_time(time_to_sec(t)*2) |
| 00:05:10 | 00:07:45                      |
1 row in set (0.00 sec)

But those functions are not available on SQLite.

sqlite> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
Error: no such function: sec_to_time


  1. And in Apache Derby (Java DB):

    ij> create table t1 (t time);
    0 rows inserted/updated/deleted
    ij> insert into t1(t) values ('00:05:10');
    1 row inserted/updated/deleted
    ij> select t,t*1.5 from t1;
    ERROR 42Y95: The '*' operator with a left operand type of 'TIME' and a right operand type of 'DECIMAL' is not supported.

    Which is correct behavior according to the SQL standard!

  2. Bernt is right - the SQL standard (I'm looking at ISO/IEC 9075-2:2003 now) does not define a * operation between any datetime type (including TIME) and a numeric value.

  3. I've created Bug #62696 to have MySQL behave the expected and/or correct way.