Squash That Old Bug

1 .Squash That Old Bug Daniel Black IBM 

2 .Overview Topics: • Choosing a bug/feature • Build/test environment • Tour of code base • Build/test • Submitting code • Asking for help 2 

3 .Choosing a Bug / Feature (MySQL) Search existing bugs https://bugs.mysql.com/search.php Status: Verified Note: (Effort/Risk fields have fallen out of use) 3 

4 .Choosing a Bug / Feature (MariaDB) Search existing bugs jql: labels = beginner-friendly jql: status = Confirmed 4 

5 .Choosing a Bug / Feature - Easy ● Regressions ● Wrong result ● Portability (e.g. fix for FreeBSD) ● Enhancement for new Linux Kernel API ● Client tools (mysql, mysqldump/pump) ● New SQL functions (verified / follow standard) 5 

6 .Choosing a Bug / Feature - Hard ● Group / GTID replication ● Optimizer Or if you must: – Easy/quick to calculate (low overhead) – Always an improvement (no regressions) 6 

7 .Build / Test environment Few dependencies: ● Git ● Cmake ● C++ compiler: gcc/ clang/ Xcode / VS17 ● Bison ● Ncurses ● Libevent (MariaDB) / boost (MySQL) Ref: https://mariadb.com/kb/en/library/compiling-mariadb-from-source/ https://dev.mysql.com/doc/mysql-sourcebuild-excerpt/8.0/en/ 7 

8 .Build / Test environment Github: https://github.com/MariaDB/server/ repos]$ git clone git@github.com:grooverdan/mariadb-server.git 8 

9 .Build / Test environment mariadb-server]$ cd ../build-mariadb-server build-mariadb-server]$ cmake -DCMAKE_BUILD_TYPE=Debug ../mariadb-server -- Running cmake version 3.14.3 -- The C compiler identification is GNU 9.1.1 -- The CXX compiler identification is GNU 9.1.1 ... -- Configuring done -- Generating done -- Build files have been written to: /home/dan/repos/build-mariadb-server build-mariadb-server]$ make -j8 Scanning dependencies of target abi_check 9 Scanning dependencies of target wsrep_api_v26 

10 .Tour of codebase 1 0 

11 .Example Bug 71386 SELECT STR_TO_DATE('01:35 PM', '%h:%i %p'); null SHOW WARNINGS Warning | 1411 | Incorrect datetime value: '01:35 PM' for function str_to_date 1 1 

12 .Create a Test Case mysql-server]$ git grep -l STR_TO_DATE mysql-test/ mysql-test/r/date_formats.result mysql-test/r/func_time.result ... mysql-test/t/date_formats.test mysql-test/t/func_time.test mysql-test/t/ignore_strict.test mysql-test/t/parser.test mysql-test/t/strict.test mysql-test/t/type_datetime.test mysql-test/t/type_temporal_fractional.test 1 2 

13 .Create a Test Case mysql-server]$ cat mysql-test/t/bug71386.test SELECT STR_TO_DATE('01:35 PM', '%h:%i %p'); build-mysql-8.0]$ mysql-test/mtr bug71386 … ----------- MYSQLTEST OUTPUT START ----------- SELECT STR_TO_DATE('01:35 PM', '%h:%i %p'); STR_TO_DATE('01:35 PM', '%h:%i %p') NULL Warnings: Warning 1411 Incorrect datetime value: '01:35 PM' for function str_to_date 1 3 

14 .Create a Test Case build-mysql-8.0]$ mysql-test/mtr --ddd bug71386 1 4 

15 .Find the Cause mysql-server] $ git grep -B 1 "' for function " share/errmsg-utf8.txt-ER_WRONG_VALUE_FOR_TYPE share/errmsg-utf8.txt: eng "Incorrect %-.32s value: '%-.128s' for function %-.32s" 1 5 

16 .Find the Cause mysql-server]$ git grep -l ER_WRONG_VALUE_FOR_TYPE mysql-test/include/ctype_inet.inc .. mysql-test/t/window_functions.test share/errmsg-utf8.txt sql/error_handler.cc sql/item_func.cc sql/item_geofunc.cc sql/item_inetfunc.cc sql/item_strfunc.cc sql/item_timefunc.cc 1 6 

17 .Find the Cause “Warning 1411 Incorrect datetime value: '01:35 PM' for function str_to_date” static bool extract_date_time(const Date_time_format *format, const char *val…. 1 7 

18 .Find the Cause “Warning 1411 Incorrect datetime value: '01:35 PM' for function str_to_date” bool Item_func_str_to_date::val_datetime(MYSQL_TIME *ltime, my_time_flags_t fuzzy_date) { 1 8 

19 .Find the Cause 1 9 

20 .Find the Cause 2 0 

21 .Find the Cause 2 1 

22 .Find the Cause 2 2 

23 .Find the Cause 2 3 

24 .Find the Cause mysql-server]$ cat ./mysql-test/t/bug71386.test SELECT STR_TO_DATE('1/1/2010', '%m/%d/%Y'); SELECT STR_TO_DATE('1/1/2010 01:35 PM', '%m/%d/%Y %h:%i %p'); SELECT STR_TO_DATE('01:35 PM', '%h:%i %p'); ; Thread 42 "mysqld" hit Breakpoint 2, Item_func_str_to_date::val_datetime (this=0x7fff941064e8, ltime=0x7fffec06d510, fuzzy_date=1) at /home/dan/repos/mysql- server/sql/item_timefunc.cc:3059 (gdb) p data_type() $1 = MYSQL_TYPE_DATE (gdb) cont Continuing. (gdb) p data_type() $2 = MYSQL_TYPE_DATETIME (gdb) cont Continuing. (gdb) p data_type() $3 = MYSQL_TYPE_TIME 2 4 

25 .A Solution ; 2 5 

26 .Test Solution build-mysql-8.0]$ mysql-test/mtr bug71386 … ----------- MYSQLTEST OUTPUT START ----------- SELECT STR_TO_DATE('1/1/2010', '%m/%d/%Y'); STR_TO_DATE('1/1/2010', '%m/%d/%Y') 2010-01-01 SELECT STR_TO_DATE('1/1/2010 01:35 PM', '%m/%d/%Y %h:%i %p'); STR_TO_DATE('1/1/2010 01:35 PM', '%m/%d/%Y %h:%i %p') 2010-01-01 13:35:00 SELECT STR_TO_DATE('01:35 PM', '%h:%i %p'); STR_TO_DATE('01:35 PM', '%h:%i %p') 2 6 13:35:00 

27 .Test Solution build-mysql-8.0]$ mysql-test/mtr func_time CURRENT_TEST: main.func_time --- /home/dan/repos/mysql-server/mysql-test/r/func_time.result 2019-05-03 09:08:35.340048269 +0300 +++ /home/dan/repos/build-mysql-8.0/mysql-test/var/log/func_time.reject 2019-05-14 12:50:45.124999118 +0300 @@ -1230,9 +1230,7 @@ set time_zone= @@global.time_zone; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE -NULL -Warnings: -Warning 1411Incorrect datetime value: '10:00 PM' for function str_to_date +22:10:00 create table t1 (field DATE); insert into t1 values ('2006-11-06'); select * from t1 where field < '2006-11-06 04:08:36.0'; 2 7 

28 .Record New Test Result build-mysql-8.0]$ mysql-test/mtr --record func_time mysql-server]$ git diff -U1 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b99d37eeb31..4f78a52a9be 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1233,3 +1233 @@ str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE -NULL -Warnings: -Warning 1411 Incorrect datetime value: '10:00 PM' for function str_to_date +22:10:00 diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 16e0b53636d..30c9526cfd6 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -3076,0 +3077 @@ bool Item_func_str_to_date::val_datetime(MYSQL_TIME *ltime, + (data_type() != MYSQL_TYPE_TIME) && 2 8 

29 .Submit Changes mysql-server]$ git checkout -b 8.0-bug-71386 M mysql-test/r/func_time.result M sql/item_timefunc.cc Switched to a new branch '8.0-bug-71386' 2 9