diff options
Diffstat (limited to 'mysql-test/r')
25 files changed, 352 insertions, 72 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index db28a2748aa..6ac23c6ce80 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -491,15 +491,15 @@ ANALYZE "query_block": { "select_id": 1, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "having_condition": "TOP > t2.a", "filesort": { "sort_key": "t2.a", "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 0, - "volatile parameter": "REPLACED", + "r_buffer_size": "REPLACED", "temporary_table": { "table": { "table_name": "t2", @@ -507,7 +507,7 @@ ANALYZE "r_loops": 1, "rows": 256, "r_rows": 256, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } @@ -522,14 +522,14 @@ ANALYZE "query_block": { "select_id": 1, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filesort": { "sort_key": "t2.a", "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 256, - "volatile parameter": "REPLACED", + "r_buffer_size": "REPLACED", "temporary_table": { "table": { "table_name": "t2", @@ -537,7 +537,7 @@ ANALYZE "r_loops": 1, "rows": 256, "r_rows": 256, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } @@ -563,14 +563,14 @@ ANALYZE "query_block": { "select_id": 1, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filesort": { "sort_key": "t2.a", "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 256, - "volatile parameter": "REPLACED", + "r_buffer_size": "REPLACED", "temporary_table": { "table": { "table_name": "t2", @@ -578,7 +578,7 @@ ANALYZE "r_loops": 1, "rows": 256, "r_rows": 256, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } @@ -600,14 +600,14 @@ ANALYZE "query_block": { "select_id": 1, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 }, @@ -619,7 +619,7 @@ ANALYZE "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 }, @@ -632,14 +632,14 @@ ANALYZE "query_block": { "select_id": 2, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", "access_type": "ALL", "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 } @@ -653,7 +653,7 @@ ANALYZE "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 }, @@ -685,22 +685,22 @@ ANALYZE "query_block": { "select_id": 1, "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filesort": { "sort_key": "group_concat(t3.f3 separator ',')", "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 0, - "volatile parameter": "REPLACED", + "r_buffer_size": "REPLACED", "temporary_table": { "filesort": { "sort_key": "(subquery#2)", "r_loops": 1, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "r_used_priority_queue": false, "r_output_rows": 0, - "volatile parameter": "REPLACED", + "r_buffer_size": "REPLACED", "temporary_table": { "table": { "table_name": "t2", @@ -708,7 +708,7 @@ ANALYZE "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100 }, @@ -719,7 +719,7 @@ ANALYZE "r_loops": 1, "rows": 2, "r_rows": 2, - "volatile parameter": "REPLACED", + "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 0, "attached_condition": "t3.f3 in (1,2)" diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 5dc60742e53..81b65c420cd 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1917,6 +1917,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_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_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/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 814cd2dc5aa..54da823c439 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2766,6 +2766,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 5a61f044148..b91dd1b87ae 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,34 +562,21 @@ 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 DATABASE d1 CHARSET latin1; -USE d1; -CREATE TABLE t1 (val TEXT); -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; -SELECT COUNT(*) FROM t1; -COUNT(*) -1 -SELECT HEX(val) FROM t1; -HEX(val) -C38322525420406E696F757A656368756E3A20E98198E2889AF58081AEE7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE78999E880B3E7B8BAEFBDAAE7B9A7E89699E296A1E7B8BAE4BBA3EFBD8CE7B8BAEFBDA9E7B8B2E2889AE38184E7B99DEFBDB3E7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE5B3A8EFBD84E8ABA0EFBDA8E89C89F580948EE599AAE7B8BAEFBDAAE7B8BAE9A198EFBDA9EFBDB1E7B9A7E581B5E289A0E7B8BAEFBDBEE7B9A7E9A194EFBDA9E882B4EFBDA5EFBDB5E980A7F5808B96E28693E99EABE38287E58F99E7B8BAE58AB1E28691E7B8BAF5808B9AE7828AE98095EFBDB1E7B8BAEFBDAFE7B8B2E288ABE6A89FE89EB3E6BA98F58081ADE88EA0EFBDBAE98095E6BA98F58081AEE89D93EFBDBAE8AD9BEFBDACE980A7F5808B96E28693E7B8BAF580918EE288AAE7B8BAE4B88AEFBC9EE7B8BAE4B99DE28691E7B8BAF5808B96EFBCA0E88DB3E6A68AEFBDB9EFBDB3E981B2E5B3A8E296A1E7B8BAE7A4BCE7828AE88DB3E6A68AEFBDB0EFBDBDE7B8BAA0E7B8BAE88B93EFBDBEE5B899EFBC9E -CREATE DATABASE d2 CHARSET utf8; -USE d2; -CREATE TABLE t1 (val TEXT); -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' IGNORE INTO TABLE t1; -Warnings: -Warning 1366 Incorrect string value: '\xF5\x80\x81\xAE\xE7\xB9...' for column 'val' at row 1 -SELECT COUNT(*) FROM t1; -COUNT(*) -1 -SELECT HEX(val) FROM t1; -HEX(val) -C38322525420406E696F757A656368756E3A20E98198E2889A3F3F3F3FE7B99DE4B88AE383A3E7B99DE696903F3F3F3FE7B9A7EFBDA8E7B99DEFBDB3E7B99DE78999E880B3E7B8BAEFBDAAE7B9A7E89699E296A1E7B8BAE4BBA3EFBD8CE7B8BAEFBDA9E7B8B2E2889AE38184E7B99DEFBDB3E7B99DE4B88AE383A3E7B99DE696903F3F3F3FE7B9A7EFBDA8E7B99DEFBDB3E7B99DE5B3A8EFBD84E8ABA0EFBDA8E89C893F3F3F3FE599AAE7B8BAEFBDAAE7B8BAE9A198EFBDA9EFBDB1E7B9A7E581B5E289A0E7B8BAEFBDBEE7B9A7E9A194EFBDA9E882B4EFBDA5EFBDB5E980A73F3F3F3FE28693E99EABE38287E58F99E7B8BAE58AB1E28691E7B8BA3F3F3F3FE7828AE98095EFBDB1E7B8BAEFBDAFE7B8B2E288ABE6A89FE89EB3E6BA983F3F3F3FE88EA0EFBDBAE98095E6BA983F3F3F3FE89D93EFBDBAE8AD9BEFBDACE980A73F3F3F3FE28693E7B8BA3F3F3F3FE288AAE7B8BAE4B88AEFBC9EE7B8BAE4B99DE28691E7B8BA3F3F3F3FEFBCA0E88DB3E6A68AEFBDB9EFBDB3E981B2E5B3A8E296A1E7B8BAE7A4BCE7828AE88DB3E6A68AEFBDB0EFBDBDE7B8BA3FE7B8BAE88B93EFBDBEE5B899EFBC9E -DROP TABLE d1.t1, d2.t1; -DROP DATABASE d1; -DROP DATABASE d2; +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; diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 2e20a78a58c..510bb929d84 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 1b9d664dbda..2541687a729 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -477,10 +477,10 @@ The following options may be given as the first argument: --max-sp-recursion-depth[=#] Maximum stored procedure recursion depth --max-statement-time=# - A SELECT query that have taken more than - max_statement_time seconds will be aborted. The argument - will be treated as a decimal value with microsecond - precision. A value of 0 (default) means no timeout + A query that has taken more than max_statement_time + seconds will be aborted. The argument will be treated as + a decimal value with microsecond precision. A value of 0 + (default) means no timeout --max-tmp-tables=# Unused, will be removed. --max-user-connections=# The maximum number of active connections for a single diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1c1eb359fba..02381ebeed5 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2937,6 +2937,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 1f7edd79029..f3187568428 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7166,6 +7166,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 93204f99808..b8295416e93 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..6bbb80c0662 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2401,5 +2401,56 @@ 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; +# +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +# +create table t1(c1 int, c2 int, primary key(c2)); +insert into t1 values(2,1),(1,2); +select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; +x +1 +(select c1 from t1 group by c1,c2 order by c1 limit 1); +c1 +1 +drop table t1; diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index af023ea12f1..d1cad4f9389 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7166,6 +7166,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 de116f46079..32d39224e2a 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7159,6 +7159,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 dd3fb658f7a..2daf33980d6 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7157,6 +7157,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 abe032f7311..09530f3ac0f 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7172,6 +7172,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 8d2c823a38a..7efb5127d2b 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7157,6 +7157,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/union.result b/mysql-test/r/union.result index 289058f1aab..3f8ad60e452 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; |