← Back to team overview

maria-discuss team mailing list archive

Re: select using date_sub produces unwanted milliseconds in results

 

Hi Dan,

On 09/24/2015 12:35 AM, Dan Ragle wrote:


On 9/22/2015 5:12 PM, Alexander Barkov wrote:

On 09/23/2015 01:00 AM, Alexander Barkov wrote:
Hi Dan,


On 09/20/2015 12:53 AM, Dan Ragle wrote:
We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping
someone
can help clarify for me what's going on in the following scenario. In
brief, we're getting milliseconds displayed in a particular date_sub
calculation (and possibly other selects with date arithmetic, trying to
figure out how to track them down is part of the goal of this post)
where we aren't expecting them.

MariaDB [testdb]> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.43-MariaDB-37.2 |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.44-MariaDB-log  |
| version_comment         | MariaDB Server      |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

MariaDB [testdb]> show create table test \G
*************************** 1. row ***************************
        Table: test
Create Table: CREATE TABLE `test` (
   `datetime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [testdb]> select datetime from test;
Field   1:  `datetime`
Catalog:    `def`
Database:   `velocitynorep`
Table:      `test`
Org_table:  `test`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY NO_DEFAULT_VALUE

+---------------------+
| datetime            |
+---------------------+
| 2015-09-18 09:23:21 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(datetime,interval 1 second) from
test;
Field   1:  `date_sub(datetime,interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY


+--------------------------------------+
| date_sub(datetime,interval 1 second) |
+--------------------------------------+
| 2015-09-18 09:23:20                  |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select substr(datetime,1,13) from test;
Field   1:  `substr(datetime,1,13)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     39
Max_length: 13
Decimals:   31
Flags:      NOT_NULL

+-----------------------+
| substr(datetime,1,13) |
+-----------------------+
| 2015-09-18 09         |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00')
     -> from test;
Field   1:  `concat(substr(datetime,1,13),':00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     57
Max_length: 19
Decimals:   31
Flags:      NOT_NULL

+----------------------------------------+
| concat(substr(datetime,1,13),':00:00') |
+----------------------------------------+
| 2015-09-18 09:00:00                    |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat('2015-09-18 09',':00:00');
Field   1:  `concat('2015-09-18 09',':00:00')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     57
Max_length: 19
Decimals:   31
Flags:      NOT_NULL


+----------------------------------+
| concat('2015-09-18 09',':00:00') |
+----------------------------------+
| 2015-09-18 09:00:00              |
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'),
     -> interval 1 second);
Field   1:  `date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY


+---------------------------------------------------------------+
| date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second) |
+---------------------------------------------------------------+
| 2015-09-18 08:59:59                                           |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select
     -> date_sub(concat(substr(datetime,1,13),':00:00'),
     -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     26
Max_length: 26
Decimals:   6
Flags:      BINARY

+---------------------------------------------------------------------+
| date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second) |
+---------------------------------------------------------------------+
| 2015-09-18 08:59:59.000000                                          |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

The last one is the one that has me stumped.

1.) Why does it include milliseconds in the output? According to this
page:

     https://mariadb.com/kb/en/mariadb/microseconds-in-mariadb/

where no precision is specified the result should not display
milliseconds, which is what happens in all the other examples above
except this one.

2.) The statement on a 5.1 MySQL server (5.1.73-log, also 64 bit Linux)
produces this:

mysql> select date_sub(concat(substr(datetime,1,13),':00:00'),
     -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------------------------------------------------------+
| date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second) |
+---------------------------------------------------------------------+
| 2015-09-18 08:59:59                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Is there some global variable I can set to revert to the 5.1
behavior? I
realize I can CAST the result to a datetime to make sure I don't get
the
fractional seconds, but as I mentioned we're converting our system over
to 5.5 and there's boatloads of code that I would have to scan through
to try and find these differences.

3.) If there is no way to revert to the 5.1 behavior, can someone help
me to understand the exact reason why the milliseconds get displayed on
the final select in MariaDB 5.5? I'll need to look for any similar
types
of results and other than the exact reduction above I'm not sure what
statements I would need to identify and test.

Thanks for any assistance!

DATE_ADD() and DATE_SUB() are hybrid type functions.
They can return the following data types:
- DATETIME
- DATE
- TIME
- VARCHAR

depending on its argument data types.


In case when the first argument is not of a temporal type, the result
data type is VARCHAR, the result fractional precision is detected from
the argument, and the exact output format is detected per row!
It can return DATE-alike and DATETIME-alike strings in the same result
set. This query demonstrates this hybrid nature of DATE_ADD/DATE_SUB
in case of a VARCHAR result:

SELECT DATE_ADD(a, INTERVAL 1 DAY) FROM (SELECT '2001-01-01' AS a UNION
SELECT '2001-01-01 10:20:30') t1;

+-----------------------------+
| DATE_ADD(a, INTERVAL 1 DAY) |
+-----------------------------+
| 2001-01-02                  |
| 2001-01-02 10:20:30.000000  |
+-----------------------------+



Now returning to your examples:


In case when the first argument is a VARCHAR constant, like in this
query:

select date_sub(concat('2015-09-18 09',':00:00'), interval 1 second);

the result data type is VARCHAR, and its fractional precision is
calculated before the actual execution of the query:
- the first argument is evaluated to '2015-09-18 09:00:00'
- the constant is parsed and the number of fractional digits
   is detected to be 0
- the query is executed, returning no fractional digits

This is why the query returns '2015-09-18 08:59:59'


In case when the first argument is a VARCHAR non-constant, like in this
query:

select date_sub(concat(substr(datetime,1,13),':00:00'), interval 1
second) from test;

it cannot know in advance how the input string data will look like,
so it sets the maximum possible precision, which is 6.

This is why you get this result:

'2015-09-18 08:59:59.000000'

Notice, the result type in both cases is VARCHAR!

A small correction:

- VARCHAR in MariaDB-10.0 and higher

- VARBINARY in earlier versions





As a workaround, I can suggest this query:

SELECT DATE_SUB(TRUNCATE(datetime,-4), INTERVAL 1 SECOND) FROM test;

It does effectively the same thing with substr followed by concat,
but it returns '2015-09-18 08:59:59' with no fractional digits.


The two queries work differently because at the prepare stage of a
query execution:

- The number of fractional digits in the string which is a result
   of CONCAT(SUBSTR(...)) is NOT known in advance.
   So it reserves the maximum possible fractional precision of 6.


- The number of fractional digits in the numeric value which is a result
   of TRUNCATE(...,-4) is perfectly known in advance to be 0 for any
   possible input, so DATE_SUB() returns no fractional digits.



Notice, the result of:

SELECT DATE_SUB(TRUNCATE(datetime,-4), INTERVAL 1 SECOND) FROM test;

is still VARCHAR, because the first argument is not of a temporal data
type: it's a number!



So perhaps you might want to do even this:

SELECT DATE_SUB(CAST(TRUNCATE(datetime,-4) AS DATETIME), INTERVAL 1
SECOND) FROM test;


It returns '2015-09-18 08:59:59', and the result type is DATETIME.

Hope this helps.

Yes, the discussion is helpful, thank you.

So to sum up (and probably oversimplify, but enough so that I know
generally what I should be looking for) any function that potentially
returns a datetime and relies on a varchar-non constant -- i.e.,
something the value of which isn't known until the query is actually
executed -- will, when returning a datetime value, present the result
with 6 digits of precision.

There are 3 functions that can decide per row:

1. DATE_ADD() and its synonyms and variations,
  like DATE_SUB(), ADDDATE(), etc.


2. ADDTIME()

Example:

MariaDB [test]> SELECT ADDTIME(a,1) FROM (SELECT '10:20:30' AS a UNION SELECT '2001-01-01 10:20:30' UNION SELECT '10:20:30.123456') t1;
+---------------------+
| ADDTIME(a,1)        |
+---------------------+
| 10:20:31            |
| 2001-01-01 10:20:31 |
| 10:20:31.123456     |
+---------------------+
3 rows in set (0.00 sec)

ADDTIME() works as follows:

- If first arg is a MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP
  result is MYSQL_TYPE_DATETIME
- If first arg is a MYSQL_TYPE_TIME result is MYSQL_TYPE_TIME
- Otherwise the result is MYSQL_TYPE_STRING, and it decides per row

3. str_to_date().


All other functions that can return a temporal data type choose
the exact data type and temporal precision before the query execution
(not per row). This includes COALESCE(), CASE, NULLIF(), IF(), etc.,
they also choose before the query execution.



So presumably something like this:

select date_sub(concat(substr(now(),1,13),':00:00'),
    -> interval 1 second) from test;

returns no fractional seconds because now() is executed and the result
known before the query itself is actually launched.

Right, this is exactly what happens.
I just traced it in the debugger to make sure.


And in my original case, even though datetime is defined in my test
table as a datetime with no (zero) precision, because the concatenation
doesn't happen until after the query is generated (on a row-by-row
basis) the precision of any returned datetime has to be set to the max
possible value.

Yes? No?

Correct.

It cannot go inside CONCAT(SUBSTR()) and see that:
- the original data source is actually DATETIME(0)
- the concatenated part also cannot introduce fractional digits

DATE_SUB() just sees that the argument is a VARCHAR non-constant,
so it switches to the "per-row" mode.


To summarize:

- DATE_SUB() and ADDTIME() guarantee a predictable output data type and
  a predictable output fractional precision if the input is of a
  temporal data type

- they can't guarantee the exact output format and fractional
  precision case of a non-temporal input



This is where CAST() is very helpful, e.g.:

DATE_SUB(CAST(TRUNCATE(...) AS DATETIME)...)
DATE_SUB(CAST(CONCAT(SUBSTR(...) AS DATETIME)...)









Dan Ragle

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp



Follow ups

References