| Bug #62696 | Using a numeric operator on a TIME column should return an error or warning | ||
|---|---|---|---|
| Submitted: | 12 Oct 2011 9:36 | Modified: | 12 Oct 2011 13:18 |
| Reporter: | Daniël van Eeden | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.5.17, 5.6.4-m5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | compatibility, STANDARDS, time | ||
[12 Oct 2011 9:36]
Daniël van Eeden
See also: http://databaseblog.myname.nl/2011/10/same-query-3-databases-3-different.html
[12 Oct 2011 13:18]
Valeriy Kravchuk
This is easy to verify:
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.17-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table t1;
Query OK, 0 rows affected (0.16 sec)
mysql> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1(t) values ('00:05:10');
Query OK, 1 row affected (0.05 sec)
mysql> select t,t*1.5 from t1;
+----------+-------+
| t | t*1.5 |
+----------+-------+
| 00:05:10 | 765 |
+----------+-------+
1 row in set (0.03 sec)
mysql> select t,t*1.5, 510*1.5 from t1;
+----------+-------+---------+
| t | t*1.5 | 510*1.5 |
+----------+-------+---------+
| 00:05:10 | 765 | 765.0 |
+----------+-------+---------+
1 row in set (0.00 sec)
But looks liek a documented "feature" (when different data types are mixed in the same expression they are treated/implicitly converted as double).
So your request to change current intended behavior is a reasonable feature request.
Description: Using a * as a operator on a TIME column does return results. The results are different than other implementations. 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; 00:05:10|0.0 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 "t1.id" CREATE TABLE test=# insert into t1(t) values ('00:05:10'); INSERT 0 1 test=# select t,t*1.5 from t1; t | ?column? ----------+---------- 00:05:10 | 00:07:45 (1 row) 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. An error should be returned according to the SQL standards. (You might need to check the SQL 2003 standards) How to repeat: See description Suggested fix: Add a new sql_mode or change the current behaviour. sql_mode ANSI = ERROR_FOR_INVALID_TIME_CALC (standards complicant, return error) POSTGRESQL = TIME_MULTIPLY_USE_SECONDS (PostgreSQL compliant, convert from/to seconds for calculation)