diff options
Diffstat (limited to 'mysql-test/r')
67 files changed, 2548 insertions, 227 deletions
diff --git a/mysql-test/r/bad_frm_crash_5029.result b/mysql-test/r/bad_frm_crash_5029.result new file mode 100644 index 00000000000..8aa0065f5ee --- /dev/null +++ b/mysql-test/r/bad_frm_crash_5029.result @@ -0,0 +1,13 @@ +show create table t1; +ERROR 42000: Unknown storage engine 'InnoDB' +call mtr.add_suppression("t1.frm is inconsistent: engine typecode 43, engine name Aria"); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +drop table t1; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 3b57b4833a9..5e933914f5d 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -780,7 +780,7 @@ SELECT CAST(CAST('20:05:05' AS TIME) as date); CAST(CAST('20:05:05' AS TIME) as date) NULL Warnings: -Warning 1292 Truncated incorrect date value: '0000-00-00' +Warning 1292 Incorrect datetime value: '20:05:05' set sql_mode=DEFAULT; create table t1 (f1 time, f2 date, f3 datetime); insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33'); diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index fff8733cfdf..4768af0176c 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1764,7 +1764,8 @@ t1 CREATE TABLE `t1` ( `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0', `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000', `MEMORY_USED` int(7) NOT NULL DEFAULT '0', - `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0' + `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0', + `QUERY_ID` bigint(4) NOT NULL DEFAULT '0' ) DEFAULT CHARSET=utf8 drop table t1; create temporary table t1 like information_schema.processlist; @@ -1784,7 +1785,8 @@ t1 CREATE TEMPORARY TABLE `t1` ( `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0', `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000', `MEMORY_USED` int(7) NOT NULL DEFAULT '0', - `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0' + `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0', + `QUERY_ID` bigint(4) NOT NULL DEFAULT '0' ) DEFAULT CHARSET=utf8 drop table t1; create table t1 like information_schema.character_sets; diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index c9370c28da0..9b38776e1ec 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -1421,12 +1421,12 @@ t1 CREATE TABLE `t1` ( drop table t1; select hex(concat(time_to_sec('10:11:12'))); hex(concat(time_to_sec('10:11:12'))) -33363637322E303030303030 +3336363732 create table t1 as select concat(time_to_sec('10:11:12')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(24) DEFAULT NULL + `c1` varbinary(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(extract(year from 20090702))); @@ -1533,7 +1533,7 @@ create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00: show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(17) DEFAULT NULL + `c1` varbinary(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(maketime(10,11,12))); @@ -1573,7 +1573,7 @@ create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(26) DEFAULT NULL + `c1` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); @@ -1583,7 +1583,7 @@ create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(26) DEFAULT NULL + `c1` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; c1 @@ -2793,8 +2793,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varbinary(26) DEFAULT NULL, - `field1_str2` varbinary(26) DEFAULT NULL, + `field_str1` varbinary(19) DEFAULT NULL, + `field1_str2` varbinary(19) DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -2805,8 +2805,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 26 10 Y 128 31 63 -def field1_str2 254 26 19 Y 128 31 63 +def field_str1 254 19 10 Y 128 0 63 +def field1_str2 254 19 19 Y 128 0 63 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -2842,8 +2842,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `addtime1` varbinary(26) DEFAULT NULL, `addtime2` varbinary(26) DEFAULT NULL, - `date_add1` varbinary(26) DEFAULT NULL, - `date_add2` varbinary(26) DEFAULT NULL + `date_add1` varbinary(19) DEFAULT NULL, + `date_add2` varbinary(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; addtime1 addtime2 date_add1 date_add2 @@ -2892,7 +2892,7 @@ CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); INSERT INTO t1 VALUES (); SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; maketime(`a`,`a`,`a`) -00:00:00 +00:00:00.000000 DROP TABLE t1; SET sql_mode=default; # diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index b7e99a363f3..b6114ba6f59 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -1813,12 +1813,12 @@ t1 CREATE TABLE `t1` ( drop table t1; select hex(concat(time_to_sec('10:11:12'))); hex(concat(time_to_sec('10:11:12'))) -33363637322E303030303030 +3336363732 create table t1 as select concat(time_to_sec('10:11:12')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(24) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(extract(year from 20090702))); @@ -1925,7 +1925,7 @@ create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00: show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(maketime(10,11,12))); @@ -1965,7 +1965,7 @@ create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); @@ -1975,7 +1975,7 @@ create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; c1 @@ -3185,8 +3185,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, - `field1_str2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -3197,8 +3197,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 26 10 Y 0 31 51 -def field1_str2 254 26 19 Y 0 31 51 +def field_str1 254 19 10 Y 0 0 51 +def field1_str2 254 19 19 Y 0 0 51 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -3234,8 +3234,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `addtime1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, `addtime2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, - `date_add1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, - `date_add2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL + `date_add1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; addtime1 addtime2 date_add1 date_add2 @@ -3284,7 +3284,7 @@ CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); INSERT INTO t1 VALUES (); SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; maketime(`a`,`a`,`a`) -00:00:00 +00:00:00.000000 DROP TABLE t1; SET sql_mode=default; # diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index b3d104a14eb..6c11a68ea90 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -1840,12 +1840,12 @@ t1 CREATE TABLE `t1` ( drop table t1; select hex(concat(time_to_sec('10:11:12'))); hex(concat(time_to_sec('10:11:12'))) -33363637322E303030303030 +3336363732 create table t1 as select concat(time_to_sec('10:11:12')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(24) DEFAULT NULL + `c1` varchar(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(extract(year from 20090702))); @@ -1952,7 +1952,7 @@ create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00: show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) DEFAULT NULL + `c1` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(maketime(10,11,12))); @@ -1992,7 +1992,7 @@ create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) DEFAULT NULL + `c1` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); @@ -2002,7 +2002,7 @@ create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) DEFAULT NULL + `c1` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; c1 @@ -3212,8 +3212,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(26) DEFAULT NULL, - `field1_str2` varchar(26) DEFAULT NULL, + `field_str1` varchar(19) DEFAULT NULL, + `field1_str2` varchar(19) DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -3224,8 +3224,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 26 10 Y 0 31 8 -def field1_str2 254 26 19 Y 0 31 8 +def field_str1 254 19 10 Y 0 0 8 +def field1_str2 254 19 19 Y 0 0 8 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -3261,8 +3261,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `addtime1` varchar(26) DEFAULT NULL, `addtime2` varchar(26) DEFAULT NULL, - `date_add1` varchar(26) DEFAULT NULL, - `date_add2` varchar(26) DEFAULT NULL + `date_add1` varchar(19) DEFAULT NULL, + `date_add2` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; addtime1 addtime2 date_add1 date_add2 @@ -3311,7 +3311,7 @@ CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); INSERT INTO t1 VALUES (); SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; maketime(`a`,`a`,`a`) -00:00:00 +00:00:00.000000 DROP TABLE t1; SET sql_mode=default; # diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index fad02c23b86..c6303f9e4de 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -2749,12 +2749,12 @@ t1 CREATE TABLE `t1` ( drop table t1; select hex(concat(time_to_sec('10:11:12'))); hex(concat(time_to_sec('10:11:12'))) -00330036003600370032002E003000300030003000300030 +00330036003600370032 create table t1 as select concat(time_to_sec('10:11:12')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(24) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(extract(year from 20090702))); @@ -2861,7 +2861,7 @@ create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00: show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(maketime(10,11,12))); @@ -2901,7 +2901,7 @@ create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); @@ -2911,7 +2911,7 @@ create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; c1 @@ -4121,8 +4121,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, - `field1_str2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -4133,8 +4133,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 26 10 Y 0 31 8 -def field1_str2 254 26 19 Y 0 31 8 +def field_str1 254 19 10 Y 0 0 8 +def field1_str2 254 19 19 Y 0 0 8 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -4170,8 +4170,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `addtime1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, `addtime2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, - `date_add1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, - `date_add2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL + `date_add1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; addtime1 addtime2 date_add1 date_add2 @@ -4220,7 +4220,7 @@ CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); INSERT INTO t1 VALUES (); SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; maketime(`a`,`a`,`a`) -00:00:00 +00:00:00.000000 DROP TABLE t1; SET sql_mode=default; SET NAMES latin1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 253614a8329..71cdcff8c14 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -3579,12 +3579,12 @@ t1 CREATE TABLE `t1` ( drop table t1; select hex(concat(time_to_sec('10:11:12'))); hex(concat(time_to_sec('10:11:12'))) -33363637322E303030303030 +3336363732 create table t1 as select concat(time_to_sec('10:11:12')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(24) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(17) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(extract(year from 20090702))); @@ -3691,7 +3691,7 @@ create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00: show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(17) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(maketime(10,11,12))); @@ -3731,7 +3731,7 @@ create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(19) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day))); @@ -3741,7 +3741,7 @@ create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day) show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(26) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(19) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; c1 @@ -4951,8 +4951,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(26) CHARACTER SET utf8 DEFAULT NULL, - `field1_str2` varchar(26) CHARACTER SET utf8 DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET utf8 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET utf8 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -4963,8 +4963,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 78 10 Y 0 31 33 -def field1_str2 254 78 19 Y 0 31 33 +def field_str1 254 57 10 Y 0 0 33 +def field1_str2 254 57 19 Y 0 0 33 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -5000,8 +5000,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `addtime1` varchar(26) CHARACTER SET utf8 DEFAULT NULL, `addtime2` varchar(26) CHARACTER SET utf8 DEFAULT NULL, - `date_add1` varchar(26) CHARACTER SET utf8 DEFAULT NULL, - `date_add2` varchar(26) CHARACTER SET utf8 DEFAULT NULL + `date_add1` varchar(19) CHARACTER SET utf8 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t1; addtime1 addtime2 date_add1 date_add2 @@ -5050,7 +5050,7 @@ CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a)); INSERT INTO t1 VALUES (); SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1; maketime(`a`,`a`,`a`) -00:00:00 +00:00:00.000000 DROP TABLE t1; SET sql_mode=default; # diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 7f69fd1a5a4..ff3f4038447 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -192,70 +192,70 @@ Tuesday 52 2001 %W %V %X 2002-01-01 15-2001-1 %d-%Y-%c 2001-01-15 select date,format,TIME(str_to_date(date, format)) as time from t1; date format time -2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12 -03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02 -0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02 -03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02 -2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12 +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02.000000 +0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 +03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12.000000 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450 -2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 -10:20:10 %H:%i:%s 10:20:10 -10:20:10 %h:%i:%s.%f 10:20:10 -10:20:10 %T 10:20:10 -10:20:10AM %h:%i:%s%p 10:20:10 -10:20:10AM %r 10:20:10 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000 +10:20:10 %H:%i:%s 10:20:10.000000 +10:20:10 %h:%i:%s.%f 10:20:10.000000 +10:20:10 %T 10:20:10.000000 +10:20:10AM %h:%i:%s%p 10:20:10.000000 +10:20:10AM %r 10:20:10.000000 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 -15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 -15 September 2001 %d %M %Y 00:00:00 -15 SEPTEMB 2001 %d %M %Y 00:00:00 -15 MAY 2001 %d %b %Y 00:00:00 -15th May 2001 %D %b %Y 00:00:00 -Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 -Sund 15 MAY 2001 %W %d %b %Y 00:00:00 -Tuesday 00 2002 %W %U %Y 00:00:00 -Thursday 53 1998 %W %u %Y 00:00:00 -Sunday 01 2001 %W %v %x 00:00:00 -Tuesday 52 2001 %W %V %X 00:00:00 -060 2004 %j %Y 00:00:00 -4 53 1998 %w %u %Y 00:00:00 -15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 -15-01-20 %d-%m-%y 00:00:00 -15-2001-1 %d-%Y-%c 00:00:00 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000 +15 September 2001 %d %M %Y 00:00:00.000000 +15 SEPTEMB 2001 %d %M %Y 00:00:00.000000 +15 MAY 2001 %d %b %Y 00:00:00.000000 +15th May 2001 %D %b %Y 00:00:00.000000 +Sunday 15 MAY 2001 %W %d %b %Y 00:00:00.000000 +Sund 15 MAY 2001 %W %d %b %Y 00:00:00.000000 +Tuesday 00 2002 %W %U %Y 00:00:00.000000 +Thursday 53 1998 %W %u %Y 00:00:00.000000 +Sunday 01 2001 %W %v %x 00:00:00.000000 +Tuesday 52 2001 %W %V %X 00:00:00.000000 +060 2004 %j %Y 00:00:00.000000 +4 53 1998 %w %u %Y 00:00:00.000000 +15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000 +15-01-20 %d-%m-%y 00:00:00.000000 +15-2001-1 %d-%Y-%c 00:00:00.000000 select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1; date format time2 -2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12 -03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02 -0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02 -03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02 -2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12 +2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000 +03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02.000000 +0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 +03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02.000000 +2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12.000000 2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450 2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450 2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450 -2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 -10:20:10 %H:%i:%s 10:20:10 -10:20:10 %h:%i:%s.%f 10:20:10 -10:20:10 %T 10:20:10 -10:20:10AM %h:%i:%s%p 10:20:10 -10:20:10AM %r 10:20:10 +2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000 +10:20:10 %H:%i:%s 10:20:10.000000 +10:20:10 %h:%i:%s.%f 10:20:10.000000 +10:20:10 %T 10:20:10.000000 +10:20:10AM %h:%i:%s%p 10:20:10.000000 +10:20:10AM %r 10:20:10.000000 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 -15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 -15 September 2001 %d %M %Y 00:00:00 -15 SEPTEMB 2001 %d %M %Y 00:00:00 -15 MAY 2001 %d %b %Y 00:00:00 -15th May 2001 %D %b %Y 00:00:00 -Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 -Sund 15 MAY 2001 %W %d %b %Y 00:00:00 -Tuesday 00 2002 %W %U %Y 00:00:00 -Thursday 53 1998 %W %u %Y 00:00:00 -Sunday 01 2001 %W %v %x 00:00:00 -Tuesday 52 2001 %W %V %X 00:00:00 -060 2004 %j %Y 00:00:00 -4 53 1998 %w %u %Y 00:00:00 -15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 -15-01-20 %d-%m-%y 00:00:00 -15-2001-1 %d-%Y-%c 00:00:00 +15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000 +15 September 2001 %d %M %Y 00:00:00.000000 +15 SEPTEMB 2001 %d %M %Y 00:00:00.000000 +15 MAY 2001 %d %b %Y 00:00:00.000000 +15th May 2001 %D %b %Y 00:00:00.000000 +Sunday 15 MAY 2001 %W %d %b %Y 00:00:00.000000 +Sund 15 MAY 2001 %W %d %b %Y 00:00:00.000000 +Tuesday 00 2002 %W %U %Y 00:00:00.000000 +Thursday 53 1998 %W %u %Y 00:00:00.000000 +Sunday 01 2001 %W %v %x 00:00:00.000000 +Tuesday 52 2001 %W %V %X 00:00:00.000000 +060 2004 %j %Y 00:00:00.000000 +4 53 1998 %w %u %Y 00:00:00.000000 +15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000 +15-01-20 %d-%m-%y 00:00:00.000000 +15-2001-1 %d-%Y-%c 00:00:00.000000 select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')) 2003-01-02 08:11:02.123456 diff --git a/mysql-test/r/delete_returning.result b/mysql-test/r/delete_returning.result new file mode 100644 index 00000000000..4d8044c1a7a --- /dev/null +++ b/mysql-test/r/delete_returning.result @@ -0,0 +1,267 @@ +drop table if exists t1,t2; +drop view if exists v1; +drop procedure if exists p1; +CREATE TABLE t1 (a int, b varchar(32)); +INSERT INTO t1 VALUES +(7,'ggggggg'), (1,'a'), (3,'ccc'), +(4,'dddd'), (1,'A'), (2,'BB'), (4,'DDDD'), +(5,'EEEEE'), (7,'GGGGGGG'), (2,'bb'); +CREATE TABLE t1c SELECT * FROM t1; +CREATE TABLE t2 (c int); +INSERT INTO t2 VALUES +(4), (5), (7), (1); +CREATE TABLE t2c SELECT * FROM t2; +CREATE VIEW v1 AS SELECT a, UPPER(b) FROM t1; +DELETE FROM t1 WHERE a=2 RETURNING * ; +a b +2 BB +2 bb +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +4 DDDD +5 EEEEE +7 GGGGGGG +INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); +DELETE FROM t1 WHERE a=2 RETURNING b; +b +bb +BB +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +4 DDDD +5 EEEEE +7 GGGGGGG +DELETE FROM t1 WHERE a=2 RETURNING c; +ERROR 42S22: Unknown column 'c' in 'field list' +INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); +DELETE FROM t1 WHERE a=2 RETURNING a, UPPER(b); +a UPPER(b) +2 BB +2 BB +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +4 DDDD +5 EEEEE +7 GGGGGGG +INSERT INTO t1 VALUES (2,'BB'), (2,'bb'); +DELETE FROM t1 WHERE a=6 RETURNING b; +b +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +2 bb +4 DDDD +5 EEEEE +7 GGGGGGG +2 BB +DELETE FROM t1 WHERE a=2 RETURNING MAX(b); +ERROR HY000: Invalid use of group function +DELETE FROM t1 WHERE a < 5 RETURNING a, (SELECT MIN(c) FROM t2 WHERE c=a+1); +a (SELECT MIN(c) FROM t2 WHERE c=a+1) +1 NULL +3 4 +4 5 +1 NULL +2 NULL +4 5 +2 NULL +SELECT * FROM t1; +a b +7 ggggggg +5 EEEEE +7 GGGGGGG +DELETE FROM t1; +INSERT INTO t1 SELECT * FROM t1c; +DELETE FROM t2 WHERE c < 5 +RETURNING (SELECT GROUP_CONCAT(b) FROM t1 GROUP BY a HAVING a=c); +(SELECT GROUP_CONCAT(b) FROM t1 GROUP BY a HAVING a=c) +dddd,DDDD +a,A +SELECT * FROM t2; +c +5 +7 +DELETE FROM t2; +INSERT INTO t2 SELECT * FROM t2c; +CREATE FUNCTION f(arg INT) RETURNS TEXT +BEGIN +RETURN (SELECT GROUP_CONCAT(b) FROM t1 WHERE a=arg); +END| +DELETE FROM t2 WHERE c < 5 RETURNING f(c); +f(c) +dddd,DDDD +a,A +SELECT * FROM t2; +c +5 +7 +DELETE FROM t2; +INSERT INTO t2 SELECT * FROM t2c; +DROP FUNCTION f; +DELETE FROM v1 WHERE a < 5 RETURNING * ; +a UPPER(b) +1 A +3 CCC +4 DDDD +1 A +2 BB +4 DDDD +2 BB +SELECT * FROM t1; +a b +7 ggggggg +5 EEEEE +7 GGGGGGG +DELETE FROM t1; +INSERT INTO t1 SELECT * FROM t1c; +CREATE VIEW v11(a,c) AS SELECT a, COUNT(b) FROM t1 GROUP BY a; +DELETE FROM v11 WHERE a < 5 RETURNING * ; +ERROR HY000: The target table v11 of the DELETE is not updatable +DROP VIEW v11; +PREPARE stmt FROM +"DELETE FROM t1 WHERE a=2 ORDER BY b LIMIT 1 RETURNING a, UPPER(b)"; +EXECUTE stmt; +a UPPER(b) +2 BB +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +4 DDDD +5 EEEEE +7 GGGGGGG +2 bb +EXECUTE stmt; +a UPPER(b) +2 BB +SELECT * FROM t1; +a b +7 ggggggg +1 a +3 ccc +4 dddd +1 A +4 DDDD +5 EEEEE +7 GGGGGGG +DEALLOCATE PREPARE stmt; +DELETE FROM t1; +INSERT INTO t1 SELECT * FROM t1c; +FLUSH PRIVILEGES; +CREATE DATABASE mysqltest; +CREATE TABLE mysqltest.t1 SELECT * FROM t1; +GRANT DELETE ON mysqltest.* TO mysqltest_1@localhost; +GRANT SELECT(b) ON mysqltest.t1 TO mysqltest_1@localhost; +DELETE FROM mysqltest.t1 WHERE a=2 RETURNING b; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 't1' +DELETE FROM mysqltest.t1 RETURNING b; +b +ggggggg +a +ccc +dddd +A +BB +DDDD +EEEEE +GGGGGGG +bb +SELECT * FROM mysqltest.t1; +a b +INSERT INTO mysqltest.t1 SELECT * FROM t1; +GRANT SELECT(a) ON mysqltest.t1 TO mysqltest_1@localhost; +DELETE FROM mysqltest.t1 WHERE a=2 RETURNING b; +b +bb +BB +SELECT * FROM mysqltest.t1; +a b +7 GGGGGGG +5 EEEEE +4 DDDD +1 A +4 dddd +3 ccc +1 a +7 ggggggg +INSERT INTO mysqltest.t1 SELECT * FROM t1; +CREATE VIEW mysqltest.v1(a) AS SELECT a FROM mysqltest.t1; +GRANT SELECT, INSERT ON mysqltest.t1 TO mysqltest_1@localhost; +DELETE FROM mysqltest.v1; +SELECT * FROM mysqltest.t1; +a b +INSERT INTO mysqltest.t1 SELECT * FROM t1; +DELETE FROM mysqltest.v1 RETURNING a; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'a' in table 'v1' +GRANT SELECT ON mysqltest.* TO mysqltest_1@localhost; +DELETE FROM mysqltest.v1 RETURNING a; +a +7 +1 +3 +4 +1 +2 +4 +5 +7 +2 +SELECT * FROM mysqltest.t1; +a b +INSERT INTO mysqltest.t1 SELECT * FROM t1; +DROP DATABASE mysqltest; +DROP USER mysqltest_1@localhost; +DROP VIEW v1; +DROP TABLE t1,t2; +DROP TABLE t1c,t2c; +# +# Bug mdev-4918: DELETE ... RETURNING subquery with more than 1 row +# +CREATE TABLE t1 (i1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2); +DELETE FROM t1 ORDER BY i1 RETURNING ( SELECT i2 FROM t2 ); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1,t2; +# +# MDEV-4919: Packets out of order on a SELECT after calling a procedure with DELETE .. RETURNING +# +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +CREATE PROCEDURE p1 (a INT) +BEGIN +DELETE FROM t1 WHERE i = a RETURNING *; +INSERT INTO t1 VALUES (a); +END | +CALL p1(1); +i +1 +SELECT * FROM t1; +i +1 +2 +DROP PROCEDURE p1; +DROP TABLE t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 363a4700ee6..685e8ddd6bc 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -417,6 +417,29 @@ MIN(i) DROP TABLE t1; # End of 5.0 tests # +# MDEV-5005: Subquery in Procedure somehow affecting temporary table +# +create temporary table if not exists t1 (id int not null); +select A.* from ( select tt.* from t1 tt ) A; +id +prepare stmt from "select A.* from ( select tt.* from t1 tt ) A "; +execute stmt; +id +deallocate prepare stmt; +drop temporary table t1; +CREATE PROCEDURE p () +BEGIN +select A.* from ( select tt.* from t1 tt ) A ; +END | +create temporary table if not exists t1 (id int not null); +CALL p(); +id +CALL p(); +id +drop procedure p; +drop temporary table t1; +# End of 5.3 tests +# # Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, # temptable views # @@ -440,7 +463,6 @@ SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); ERROR 21000: Subquery returns more than 1 row DROP TABLE t1, t2; DROP VIEW v1, v2; -set optimizer_switch=@save_derived_optimizer_switch; create table t1 (n bigint(20) unsigned, d1 datetime, d2 datetime, key (d1)); insert t1 values (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00'); insert t1 values (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00'); @@ -454,3 +476,18 @@ n d1 d2 result 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 drop table t1; +SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(7); +EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1, +(SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY <derived3> ref key0 key0 5 const 0 +3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using temporary +Warnings: +Note 1249 Select 4 was reduced during optimization +DROP TABLE t1, t2; +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 459ece978fd..d23a6706e04 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -756,7 +756,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1), (1, 2, 3); EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL PRIMARY 16 NULL 7 Using index for group-by; Using temporary +1 SIMPLE t1 range NULL a 16 NULL 7 Using index for group-by SELECT DISTINCT a, b, d, c FROM t1; a b d c 1 1 0 1 diff --git a/mysql-test/r/drop_bad_db_type.result b/mysql-test/r/drop_bad_db_type.result new file mode 100644 index 00000000000..6a125cdccf5 --- /dev/null +++ b/mysql-test/r/drop_bad_db_type.result @@ -0,0 +1,12 @@ +set debug_dbug='+d,unstable_db_type'; +install soname 'ha_archive'; +create table t1 (a int) engine=archive; +insert t1 values (1),(2),(3); +flush tables; +uninstall soname 'ha_archive'; +install soname 'ha_archive'; +t1.ARZ +t1.frm +drop table t1; +uninstall soname 'ha_archive'; +set debug_dbug='-d,unstable_db_type'; diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index fa3f2234b7b..6014f5d0149 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1402,6 +1402,39 @@ v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI drop view v1; drop table t1; # +# MDEV-4811: Assertion `offset < 0x1f' fails in type_and_offset_store +# on COLUMN_ADD +# +CREATE TABLE t1 (dyn TINYBLOB) ENGINE=MyISAM; +INSERT INTO t1 SET dyn = COLUMN_CREATE( 40, REPEAT('a', 233), 4, REPEAT('b', 322) ); +Warnings: +Warning 1265 Data truncated for column 'dyn' at row 1 +SELECT COLUMN_ADD( dyn, 6, REPEAT('x',80), 4, REPEAT('y',215) AS INTEGER ) FROM t1; +ERROR HY000: Encountered illegal format of dynamic column string +delete from t1; +#above test with 10.0 format +INSERT INTO t1 SET dyn = COLUMN_CREATE( 'a', REPEAT('a', 250), 'b', REPEAT('b', 322) ); +Warnings: +Warning 1265 Data truncated for column 'dyn' at row 1 +SELECT COLUMN_ADD( dyn, 'c', REPEAT('x',80), 'b', REPEAT('y',215) AS INTEGER ) FROM t1; +ERROR HY000: Encountered illegal format of dynamic column string +DROP table t1; +# +# MDEV-4812: Valgrind warnings (Invalid write) in +# dynamic_column_update_many on COLUMN_ADD +# +CREATE TABLE t1 (dyncol TINYBLOB) ENGINE=MyISAM; +INSERT INTO t1 SET dyncol = COLUMN_CREATE( 7, REPEAT('k',487), 209, REPEAT('x',464) ); +Warnings: +Warning 1265 Data truncated for column 'dyncol' at row 1 +SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) FROM t1; +delete from t1; +INSERT INTO t1 SET dyncol = COLUMN_CREATE( 'a', REPEAT('k',487), 'b', REPEAT('x',464) ); +Warnings: +Warning 1265 Data truncated for column 'dyncol' at row 1 +SELECT COLUMN_ADD( dyncol, 'a', '22:22:22', 'c', REPEAT('x',270) AS CHAR ) FROM t1; +DROP table t1; +# # end of 5.3 tests # # @@ -1706,3 +1739,13 @@ select hex(column_create(1, "2012-12-21 10:46:06" AS datetime)) as hex, column_json(column_create(1, "2012-12-21 10:46:06" AS datetime)) as json; hex json 00010001000595B90F000060B80A00 {"1":"2012-12-21 10:46:06"} +# +# MDEV-4849: Out of memory error and valgrind warnings on COLUMN_ADD +# +CREATE TABLE t1 (dyncol tinyblob) ENGINE=MyISAM; +INSERT INTO t1 SET dyncol = COLUMN_CREATE( 3, REPEAT('a',330), 4, 'x' ); +Warnings: +Warning 1265 Data truncated for column 'dyncol' at row 1 +SELECT COLUMN_ADD( COLUMN_ADD( dyncol, 1, REPEAT('b',130) ), 3, 'y' ) FROM t1; +ERROR HY000: Encountered illegal format of dynamic column string +DROP TABLE t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 38aa3f49c4d..9049589b6db 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1971,6 +1971,7 @@ MIN(t2.pk) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: 'j' +Warning 1292 Truncated incorrect INTEGER value: 'j' EXPLAIN SELECT MIN(t2.pk) @@ -1984,6 +1985,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'j' +Warning 1292 Truncated incorrect INTEGER value: 'j' # # 2) Test that subquery materialization is setup for query with @@ -2124,6 +2126,19 @@ select z from (select count(*) as z from t1) v group by 1; z 4 drop table t1; +CREATE TABLE t1 (i1 int, INDEX(i1)); +INSERT INTO t1 VALUES (9),(8); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (8),(4); +CREATE TABLE t3 (i3 int, INDEX(i3)); +INSERT INTO t3 VALUES (9),(8); +SELECT MAX(t3.i3) FROM t3, t2, t1 WHERE t1.i1 = t2.i2 AND ( 0 OR t3.i3 = t2.i2 ); +MAX(t3.i3) +8 +SELECT MAX(t3.i3) FROM t3, t2, t1 WHERE t1.i1 = t2.i2 AND t3.i3 = t2.i2; +MAX(t3.i3) +8 +DROP TABLE t1,t2,t3; # end of 5.3 tests # # Bug#52123 Assertion failed: aggregator == aggr->Aggrtype(), diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index ace7283e192..5b9743fb33f 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -202,12 +202,12 @@ f3 time(6) YES NULL f4 time(6) YES NULL f5 time(6) YES NULL f6 time YES NULL -f7 datetime(6) YES NULL +f7 datetime YES NULL f8 date YES NULL f9 time(6) YES NULL select * from t1; f1 f2 f3 f4 f5 f6 f7 f8 f9 -1997-01-01 1998-01-02 01:01:00.000003 49:01:01.000001 46:58:57.999999 -24:00:00.000001 10:11:12 2001-12-01 01:01:01.000000 1997-12-31 23:59:59.000001 +1997-01-01 1998-01-02 01:01:00.000003 49:01:01.000001 46:58:57.999999 -24:00:00.000001 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59.000001 create table test(t1 datetime, t2 time, t3 time, t4 datetime); insert into test values ('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'), diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index ba9500bff46..93757800505 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -160,6 +160,25 @@ CONVERT( a USING latin1 ) DROP TABLE t1, t2; # +# Start of 5.3 tests +# +# +# MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(11); +SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; +INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) +8 +8 +Warnings: +Warning 1292 Incorrect datetime value: '10' +Warning 1292 Incorrect datetime value: '11' +DROP TABLE t1; +# +# End of 5.3 tests +# +# # BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 # CREATE TABLE t1(days set('1','2','3','4','5','6','7')); @@ -201,3 +220,22 @@ NULL 1,2,3,4,5,6,7 DROP TABLE t1; +# +# Start of 5.3 tests +# +# +# MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(11); +SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; +INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) +8 +8 +Warnings: +Warning 1292 Incorrect datetime value: '10' +Warning 1292 Incorrect datetime value: '11' +DROP TABLE t1; +# +# End of 5.3 tests +# diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index ad5d640b402..a588d89b919 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2623,13 +2623,13 @@ Warning 1292 Truncated incorrect DECIMAL value: '' # and other crashes # CREATE TABLE t1 ( a TEXT ); -SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'bug58165.txt'; +SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt';; SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ); insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' ) x Warnings: Warning 1292 Truncated incorrect INTEGER value: 'b' -LOAD DATA INFILE 'bug58165.txt' INTO TABLE t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1;; SELECT * FROM t1; a aaaaaaaaaaaaaa diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index f50458695a7..ee97371d344 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -11,17 +11,17 @@ now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(no 0 0 0 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0; from_unixtime(unix_timestamp("1994-03-02 10:11:12")) from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s") from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0 -1994-03-02 10:11:12.000000 1994-03-02 10:11:12 19940302101112.000000 +1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"), sec_to_time(time_to_sec("0:30:47")/6.21); sec_to_time(9001) sec_to_time(9001)+0 time_to_sec("15:12:22") sec_to_time(time_to_sec("0:30:47")/6.21) -02:30:01 23001 54742.000000 00:04:57.423510 +02:30:01 23001 54742 00:04:57.4235 select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899); sec_to_time(9001.1) time_to_sec('15:12:22.123456') time_to_sec(15.5566778899) 02:30:01.1 54742.123456 15.556677 select sec_to_time(time_to_sec('-838:59:59')); sec_to_time(time_to_sec('-838:59:59')) --838:59:59.000000 +-838:59:59 select sec_to_time('9001.1'), sec_to_time('1234567890123.123'); sec_to_time('9001.1') sec_to_time('1234567890123.123') 02:30:01.100000 838:59:59.999999 @@ -77,7 +77,7 @@ HOUR("1997-03-03 23:03:22") MINUTE("23:03:22") SECOND(230322) 23 3 22 select TIME(230322), TIME(230322.33), TIME("230322.33"); TIME(230322) TIME(230322.33) TIME("230322.33") -23:03:22 23:03:22.33 23:03:22.330000 +23:03:22 23:03:22.33 23:03:22.33 select week(19980101),week(19970101),week(19980101,1),week(19970101,1); week(19980101) week(19970101) week(19980101,1) week(19970101,1) 0 0 1 1 @@ -569,7 +569,7 @@ select @a:=FROM_UNIXTIME(1); 1970-01-01 03:00:01 select unix_timestamp(@a); unix_timestamp(@a) -1.000000 +1 select unix_timestamp('1969-12-01 19:00:01'); unix_timestamp('1969-12-01 19:00:01') NULL @@ -611,10 +611,10 @@ unix_timestamp('1969-12-30 01:00:00') NULL select unix_timestamp('2038-01-17 12:00:00'); unix_timestamp('2038-01-17 12:00:00') -2147331600.000000 +2147331600 select unix_timestamp('1970-01-01 03:00:01'); unix_timestamp('1970-01-01 03:00:01') -1.000000 +1 select unix_timestamp('2038-01-19 07:14:07'); unix_timestamp('2038-01-19 07:14:07') NULL @@ -971,7 +971,7 @@ Warnings: Warning 1292 Truncated incorrect time value: '15461882265600' SELECT TIME_TO_SEC('916:40:00'); TIME_TO_SEC('916:40:00') -3020399.999999 +3020399 Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' SELECT ADDTIME('500:00:00', '416:40:00'); @@ -1950,3 +1950,400 @@ NULL Warnings: Warning 1411 Incorrect datetime value: '2020' for function str_to_date SET TIME_ZONE=DEFAULT; +# +# MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context +# +CREATE TABLE t1 (a TIMESTAMP(3)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +c1 c2 c2 +20010101102030 20010101102030.999 20010101102031 +DROP TABLE t1; +CREATE TABLE t1 (a TIME(3)); +INSERT INTO t1 VALUES ('10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +c1 c2 c2 +102030 102030.999 102031 +DROP TABLE t1; +SELECT +CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, +CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, +COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, +CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, +TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, +DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, +TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, +CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, +IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, +CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, +TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, +DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, +TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, +CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, +IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, +CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, +TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, +DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, +TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +SELECT +CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, +CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, +CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, +CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, +TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, +DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, +TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010101000000 20010101000000.0000 20010101000000 2001-01-01 00:00:00 00:00:00 2001-01-01 2001-01-01 00:00:00 +SELECT +CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, +CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, +CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, +CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, +TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, +DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, +TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; +c1 c2 c3 c4 c5 c6 c7 +20010102102030 20010102102030.0000 20010102102030 2001-01-02 10:20:30 10:20:30 2001-01-02 2001-01-02 10:20:30 +CREATE TABLE t1 AS SELECT +CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1, +CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2, +CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3, +CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` varchar(10) DEFAULT NULL, + `c2` varchar(10) DEFAULT NULL, + `c3` varchar(10) DEFAULT NULL, + `c4` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types +# +CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01'); +SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +CASE WHEN 0 THEN dt2 ELSE t3 END +0000-00-00 00:00:00.567 +CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +SELECT * FROM t2; +CASE WHEN 0 THEN dt2 ELSE t3 END +0000-00-00 00:00:00.567 +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +CASE WHEN 0 THEN dt2 ELSE t3 END datetime(3) YES NULL +DROP TABLE t2; +SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1; +CASE WHEN 1 THEN dt2 ELSE t3 END +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1; +CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1; +CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) +2001-01-01 00:00:00.120 +SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +CONCAT(CASE WHEN 1 THEN d ELSE t3 END) +2002-01-01 00:00:00.000 +SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1; +CASE WHEN 1 THEN t3 ELSE d END +0000-00-00 00:00:00.567 +SELECT COALESCE(d, t3) FROM t1; +COALESCE(d, t3) +2002-01-01 00:00:00.000 +SELECT CONCAT(COALESCE(d, t3)) FROM t1; +CONCAT(COALESCE(d, t3)) +2002-01-01 00:00:00.000 +SELECT COALESCE(dt2, t3) FROM t1; +COALESCE(dt2, t3) +2001-01-01 00:00:00.120 +SELECT CONCAT(COALESCE(dt2, t3)) FROM t1; +CONCAT(COALESCE(dt2, t3)) +2001-01-01 00:00:00.120 +SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1; +IFNULL(dt2, t3) CONCAT(IFNULL(dt2, t3)) +2001-01-01 00:00:00.120 2001-01-01 00:00:00.120 +SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1; +IFNULL(d, t3) CONCAT(IFNULL(d, t3)) +2002-01-01 00:00:00.000 2002-01-01 00:00:00.000 +DROP TABLE t1; +# +# MDEV-4724 Some temporal functions do not preserve microseconds +# +SELECT MAKETIME(10,10,10.231); +MAKETIME(10,10,10.231) +10:10:10.231 +SELECT MAKETIME(0, 0, 59.9); +MAKETIME(0, 0, 59.9) +00:00:59.9 +CREATE TABLE t1 AS SELECT +MAKETIME(10,00,00), +MAKETIME(10,00,00.1), +MAKETIME(10,00,00.12), +MAKETIME(10,00,00.123), +MAKETIME(10,00,00.1234), +MAKETIME(10,00,00.12345), +MAKETIME(10,00,00.123456); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +MAKETIME(10,00,00) time YES NULL +MAKETIME(10,00,00.1) time(1) YES NULL +MAKETIME(10,00,00.12) time(2) YES NULL +MAKETIME(10,00,00.123) time(3) YES NULL +MAKETIME(10,00,00.1234) time(4) YES NULL +MAKETIME(10,00,00.12345) time(5) YES NULL +MAKETIME(10,00,00.123456) time(6) YES NULL +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIME('10:00:00'), +TIME('10:00:00.1'), +TIME('10:00:00.12'), +TIME('10:00:00.123'), +TIME('10:00:00.1234'), +TIME('10:00:00.12345'), +TIME('10:00:00.12346'); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIME('10:00:00') time YES NULL +TIME('10:00:00.1') time(1) YES NULL +TIME('10:00:00.12') time(2) YES NULL +TIME('10:00:00.123') time(3) YES NULL +TIME('10:00:00.1234') time(4) YES NULL +TIME('10:00:00.12345') time(5) YES NULL +TIME('10:00:00.12346') time(5) YES NULL +DROP TABLE t1; +SET TIME_ZONE='+00:00'; +SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17'); +NOW() UNIX_TIMESTAMP() UNIX_TIMESTAMP(NOW()) UNIX_TIMESTAMP('2012-10-16 22:46:17') +2012-10-16 22:46:17 1350427577 1350427577 1350427577 +SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); +SELECT @@timestamp, FROM_UNIXTIME(@@timestamp); +@@timestamp FROM_UNIXTIME(@@timestamp) +97445.123456 1970-01-02 03:04:05.123456 +SET TIME_ZONE=DEFAULT; +SET TIMESTAMP=DEFAULT; +SELECT TIME('2012-10-16 15:54:16.12'); +TIME('2012-10-16 15:54:16.12') +15:54:16.12 +SELECT TIMESTAMP('2012-10-16 15:54:16.12'); +TIMESTAMP('2012-10-16 15:54:16.12') +2012-10-16 15:54:16.12 +SELECT TIMEDIFF('10:10:10.1','00:00:00'); +TIMEDIFF('10:10:10.1','00:00:00') +10:10:10.1 +SELECT TIME_TO_SEC('10:10:10'); +TIME_TO_SEC('10:10:10') +36610 +SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12'); +ADDTIME(TIME('10:10:10.1'),'10:10:10.12') +20:20:20.22 +SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12'); +ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12') +2001-01-01 20:20:20.22 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) +2001-01-01 00:00:01 +SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND) +2001-01-01 00:00:01.1 +SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND) +2001-01-01 00:00:01.12 +SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND) +2001-01-01 00:00:01.123 +SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND) +2001-01-01 00:00:01.1234 +SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND) +2001-01-01 00:00:01.12345 +SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND) +2001-01-01 00:00:01.123456 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND) +2001-01-01 00:00:01 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND) +2001-01-01 00:00:01.1 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND) +2001-01-01 00:00:01.12 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND) +2001-01-01 00:00:01.123 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND) +2001-01-01 00:00:01.1234 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND) +2001-01-01 00:00:01.12345 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND) +2001-01-01 00:00:01.123456 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND) +2000-12-31 23:59:59 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND) +2000-12-31 23:59:58.9 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND) +2000-12-31 23:59:58.88 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND) +2000-12-31 23:59:58.877 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND) +2000-12-31 23:59:58.8766 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND) +2000-12-31 23:59:58.87655 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND) +2000-12-31 23:59:58.876544 +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND); +DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND) +2001-01-01 00:00:00.000000 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: 'xxx' +SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00'); +CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00') +2001-01-01 11:20:30.12 +# +# MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision +# +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +t0 + INTERVAL 1.1 SECOND +00:00:01.1 +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +t0 + INTERVAL 1.1 SECOND time(1) YES NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +t0 + INTERVAL 1.1 SECOND +2001-01-01 00:00:01.1 +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +Field Type Null Key Default Extra +t0 + INTERVAL 1.1 SECOND datetime(1) YES NULL +DROP TABLE t1, t2; +# +# MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10') +# +CREATE TABLE t1 AS SELECT +TIMESTAMP('2001-01-01','10:10:10'), +TIMESTAMP('2001-01-01','10:10:10.1'), +TIMESTAMP('2001-01-01','10:10:10.12'), +TIMESTAMP('2001-01-01','10:10:10.123'), +TIMESTAMP('2001-01-01','10:10:10.1234'), +TIMESTAMP('2001-01-01','10:10:10.12345'), +TIMESTAMP('2001-01-01','10:10:10.123456'), +TIMESTAMP('2001-01-01','10:10:10.1234567'); +Warnings: +Note 1292 Truncated incorrect time value: '10:10:10.1234567' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('2001-01-01','10:10:10') datetime YES NULL +TIMESTAMP('2001-01-01','10:10:10.1') datetime(1) YES NULL +TIMESTAMP('2001-01-01','10:10:10.12') datetime(2) YES NULL +TIMESTAMP('2001-01-01','10:10:10.123') datetime(3) YES NULL +TIMESTAMP('2001-01-01','10:10:10.1234') datetime(4) YES NULL +TIMESTAMP('2001-01-01','10:10:10.12345') datetime(5) YES NULL +TIMESTAMP('2001-01-01','10:10:10.123456') datetime(6) YES NULL +TIMESTAMP('2001-01-01','10:10:10.1234567') datetime(6) YES NULL +SELECT * FROM t1; +TIMESTAMP('2001-01-01','10:10:10') TIMESTAMP('2001-01-01','10:10:10.1') TIMESTAMP('2001-01-01','10:10:10.12') TIMESTAMP('2001-01-01','10:10:10.123') TIMESTAMP('2001-01-01','10:10:10.1234') TIMESTAMP('2001-01-01','10:10:10.12345') TIMESTAMP('2001-01-01','10:10:10.123456') TIMESTAMP('2001-01-01','10:10:10.1234567') +2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIMESTAMP('2001-01-01 00:00:00','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'), +TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10'); +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00.1234567' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('2001-01-01 00:00:00','10:10:10') datetime YES NULL +TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') datetime(1) YES NULL +TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') datetime(2) YES NULL +TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') datetime(3) YES NULL +TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') datetime(4) YES NULL +TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') datetime(5) YES NULL +TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') datetime(6) YES NULL +TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') datetime(6) YES NULL +SELECT * FROM t1; +TIMESTAMP('2001-01-01 00:00:00','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10') TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10') TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10') TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10') +2001-01-01 10:10:10 2001-01-01 10:10:10.1 2001-01-01 10:10:10.12 2001-01-01 10:10:10.123 2001-01-01 10:10:10.1234 2001-01-01 10:10:10.12345 2001-01-01 10:10:10.123456 2001-01-01 10:10:10.123456 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIMESTAMP('00:00:00','10:10:10'), +TIMESTAMP(TIME('00:00:00'),'10:10:10'); +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +TIMESTAMP('00:00:00','10:10:10') datetime YES NULL +TIMESTAMP(TIME('00:00:00'),'10:10:10') datetime YES NULL +SELECT * FROM t1; +TIMESTAMP('00:00:00','10:10:10') TIMESTAMP(TIME('00:00:00'),'10:10:10') +NULL NULL +DROP TABLE t1; +# +# MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) +# +SELECT MAKETIME(0, 0, -0.1); +MAKETIME(0, 0, -0.1) +NULL diff --git a/mysql-test/r/func_time_hires.result b/mysql-test/r/func_time_hires.result index 4aa0333c4b4..62004687c22 100644 --- a/mysql-test/r/func_time_hires.result +++ b/mysql-test/r/func_time_hires.result @@ -15,10 +15,10 @@ current_time(3) 01:01:01.123 current_timestamp(4) 2011-01-01 01:01:01.1234 localtime(5) 2011-01-01 01:01:01.12345 localtimestamp(6) 2011-01-01 01:01:01.123456 -time_to_sec('12:34:56') 45296.000000 -time_to_sec('12:34:56.789') 45296.789000 +time_to_sec('12:34:56') 45296 +time_to_sec('12:34:56.789') 45296.789 select sec_to_time(time_to_sec('1:2:3')), sec_to_time(time_to_sec('2:3:4.567890')); -sec_to_time(time_to_sec('1:2:3')) 01:02:03.000000 +sec_to_time(time_to_sec('1:2:3')) 01:02:03 sec_to_time(time_to_sec('2:3:4.567890')) 02:03:04.567890 select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222)); time_to_sec(sec_to_time(11111)) 11111 @@ -48,7 +48,7 @@ t1 CREATE TABLE `t1` ( `localtime(5)` datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000', `localtimestamp(6)` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', `time_to_sec(123456)` bigint(17) DEFAULT NULL, - `time_to_sec('12:34:56.789')` decimal(22,6) DEFAULT NULL + `time_to_sec('12:34:56.789')` decimal(19,3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t1; sec_to_time(12345) 03:25:45 @@ -63,15 +63,15 @@ current_timestamp(4) 2011-01-01 01:01:01.1234 localtime(5) 2011-01-01 01:01:01.12345 localtimestamp(6) 2011-01-01 01:01:01.123456 time_to_sec(123456) 45296 -time_to_sec('12:34:56.789') 45296.789000 +time_to_sec('12:34:56.789') 45296.789 drop table t1; select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));; -unix_timestamp('2011-01-01 01:01:01') 1293832861.000000 +unix_timestamp('2011-01-01 01:01:01') 1293832861 unix_timestamp('2011-01-01 01:01:01.123456') 1293832861.123456 unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))) 1293832861 unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4))) 1293832861.1234 select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));; -from_unixtime(unix_timestamp('2011/1/1 1:1:1')) 2011-01-01 01:01:01.000000 +from_unixtime(unix_timestamp('2011/1/1 1:1:1')) 2011-01-01 01:01:01 from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')) 2011-01-01 01:01:01.123456 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))) 2011-01-01 01:01:01 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4)))) 2011-01-01 01:01:01.1234 @@ -167,7 +167,7 @@ CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00') 2011-01-02 15:00:00 SELECT CONVERT_TZ('2011-01-02 12:00:00.123', '+00:00', '+03:00'); CONVERT_TZ('2011-01-02 12:00:00.123', '+00:00', '+03:00') -2011-01-02 15:00:00.123000 +2011-01-02 15:00:00.123 SELECT CONVERT_TZ('2011-01-02 12:00:00.123456', '+00:00', '+03:00'); CONVERT_TZ('2011-01-02 12:00:00.123456', '+00:00', '+03:00') 2011-01-02 15:00:00.123456 @@ -200,7 +200,7 @@ time(f1) alter table t1 modify f1 varchar(100); select time(f1) from t1; time(f1) -21:00:00 +21:00:00.000000 select time(f1) from t1 union all select time(f1 + interval 1 second) from t1; time(f1) 21:00:00.000000 diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index be05f17c281..c50f6e05621 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1618,8 +1618,8 @@ ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't show grants for mysqluser10@localhost; Grants for mysqluser10@localhost GRANT USAGE ON *.* TO 'mysqluser10'@'localhost' -GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser10'@'localhost' GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser10'@'localhost' +GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser10'@'localhost' GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser10'@'localhost' GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser10'@'localhost' select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index a1f19204274..367cd4119c7 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2080,7 +2080,7 @@ f1 f2 explain select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx 5 NULL 7 Using index for group-by; Using temporary; Using filesort +1 SIMPLE t1 index NULL idx 5 NULL 20 Using index; Using temporary; Using filesort select col1 f1, col1 f2 from t1 group by f2 order by f2, f1; f1 f2 1 1 diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 229481f5ec8..43b0f4d56f3 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2126,6 +2126,31 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +create table t4 as select distinct a1, a2, b, c from t1; +alter table t4 add unique index idxt4 (a1, a2, b, c); +explain +select a1, a2, b, min(c) from t4 group by a1, a2, b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 index NULL idxt4 163 NULL 64 Using index +select a1, a2, b, min(c) from t4 group by a1, a2, b; +a1 a2 b min(c) +a a a a111 +a a b e112 +a b a i121 +a b b m122 +b a a a211 +b a b e212 +b b a i221 +b b b m222 +c a a a311 +c a b e312 +c b a i321 +c b b m322 +d a a a411 +d a b e412 +d b a i421 +d b b m422 +drop table t4; explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index @@ -2249,14 +2274,14 @@ COUNT(DISTINCT a) 1 DROP TABLE t1; DROP PROCEDURE a; -CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)); +CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a)); INSERT INTO t1 (a) VALUES (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL PRIMARY 66 NULL 12 Using index for group-by +1 SIMPLE t1 range NULL a 66 NULL 6 Using index for group-by SELECT DISTINCT a,a FROM t1 ORDER BY a; a a @@ -2314,11 +2339,11 @@ t1; id2 id3 id5 id4 id3 id6 id5 id1 1 1 1 1 1 1 1 1 DROP TABLE t1,t2,t3,t4,t5,t6; -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)); +CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b)); INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3); explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by +1 SIMPLE t1 range a,b a 10 NULL 1 Using where; Using index for group-by SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; MAX(b) a 1 1 @@ -2329,7 +2354,7 @@ CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); INSERT INTO t2 SELECT a,b,b FROM t1; explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by +1 SIMPLE t2 ref PRIMARY PRIMARY 8 const,const 1 Using where; Using index SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; MIN(c) 2 @@ -2525,7 +2550,7 @@ a MIN(b) MAX(b) AVG(b) 2 1 3 2.0000 1 1 3 2.0000 DROP TABLE t1; -create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM; +create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM; insert into t1 (a,b) values (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6), (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13), @@ -2597,7 +2622,7 @@ a b 3 13 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY,index PRIMARY 4 const 15 100.00 Using index; Using temporary +1 SIMPLE t1 ref a,index a 5 const 15 100.00 Using index; Using temporary Warnings: Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a` drop table t1; @@ -3363,9 +3388,10 @@ COUNT(DISTINCT a, b + 0) 16 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL a 10 NULL 9 Using index for group-by +1 SIMPLE t1 index NULL a 10 NULL 16 Using index SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT b) < 10; COUNT(DISTINCT a) +2 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 HAVING COUNT(DISTINCT c) < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 16 @@ -3499,7 +3525,7 @@ WHERE b = 13 AND c = 42 GROUP BY a; a COUNT(DISTINCT a) SUM(DISTINCT a) EXPLAIN SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL a 10 NULL 9 Using where; Using index for group-by +1 SIMPLE t2 index NULL a 15 NULL 16 Using where; Using index SELECT COUNT(DISTINCT a, b), SUM(DISTINCT a) FROM t2 WHERE b = 42; COUNT(DISTINCT a, b) SUM(DISTINCT a) 0 NULL @@ -3547,7 +3573,7 @@ DROP TABLE t1,t2; CREATE TABLE t1 ( f1 int(11) NOT NULL DEFAULT '0', f2 char(1) NOT NULL DEFAULT '', -PRIMARY KEY (f1,f2) +KEY (f1,f2) ) ; insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), (3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); @@ -3558,7 +3584,7 @@ f1 COUNT(DISTINCT f2) 3 4 explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL PRIMARY 5 NULL 9 Using index for group-by (scanning) +1 SIMPLE t1 range NULL f1 5 NULL 9 Using index for group-by (scanning) drop table t1; # End of test#50539. # @@ -3582,7 +3608,7 @@ INSERT INTO faulty (b, c) VALUES EXPLAIN SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE faulty range b_and_c b_and_c 12 NULL 2 Using where; Using index for group-by; Using filesort +1 SIMPLE faulty ref b_and_c b_and_c 4 const 2 Using where; Using index SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c; b c 1802 2013-02-28 09:00:00 diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index ce962a96f29..bf25b1c28ee 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -43,7 +43,7 @@ create view v1 (c) as SELECT table_name FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name not like 'ndb_%' AND table_name not like 'innodb_%' AND -table_name not like 'pbxt_%' AND table_name not like 'xtradb_%'; +table_name not like 'xtradb_%'; select * from v1; c ALL_PLUGINS diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 7110f541fb7..a722ab8d97f 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -663,9 +663,10 @@ Warning 1365 Division by 0 Warning 1048 Column 'data' cannot be null update t1 set data='envelope' where 1/0 or 1; affected rows: 2 -info: Rows matched: 2 Changed: 2 Warnings: 3 +info: Rows matched: 2 Changed: 2 Warnings: 4 Warnings: Warning 1365 Division by 0 +Warning 1365 Division by 0 Warning 1265 Data truncated for column 'data' at row 1 Warning 1265 Data truncated for column 'data' at row 2 insert t1 (data) values (default), (1/0), ('dead beef'); diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 0e5b36acb10..fdd2027990f 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1460,7 +1460,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE D system PRIMARY NULL NULL NULL 1 1 SIMPLE DSAR system NULL NULL NULL NULL 1 1 SIMPLE DT range t_id t_id 2 NULL 2 Using where -1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,test.DT.t_id,test.D.birthday 1 Using index +1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,test.DT.t_id,func 1 Using index SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id; @@ -1477,6 +1477,25 @@ DROP TABLE t1,t2,t3,t4,t5; # SELECT * FROM t5 JOIN (t1 JOIN t2 UNION SELECT * FROM t3 JOIN t4); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +# +# MDEV-4959: join of const table with NULL fields +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 int, a int, b int) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; +i1 i2 a b +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where ((`v2`.`i2` = NULL) and (`v2`.`a` < `v2`.`b`)) +DROP VIEW v2; +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; # # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 8920539ef2e..c9b694c9532 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2117,4 +2117,109 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id WHERE a.modified > b.modified or b.modified IS NULL; id modified DROP TABLE t1; +# +# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +# +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +drop table t0, t1; +# +# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +# (this is a regression after fix for MDEV-4817) +# +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +id d i +1 0000-00-00 NULL +2 0000-00-00 NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +i1 d1 i2 j2 +2 0000-00-00 NULL NULL +DROP TABLE t1,t2; +# Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +i1 i2 a b +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; +i1 i2 a b +drop view v2; +drop table t1,t2; +# +# Bug mdev-4942: LEFT JOIN with conjunctive +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 ( i1 int, d1 date ); +INSERT INTO t1 VALUES (1,'2001-06-26'), (2,'2000-11-16'); +CREATE TABLE t2 ( i2 int, d2 date NOT NULL ); +INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25'); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL; +i1 d1 i2 d2 +DROP TABLE t1,t2; +# +# Bug mdev-4952: LEFT JOIN with disjunctive +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 10), (2, 11); +CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200); +SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2 +WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL; +a1 b1 dt a2 b2 +DROP TABLE t1,t2; +# +# Bug mdev-4962: nested outer join with +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (10),(20); +CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 +WHERE d3 IS NULL; +i1 i2 i3 d3 +1 NULL NULL NULL +2 NULL NULL NULL +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 +WHERE d3 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result index 95f6b3ab9a2..339cc59ed69 100644 --- a/mysql-test/r/join_outer_innodb.result +++ b/mysql-test/r/join_outer_innodb.result @@ -14,8 +14,8 @@ EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index DROP TABLE t1,t2; # # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 43443aa2fef..eae21a090c4 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2128,6 +2128,111 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id WHERE a.modified > b.modified or b.modified IS NULL; id modified DROP TABLE t1; +# +# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +# +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index +drop table t0, t1; +# +# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +# (this is a regression after fix for MDEV-4817) +# +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +id d i +1 0000-00-00 NULL +2 0000-00-00 NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +i1 d1 i2 j2 +2 0000-00-00 NULL NULL +DROP TABLE t1,t2; +# Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +i1 i2 a b +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; +i1 i2 a b +drop view v2; +drop table t1,t2; +# +# Bug mdev-4942: LEFT JOIN with conjunctive +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 ( i1 int, d1 date ); +INSERT INTO t1 VALUES (1,'2001-06-26'), (2,'2000-11-16'); +CREATE TABLE t2 ( i2 int, d2 date NOT NULL ); +INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25'); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL; +i1 d1 i2 d2 +DROP TABLE t1,t2; +# +# Bug mdev-4952: LEFT JOIN with disjunctive +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 10), (2, 11); +CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES +('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200); +SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2 +WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL; +a1 b1 dt a2 b2 +DROP TABLE t1,t2; +# +# Bug mdev-4962: nested outer join with +# <non-nullable datetime field> IS NULL in WHERE +# causes an assert failure +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (10),(20); +CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 +WHERE d3 IS NULL; +i1 i2 i3 d3 +1 NULL NULL NULL +2 NULL NULL NULL +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 +WHERE d3 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result index e91db0c9036..6afce67cc1a 100644 --- a/mysql-test/r/kill.result +++ b/mysql-test/r/kill.result @@ -298,5 +298,15 @@ select 1; Got one of the listed errors select 1; Got one of the listed errors +# +# MDEV-4911 - add KILL query id, and add query id information to +# processlist +# +SELECT SLEEP(1000); +KILL QUERY ID @id; +SLEEP(1000) +1 +KILL QUERY ID 0; +ERROR HY000: Unknown query id: 0 SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 67712ccabe6..f9e4a88fe95 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -18,7 +18,7 @@ n 4 drop table t1; create table t1(n int); -insert into t1 values (1); +insert into t1 values (1),(2); select get_lock("mysqltest_lock", 100); get_lock("mysqltest_lock", 100) 1 @@ -27,11 +27,13 @@ update low_priority t1 set n = 4; select n from t1; n 1 +2 select release_lock("mysqltest_lock"); release_lock("mysqltest_lock") 1 n 1 +2 select release_lock("mysqltest_lock"); release_lock("mysqltest_lock") 1 diff --git a/mysql-test/r/myisam_optimize.result b/mysql-test/r/myisam_optimize.result new file mode 100644 index 00000000000..5c9dee9a9ca --- /dev/null +++ b/mysql-test/r/myisam_optimize.result @@ -0,0 +1,23 @@ +call mtr.add_suppression(" marked as crashed and should be repaired"); +create table t1 (a int auto_increment primary key, b text); +insert t1 (b) select repeat("A", 100); +insert t1 (b) select repeat("B", 200) from t1; +insert t1 (b) select repeat("C", 300) from t1; +delete from t1 where a < 3; +explain select a, left(b,10) from t1 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort +select left(b,10), a from t1 order by a; +left(b,10) a +CCCCCCCCCC 3 +CCCCCCCCCC 4 +set debug_sync='myisam_before_repair_by_sort wait_for go'; +optimize table t1; +select a, left(b,10) from t1 order by a; +set debug_sync='now signal go'; +Table Op Msg_type Msg_text +test.t1 optimize status OK +a left(b,10) +3 CCCCCCCCCC +4 CCCCCCCCCC +drop table t1; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 4d2ac25a0eb..cb705d285fe 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -515,5 +515,9 @@ aa`bb``cc drop database `aa``bb````cc`; a >>\ndelimiter\n<< ++-------------------+ +| a | +| aaaaaaaaaaaaaaaaa | ++-------------------+ End of tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index dd000463548..a5b4efe95aa 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -630,7 +630,7 @@ We expect this value to be 2 (one for the INSERT, one for COMMIT). The bug being tested was that 'Query' lines were not preceded by '#' If the line is in the table, it had to have been preceded by a '#' -SELECT COUNT(*) AS `BUG#28293_expect_2` FROM patch WHERE a LIKE '%Query%'; +SELECT COUNT(*) AS `BUG#28293_expect_2` FROM patch WHERE a LIKE '#%Query%'; BUG#28293_expect_2 2 DROP TABLE patch; @@ -914,3 +914,339 @@ t1 SHOW TABLES IN test; Tables_in_test SET GLOBAL SERVER_ID = 1; +# +# MDEV-4645: Incorrect reads of frozen binlog events; +# FDE corrupted in relay log +# +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +# at 4 +#130807 23:29:20 server id 1 end_log_pos 106 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 4 |20 ae 02 52 |0f |01 00 00 00 |66 00 00 00 |6a 00 00 00 |00 01 +# +# 17 04 00 35 2e 31 2e 36 33 2d 67 6f 6f 67 6c 65 2d |..5.1.63-google-| +# 27 64 65 62 75 67 2d 6c 6f 67 00 00 00 00 00 00 00 |debug-log.......| +# 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| +# 47 00 00 00 00 20 ae 02 52 17 38 0d 00 08 00 12 00 |.... ..R.8......| +# 57 04 04 04 04 12 00 00 53 00 04 1a 08 00 00 00 08 |.......S........| +# 67 08 08 02 |...| +# +# Event: Start: binlog v 4, server v 5.1.63-google-debug-log created 130807 23:29:20 at startup +ROLLBACK/*!*/; +BINLOG ' +IK4CUg8BAAAAZgAAAGoAAAAAAQQANS4xLjYzLWdvb2dsZS1kZWJ1Zy1sb2cAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAAAgrgJSFzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'/*!*/; +# at 106 +#130807 23:29:24 server id 1 end_log_pos 207 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 6a |24 ae 02 52 |02 |01 00 00 00 |65 00 00 00 |cf 00 00 00 |00 00 +# +# 81 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 91 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# a1 04 08 00 08 00 08 00 00 63 72 65 61 74 65 20 74 |........create t| +# b1 61 62 6c 65 20 74 65 73 74 2e 74 31 20 28 69 64 |able test.t1 (id| +# c1 20 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 29 | int not null)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907364/*!*/; +SET @@session.pseudo_thread_id=1/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table test.t1 (id int not null) +/*!*/; +# at 207 +#130807 23:29:26 server id 1 end_log_pos 305 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# cf |26 ae 02 52 |02 |01 00 00 00 |62 00 00 00 |31 01 00 00 |00 00 +# +# e6 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# f6 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 106 04 08 00 08 00 08 00 00 69 6e 73 65 72 74 20 69 |........insert i| +# 116 6e 74 6f 20 74 65 73 74 2e 74 31 20 28 69 64 29 |nto test.t1 (id)| +# 126 20 76 61 6c 75 65 73 20 28 31 29 | values (1)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907366/*!*/; +insert into test.t1 (id) values (1) +/*!*/; +# at 305 +#130807 23:29:28 server id 1 end_log_pos 386 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 131 |28 ae 02 52 |02 |01 00 00 00 |51 00 00 00 |82 01 00 00 |00 00 +# +# 148 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 158 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 168 04 08 00 08 00 08 00 00 64 72 6f 70 20 74 61 62 |........drop tab| +# 178 6c 65 20 74 65 73 74 2e 74 31 |le test.t1| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907368/*!*/; +drop table test.t1 +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +# at 4 +#130807 23:25:35 server id 1 end_log_pos 106 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 4 |3f ad 02 52 |0f |01 00 00 00 |66 00 00 00 |6a 00 00 00 |00 00 +# +# 17 04 00 35 2e 31 2e 36 33 2d 67 6f 6f 67 6c 65 2d |..5.1.63-google-| +# 27 64 65 62 75 67 2d 6c 6f 67 00 00 00 00 00 00 00 |debug-log.......| +# 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| +# 47 00 00 00 00 3f ad 02 52 1b 38 0d 00 08 00 12 00 |....?..R.8......| +# 57 04 04 04 04 12 00 00 53 00 04 1a 08 00 00 00 08 |.......S........| +# 67 08 08 02 |...| +# +# Event: Start: binlog v 4, server v 5.1.63-google-debug-log created 130807 23:25:35 at startup +ROLLBACK/*!*/; +BINLOG ' +P60CUg8BAAAAZgAAAGoAAAAAAAQANS4xLjYzLWdvb2dsZS1kZWJ1Zy1sb2cAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAAA/rQJSGzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'/*!*/; +# at 106 +#130807 23:25:41 server id 1 end_log_pos 211 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 6a |45 ad 02 52 |02 |01 00 00 00 |69 00 00 00 |d3 00 00 00 |00 00 +# +# 85 01 00 00 00 01 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 95 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# a5 04 08 00 08 00 08 00 00 63 72 65 61 74 65 20 74 |........create t| +# b5 61 62 6c 65 20 74 65 73 74 2e 74 31 20 28 69 64 |able test.t1 (id| +# c5 20 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 29 | int not null)| +# +# Event: Query thread_id=1 exec_time=1 error_code=0 +SET TIMESTAMP=1375907141/*!*/; +SET @@session.pseudo_thread_id=1/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table test.t1 (id int not null) +/*!*/; +# at 211 +#130807 23:25:44 server id 1 end_log_pos 313 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# d3 |48 ad 02 52 |02 |01 00 00 00 |66 00 00 00 |39 01 00 00 |00 00 +# +# ee 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# fe 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 10e 04 08 00 08 00 08 00 00 69 6e 73 65 72 74 20 69 |........insert i| +# 11e 6e 74 6f 20 74 65 73 74 2e 74 31 20 28 69 64 29 |nto test.t1 (id)| +# 12e 20 76 61 6c 75 65 73 20 28 31 29 | values (1)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907144/*!*/; +insert into test.t1 (id) values (1) +/*!*/; +# at 313 +#130807 23:25:48 server id 1 end_log_pos 398 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 139 |4c ad 02 52 |02 |01 00 00 00 |55 00 00 00 |8e 01 00 00 |00 00 +# +# 154 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 164 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 174 04 08 00 08 00 08 00 00 64 72 6f 70 20 74 61 62 |........drop tab| +# 184 6c 65 20 74 65 73 74 2e 74 31 |le test.t1| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907148/*!*/; +drop table test.t1 +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +# at 4 +#130807 23:20:55 server id 1 end_log_pos 106 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 4 |27 ac 02 52 |0f |01 00 00 00 |66 00 00 00 |6a 00 00 00 |00 01 +# +# 17 04 00 35 2e 31 2e 36 33 2d 67 6f 6f 67 6c 65 2d |..5.1.63-google-| +# 27 64 65 62 75 67 2d 6c 6f 67 00 00 00 00 00 00 00 |debug-log.......| +# 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| +# 47 00 00 00 00 27 ac 02 52 1f 38 0d 00 08 00 12 00 |....'..R.8......| +# 57 04 04 04 04 12 00 00 53 00 04 1a 08 00 00 00 08 |.......S........| +# 67 08 08 02 |...| +# +# Event: Start: binlog v 4, server v 5.1.63-google-debug-log created 130807 23:20:55 at startup +ROLLBACK/*!*/; +BINLOG ' +J6wCUg8BAAAAZgAAAGoAAAAAAQQANS4xLjYzLWdvb2dsZS1kZWJ1Zy1sb2cAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAAAnrAJSHzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'/*!*/; +# at 106 +#130807 23:21:19 server id 1 end_log_pos 215 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 6a |3f ac 02 52 |02 |01 00 00 00 |6d 00 00 00 |d7 00 00 00 |00 00 +# +# 89 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 99 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# a9 04 08 00 08 00 08 00 00 63 72 65 61 74 65 20 74 |........create t| +# b9 61 62 6c 65 20 74 65 73 74 2e 74 31 20 28 69 64 |able test.t1 (id| +# c9 20 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 29 | int not null)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375906879/*!*/; +SET @@session.pseudo_thread_id=1/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table test.t1 (id int not null) +/*!*/; +# at 215 +#130807 23:21:31 server id 1 end_log_pos 321 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# d7 |4b ac 02 52 |02 |01 00 00 00 |6a 00 00 00 |41 01 00 00 |00 00 +# +# f6 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 106 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 116 04 08 00 08 00 08 00 00 69 6e 73 65 72 74 20 69 |........insert i| +# 126 6e 74 6f 20 74 65 73 74 2e 74 31 20 28 69 64 29 |nto test.t1 (id)| +# 136 20 76 61 6c 75 65 73 20 28 31 29 | values (1)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375906891/*!*/; +insert into test.t1 (id) values (1) +/*!*/; +# at 321 +#130807 23:21:41 server id 1 end_log_pos 410 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 141 |55 ac 02 52 |02 |01 00 00 00 |59 00 00 00 |9a 01 00 00 |00 00 +# +# 160 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 170 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 180 04 08 00 08 00 08 00 00 64 72 6f 70 20 74 61 62 |........drop tab| +# 190 6c 65 20 74 65 73 74 2e 74 31 |le test.t1| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375906901/*!*/; +drop table test.t1 +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +# at 4 +#130807 23:38:51 server id 1 end_log_pos 106 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 4 |5b b0 02 52 |0f |01 00 00 00 |66 00 00 00 |6a 00 00 00 |00 00 +# +# 17 04 00 35 2e 31 2e 36 33 2d 67 6f 6f 67 6c 65 2d |..5.1.63-google-| +# 27 64 65 62 75 67 2d 6c 6f 67 00 00 00 00 00 00 00 |debug-log.......| +# 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| +# 47 00 00 00 00 5b b0 02 52 13 38 0d 00 08 00 12 00 |....[..R.8......| +# 57 04 04 04 04 12 00 00 53 00 04 1a 08 00 00 00 08 |.......S........| +# 67 08 08 02 |...| +# +# Event: Start: binlog v 4, server v 5.1.63-google-debug-log created 130807 23:38:51 at startup +ROLLBACK/*!*/; +BINLOG ' +W7ACUg8BAAAAZgAAAGoAAAAAAAQANS4xLjYzLWdvb2dsZS1kZWJ1Zy1sb2cAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAABbsAJSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'/*!*/; +# at 106 +#130807 23:38:53 server id 1 end_log_pos 203 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 6a |5d b0 02 52 |02 |01 00 00 00 |61 00 00 00 |cb 00 00 00 |00 00 +# +# 7d 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 8d 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 9d 04 08 00 08 00 08 00 00 63 72 65 61 74 65 20 74 |........create t| +# ad 61 62 6c 65 20 74 65 73 74 2e 74 31 20 28 69 64 |able test.t1 (id| +# bd 20 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 29 | int not null)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907933/*!*/; +SET @@session.pseudo_thread_id=1/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table test.t1 (id int not null) +/*!*/; +# at 203 +#130807 23:38:55 server id 1 end_log_pos 297 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# cb |5f b0 02 52 |02 |01 00 00 00 |5e 00 00 00 |29 01 00 00 |00 00 +# +# de 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# ee 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# fe 04 08 00 08 00 08 00 00 69 6e 73 65 72 74 20 69 |........insert i| +# 10e 6e 74 6f 20 74 65 73 74 2e 74 31 20 28 69 64 29 |nto test.t1 (id)| +# 11e 20 76 61 6c 75 65 73 20 28 31 29 | values (1)| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907935/*!*/; +insert into test.t1 (id) values (1) +/*!*/; +# at 297 +#130807 23:38:57 server id 1 end_log_pos 374 +# Position +# |Timestamp |Type |Master ID |Size |Master Pos |Flags +# 129 |61 b0 02 52 |02 |01 00 00 00 |4d 00 00 00 |76 01 00 00 |00 00 +# +# 13c 01 00 00 00 00 00 00 00 00 00 00 1a 00 00 00 40 |...............@| +# 14c 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 |.............std| +# 15c 04 08 00 08 00 08 00 00 64 72 6f 70 20 74 61 62 |........drop tab| +# 16c 6c 65 20 74 65 73 74 2e 74 31 |le test.t1| +# +# Event: Query thread_id=1 exec_time=0 error_code=0 +SET TIMESTAMP=1375907937/*!*/; +drop table test.t1 +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ffaa1a06b95..a473279ce40 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5196,7 +5196,7 @@ CREATE TABLE b12809202_db.t1 (c1 INT); CREATE TABLE b12809202_db.t2 (c1 INT); INSERT INTO b12809202_db.t1 VALUES (1), (2), (3); INSERT INTO b12809202_db.t2 VALUES (1), (2), (3); -# Starting mysqldump with --single-transaction & --flush-log options.. +# Starting mysqldump with --single-transaction & --flush-logs options.. # Note : In the following dump the transaction # should start only after the logs are # flushed, as 'flush logs' causes implicit diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 916deeee1dd..836de3d5842 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -343,3 +343,33 @@ Field Type Null Key Default Extra IFNULL(NULL, b) decimal(1,0) YES NULL DROP TABLE t1, t2; # End of 5.0 tests +# +# MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL +# +CREATE TABLE t1 (dt DATETIME NOT NULL); +INSERT INTO t1 VALUES (NOW()),(NOW()); +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; +dt +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; +dt +DROP TABLE t1; +CREATE TABLE t1 (dt INT NOT NULL); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN +SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); +dt +DROP TABLE t1; diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 940c3da9153..f2429c09149 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -188,7 +188,7 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`ID`,`aaaa,aaaaa`,`ddddddddd`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (ID) -SUBPARTITION BY LINEAR KEY (ID,`aaaa,aaaaa`) +SUBPARTITION BY LINEAR KEY (`ID`,`aaaa,aaaaa`) SUBPARTITIONS 2 (PARTITION p01 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION p11 VALUES LESS THAN (200) ENGINE = MyISAM, diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index 62ae0e3747f..4075d551b1a 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -44,9 +44,11 @@ set global example_ulong_var=500; set global example_enum_var= e1; show status like 'example%'; Variable_name Value -Example_func_example enum_var is 0, ulong_var is 500, really +Example_func_example enum_var is 0, ulong_var is 500, double_var is 8.500000, really show variables like 'example%'; Variable_name Value +example_double_thdvar 8.500000 +example_double_var 8.500000 example_enum_var e1 example_ulong_var 500 example_varopt_default 5 @@ -215,6 +217,84 @@ select 1; 1 1 UNINSTALL PLUGIN example; +# +# Bug #16194302 SUPPORT FOR FLOATING-POINT SYSTEM +# VARIABLES USING THE PLUGIN INTERFACE. +# +INSTALL PLUGIN example SONAME 'ha_example'; +SET GLOBAL example_double_var = -0.1; +Warnings: +Warning 1292 Truncated incorrect example_double_var value: '-0.1' +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +0.500000 +SET GLOBAL example_double_var = 0.000001; +Warnings: +Warning 1292 Truncated incorrect example_double_var value: '0.000001' +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +0.500000 +SET GLOBAL example_double_var = 0.4; +Warnings: +Warning 1292 Truncated incorrect example_double_var value: '0.4' +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +0.500000 +SET GLOBAL example_double_var = 123.456789; +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +123.456789 +SET GLOBAL example_double_var = 500; +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +500.000000 +SET GLOBAL example_double_var = 999.999999; +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +999.999999 +SET GLOBAL example_double_var = 1000.51; +Warnings: +Warning 1292 Truncated incorrect example_double_var value: '1000.51' +SELECT @@GLOBAL.example_double_var; +@@GLOBAL.example_double_var +1000.500000 +SET SESSION example_double_thdvar = -0.1; +Warnings: +Warning 1292 Truncated incorrect example_double_thdvar value: '-0.1' +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +0.500000 +SET SESSION example_double_thdvar = 0.000001; +Warnings: +Warning 1292 Truncated incorrect example_double_thdvar value: '0.000001' +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +0.500000 +SET SESSION example_double_thdvar = 0.4; +Warnings: +Warning 1292 Truncated incorrect example_double_thdvar value: '0.4' +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +0.500000 +SET SESSION example_double_thdvar = 123.456789; +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +123.456789 +SET SESSION example_double_thdvar = 500; +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +500.000000 +SET SESSION example_double_thdvar = 999.999999; +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +999.999999 +SET SESSION example_double_thdvar = 1000.51; +Warnings: +Warning 1292 Truncated incorrect example_double_thdvar value: '1000.51' +SELECT @@SESSION.example_double_thdvar; +@@SESSION.example_double_thdvar +1000.500000 +UNINSTALL PLUGIN example; UNINSTALL PLUGIN MyISAM; ERROR HY000: Built-in plugins cannot be deleted select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index d7bc193a15c..1c3c2de764b 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1793,8 +1793,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) DEFAULT NULL, - `param10` decimal(65,30) DEFAULT NULL, + `const10` bigint(17) DEFAULT NULL, + `param10` bigint(20) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1823,8 +1823,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 -def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 +def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 +def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1850,8 +1850,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861.000000 -param10 662680861.000000000000000000000000000000 +const10 662680861 +param10 662680861 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index ce9409762e5..8b4b6daa108 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -1776,8 +1776,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) DEFAULT NULL, - `param10` decimal(65,30) DEFAULT NULL, + `const10` bigint(17) DEFAULT NULL, + `param10` bigint(20) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1806,8 +1806,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 -def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 +def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 +def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1833,8 +1833,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861.000000 -param10 662680861.000000000000000000000000000000 +const10 662680861 +param10 662680861 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index d5fc8f7e8da..0ea54a45afa 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -1777,8 +1777,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) DEFAULT NULL, - `param10` decimal(65,30) DEFAULT NULL, + `const10` bigint(17) DEFAULT NULL, + `param10` bigint(20) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1807,8 +1807,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 -def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 +def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 +def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1834,8 +1834,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861.000000 -param10 662680861.000000000000000000000000000000 +const10 662680861 +param10 662680861 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index bb74363f68c..2fc9475e926 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -1713,8 +1713,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) DEFAULT NULL, - `param10` decimal(65,30) DEFAULT NULL, + `const10` bigint(17) DEFAULT NULL, + `param10` bigint(20) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -1743,8 +1743,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 -def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 +def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 +def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -1770,8 +1770,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861.000000 -param10 662680861.000000000000000000000000000000 +const10 662680861 +param10 662680861 const11 1991 param11 1991 const12 NULL @@ -5067,8 +5067,8 @@ t5 CREATE TABLE `t5` ( `param08` longtext, `const09` datetime DEFAULT NULL, `param09` longtext, - `const10` decimal(22,6) DEFAULT NULL, - `param10` decimal(65,30) DEFAULT NULL, + `const10` bigint(17) DEFAULT NULL, + `param10` bigint(20) DEFAULT NULL, `const11` int(4) DEFAULT NULL, `param11` bigint(20) DEFAULT NULL, `const12` binary(0) DEFAULT NULL, @@ -5097,8 +5097,8 @@ def test t5 t5 const08 const08 253 19 19 N 1 0 8 def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8 def test t5 t5 const09 const09 12 19 19 Y 128 0 63 def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8 -def test t5 t5 const10 const10 246 24 16 Y 32768 6 63 -def test t5 t5 param10 param10 246 67 40 Y 32768 30 63 +def test t5 t5 const10 const10 8 17 9 Y 32768 0 63 +def test t5 t5 param10 param10 8 20 9 Y 32768 0 63 def test t5 t5 const11 const11 3 4 4 Y 32768 0 63 def test t5 t5 param11 param11 8 20 4 Y 32768 0 63 def test t5 t5 const12 const12 254 0 0 Y 128 0 63 @@ -5124,8 +5124,8 @@ const08 1991-08-05 01:01:01 param08 1991-08-05 01:01:01 const09 1991-08-05 01:01:01 param09 1991-08-05 01:01:01 -const10 662680861.000000 -param10 662680861.000000000000000000000000000000 +const10 662680861 +param10 662680861 const11 1991 param11 1991 const12 NULL diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 146d250d687..d41bfaa2c67 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -712,14 +712,14 @@ INSERT INTO t1 VALUES 'd8c4177d09f8b11f5.52725522'); EXPLAIN SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition 1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; oxid @@ -728,6 +728,11 @@ d8c4177d206a333d2.74422679 d8c4177d225791924.30714720 d8c4177d2380fc201.39666693 d8c4177d24ccef970.14957924 +d8c4177d151affab2.81582771 +d8c4177d206a333d2.74422678 +d8c4177d225791924.30714721 +d8c4177d2380fc201.39666694 +d8c4177d24ccef970.14957925 DROP TABLE t1; create table t1 ( c1 char(10), c2 char(10), c3 char(10), c4 char(10), @@ -1590,6 +1595,8 @@ NULL Warnings: Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 @@ -1885,6 +1892,46 @@ AAA AAA AAA AAAA AAAA AAAA AAAAA AAAAA AAAAA DROP TABLE t1; +# +# mdev-4894: Poor performance with unnecessary +# (bug#70021) 'Range checked for each record' +# +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Using join buffer (flat, BNL join) +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +count(*) +128 +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Using join buffer (flat, BNL join) +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +count(*) +126 +drop table t1,t2; End of 5.1 tests # # LP Bug #533117: Wrong use_count in SEL_ARG trees diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 3769ceb9145..a5c14d99975 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -714,14 +714,14 @@ INSERT INTO t1 VALUES 'd8c4177d09f8b11f5.52725522'); EXPLAIN SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition 1 SIMPLE s ALL OXLEFT NULL NULL NULL 12 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s -WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; oxid @@ -730,6 +730,11 @@ d8c4177d206a333d2.74422679 d8c4177d225791924.30714720 d8c4177d2380fc201.39666693 d8c4177d24ccef970.14957924 +d8c4177d151affab2.81582771 +d8c4177d206a333d2.74422678 +d8c4177d225791924.30714721 +d8c4177d2380fc201.39666694 +d8c4177d24ccef970.14957925 DROP TABLE t1; create table t1 ( c1 char(10), c2 char(10), c3 char(10), c4 char(10), @@ -1592,6 +1597,8 @@ NULL Warnings: Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Warning 1411 Incorrect datetime value: '2007-20-00' for function str_to_date SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' 1 @@ -1887,6 +1894,46 @@ AAA AAA AAA AAAA AAAA AAAA AAAAA AAAAA AAAAA DROP TABLE t1; +# +# mdev-4894: Poor performance with unnecessary +# (bug#70021) 'Range checked for each record' +# +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +count(*) +128 +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 64 +1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +count(*) +126 +drop table t1,t2; End of 5.1 tests # # LP Bug #533117: Wrong use_count in SEL_ARG trees diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a6c12dfba52..6d3d9ce555d 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -5351,7 +5351,7 @@ a b c 8 8 8 DROP TABLE t1,t2; # -# Bug mdev-4413: another manifestations of bug mdev-2474 +# Bug mdev-4413: another manifestations of bug mdev-4274 # (valgrind complains) # CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; @@ -5363,4 +5363,151 @@ WHERE c = a AND ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); a b c DROP TABLE t1, t2; +# +# Bug mdev-4355: equalities from the result of simplification of OR +# are not propagated to lower AND levels +# +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +a b +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` = 5)) +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +a b +DROP TABLE t1; +# +# Bug mdev-4418: impossible multiple equality in OR formula +# after row substitution +# +CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,'j'), (8,'v'); +CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('k','k'); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136)) +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +a b c d +DROP TABLE t1,t2; +# +# Bug mdev-4944: range conditition in OR formula with fields +# belonging to multiple equalities +# +CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,8); +CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8), (9); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0 +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +i1 j1 i2 +DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index e9c88771752..c4f071aedb0 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5362,7 +5362,7 @@ a b c 8 8 8 DROP TABLE t1,t2; # -# Bug mdev-4413: another manifestations of bug mdev-2474 +# Bug mdev-4413: another manifestations of bug mdev-4274 # (valgrind complains) # CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; @@ -5374,7 +5374,154 @@ WHERE c = a AND ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); a b c DROP TABLE t1, t2; +# +# Bug mdev-4355: equalities from the result of simplification of OR +# are not propagated to lower AND levels +# +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +a b +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` = 5)) +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +a b +DROP TABLE t1; +# +# Bug mdev-4418: impossible multiple equality in OR formula +# after row substitution +# +CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,'j'), (8,'v'); +CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('k','k'); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136)) +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +a b c d +DROP TABLE t1,t2; +# +# Bug mdev-4944: range conditition in OR formula with fields +# belonging to multiple equalities +# +CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,8); +CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8), (9); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0 +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +i1 j1 i2 +DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index a6c12dfba52..6d3d9ce555d 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -5351,7 +5351,7 @@ a b c 8 8 8 DROP TABLE t1,t2; # -# Bug mdev-4413: another manifestations of bug mdev-2474 +# Bug mdev-4413: another manifestations of bug mdev-4274 # (valgrind complains) # CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; @@ -5363,4 +5363,151 @@ WHERE c = a AND ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); a b c DROP TABLE t1, t2; +# +# Bug mdev-4355: equalities from the result of simplification of OR +# are not propagated to lower AND levels +# +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1)) +SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); +a b +5 11 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 +SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); +a b +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` = 5)) +SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); +a b +DROP TABLE t1; +# +# Bug mdev-4418: impossible multiple equality in OR formula +# after row substitution +# +CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,'j'), (8,'v'); +CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('k','k'); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136)) +SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); +a b c d +DROP TABLE t1,t2; +# +# Bug mdev-4944: range conditition in OR formula with fields +# belonging to multiple equalities +# +CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,8); +CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8), (9); +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0 +SELECT * FROM t1, t2 +WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); +i1 j1 i2 +DROP TABLE t1,t2; +# +# Bug mdev-4971: equality propagation after merging degenerate +# disjunction into embedding AND level +# +CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; +CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +EXPLAIN EXTENDED +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +INSERT INTO t1 VALUES (3,1,6); +SELECT * FROM t1, t2 +WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); +pk1 a1 b1 pk2 a2 +3 1 6 1 1 +DROP TABLE t1,t2; End of 5.3 tests +# +# mysql BUG#1271 Undefined variable in PASSWORD() +# function is not handled correctly +# +create table t1 ( +name VARCHAR(50) NOT NULL PRIMARY KEY, +pw VARCHAR(41) NOT NULL); +INSERT INTO t1 (name, pw) +VALUES ('tom', PASSWORD('my_pw')); +SET @pass='my_pw'; +SET @wrong='incorrect'; +select * from t1; +name pw +tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 +select length(PASSWORD(@pass)); +length(PASSWORD(@pass)) +41 +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); +name +tom +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); +name +SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); +name +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) +tom +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) +NULL +select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); +(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) +NULL +drop table t1; +End of 10.0 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 912ffe07d10..214aac28662 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7820,4 +7820,41 @@ Warning 1329 No data - zero rows fetched, selected, or processed drop procedure p1; drop procedure p2; drop table t1; +# +# MDEV-4978 - Server cursor is broken with blobs in the select list, +# ORDER BY does not work +# +CREATE TABLE t1(a INT, b BLOB); +INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f'); +CREATE PROCEDURE p1() +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE v1 INT; +DECLARE v2 BLOB; +DECLARE c1 CURSOR FOR SELECT * FROM t1 ORDER BY a; +DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; +OPEN c1; +REPEAT +FETCH c1 INTO v1, v2; +IF NOT done THEN +SELECT v1; +END IF; +UNTIL done END REPEAT; +CLOSE c1; +END| +CALL p1; +v1 +1 +v1 +2 +v1 +3 +v1 +4 +v1 +5 +v1 +6 +DROP PROCEDURE p1; +DROP TABLE t1; # End of 5.5 test diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index 540a66e7508..e95bae39920 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -32,9 +32,9 @@ drop procedure bug4902_2| drop procedure if exists bug6807| create procedure bug6807() begin -declare id int; -set id = connection_id(); -kill query id; +declare a int; +set a = connection_id(); +kill query a; select 'Not reached'; end| call bug6807()| @@ -169,7 +169,7 @@ drop procedure if exists p1; create table t1 (value varchar(15)); create procedure p1() update t1 set value='updated' where value='old'; call p1(); -insert into t1 (value) values ("old"); +insert into t1 (value) values ("old"),("irrelevant"); select get_lock('b26162',120); get_lock('b26162',120) 1 @@ -179,11 +179,13 @@ call p1();; select 'rl_contender', value from t1; rl_contender value rl_contender old +rl_contender irrelevant select release_lock('b26162'); release_lock('b26162') 1 rl_acquirer value rl_acquirer old +rl_acquirer irrelevant drop procedure p1; drop table t1; set session low_priority_updates=default; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 38e955e349c..890ba2bb2d7 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -237,6 +237,34 @@ a b DROP TABLE t1,t2,t3; set optimizer_switch=@tmp_mdev567; # +# MDEV-4996: degenerate OR formula in WHERE of a subquery +# +CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (2,'x'), (8,'d'); +CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p'); +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +a c1 +2 x +8 d +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2a ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (((`test`.`t2b`.`m` <> `test`.`t1`.`a`) or (`test`.`t2b`.`m` = `test`.`t2a`.`m`)) and trigcond(((<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2`) or isnull(`test`.`t2a`.`c2`))) and (`test`.`t2c`.`c2` = `test`.`t2b`.`c2`) and (`test`.`t2b`.`n` = `test`.`t2a`.`m`)) having trigcond(<is_not_null_test>(`test`.`t2a`.`c2`))))))) +DROP TABLE t1,t2; +# # MDEV-614, also MDEV-536, also LP:1050806: # different result for a query using subquery between 5.5.25 and 5.5.27 # @@ -295,4 +323,29 @@ node_uid date mirror_date result 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 set optimizer_switch=@tmp_mdev614; DROP TABLE t1; +# +# MDEV-4420: non-expensive single-value subquery used as +# used as an access key to join a table +# +create table t1 (a varchar(3)); +insert into t1 values ('USA'), ('FRA'); +create table t2 select * from t1; +insert into t2 values ('RUS'); +create table t3 select * from t2; +create index idx on t3(a); +explain extended +select * from t1, t2 left join t3 on ( t2.a = t3.a ) +where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ref idx idx 6 func 2 100.00 Using where; Using index +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where ((`test`.`t1`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`)) and (`test`.`t2`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`))) +select * from t1, t2 left join t3 on ( t2.a = t3.a ) +where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); +a a a +FRA FRA FRA +drop table t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index c08aa6124b8..e5a2fe12526 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -3129,6 +3129,7 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL f Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'f' SET @@optimizer_switch = 'subquery_cache=on'; /* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( SELECT SUBQUERY2_t1 .`col_int_key` @@ -3144,6 +3145,7 @@ WHERE table1 .`col_varchar_key` ) field10 1 NULL f Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'f' drop table t1,t2,t3,t4; set @@optimizer_switch= default; #launchpad BUG#611625 diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 0a98efb5c0e..e1c9df3b00b 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2016,6 +2016,28 @@ drop table t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; +# +# MDEV-4908: Assertion `((Item_cond *) cond)->functype() == +# ((Item_cond *) new_item)->functype()' fails on a query with +# IN and equal conditions, AND/OR, materialization+semijoin +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'materialization=on,semijoin=on'; +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); +pk a b +drop table t1; +SET optimizer_switch=@save_optimizer_switch; +# +# MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries +# +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); +pk a b +DROP TABLE t1; +# End of 5.3 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 3fc91b452a5..2d229db9ec5 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2485,7 +2485,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 test.t3.b 1 Using index +1 PRIMARY t2 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index fbe6db4edda..74fea8957a9 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1399,7 +1399,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t2`.`i2` > 0)) +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t3`.`i3` > 0)) SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index f18de3c4d0b..a0ebbb3305d 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2499,7 +2499,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 test.t3.b 1 Using index +1 PRIMARY t2 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 4e8d1697b83..bcdd82b790c 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2056,3 +2056,25 @@ drop table t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; +# +# MDEV-4908: Assertion `((Item_cond *) cond)->functype() == +# ((Item_cond *) new_item)->functype()' fails on a query with +# IN and equal conditions, AND/OR, materialization+semijoin +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'materialization=on,semijoin=on'; +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b ); +pk a b +drop table t1; +SET optimizer_switch=@save_optimizer_switch; +# +# MDEV-5011: ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries +# +CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,3,5),(2,4,6); +SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); +pk a b +DROP TABLE t1; +# End of 5.3 tests diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 63b3304641b..0b19b6b4eaf 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -628,4 +628,24 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual where 1 having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1)))))) DROP TABLE t1,t2; +# +# MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables +# +CREATE TABLE t1 (alpha3 VARCHAR(3)); +INSERT INTO t1 VALUES ('USA'),('CAN'); +CREATE TABLE t2 ( t3_code VARCHAR(3), name VARCHAR(64)); +INSERT INTO t2 VALUES ('USA','Austin'),('USA','Boston'); +CREATE TABLE t3 ( code VARCHAR(3), name VARCHAR(64), PRIMARY KEY (code), UNIQUE KEY (name)); +INSERT INTO t3 VALUES ('CAN','Canada'),('USA','United States'); +SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; +alpha3 t3_code name code name +USA USA Austin USA United States +USA USA Boston USA United States +CAN NULL NULL NULL NULL +SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; +alpha3 +USA +USA +CAN +DROP TABLE t1, t2, t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result index 5ae7e6f8117..d84fe54ba8c 100644 --- a/mysql-test/r/timezone.result +++ b/mysql-test/r/timezone.result @@ -7,7 +7,7 @@ select @a:=FROM_UNIXTIME(1); 1970-01-01 01:00:01 select unix_timestamp(@a); unix_timestamp(@a) -1.000000 +1 CREATE TABLE t1 (ts int); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00')); INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00')); @@ -44,7 +44,7 @@ unix_timestamp('1970-01-01 01:00:01'), unix_timestamp('2038-01-19 04:14:07'), unix_timestamp('2038-01-19 04:14:08'); unix_timestamp('1970-01-01 01:00:00') unix_timestamp('1970-01-01 01:00:01') unix_timestamp('2038-01-19 04:14:07') unix_timestamp('2038-01-19 04:14:08') -0.000000 1.000000 2147483647.000000 NULL +0 1 2147483647 NULL select unix_timestamp('1969-12-31 23:59:59'), unix_timestamp('1970-01-01 00:00:00'), unix_timestamp('1970-01-01 00:59:59'); unix_timestamp('1969-12-31 23:59:59') unix_timestamp('1970-01-01 00:00:00') unix_timestamp('1970-01-01 00:59:59') NULL NULL NULL diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index e2e337628ce..70f5ef9edc1 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -309,3 +309,22 @@ CONVERT_TZ(1, 1, a) NULL DROP TABLE t1; End of 5.1 tests +# +# Start of 5.3 tests +# +# +# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +# +SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '00:00:00' +SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '00:00:00' +# +# End of 5.3 tests +# diff --git a/mysql-test/r/timezone4.result b/mysql-test/r/timezone4.result index ad0672890a2..28028bea657 100644 --- a/mysql-test/r/timezone4.result +++ b/mysql-test/r/timezone4.result @@ -3,4 +3,4 @@ from_unixtime(0) 1969-12-31 14:00:00 select unix_timestamp('1969-12-31 14:00:01'); unix_timestamp('1969-12-31 14:00:01') -1.000000 +1 diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index af747dca562..4ead8f8d743 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -311,6 +311,44 @@ NULL Warnings: Warning 1292 Incorrect datetime value: '2022-00-00 00:00:00' # +# MDEV-4804 Date comparing false result +# +SET @h0="20111107"; +SET @h1="0"; +SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00'); +SELECT +COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d, +COALESCE(DATE(@h1),DATE(NOW())) AS h1d, +COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1; +h0d h1d compare_h0_gt_h1 +2011-11-07 2013-08-19 0 +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +SELECT +DATE('20011107'), +DATE('0'), +COALESCE(DATE('0'),CURRENT_DATE) AS d1, +DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp; +DATE('20011107') DATE('0') d1 cmp +2001-11-07 NULL 2013-08-19 0 +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +SELECT +DATE('20011107'), +DATE('0'), +IFNULL(DATE('0'),CURRENT_DATE) AS d1, +DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp; +DATE('20011107') DATE('0') d1 cmp +2001-11-07 NULL 2013-08-19 0 +Warnings: +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0' +SET @@timestamp=DEFAULT; +# # End of 5.3 tests # # diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 561f80a62ce..8cfda40a11a 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -657,13 +657,8 @@ create table t1 (d date, t time) engine=myisam; insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31'); select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond; cond group_concat( d ) -NULL 2000-12-03 -NULL 2008-05-03 -Warnings: -Warning 1292 Incorrect datetime value: '22:55:23' -Warning 1292 Incorrect datetime value: '10:19:31' -Warning 1292 Incorrect datetime value: '22:55:23' -Warning 1292 Incorrect datetime value: '10:19:31' +0000-00-00 10:19:31 2008-05-03 +0000-00-00 22:55:23 2000-12-03 drop table t1; # # Semantics of the condition <non-nullable datetime field> IS NULL diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 4167cbbe252..32bbb6baa0b 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -183,6 +183,15 @@ NULL Warnings: Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' # +# MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +# +SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))) +00:00:01 +SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))); +CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00'))) +768:00:01 +# # End of 5.3 tests # CREATE TABLE t1 (f1 TIME); diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 78ed5bb65d5..178f9fb7db4 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -126,14 +126,14 @@ select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; set @a=0; select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; @a @a:="hello" @a @a:=3 @a @a:="hello again" -0 hello 0 3 0 hello again -0 hello 0 3 0 hello again -0 hello 0 3 0 hello again +0 hello 0 3 3 hello again +0 hello 0 3 3 hello again +0 hello 0 3 3 hello again select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; @a @a:="hello" @a @a:=3 @a @a:="hello again" -hello again hello hello again 3 hello again hello again -hello again hello hello again 3 hello again hello again -hello again hello hello again 3 hello again hello again +hello again hello hello 3 3 hello again +hello again hello hello 3 3 hello again +hello again hello hello 3 3 hello again drop table t1; set @a=_latin2'test'; select charset(@a),collation(@a),coercibility(@a); diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index f206ec37146..9b1c1b6955e 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1548,7 +1548,7 @@ one 1 explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); one @@ -1559,7 +1559,7 @@ one set sql_buffer_result=1; explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); one diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 98b4b77e425..53c9267f019 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4415,7 +4415,7 @@ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 'r' AS `f4` from dual where ((20 <> 0) or 0) +Note 1003 select 'r' AS `f4` from dual where (20 <> 0) DROP VIEW v1; DROP TABLE t1; # diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 2078bc6d63f..52e8cd54695 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -1146,6 +1146,8 @@ drop user olga@localhost; drop user pjotr@localhost; drop user quintessa@localhost; drop database mysqltest1; +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE End of 5.0 tests. DROP VIEW IF EXISTS v1; DROP TABLE IF EXISTS t1; |