diff options
Diffstat (limited to 'mysql-test/r')
54 files changed, 645 insertions, 108 deletions
diff --git a/mysql-test/r/adddate_454.result b/mysql-test/r/adddate_454.result index 0993cdce32c..8b7c17cd47e 100644 --- a/mysql-test/r/adddate_454.result +++ b/mysql-test/r/adddate_454.result @@ -4,6 +4,8 @@ select * from t1; d 2012-00-00 update t1 set d = adddate(d, interval 1 day); +Warnings: +Warning 1292 Incorrect datetime value: '2012-00-00' select * from t1; d NULL diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index ac7be28d736..03b4b84e461 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -268,37 +268,37 @@ cast(010203101112.121314 as datetime) 0001-02-03 10:11:12 select cast(120010203101112.121314 as datetime); cast(120010203101112.121314 as datetime) -0000-00-00 00:00:00 +NULL Warnings: Warning 1292 Incorrect datetime value: '120010203101112.121314' select cast(cast(1.1 as decimal) as datetime); cast(cast(1.1 as decimal) as datetime) -0000-00-00 00:00:00 +NULL Warnings: Warning 1292 Incorrect datetime value: '1' select cast(cast(-1.1 as decimal) as datetime); cast(cast(-1.1 as decimal) as datetime) -0000-00-00 00:00:00 +NULL Warnings: Warning 1292 Incorrect datetime value: '-1' select cast('0' as date); cast('0' as date) -0000-00-00 +NULL Warnings: Warning 1292 Incorrect datetime value: '0' select cast('' as date); cast('' as date) -0000-00-00 +NULL Warnings: Warning 1292 Incorrect datetime value: '' select cast('0' as datetime); cast('0' as datetime) -0000-00-00 00:00:00 +NULL Warnings: Warning 1292 Incorrect datetime value: '0' select cast('' as datetime); cast('' as datetime) -0000-00-00 00:00:00 +NULL Warnings: Warning 1292 Incorrect datetime value: '' select cast('0' as time); @@ -306,7 +306,7 @@ cast('0' as time) 00:00:00 select cast('' as time); cast('' as time) -00:00:00 +NULL Warnings: Warning 1292 Truncated incorrect time value: '' select cast(NULL as DATE); @@ -323,13 +323,13 @@ cast(NULL as BINARY) NULL select cast(cast(120010203101112.121314 as double) as datetime); cast(cast(120010203101112.121314 as double) as datetime) -0000-00-00 00:00:00 +NULL select cast(cast(1.1 as double) as datetime); cast(cast(1.1 as double) as datetime) 0000-00-00 00:00:01 select cast(cast(-1.1 as double) as datetime); cast(cast(-1.1 as double) as datetime) -0000-00-00 00:00:00 +NULL explain extended select cast(10 as double(5,2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used @@ -786,4 +786,10 @@ SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY; CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY NULL Warnings: -Warning 1292 Truncated incorrect date value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +SET SQL_MODE=ALLOW_INVALID_DATES; +SELECT DATE("foo"); +DATE("foo") +NULL +Warnings: +Warning 1292 Incorrect datetime value: 'foo' diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 3bbd25cbbcc..7f69fd1a5a4 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -521,7 +521,7 @@ TIME_FORMAT("25:00:00", '%l %p') 1 AM SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896) -1151414896 +NULL Warnings: Warning 1292 Incorrect datetime value: '%Y-%m-%d %H:%i:%s' select str_to_date('04 /30/2004', '%m /%d/%Y'); diff --git a/mysql-test/r/fulltext_derived_4257.result b/mysql-test/r/fulltext_derived_4257.result new file mode 100644 index 00000000000..8479baef388 --- /dev/null +++ b/mysql-test/r/fulltext_derived_4257.result @@ -0,0 +1,6 @@ +set optimizer_switch = 'derived_merge=on'; +create table t1 (c1 char(8), c2 char(8)) engine=myisam; +insert into t1 values ('test1','test2'),('test3','test4'); +select * from (select c1 from t1 where match (c2) against ('hello' in boolean mode)) as alias; +c1 +drop table t1; diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index 1f20ddc6cbb..72c7a5a128f 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -168,7 +168,7 @@ date("1997-12-31 23:59:59.000001") 1997-12-31 select date("1997-13-31 23:59:59.000001"); date("1997-13-31 23:59:59.000001") -0000-00-00 +NULL Warnings: Warning 1292 Incorrect datetime value: '1997-13-31 23:59:59.000001' select time("1997-12-31 23:59:59.000001"); @@ -176,7 +176,7 @@ time("1997-12-31 23:59:59.000001") 23:59:59.000001 select time("1997-12-31 25:59:59.000001"); time("1997-12-31 25:59:59.000001") -00:00:00 +NULL Warnings: Warning 1292 Truncated incorrect time value: '1997-12-31 25:59:59.000001' select microsecond("1997-12-31 23:59:59.000001"); @@ -250,8 +250,6 @@ a select microsecond(19971231235959.01) as a; a 10000 -Warnings: -Warning 1292 Truncated incorrect time value: '19971231235959.01' select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a; a 1997-12-31 00:00:10.090000 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index e8526ae8f1f..f50458695a7 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1222,14 +1222,13 @@ DROP TABLE t1,t2; set time_zone= @@global.time_zone; select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE -NULL -Warnings: -Warning 1411 Incorrect datetime value: '10:00 PM' for function str_to_date +22:10:00 select str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute; str_to_date("1997-00-04 22:23:00","%Y-%m-%D") + interval 10 minute NULL Warnings: -Warning 1411 Incorrect datetime value: '1997-00-04 22:23:00' for function str_to_date +Warning 1292 Truncated incorrect date value: '1997-00-04 22:23:00' +Warning 1292 Incorrect datetime value: '1997-00-04' create table t1 (field DATE); insert into t1 values ('2006-11-06'); select * from t1 where field < '2006-11-06 04:08:36.0'; @@ -1423,13 +1422,15 @@ MAKEDATE(11111111,1) NULL SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1); WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1) -0 +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' # # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0, # DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); Warnings: -Warning 1292 Incorrect datetime value: '0' +Warning 1292 Incorrect datetime value: '0000-00-00' # # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY # SIMILAR TO '2009-10-00' @@ -1713,6 +1714,7 @@ min(timestampadd(month, 1>'', from_days('%Z'))) NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '%Z' +Warning 1292 Incorrect datetime value: '0000-00-00' create table t1(a time); insert into t1 values ('00:00:00'),('00:01:00'); select 1 from t1 where 1 < some (select cast(a as datetime) from t1); @@ -1781,7 +1783,7 @@ Warnings: Warning 1441 Datetime function: time field overflow select cast('131415.123e0' as time); cast('131415.123e0' as time) -00:00:00 +NULL Warnings: Warning 1292 Truncated incorrect time value: '131415.123e0' select cast('2010-01-02 03:04:05' as datetime) between null and '2010-01-02 03:04:04'; @@ -1801,12 +1803,12 @@ unix_timestamp(null) NULL select truncate(date('2010-40-10'), 6); truncate(date('2010-40-10'), 6) -0.000000 +NULL Warnings: Warning 1292 Incorrect datetime value: '2010-40-10' select extract(month from '2010-40-50'); extract(month from '2010-40-50') -0 +NULL Warnings: Warning 1292 Incorrect datetime value: '2010-40-50' select subtime('0000-00-10 10:10:10', '30 10:00:00'); @@ -1884,6 +1886,8 @@ insert into t1 values ('0000-00-00'); select timestampadd(week, 1, f1) from t1; timestampadd(week, 1, f1) NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' select timestampadd(week, 1, date("0000-00-00")); timestampadd(week, 1, date("0000-00-00")) NULL @@ -1936,3 +1940,13 @@ SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF(NULL, '12:12:12')); 1 SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); 1 +# +# MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +# +SET TIME_ZONE='+02:00'; +SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); +UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')) +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2020' for function str_to_date +SET TIME_ZONE=DEFAULT; diff --git a/mysql-test/r/grant_4332.result b/mysql-test/r/grant_4332.result index ef92b62ab32..cca7825a07e 100644 --- a/mysql-test/r/grant_4332.result +++ b/mysql-test/r/grant_4332.result @@ -41,7 +41,7 @@ a17aaaaaaaaaaaaa0 localhost b64bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb0 localhost c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0 localhost select user,host from mysql.db; -ERROR 42000: SELECT command denied to user 'b64bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'@'localhost' for table 'db' +ERROR 42000: SELECT command denied to user 'b64bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb0'@'localhost' for table 'db' select user(), current_user(); user() current_user() c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost @@ -54,15 +54,37 @@ user b64bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb0 c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0 select user,host from mysql.tables_priv; -ERROR 42000: SELECT command denied to user 'c80ccccccccccccccccccccccccccccccccccccccccccccc'@'localhost' for column 'host' in table 'tables_priv' +ERROR 42000: SELECT command denied to user 'c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0'@'localhost' for column 'host' in table 'tables_priv' +use mtr; +ERROR 42000: Access denied for user 'c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0'@'localhost' to database 'mtr' +drop procedure mtr.add_suppression; +ERROR 42000: alter routine command denied to user 'c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0'@'localhost' for routine 'mtr.add_suppression' create procedure test.p1() select user(), current_user(), user from mysql.tables_priv; show create procedure test.p1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation p1 CREATE DEFINER=`c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0`@`localhost` PROCEDURE `p1`() select user(), current_user(), user from mysql.tables_priv latin1 latin1_swedish_ci latin1_swedish_ci +select definer from information_schema.routines; +definer +c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost create table test.t1 (a text); create event e1 on schedule every 1 second do insert test.t1 values (concat(user(), ' ', current_user())); +select definer from information_schema.events; +definer +c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost +create view v1 as select * from t1; +select definer from information_schema.views; +definer +c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost +drop view v1; +create trigger tr1 before delete on t1 for each row set @a:=1; +select definer from information_schema.triggers; +definer +c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost +drop trigger tr1; +connect(localhost,c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0,foobar,test,MASTER_PORT,MASTER_SOCKET); +ERROR 28000: Access denied for user 'c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0'@'localhost' (using password: YES) call test.p1(); user() current_user() user root@localhost c80cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc0@localhost b64bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb0 diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index cc7c9c4d364..229481f5ec8 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3561,3 +3561,45 @@ 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) drop table t1; # End of test#50539. +# +# MDEV-4219 A simple select query returns random data (upstream bug#68473) +# +drop table if exists faulty; +CREATE TABLE faulty ( +a int(11) unsigned NOT NULL AUTO_INCREMENT, +b int(11) unsigned NOT NULL, +c datetime NOT NULL, +PRIMARY KEY (a), +UNIQUE KEY b_and_c (b,c) +); +INSERT INTO faulty (b, c) VALUES +(1801, '2013-02-15 09:00:00'), +(1802, '2013-02-28 09:00:00'), +(1802, '2013-03-01 09:00:00'), +(5, '1990-02-15 09:00:00'), +(5, '2013-02-15 09:00:00'), +(5, '2009-02-15 17:00:00'); +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 +SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c; +b c +1802 2013-02-28 09:00:00 +1802 2013-03-01 09:00:00 +drop table faulty; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3); +INSERT INTO t1 SELECT a + 1, b FROM t1; +INSERT INTO t1 SELECT a + 2, b FROM t1; +CREATE INDEX break_it ON t1 (a, b); +EXPLAIN +SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range break_it break_it 10 NULL 2 Using where; Using index for group-by; Using filesort +SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; +a b +3 1 +3 2 +3 3 +drop table t1; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 0b6959d15a9..e771d48b6d2 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1617,4 +1617,30 @@ GROUP BY 2; COUNT(DISTINCT t2.b) CONCAT(t1.c) 2 0 DROP TABLE t1,t2,t3; +# +# MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX +# +CREATE TABLE t1 ( +pk int, +code char(2), +population_rate int, +area_rate int, +primary key (pk), +index (code), +key (population_rate), +key (area_rate) +); +INSERT INTO t1 VALUES (1,'WI',20, 23), (2, 'WA', 13, 18); +EXPLAIN +SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) +WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) +AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,code,population_rate,area_rate PRIMARY,population_rate,area_rate 4,5,5 NULL 2 Using sort_union(PRIMARY,population_rate,area_rate); Using where +SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) +WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL ) +AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX'); +pk code population_rate area_rate +1 WI 20 23 +DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; diff --git a/mysql-test/r/information_schema_routines.result b/mysql-test/r/information_schema_routines.result index 36f8637faa9..b8f4fb9b1ef 100644 --- a/mysql-test/r/information_schema_routines.result +++ b/mysql-test/r/information_schema_routines.result @@ -30,7 +30,7 @@ ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `SQL_MODE` varchar(8192) NOT NULL DEFAULT '', `ROUTINE_COMMENT` longtext NOT NULL, - `DEFINER` varchar(77) NOT NULL DEFAULT '', + `DEFINER` varchar(189) NOT NULL DEFAULT '', `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT '' @@ -587,14 +587,14 @@ ORDINAL_POSITION 28 COLUMN_DEFAULT IS_NULLABLE NO DATA_TYPE varchar -CHARACTER_MAXIMUM_LENGTH 77 -CHARACTER_OCTET_LENGTH 231 +CHARACTER_MAXIMUM_LENGTH 189 +CHARACTER_OCTET_LENGTH 567 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci -COLUMN_TYPE varchar(77) +COLUMN_TYPE varchar(189) COLUMN_KEY EXTRA PRIVILEGES # @@ -688,7 +688,7 @@ CREATED datetime NO 0000-00-00 00:00:00 LAST_ALTERED datetime NO 0000-00-00 00:00:00 SQL_MODE varchar(8192) NO ROUTINE_COMMENT longtext NO NULL -DEFINER varchar(77) NO +DEFINER varchar(189) NO CHARACTER_SET_CLIENT varchar(32) NO COLLATION_CONNECTION varchar(32) NO DATABASE_COLLATION varchar(32) NO diff --git a/mysql-test/r/innodb_icp,innodb_plugin.rdiff b/mysql-test/r/innodb_icp,innodb_plugin.rdiff index c74a8473486..3cb85b79ece 100644 --- a/mysql-test/r/innodb_icp,innodb_plugin.rdiff +++ b/mysql-test/r/innodb_icp,innodb_plugin.rdiff @@ -1,5 +1,5 @@ ---- r/innodb_icp.result 2012-02-25 17:31:11.000000000 +0100 -+++ /usr/home/serg/Abk/mysql/5.5-mtr/mysql-test/r/innodb_icp,innodb_plugin.reject 2012-02-25 22:32:18.000000000 +0100 +--- r/innodb_icp.result 2013-07-16 17:01:00.000000000 +0400 ++++ r/innodb_icp,innodb_plugin.reject 2013-07-16 17:16:53.000000000 +0400 @@ -213,7 +213,7 @@ EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; @@ -45,5 +45,14 @@ +Handler_icp_attempts 0 +Handler_icp_match 0 DROP TABLE t1; - # - # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 + create table t1 (a int,b char(5),primary key (a), key (b(1))); + insert into t1 values ('a','b'); +@@ -868,7 +868,7 @@ + EXPLAIN + SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); + id select_type table type possible_keys key key_len ref rows Extra +-1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using index condition; Using where ++1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where + SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); + c1 c2 + aa x diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index 54ad9ecafad..07c75986392 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -852,6 +852,33 @@ select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; # +# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +# +CREATE TABLE t1 ( +c1 TEXT , +c2 VARCHAR(2) , +INDEX idx1 (c2,c1(2)), +INDEX idx2 (c2,c1(1)) +); +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using index condition; Using where +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +DROP TABLE t1; +# # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 # CREATE TABLE t1 ( a INT ) diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 1d045d0a58d..0e5b36acb10 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1472,6 +1472,11 @@ dog_id dog_id birthday dog_id t_id birthday dog_id t_id birthday a_id dog_id 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5992424 5918 SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4,t5; +# +# MDEV-4752: Segfault during parsing of illegal query +# +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 SET optimizer_switch=@save_optimizer_switch; # # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING diff --git a/mysql-test/r/mdev316.result b/mysql-test/r/mdev316.result index 78272549e81..43082769872 100644 --- a/mysql-test/r/mdev316.result +++ b/mysql-test/r/mdev316.result @@ -16,7 +16,7 @@ cast(convert(0x0030 using ucs2) as decimal(5.2)) cast(concat(convert(0x0030 usin 0 0 select cast(convert(_ucs2 0x0030 using latin1) as date), cast(concat(convert(_ucs2 0x0030 using latin1)) as date); cast(convert(_ucs2 0x0030 using latin1) as date) cast(concat(convert(_ucs2 0x0030 using latin1)) as date) -0000-00-00 0000-00-00 +NULL NULL Warnings: Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' diff --git a/mysql-test/r/mrr_derived_crash_4610.result b/mysql-test/r/mrr_derived_crash_4610.result new file mode 100644 index 00000000000..8dcdfda9276 --- /dev/null +++ b/mysql-test/r/mrr_derived_crash_4610.result @@ -0,0 +1,19 @@ +create table t1 (f1 char(4) primary key) engine=innodb charset=utf8 ; +insert into t1 values ('aaaa'); +create table t2 (f2 text, f3 char(4) not null) engine=innodb charset=utf8 ; +create table t3 (id int not null) engine=innodb charset=utf8 ; +create table t4 (val int not null) engine=innodb charset=utf8; +explain select 1 from +(select f2, f3, val, count(id) from t4 join t2 left join t3 on 0) top +join t1 on f1 = f3 where f3 = 'aaaa' order by val; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 12 const 1 Using index; Using filesort +1 PRIMARY <derived2> ref key0 key0 13 const 0 Using where +2 DERIVED t4 ALL NULL NULL NULL NULL 1 +2 DERIVED t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +2 DERIVED t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +select 1 from +(select f2, f3, val, count(id) from t4 join t2 left join t3 on 0) top +join t1 on f1 = f3 where f3 = 'aaaa' order by val; +1 +drop table t1, t2, t3, t4; diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result index 5e49fb6e956..f7adcfb19fd 100644 --- a/mysql-test/r/mrr_icp_extra.result +++ b/mysql-test/r/mrr_icp_extra.result @@ -105,7 +105,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Rowid-ordered scan; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index bc7ebf9c439..2c157102270 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -211,7 +211,7 @@ c-1006=w EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +1 SIMPLE t3 range c1 c1 12 NULL 2 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; c1 EXPLAIN @@ -855,6 +855,33 @@ insert into t1 values ('',1); select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; +# +# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +# +CREATE TABLE t1 ( +c1 TEXT , +c2 VARCHAR(2) , +INDEX idx1 (c2,c1(2)), +INDEX idx2 (c2,c1(1)) +); +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +DROP TABLE t1; drop table if exists t0, t1, t1i, t1m; # # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed diff --git a/mysql-test/r/mysql_binary_mode.result b/mysql-test/r/mysql_binary_mode.result new file mode 100644 index 00000000000..cb230037108 --- /dev/null +++ b/mysql-test/r/mysql_binary_mode.result @@ -0,0 +1,50 @@ +RESET MASTER; +# Bug#33048 Not able to recover binary/blob data correctly using mysqlbinlog +# -------------------------------------------------------------------------- +# The test verify that 0x00 and 0x0D0A sequence can be handled correctly by +# mysql + +CREATE TABLE `A
+B` (c1 CHAR(100)); +# It is a faked statement. ASCII 0 is in the original statement, it would +# make the test result to become a binary file which was difficult to get +# the diff result if the original query was logged in the result. +INSERT INTO `A\r\nB` VALUES("A\0B"); + +INSERT INTO `A
+B` VALUES("A
+B"); +SELECT HEX(c1) FROM `A
+B`; +HEX(c1) +410042 +410D0A42 + +FLUSH LOGS; +DROP TABLE `A
+B`; + +RESET MASTER; +# '--exec mysql ...' without --binary-mode option +# It creates the table with a wrong table name and generates an error. +# (error output was suppressed to make the test case platform agnostic) + +# It is not in binary_mode, so table name '0x410D0A42' can be translated to +# '0x410A42' by mysql depending on the OS - Windows or Unix-like. +DROP TABLE `TABLE_NAME_MASKED`; + +# In binary_mode, table name '0x410D0A42' and string '0x410042' can be +# handled correctly. +RESET MASTER; +SELECT HEX(c1) FROM `A
+B`; +HEX(c1) +410042 +410D0A42 + +DROP TABLE `A
+B`; +RESET MASTER; +include/assert.inc [Table and contents created through mysqltest match 0x610D0A62.] +include/assert.inc [Table and contents created while replaying binary log without --binary-mode set match 0x61(0D)0A62.] +include/assert.inc [Table and contents created while replaying binary log with --binary-mode set match 0x610D0A62.] diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 3921d6c9627..a08e6f63fb4 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -136,7 +136,6 @@ Phase 3/3: Running 'mysql_fix_privilege_tables'... OK DROP USER mysqltest1@'%'; Run mysql_upgrade with a non existing server socket -Phase 1/3: Fixing table and database names mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 51dfc3123a4..5f8cf38ac09 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1174,7 +1174,7 @@ performance-schema-max-rwlock-instances 1000000 performance-schema-max-socket-classes 10 performance-schema-max-socket-instances 1000 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 173 +performance-schema-max-statement-classes 174 performance-schema-max-table-handles 10000 performance-schema-max-table-instances 1000 performance-schema-max-thread-classes 50 diff --git a/mysql-test/r/not_partition.result b/mysql-test/r/not_partition.result index 3c0523279a9..adda476da95 100644 --- a/mysql-test/r/not_partition.result +++ b/mysql-test/r/not_partition.result @@ -3,7 +3,7 @@ FLUSH TABLES; SELECT * FROM t1; ERROR 42000: Unknown storage engine 'partition' TRUNCATE TABLE t1; -ERROR 42S02: Table 'test.t1' doesn't exist +ERROR 42000: Unknown storage engine 'partition' ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze Error Unknown storage engine 'partition' @@ -21,17 +21,17 @@ Table Op Msg_type Msg_text test.t1 repair Error Unknown storage engine 'partition' test.t1 repair error Corrupt ALTER TABLE t1 REPAIR PARTITION ALL; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 CHECK PARTITION ALL; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 OPTIMIZE PARTITION ALL; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 ANALYZE PARTITION ALL; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 REBUILD PARTITION ALL; ERROR 42000: Unknown storage engine 'partition' ALTER TABLE t1 TRUNCATE PARTITION ALL; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 ENGINE Memory; ERROR 42000: Unknown storage engine 'partition' ALTER TABLE t1 ADD (new INT); @@ -46,9 +46,9 @@ joined DATE NOT NULL ) PARTITION BY KEY(joined) PARTITIONS 6; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working ALTER TABLE t1 PARTITION BY KEY(joined) PARTITIONS 2; -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working drop table t1; ERROR 42S02: Unknown table 't1' CREATE TABLE t1 ( @@ -65,7 +65,7 @@ PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE ); -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working drop table t1; ERROR 42S02: Unknown table 't1' CREATE TABLE t1 (id INT, purchased DATE) @@ -76,7 +76,7 @@ PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ); -ERROR HY000: The 'partitioning' feature is disabled; you need MySQL built with '--with-plugin-partition' to have it working +ERROR HY000: The 'partitioning' feature is disabled; you need MariaDB built with '--with-plugin-partition' to have it working drop table t1; ERROR 42S02: Unknown table 't1' create table t1 (a varchar(10) charset latin1 collate latin1_bin); diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index ba79011f53e..e80851329a4 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -78,13 +78,13 @@ insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a is null and b = 2 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a is null and b = 7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a=2 and b = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 1 Using where @@ -93,25 +93,25 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where +1 SIMPLE t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using index condition +1 SIMPLE t1 range a a 5 NULL 1 Using where explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 4 Using index condition; Using where +1 SIMPLE t1 range a,b a 5 NULL 4 Using where explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using index condition +1 SIMPLE t1 range a a 5 NULL 1 Using where explain select * from t1 where b like "6%"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 12 NULL 1 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 054dc9e4fc4..a8e610a561a 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -618,7 +618,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result index 36a72fd84ce..e91855b8dcd 100644 --- a/mysql-test/r/outfile_loaddata.result +++ b/mysql-test/r/outfile_loaddata.result @@ -143,15 +143,8 @@ TRUNCATE t2; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ъ'; Warnings: Warning 1638 Non-ASCII separator arguments are not fully supported -Warning 1265 Data truncated for column 'a' at row 1 -Warning 1261 Row 1 doesn't contain data for all columns -Warning 1261 Row 1 doesn't contain data for all columns -Warning 1265 Data truncated for column 'a' at row 2 -Warning 1261 Row 2 doesn't contain data for all columns -Warning 1261 Row 2 doesn't contain data for all columns SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; a b c -1 NULL NULL 1 ABC-АБВ DEF-ÂÃÄ 2 NULL NULL SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'ъ'; @@ -181,7 +174,6 @@ Warning 1638 Non-ASCII separator arguments are not fully supported SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; a b c 1 ABC-АБВ DEF-ÂÃÄ -1 ABC-АБВ DEF-ÂÃÄÑŠ2 2 NULL NULL # Default (binary) charset: SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1; diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 54378f16d49..e81eea6e887 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -555,14 +555,10 @@ ERROR 42000: Incorrect parameters in the call to native function 'atan' DROP TABLE IF EXISTS t1; SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE -NULL -Warnings: -Warning 1411 Incorrect datetime value: '10:00 PM' for function str_to_date +22:10:00 SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE; STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE -NULL -Warnings: -Warning 1411 Incorrect datetime value: '10:00 PM' for function str_to_date +22:01:00 SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; "1997-12-31 23:59:59" + INTERVAL 1 SECOND 1998-01-01 00:00:00 diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result index 8d72d8781ee..804d9dd1c34 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -699,6 +699,7 @@ a tz 2038-01-19 03:14:06 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1264 Out of range value for column 'a' at row 34 Warning 1264 Out of range value for column 'a' at row 35 SELECT MIN(a), MAX(a) FROM t2; @@ -780,6 +781,10 @@ a tz 2038-01-19 03:14:07 UTC 2011-10-30 00:00:02 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' SELECT MIN(a), MAX(a) FROM t2; MIN(a) MAX(a) 1970-01-01 00:00:01 2038-01-19 03:14:06 @@ -1174,6 +1179,7 @@ a tz 2038-01-19 06:14:06 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1299 Invalid TIMESTAMP value in column 'a' at row 8 Warning 1299 Invalid TIMESTAMP value in column 'a' at row 9 Warning 1264 Out of range value for column 'a' at row 34 @@ -1258,6 +1264,9 @@ a tz 2011-10-30 03:00:02 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' Warning 1299 Invalid TIMESTAMP value in column 'a' at row 18 Warning 1299 Invalid TIMESTAMP value in column 'a' at row 19 SELECT MIN(a), MAX(a) FROM t2; diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index f4d473c00d5..ec7fd798d4c 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -2235,10 +2235,9 @@ INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); # test with an invalid date, which lead to item->null_value is set. EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p20090401 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Warning 1292 Incorrect datetime value: '2009-04-99' -Warning 1292 Incorrect datetime value: '2009-04-99' DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index e175d59cbac..241e7a11ecc 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -199,3 +199,24 @@ select 1; 1 1 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%'; +plugin_name +install soname 'ha_example'; +select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; +plugin_name +EXAMPLE +UNUSABLE +uninstall plugin example; +select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; +plugin_name +UNUSABLE +install soname 'ha_example'; +select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; +plugin_name +EXAMPLE +UNUSABLE +uninstall soname 'ha_example'; +select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; +plugin_name diff --git a/mysql-test/r/processlist.result b/mysql-test/r/processlist.result new file mode 100644 index 00000000000..fc03f920533 --- /dev/null +++ b/mysql-test/r/processlist.result @@ -0,0 +1,15 @@ +SET DEBUG_SYNC = 'dispatch_command_before_set_time WAIT_FOR do_set_time'; +SELECT 1; +SET DEBUG_SYNC = 'fill_schema_processlist_after_unow SIGNAL do_set_time WAIT_FOR fill_schema_proceed'; +SELECT INFO,TIME,TIME_MS FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NULL; +1 +1 +SET DEBUG_SYNC = 'now SIGNAL fill_schema_proceed'; +INFO TIME TIME_MS +NULL 0 0.000 +select sleep(5); +sleep(5) +0 +select command, time < 5 from information_schema.processlist where id != connection_id(); +command time < 5 +Sleep 1 diff --git a/mysql-test/r/servers.result b/mysql-test/r/servers.result new file mode 100644 index 00000000000..c9d82c9cfcc --- /dev/null +++ b/mysql-test/r/servers.result @@ -0,0 +1,8 @@ +# +# MDEV-4594 - CREATE SERVER crashes embedded +# +CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS(HOST 'localhost'); +SELECT * FROM mysql.servers; +Server_name Host Db Username Password Port Socket Wrapper Owner +s1 localhost 0 mysql +DROP SERVER s1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 809aef3ac99..44a37b9a9e5 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -998,7 +998,7 @@ def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT Statement 252 589815 1 def information_schema TRIGGERS TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 def information_schema TRIGGERS TRIGGERS CREATED Created 12 19 0 Y 128 0 63 def information_schema TRIGGERS TRIGGERS SQL_MODE sql_mode 253 24576 0 N 1 0 33 -def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 231 14 N 1 0 33 +def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 567 14 N 1 0 33 def information_schema TRIGGERS TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 1 0 33 def information_schema TRIGGERS TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 1 0 33 def information_schema TRIGGERS TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 @@ -1042,7 +1042,7 @@ def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFER def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 1 0 33 def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 1 0 33 def information_schema TRIGGERS TRIGGERS SQL_MODE SQL_MODE 253 24576 0 N 1 0 33 -def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 231 14 N 1 0 33 +def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 567 14 N 1 0 33 TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER def test t1_bi INSERT def test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW root@localhost ---------------------------------------------------------------- @@ -1065,7 +1065,7 @@ def information_schema VIEWS VIEWS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 def information_schema VIEWS VIEWS VIEW_DEFINITION VIEW_DEFINITION 252 589815 15 N 17 0 33 def information_schema VIEWS VIEWS CHECK_OPTION CHECK_OPTION 253 24 4 N 1 0 33 def information_schema VIEWS VIEWS IS_UPDATABLE IS_UPDATABLE 253 9 2 N 1 0 33 -def information_schema VIEWS VIEWS DEFINER DEFINER 253 231 14 N 1 0 33 +def information_schema VIEWS VIEWS DEFINER DEFINER 253 567 14 N 1 0 33 def information_schema VIEWS VIEWS SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 def information_schema VIEWS VIEWS CHARACTER_SET_CLIENT CHARACTER_SET_CLIENT 253 96 6 N 1 0 33 def information_schema VIEWS VIEWS COLLATION_CONNECTION COLLATION_CONNECTION 253 96 6 N 1 0 33 @@ -1123,7 +1123,7 @@ def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 0 N 1 0 33 def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 -def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 231 14 N 1 0 33 +def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 1 0 33 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER p1 def test p1 PROCEDURE NULL SQL SELECT 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost ---------------------------------------------------------------- @@ -1178,7 +1178,7 @@ def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 0 N 1 0 33 def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 -def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 231 14 N 1 0 33 +def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 1 0 33 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER f1 def test f1 FUNCTION int(11) SQL RETURN 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost ---------------------------------------------------------------- diff --git a/mysql-test/r/shutdown.result b/mysql-test/r/shutdown.result new file mode 100644 index 00000000000..fae3af1cd08 --- /dev/null +++ b/mysql-test/r/shutdown.result @@ -0,0 +1,5 @@ +create user user1@localhost; +shutdown; +ERROR 42000: Access denied; you need (at least one of) the SHUTDOWN privilege(s) for this operation +shutdown; +drop user user1@localhost; diff --git a/mysql-test/r/sp_missing_4665.result b/mysql-test/r/sp_missing_4665.result new file mode 100644 index 00000000000..47587c180c6 --- /dev/null +++ b/mysql-test/r/sp_missing_4665.result @@ -0,0 +1,6 @@ +create table t (a int); +create or replace view v as select 1 from t where a; +delete from v where (select g()); +ERROR 42000: FUNCTION test.g does not exist +drop view v; +drop table t; diff --git a/mysql-test/r/str_to_datetime_457.result b/mysql-test/r/str_to_datetime_457.result index 630be9ffbaa..4fd0d00691c 100644 --- a/mysql-test/r/str_to_datetime_457.result +++ b/mysql-test/r/str_to_datetime_457.result @@ -12,12 +12,12 @@ Warning 1292 Truncated incorrect datetime value: '2012103123595912' Warning 1292 Truncated incorrect datetime value: '20121031235959123' select cast(0 as date), cast('0000-00-00' as date), cast('0' as date); cast(0 as date) cast('0000-00-00' as date) cast('0' as date) -0000-00-00 0000-00-00 0000-00-00 +0000-00-00 0000-00-00 NULL Warnings: Warning 1292 Incorrect datetime value: '0' select extract(hour from '100000:02:03'), extract(hour from '100000:02:03 '); extract(hour from '100000:02:03') extract(hour from '100000:02:03 ') -0 0 +NULL NULL Warnings: Warning 1292 Truncated incorrect time value: '100000:02:03' Warning 1292 Truncated incorrect time value: '100000:02:03 ' diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 139994445f9..cee4cf3ebe6 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -206,7 +206,9 @@ INSERT INTO t1 (col1) VALUES (STR_TO_DATE('15.10.2004','%d.%m.%Y')); INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); -ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date +Warnings: +Note 1265 Data truncated for column 'col1' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); @@ -221,8 +223,6 @@ INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date -INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); -ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); @@ -238,7 +238,7 @@ ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_da INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); -ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function str_to_date +ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function str_to_date INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index b3536661125..eac53365329 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5173,9 +5173,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result index 579277f11f9..a71e57b2566 100644 --- a/mysql-test/r/subselect_exists_to_in.result +++ b/mysql-test/r/subselect_exists_to_in.result @@ -5179,9 +5179,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 0b339738432..a5800883711 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -450,4 +450,48 @@ a d2 select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`; d2 drop table t1,t2; +# +# MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN with GROUP BY and HAVING in EXISTS SQ, +# MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; +CREATE TABLE t3 (d INT) ENGINE=InnoDB; +EXPLAIN +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using index; Using temporary; Using filesort +2 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); +a +DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( +pk int auto_increment primary key, +col_int_key int(11), +key col_int_key (col_int_key),col_varchar_key varchar(128), +key (col_varchar_key) +) engine=innodb; +EXPLAIN +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 +FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN +t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) +) +GROUP BY SQ2_field1 +HAVING SQ2_alias1 . col_int_key >= 7 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY SQ2_alias2 index NULL col_int_key 5 NULL 1 Using index; Using temporary; Using filesort +2 SUBQUERY SQ2_alias1 ref col_int_key col_int_key 5 test.SQ2_alias2.col_int_key 1 Using where; Using index +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 +FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN +t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) +) +GROUP BY SQ2_field1 +HAVING SQ2_alias1 . col_int_key >= 7 +); +1 +drop table t1; set optimizer_switch=@subselect_innodb_tmp; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index ce9a4ad444f..bf98b912a9e 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5175,9 +5175,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index e017e67cfe6..05f7a25e0ef 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -5171,9 +5171,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 492321c5d81..ee84bfd1eca 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5179,9 +5179,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index eb92936e85d..5a7e303f4b9 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5171,9 +5171,9 @@ a 1 SELECT * FROM t1 JOIN ((SELECT 1 UNION SELECT 1)) ON 1; 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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a UNION SELECT 1) ON 1; -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 'ON 1' at line 1 +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 ') ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; -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 ') ON 1' at line 1 +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 ')) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; 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 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 56c3044c4e4..3fc91b452a5 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2884,4 +2884,40 @@ HAVING c <> ( SELECT MAX( c ) FROM t1 ) ORDER BY sm; c sm DROP TABLE t1,t2; +# +# mdev-4173 ignored duplicate value when converting heap to temp table +# could lead to extra rows in semijoin queries or missing row in union queries +# +CREATE TABLE t1 (i1 INT) engine=myisam; +INSERT INTO t1 VALUES +(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4); +CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam; +INSERT INTO t2 VALUES +(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4), +(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6); +CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam; +INSERT INTO t3 VALUES +(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1); +select @@max_heap_table_size into @tmp_max_heap_table_size; +select @@join_buffer_size into @tmp_join_buffer_size; +set max_heap_table_size=16*1024; +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +DROP TABLE t1, t2, t3; +set join_buffer_size = @tmp_join_buffer_size; +set max_heap_table_size = @tmp_max_heap_table_size; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index c4b9933ae07..4f94f5a704c 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1094,5 +1094,27 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +DROP TABLE IF EXISTS t1,t2,t3,t4; +# +# MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin +# +CREATE TABLE t1 ( t1_pk1 varchar(3), t1_pk2 varchar(52), PRIMARY KEY (t1_pk1,t1_pk2)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('CHN','Chinese'),('USA','English'); +CREATE TABLE t2 ( t2_i int(11), t2_c varchar(52)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (86,'English'); +CREATE TABLE t3 ( t3_i int(11), t3_c varchar(35)) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3989,'Abilene'),(3873,'Akron'); +create table t4 like t1; +insert into t4 select * from t1; +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +t1_pk1 t1_pk2 t3_i t3_c +explain +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where; Using index +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 index NULL PRIMARY 59 NULL 2 Using where; Using index; End temporary +DROP TABLE t1,t2,t3,t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index d9b9815133c..e0c5864f1b3 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1109,6 +1109,28 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +DROP TABLE IF EXISTS t1,t2,t3,t4; +# +# MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin +# +CREATE TABLE t1 ( t1_pk1 varchar(3), t1_pk2 varchar(52), PRIMARY KEY (t1_pk1,t1_pk2)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('CHN','Chinese'),('USA','English'); +CREATE TABLE t2 ( t2_i int(11), t2_c varchar(52)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (86,'English'); +CREATE TABLE t3 ( t3_i int(11), t3_c varchar(35)) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3989,'Abilene'),(3873,'Akron'); +create table t4 like t1; +insert into t4 select * from t1; +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +t1_pk1 t1_pk2 t3_i t3_c +explain +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where; Using index +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY t4 hash_index NULL #hash#$hj:PRIMARY 55:59 test.t3.t3_c 2 Using where; End temporary; Using join buffer (incremental, BNLH join) +DROP TABLE t1,t2,t3,t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 1af507668da..fbe6db4edda 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1096,6 +1096,28 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +DROP TABLE IF EXISTS t1,t2,t3,t4; +# +# MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin +# +CREATE TABLE t1 ( t1_pk1 varchar(3), t1_pk2 varchar(52), PRIMARY KEY (t1_pk1,t1_pk2)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('CHN','Chinese'),('USA','English'); +CREATE TABLE t2 ( t2_i int(11), t2_c varchar(52)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (86,'English'); +CREATE TABLE t3 ( t3_i int(11), t3_c varchar(35)) ENGINE=InnoDB; +INSERT INTO t3 VALUES (3989,'Abilene'),(3873,'Akron'); +create table t4 like t1; +insert into t4 select * from t1; +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +t1_pk1 t1_pk2 t3_i t3_c +explain +SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where; Using index +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 index NULL PRIMARY 59 NULL 2 Using where; Using index; End temporary +DROP TABLE t1,t2,t3,t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 7be29201c63..f18de3c4d0b 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2898,6 +2898,42 @@ HAVING c <> ( SELECT MAX( c ) FROM t1 ) ORDER BY sm; c sm DROP TABLE t1,t2; +# +# mdev-4173 ignored duplicate value when converting heap to temp table +# could lead to extra rows in semijoin queries or missing row in union queries +# +CREATE TABLE t1 (i1 INT) engine=myisam; +INSERT INTO t1 VALUES +(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4); +CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam; +INSERT INTO t2 VALUES +(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4), +(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6); +CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam; +INSERT INTO t3 VALUES +(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1); +select @@max_heap_table_size into @tmp_max_heap_table_size; +select @@join_buffer_size into @tmp_join_buffer_size; +set max_heap_table_size=16*1024; +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +COUNT(*) +2834 +DROP TABLE t1, t2, t3; +set join_buffer_size = @tmp_join_buffer_size; +set max_heap_table_size = @tmp_max_heap_table_size; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/temporal_literal.result b/mysql-test/r/temporal_literal.result index c79ed311d16..84749302337 100644 --- a/mysql-test/r/temporal_literal.result +++ b/mysql-test/r/temporal_literal.result @@ -399,9 +399,7 @@ Warnings: Warning 1292 Incorrect datetime value: '00:00:00' SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR); DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR) -NULL -Warnings: -Warning 1292 Incorrect datetime value: '00:00:00' +01:00:00 SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00'); TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00') NULL diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 2d4a290751f..af747dca562 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -136,7 +136,7 @@ select @d:=1311; 1311 select year(@d), month(@d), day(@d), cast(@d as date); year(@d) month(@d) day(@d) cast(@d as date) -0 0 0 0000-00-00 +NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '1311' Warning 1292 Incorrect datetime value: '1311' @@ -303,6 +303,17 @@ Warnings: Warning 1265 Data truncated for column 'f1' at row 1 drop table t1; # +# MDEV-4634 Crash in CONVERT_TZ +# +SELECT CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5'); +CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2022-00-00 00:00:00' +# +# End of 5.3 tests +# +# # Bug #33629: last_day function can return null, but has 'not null' # flag set for result # diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index d6676bbf16b..1b53f92f82a 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -657,8 +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 ) -0000-00-00 00:00:00 2000-12-03 -0000-00-00 00:00:00 2008-05-03 +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' @@ -681,6 +681,14 @@ c a b 1 1 0000-00-00 3 NULL NULL drop table t1,t2; +# +# MDEV-4634 Crash in CONVERT_TZ +# +SELECT CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+7:5'); +CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2022-00-00 00:00:00' End of 5.3 tests # # Start of 5.5 tests diff --git a/mysql-test/r/type_datetime_hires.result b/mysql-test/r/type_datetime_hires.result index ba1fe4adedb..203e45b86cb 100644 --- a/mysql-test/r/type_datetime_hires.result +++ b/mysql-test/r/type_datetime_hires.result @@ -338,3 +338,15 @@ select * from t1; a b 2010-01-02 03:04:05.000000 2010-01-02 03:04:05 drop table t1; +# +# MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query +# +SET @@time_zone='+00:00'; +CREATE TABLE t1 (a DATETIME(4) NOT NULL); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00'); +SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1; +UNIX_TIMESTAMP(a) +NULL +978307200.0000 +DROP TABLE t1; +SET @@time_zone=DEFAULT; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index 7bc0327178b..4167cbbe252 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -174,6 +174,17 @@ select f1, f1 = '2010-10-11 23:38:57' from t1; f1 f1 = '2010-10-11 23:38:57' 23:38:57 0 drop table t1; +# +# MDEV-4634 Crash in CONVERT_TZ +# +SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5'); +CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' +# +# End of 5.3 tests +# CREATE TABLE t1 (f1 TIME); INSERT INTO t1 VALUES ('24:00:00'); SELECT '24:00:00' = (SELECT f1 FROM t1); diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 0db68c4934f..417ac0158ce 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1491,7 +1491,7 @@ SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; SELECT @@GLOBAL.max_binlog_cache_size; @@GLOBAL.max_binlog_cache_size -5368709120 +max_size SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; # # Bug #37168 : Missing variable - skip_name_resolve diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ef4bcc33c48..40d6c71b5af 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4863,6 +4863,18 @@ a 1 drop view v1; drop table t1,t2; +# +# MDEV-4593: p_s: crash in simplify_joins with delete using subselect +# from view +# +create table `t1`(`a` int); +create table `t2`(`a` int); +create or replace view `v1` as select `a` from `t1`; +prepare s from "delete from `t2` order by (select 1 from `v1`)"; +execute s; +deallocate prepare s; +drop view v1; +drop tables t1,t2; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- |