[MDEV-413] No warning is produced on microsecond precision truncation, as it is done for other data types Created: 2012-07-27  Updated: 2014-06-06

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.25, 5.3.7
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-369 Mismatches in MySQL engines test suite Closed

 Description   

When a value with microseconds is truncated while being inserted into a time column, it does not cause a warning, as it happens in the similar situation with other data types:

Microseconds truncated, no warning:

CREATE TABLE t (c1 TIME(3));
 
INSERT INTO t VALUES ('10:11:12.12345');
 
SELECT * FROM t;
 
c1
 
10:11:12.123

Fractional part of a decimal value truncated, a warning is produced:

CREATE TABLE t (d DECIMAL(5,3));
 
INSERT INTO t VALUES (12.34567);
 
Warnings:
 
Note	1265	Data truncated for column 'd' at row 1
 
SELECT * FROM t;
 
d
 
12.346

It might cause a confusion, especially if a user does not expect that the server knows about microseconds and takes them into account:

CREATE TABLE t (c1 TIME);
 
INSERT INTO t VALUES ('10:11:12.123');
 
SELECT * FROM t WHERE c1 = '10:11:12.123';
 
c1
 
DROP TABLE t;

The empty result set is understandable considering that TIME is actually TIME(0), and that the inserted value is silently truncated, while in the select the string literal is converted into a temporal value with microseconds; but it is not obvious at all without a warning about truncation.



 Comments   
Comment by Elena Stepanova [ 2012-07-27 ]

If this problem gets fixed, we will need to re-run 'engines' suite (it is not a part of the default set), and update result files accordingly.

Comment by Sergei Golubchik [ 2012-08-06 ]

Elena, please test this bugfix

Comment by Elena Stepanova [ 2012-08-06 ]

From IRC (for my reference):

<serg> I've committed a patch. besides this issue it also makes temporal warnings more close to other types, e.g. assigning invalid value will produce
<serg> Incorrect datetime value: 'test' for column 'a' at row 1
<serg> just like it does for decimal
<serg> not Data truncated for column 'a' at row 1 as before
<serg> and also slightly changed datetime value parser, 20100102121110,foobar will be parsed as 20100102121110 (that is as 2010-01-02 12:11:10) plus a truncated warning. Before my patch it would be zero date, but the same with a dot instead of a comma would be parsed as non-zero date
<serg> I mean 20100102121110,foobar produces zero date, but 20100102121110.foobar would do 2010-01-02 12:11:10
<serg> before my patch

Comment by Elena Stepanova [ 2012-08-07 ]

5.5-serg revno 3458

Two warnings (a note and a warning) are produced together, looks like a bit of overkill:

create table t1 (a time);

  1. Query OK, 0 rows affected (0.19 sec)

INSERT INTO t1 VALUES('-850:00:00');

  1. Query OK, 1 row affected, 2 warnings (0.04 sec)

show warnings;

  1. -------------------------------------------------------
  2. Level Code Message
  3. -------------------------------------------------------
  4. Note 1265 Data truncated for column 'a' at row 1
  5. Warning 1264 Out of range value for column 'a' at row 1
  6. -------------------------------------------------------
  7. 2 rows in set (0.00 sec)

I'd expect one or another (on maria-5.5 it's only 1264, it's probably more precise).

Comment by Elena Stepanova [ 2012-08-07 ]

5.5-serg revno 3458
Duplicate warning is produced (on maria-5.5 there was only one, as expected):

create table t (a timestamp(3));

  1. Query OK, 0 rows affected (0.18 sec)

insert into t values ('2012-12-31 23:59:59.101999999999999999');

  1. Query OK, 1 row affected, 2 warnings (0.05 sec)

show warnings;

  1. -------------------------------------------------
  2. Level Code Message
  3. -------------------------------------------------
  4. Note 1265 Data truncated for column 'a' at row 1
  5. Note 1265 Data truncated for column 'a' at row 1
  6. -------------------------------------------------
  7. 2 rows in set (0.00 sec)
Comment by Elena Stepanova [ 2012-08-15 ]

Some inconsistency in the type of the warning (hard to say if it's a regression, since in maria/5.5 one of the warnings was missing at all, which is what this bug was initially about):

create table t1 (f1 time(1), f6 time(6));

insert into t1 values ('-838:59:59.999999','-838:59:59.999999');

  1. Query OK, 1 row affected, 1 warning (0.17 sec)

show warnings;

  1. --------------------------------------------------
  2. Level Code Message
  3. --------------------------------------------------
  4. Note 1265 Data truncated for column 'f1' at row 1
  5. --------------------------------------------------

update t1 set f1=f6;

  1. Query OK, 0 rows affected, 1 warning (0.03 sec)

show warnings;

  1. --------------------------------------------------------
  2. Level Code Message
  3. --------------------------------------------------------
  4. Warning 1264 Out of range value for column 'f1' at row 1
  5. --------------------------------------------------------

That is, the same value in the first case causes 1265, while in the second case 1264.

Comment by Elena Stepanova [ 2012-08-16 ]

Sergei,

As discussed, I've put observations related to this bugfix as comments above, and filed as separate reports whatever unrelated I encountered while digging (mostly trivia). Nothing serious about the bugfix either, but it would be nice to get rid of the duplicate warnings described in the first 2 comments while we still can – if they creep into the tree, they're going to stay there forever..

Generated at Thu Feb 08 06:28:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.