maria-discuss team mailing list archive

[Dan Ragle <daniel@xxxxxxxxxxxxxx>]

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!

Dan Ragle