My guess with no code inspection is that substr returns a string where s date literal string is a date literal (parser handles this). As such the string conversion to date probably produces a time with microsecond resolution, so you see 0 microseconds. This is likely because concat returns the data type of the first argument, which is a date literal, but substr returns string, which must be cast internally.
--Justin Sent from my iPhone > On Sep 19, 2015, at 3:53 PM, Dan Ragle <[email protected]> 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! > > Dan Ragle > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
