diff options
Diffstat (limited to 'mysql-test/r')
29 files changed, 428 insertions, 19 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 2edacb6c7e9..1e39c03696f 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -583,7 +583,7 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `cast(1 as unsigned)` int(1) unsigned NOT NULL, - `cast(1 as signed)` int(1) NOT NULL, + `cast(1 as signed)` int(2) NOT NULL, `cast(1 as double(5,2))` double(5,2) DEFAULT NULL, `cast(1 as decimal(5,3))` decimal(5,3) NOT NULL, `cast("A" as binary)` varbinary(1) NOT NULL, @@ -822,3 +822,74 @@ utf8_bin select collation(cast("a" as char(10) binary ascii)); collation(cast("a" as char(10) binary ascii)) latin1_bin +# +# MDEV-11030 Assertion `precision > 0' failed in decimal_bin_size +# +SELECT * FROM (SELECT IFNULL(CONVERT(NULL, UNSIGNED), NULL)) sq; +IFNULL(CONVERT(NULL, UNSIGNED), NULL) +NULL +CREATE TABLE t1 AS SELECT IFNULL(CONVERT(NULL, UNSIGNED), NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `IFNULL(CONVERT(NULL, UNSIGNED), NULL)` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT COALESCE(CONVERT(NULL, UNSIGNED), NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COALESCE(CONVERT(NULL, UNSIGNED), NULL)` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT CASE WHEN TRUE THEN CONVERT(NULL, UNSIGNED) ELSE NULL END; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CASE WHEN TRUE THEN CONVERT(NULL, UNSIGNED) ELSE NULL END` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT IFNULL(CONVERT(NULL,SIGNED),CONVERT(NULL,UNSIGNED)) AS a; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(1,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +-1, +CONVERT(NULL,SIGNED), +CONCAT(CONVERT(NULL,SIGNED)), +1, +CONVERT(NULL,UNSIGNED), +CONCAT(CONVERT(NULL,UNSIGNED)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `-1` int(2) NOT NULL, + `CONVERT(NULL,SIGNED)` int(2) DEFAULT NULL, + `CONCAT(CONVERT(NULL,SIGNED))` varchar(2) DEFAULT NULL, + `1` int(1) NOT NULL, + `CONVERT(NULL,UNSIGNED)` int(1) unsigned DEFAULT NULL, + `CONCAT(CONVERT(NULL,UNSIGNED))` varchar(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +CONVERT('',SIGNED), +CONCAT(CONVERT('',SIGNED)), +CONVERT('',UNSIGNED), +CONCAT(CONVERT('',UNSIGNED)); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +Warning 1292 Truncated incorrect INTEGER value: '' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CONVERT('',SIGNED)` int(2) NOT NULL, + `CONCAT(CONVERT('',SIGNED))` varchar(2) NOT NULL, + `CONVERT('',UNSIGNED)` int(1) unsigned NOT NULL, + `CONCAT(CONVERT('',UNSIGNED))` varchar(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index f9ac0dd4c7f..08fbd05ab58 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1912,6 +1912,13 @@ end| create table t1 as select f1(); ERROR 42S02: Table 'test.t1' doesn't exist drop function f1; +# +# MDEV-10274 Bundling insert with create statement +# for table with unsigned Decimal primary key issues warning 1194 +# +create table t1(ID decimal(2,1) unsigned NOT NULL, PRIMARY KEY (ID))engine=memory +select 2.1 ID; +drop table t1; End of 5.5 tests create table t1; ERROR 42000: A table must have at least 1 column diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 0ac76e91c21..b6eb6076895 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4576,6 +4576,16 @@ SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061)); CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061)) 1 # +# MDEV-11685: sql_mode can't be set with non-ascii connection charset +# +SET character_set_connection=ucs2; +SET sql_mode='NO_ENGINE_SUBSTITUTION'; +SELECT @@sql_mode; +@@sql_mode +NO_ENGINE_SUBSTITUTION +SET sql_mode=DEFAULT; +SET NAMES utf8; +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_ucs2_def.result b/mysql-test/r/ctype_ucs2_def.result index 5ca7d1689d2..9d8f5181103 100644 --- a/mysql-test/r/ctype_ucs2_def.result +++ b/mysql-test/r/ctype_ucs2_def.result @@ -1,4 +1,4 @@ -call mtr.add_suppression("Cannot use ucs2 as character_set_client"); +call mtr.add_suppression("'ucs2' can not be used as client character set"); show variables like 'collation_server'; Variable_name Value collation_server ucs2_unicode_ci diff --git a/mysql-test/r/ctype_ucs2_query_cache.result b/mysql-test/r/ctype_ucs2_query_cache.result index 9a7580324c1..eba7f2fb0fc 100644 --- a/mysql-test/r/ctype_ucs2_query_cache.result +++ b/mysql-test/r/ctype_ucs2_query_cache.result @@ -1,4 +1,4 @@ -call mtr.add_suppression("Cannot use ucs2 as character_set_client"); +call mtr.add_suppression("'ucs2' can not be used as client character set"); # # Start of 5.5 tests # diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result index 09ba94bd12d..32deafae3b7 100644 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@ -1584,6 +1584,16 @@ ERROR HY000: Invalid utf16 character string: 'DE9899' DO LPAD(_utf16 0x0061 COLLATE utf16_unicode_ci, 10000, 0x0061DE989999); ERROR HY000: Invalid utf16 character string: 'DE9899' # +# MDEV-11685: sql_mode can't be set with non-ascii connection charset +# +SET character_set_connection=utf16; +SET sql_mode='NO_ENGINE_SUBSTITUTION'; +SELECT @@sql_mode; +@@sql_mode +NO_ENGINE_SUBSTITUTION +SET sql_mode=DEFAULT; +SET NAMES utf8; +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_utf16_def.result b/mysql-test/r/ctype_utf16_def.result index 9d3d110fc99..98b6f7d913d 100644 --- a/mysql-test/r/ctype_utf16_def.result +++ b/mysql-test/r/ctype_utf16_def.result @@ -1,4 +1,4 @@ -call mtr.add_suppression("Cannot use utf16 as character_set_client"); +call mtr.add_suppression("'utf16' can not be used as client character set"); SHOW VARIABLES LIKE 'collation_server'; Variable_name Value collation_server utf16_general_ci diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index f5365dd0c31..de4c111ec4b 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -1668,6 +1668,16 @@ c Warnings: Warning 1300 Invalid utf32 character string: '\xFF\xFF\x00\x00' # +# MDEV-11685: sql_mode can't be set with non-ascii connection charset +# +SET character_set_connection=utf32; +SET sql_mode='NO_ENGINE_SUBSTITUTION'; +SELECT @@sql_mode; +@@sql_mode +NO_ENGINE_SUBSTITUTION +SET sql_mode=DEFAULT; +SET NAMES utf8; +# # End of 5.5 tests # # diff --git a/mysql-test/r/events_slowlog.result b/mysql-test/r/events_slowlog.result new file mode 100644 index 00000000000..7de5925bc0f --- /dev/null +++ b/mysql-test/r/events_slowlog.result @@ -0,0 +1,13 @@ +set @event_scheduler_save= @@global.event_scheduler; +set @slow_query_log_save= @@global.slow_query_log; +set global event_scheduler= on; +set global slow_query_log= on; +set global long_query_time=0.2; +create table t1 (i int); +insert into t1 values (0); +create event ev on schedule at CURRENT_TIMESTAMP + INTERVAL 1 second do update t1 set i=1+sleep(0.5); +FOUND /update t1 set i=1/ in mysqld-slow.log +drop table t1; +set global event_scheduler= @event_scheduler_save; +set global slow_query_log= @slow_query_log_save; +set global long_query_time= @@session.long_query_time; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 4d0a2a021f9..d5d8deca3fb 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2765,6 +2765,12 @@ id date1 date2 DATE_ADD(a.date1,INTERVAL -10 DAY) TO_DAYS(a.date1)-10 18 2010-10-13 2010-10-03 2010-10-03 734413 DROP TABLE t1; # +# MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() +# +SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; +1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 +3 +# # Start of 10.0 tests # # diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 0450622411a..6a3ea839535 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -799,3 +799,32 @@ a b c 9 d d DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-10927: Crash When Using sort_union Optimization +# +set @tmp_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION sort_buffer_size = 1024; +create table t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col1 int(11) NOT NULL, +col2 int(11) NOT NULL, +col3 int(11) NOT NULL, +key2 int(11) NOT NULL, +col4 int(11) NOT NULL, +key1 int(11) NOT NULL, +PRIMARY KEY (pk), +KEY key1 (key1), +KEY key2 (key2) +) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 + D.a*1000 from t2 A, t2 B, t2 C, t2 D; +insert into t1 (key1, key2, col1,col2,col3,col4) +select a,a, a,a,a,a from t3; +SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); +sum(col1) +33632261 +drop table t1,t2,t3; +set optimizer_switch=@tmp_optimizer_switch; diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result index e0a0f28dec6..91720d12ac4 100644 --- a/mysql-test/r/information_schema_part.result +++ b/mysql-test/r/information_schema_part.result @@ -151,3 +151,11 @@ select create_options from information_schema.tables where table_schema="test"; create_options partitioned drop table t1; +# +# MDEV-11353 - Identical logical conditions +# +CREATE TABLE t1(a INT) CHECKSUM=1 SELECT 1; +SELECT CHECKSUM FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +CHECKSUM +3036305396 +DROP TABLE t1; diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 1987c5c0559..e8e6e16fe5a 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -242,14 +242,16 @@ ERROR 42S22: Unknown column 'a' in 'field list' DROP TABLE t1,t2; SET SQL_MODE = 'TRADITIONAL'; CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL); +INSERT INTO t1 VALUES (1,1); INSERT INTO t1 (a) VALUES (1); ERROR HY000: Field 'b' doesn't have a default value INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b; ERROR HY000: Field 'b' doesn't have a default value +INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = a; INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b; -ERROR HY000: Field 'b' doesn't have a default value SELECT * FROM t1; a b +1 1 DROP TABLE t1; CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY, f2 VARCHAR(5) NOT NULL UNIQUE); diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 74e64d92856..6a8581e0605 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5755,11 +5755,13 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id col1 -select timestampdiff(second, @init_time, now()) <= 1; -timestampdiff(second, @init_time, now()) <= 1 +select timestampdiff(second, @init_time, now()) <= 5; +timestampdiff(second, @init_time, now()) <= 5 1 set join_cache_level=2; set @init_time:=now(); @@ -5791,11 +5793,13 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id col1 -select timestampdiff(second, @init_time, now()) <= 1; -timestampdiff(second, @init_time, now()) <= 1 +select timestampdiff(second, @init_time, now()) <= 5; +timestampdiff(second, @init_time, now()) <= 5 1 EXPLAIN SELECT t.* @@ -5826,6 +5830,8 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id select_type table type possible_keys key key_len ref rows Extra @@ -5855,6 +5861,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c23 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c24 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c25 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE c26 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE c27 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) set join_buffer_size=default; set join_cache_level = default; DROP TABLE t1,t2; diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index d21402a336a..c865fcef53f 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -537,7 +537,7 @@ disconnect con1; # Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U # CREATE TABLE t1(f1 INT); -SELECT 0xE1C330 INTO OUTFILE 't1.dat'; +SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; DROP TABLE t1; # @@ -562,12 +562,24 @@ FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; Got one of the listed errors SET @@sql_mode= @old_mode; DROP TABLE t1; - # -# Bug#23080148 - Backport of Bug#20683959. -# Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY -# UNDER DB CHARSET IS UTF8. +# MDEV-11079 Regression: LOAD DATA INFILE lost BLOB support using utf8 load files +# +CREATE TABLE t1 (a mediumblob NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +LOAD DATA INFILE '../../std_data/loaddata/mdev-11079.txt' INTO TABLE t1 CHARSET utf8 FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; +SELECT HEX(a) FROM t1; +HEX(a) +25AAABAC +DROP TABLE t1; # +# MDEV-11631 LOAD DATA INFILE fails to load data with an escape character followed by a multi-byte character +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +LOAD DATA INFILE '../../std_data/loaddata/mdev-11631.txt' INTO TABLE t1 CHARACTER SET utf8; +SELECT HEX(a) FROM t1; +HEX(a) +C3A4 +DROP TABLE t1; CREATE DATABASE d1 CHARSET latin1; USE d1; CREATE TABLE t1 (val TEXT); diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 383ad10ba66..16a59ece0ac 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -70,9 +70,9 @@ sleep(0.5) select count(*) FROM mysql.slow_log; count(*) 1 -truncate mysql.slow_log; set @@long_query_time=default; set global slow_query_log= @org_slow_query_log; set @@log_slow_filter=default; set @@log_slow_verbosity=default; set global log_output= default; +truncate mysql.slow_log; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 203921ce08f..2b62252a129 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -461,6 +461,10 @@ The following options may be given as the first argument: --max-seeks-for-key=# Limit assumed max number of seeks when looking up rows based on a key + --max-session-mem-used=# + Amount of memory a single user session is allowed to + allocate. This limits the value of the session variable + MEM_USED --max-sort-length=# The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored) @@ -1311,6 +1315,7 @@ max-prepared-stmt-count 16382 max-recursive-iterations 18446744073709551615 max-relay-log-size 1073741824 max-seeks-for-key 18446744073709551615 +max-session-mem-used 9223372036854775807 max-sort-length 1024 max-sp-recursion-depth 0 max-statement-time 0 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index b4d78ac8347..5ba8d827cd8 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2935,6 +2935,17 @@ t2 A, t2 B where A.b = B.b order by A.col2, B.col2 limit 10, 1000000; drop table t1,t2,t3; +# +# mdev-10705 : long order by list that can be skipped +# +SELECT 1 +UNION +( SELECT 2 +ORDER BY NULL, @a0 := 3, @a1 := 3, @a2 := 3, @a3 := 3, @a4 := 3, +@a5 := 3, @a6 := 3, @a7 := 3, @a8 := 3, @a9 := 3, @a10 := 3 ); +1 +1 +2 End of 5.5 tests # # MDEV-5884: EXPLAIN UPDATE ... ORDER BY LIMIT shows wrong #rows diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index dee15c3b451..be8835bfe01 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7165,6 +7165,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index e87f4b9b451..069a19c4001 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -348,4 +348,49 @@ 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; +# +# MDEV-10148: Database crashes in the query to the View +# +CREATE TABLE t1 ( +key_code INT(11) NOT NULL, +value_string VARCHAR(50) NULL DEFAULT NULL, +PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE TABLE t2 ( +key_code INT(11) NOT NULL, +target_date DATE NULL DEFAULT NULL, +PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE TABLE t3 ( +now_date DATE NOT NULL, +PRIMARY KEY (now_date) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE VIEW v1 +AS +SELECT +B.key_code, +B.target_date +FROM +t2 B INNER JOIN t3 C ON +B.target_date = C.now_date +; +SET @s = 'SELECT A.* FROM t1 A WHERE A.key_code IN (SELECT key_code FROM v1)'; +PREPARE stmt FROM @s; +EXECUTE stmt; +key_code value_string +EXECUTE stmt; +key_code value_string +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; +create table t1 (a int); +create table t2 (a int); +create table t3(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t2 select a from t1; +insert into t3 select a from t1; +select null in (select a from t1 where a < out3.a union select a from t2 where +(select a from t3) +1 < out3.a+1) from t3 out3; +ERROR 21000: Subquery returns more than 1 row +drop table t1, t2, t3; diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 792ce657ba0..dd1c29ba2ca 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2401,5 +2401,44 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; x 0 drop table t1; +# +# MDEV-7691: Assertion `outer_context || !*from_field || *from_field == not_found_field' ... +# +set optimizer_switch=default; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(8); +PREPARE stmt FROM " +SELECT * FROM t2 +HAVING 0 IN ( + SELECT a FROM t1 + WHERE a IN ( + SELECT a FROM t1 + WHERE b = a + ) +) +"; +EXECUTE stmt; +b +EXECUTE stmt; +b +# Alternative test case, without HAVING +CREATE TABLE t3 (i INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6); +PREPARE stmt FROM " +SELECT * FROM t3 AS t10 +WHERE EXISTS ( + SELECT * FROM t3 AS t20 WHERE t10.i IN ( + SELECT i FROM t3 + ) +)"; +EXECUTE stmt; +i +6 +EXECUTE stmt; +i +6 +drop table t1, t2, t3; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 7f6ff7a6a16..f8657642dc0 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7165,6 +7165,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 6a17f8c8bf5..2ecd4a70b73 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7158,6 +7158,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c37fc66613f..0005556e51c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7156,6 +7156,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 0fec9524dd5..0c1183e90ac 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7171,6 +7171,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6b45582eb45..82eb7ed60a2 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7156,6 +7156,17 @@ NULL drop view v2; drop table t1,t2; # +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; +# # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops # with UNION in ALL subquery # diff --git a/mysql-test/r/trigger_no_defaults-11698.result b/mysql-test/r/trigger_no_defaults-11698.result new file mode 100644 index 00000000000..40546cee41d --- /dev/null +++ b/mysql-test/r/trigger_no_defaults-11698.result @@ -0,0 +1,22 @@ +set sql_mode='strict_all_tables'; +create table t1 (a int not null, b int); +insert t1 (b) values (1); +ERROR HY000: Field 'a' doesn't have a default value +create trigger trgi before insert on t1 for each row +case new.b +when 10 then +set new.a = new.b; +when 30 then +set new.a = new.a; +else +do 1; +end case| +insert t1 (b) values (10); +insert t1 (b) values (20); +ERROR HY000: Field 'a' doesn't have a default value +insert t1 (b) values (30); +select * from t1; +a b +10 10 +0 30 +drop table t1; diff --git a/mysql-test/r/trigger_null-8605.result b/mysql-test/r/trigger_null-8605.result index b187fc19554..10315988708 100644 --- a/mysql-test/r/trigger_null-8605.result +++ b/mysql-test/r/trigger_null-8605.result @@ -354,3 +354,13 @@ show columns from t1; Field Type Null Key Default Extra a int(11) NO PRI NULL drop table t1; +create table t1 ( +pk int primary key, +i int, +v1 int as (i) virtual, +v2 int as (i) virtual +); +create trigger tr before update on t1 for each row set @a = 1; +insert into t1 (pk, i) values (null, null); +ERROR 23000: Column 'pk' cannot be null +drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index adaaf084a3d..8712519350d 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1233,12 +1233,9 @@ a b select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a; a b 1 a -2 b select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a; a b 1 a -2 b -3 c select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a; a b 1 a @@ -1621,7 +1618,6 @@ NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 select NULL AS `a` from `test`.`t1` union select NULL AS `a` from `test`.`t1` order by `a` DROP TABLE t1; -End of 5.0 tests # # Bug#32858: Error: "Incorrect usage of UNION and INTO" does not take # subselects into account @@ -1736,6 +1732,16 @@ a 6 7 8 +(select a from t1 where false) UNION (select a from t1) limit 8; +a +10 +2 +3 +4 +5 +6 +7 +8 drop table t1; # # Bug#11765255 58201: @@ -2031,6 +2037,25 @@ bbbb dddd drop table t1; # +# MDEV-10172: UNION query returns incorrect rows outside +# conditional evaluation +# +create table t1 (d datetime not null primary key); +insert into t1(d) values ('2016-06-01'),('2016-06-02'),('2016-06-03'),('2016-06-04'); +select * from +( +select * from t1 where d between '2016-06-02' and '2016-06-05' + union +(select * from t1 where d < '2016-06-05' order by d desc limit 1) +) onlyJun2toJun4 +order by d; +d +2016-06-02 00:00:00 +2016-06-03 00:00:00 +2016-06-04 00:00:00 +drop table t1; +End of 5.0 tests +# # WL#1763 Avoid creating temporary table in UNION ALL # EXPLAIN SELECT 1 UNION ALL SELECT 1 LIMIT 1 OFFSET 1; |