maria-discuss team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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.
>
>
>
>
>
> > 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