diff options
Diffstat (limited to 'mysql-test/r')
56 files changed, 507 insertions, 58 deletions
diff --git a/mysql-test/r/comments.result b/mysql-test/r/comments.result index ce817b5012f..c13eb510326 100644 --- a/mysql-test/r/comments.result +++ b/mysql-test/r/comments.result @@ -35,6 +35,12 @@ select 1 /*M!50000 +1 */; select 1 /*M!50300 +1 */; 1 +1 2 +select 2 /*M!99999 +1 */; +2 +1 +3 +select 2 /*M!100000 +1 */; +2 +1 +3 select 2 /*M!999999 +1 */; 2 2 diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result index 6925da6b206..5c75bc098e3 100644 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@ -671,6 +671,21 @@ FF9D EFBE9D D800DF84 F0908E84 DBC0DC00 F4808080 DROP TABLE IF EXISTS t1; +# +# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES +# OUT-OF-ORDER RESULTS +# +CREATE TABLE t1 SELECT ('a a') as n; +INSERT INTO t1 VALUES('a b'); +SELECT * FROM t1 ORDER BY LOWER(n) ASC; +n +a a +a b +SELECT * FROM t1 ORDER BY LOWER(n) DESC; +n +a b +a a +DROP TABLE t1; select @@collation_connection; @@collation_connection utf16_bin diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result index 8f63dda2adc..baf3f4bb0f8 100644 --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@ -714,6 +714,21 @@ HEX(a) HEX(CONVERT(a USING utf8mb4)) 00D884DF F0908E84 C0DB00DC F4808080 DROP TABLE IF EXISTS t1; +# +# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES +# OUT-OF-ORDER RESULTS +# +CREATE TABLE t1 SELECT ('a a') as n; +INSERT INTO t1 VALUES('a b'); +SELECT * FROM t1 ORDER BY LOWER(n) ASC; +n +a a +a b +SELECT * FROM t1 ORDER BY LOWER(n) DESC; +n +a b +a a +DROP TABLE t1; select @@collation_connection; @@collation_connection utf16le_bin diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index e0bede76fb7..b67de4d0051 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -670,6 +670,21 @@ HEX(a) HEX(CONVERT(a USING utf8mb4)) 00010384 F0908E84 00100000 F4808080 DROP TABLE IF EXISTS t1; +# +# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES +# OUT-OF-ORDER RESULTS +# +CREATE TABLE t1 SELECT ('a a') as n; +INSERT INTO t1 VALUES('a b'); +SELECT * FROM t1 ORDER BY LOWER(n) ASC; +n +a a +a b +SELECT * FROM t1 ORDER BY LOWER(n) DESC; +n +a b +a a +DROP TABLE t1; select @@collation_connection; @@collation_connection utf32_bin diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index 73ceae39295..55d5c6eed86 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -1149,6 +1149,21 @@ EFBE9D EFBE9D F0908E84 F0908E84 F4808080 F4808080 DROP TABLE IF EXISTS t1; +# +# BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES +# OUT-OF-ORDER RESULTS +# +CREATE TABLE t1 SELECT ('a a') as n; +INSERT INTO t1 VALUES('a b'); +SELECT * FROM t1 ORDER BY LOWER(n) ASC; +n +a a +a b +SELECT * FROM t1 ORDER BY LOWER(n) DESC; +n +a b +a a +DROP TABLE t1; select @@collation_connection; @@collation_connection utf8mb4_bin diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index cbf5900d57c..80d2d64dced 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2253,7 +2253,7 @@ EXPLAIN EXTENDED SELECT a FROM v1 WHERE a > 100 ORDER BY b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select 4 AS `a` from dual where (4 > 100) order by 1 +Note 1003 select 4 AS `a` from dual where 0 order by 1 DROP VIEW v1; DROP TABLE t1; CREATE TABLE IF NOT EXISTS `galleries` ( diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 019099bde14..6f68483f684 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -999,4 +999,44 @@ c 11112222 33334444 DROP TABLE t1; +# +# Bug#16539979 BASIC SELECT COUNT(DISTINCT ID) IS BROKEN. +# Bug#17867117 ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK +# +SET @tmp_table_size_save= @@tmp_table_size; +SET @@tmp_table_size= 1024; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 SELECT a+8 FROM t1; +INSERT INTO t1 SELECT a+16 FROM t1; +INSERT INTO t1 SELECT a+32 FROM t1; +INSERT INTO t1 SELECT a+64 FROM t1; +INSERT INTO t1 VALUE(NULL); +SELECT COUNT(DISTINCT a) FROM t1; +COUNT(DISTINCT a) +128 +SELECT COUNT(DISTINCT (a+0)) FROM t1; +COUNT(DISTINCT (a+0)) +128 +DROP TABLE t1; +create table tb( +id int auto_increment primary key, +v varchar(32)) +engine=myisam charset=gbk; +insert into tb(v) values("aaa"); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +insert into tb(v) (select v from tb); +update tb set v=concat(v, id); +select count(distinct case when id<=64 then id end) from tb; +count(distinct case when id<=64 then id end) +64 +select count(distinct case when id<=63 then id end) from tb; +count(distinct case when id<=63 then id end) +63 +drop table tb; +SET @@tmp_table_size= @tmp_table_size_save; End of 5.5 tests diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result index 88a9d114bc6..006c51d1880 100644 --- a/mysql-test/r/error_simulation.result +++ b/mysql-test/r/error_simulation.result @@ -94,7 +94,7 @@ INSERT INTO t1 VALUES (1),(2); INSERT INTO t2 VALUES (1),(2); SET SESSION debug_dbug="+d,bug11747970_raise_error"; INSERT IGNORE INTO t2 SELECT f1 FROM t1 a WHERE NOT EXISTS (SELECT 1 FROM t2 b WHERE a.f1 = b.f1); -ERROR HY000: Unknown error +ERROR 70100: Query execution was interrupted SET SESSION debug_dbug = DEFAULT; DROP TABLE t1,t2; # diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result index 37951c30787..084587079ba 100644 --- a/mysql-test/r/events_trans.result +++ b/mysql-test/r/events_trans.result @@ -135,3 +135,4 @@ SELECT * FROM t2; a ROLLBACK WORK TO SAVEPOINT A; DROP TABLE t1, t2; +DROP EVENT e1; diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result index c2f1e5bf273..011dec4d555 100644 --- a/mysql-test/r/func_compress.result +++ b/mysql-test/r/func_compress.result @@ -102,7 +102,6 @@ a foo Warnings: Warning 1259 ZLIB: Input data corrupted -Warning 1259 ZLIB: Input data corrupted explain select *, uncompress(a) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 8e50c045775..8e2bdeae93c 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1458,6 +1458,8 @@ DROP TABLE derived1; DROP TABLE D; CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,1), (1,2), (1,3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); SET SQL_MODE='ONLY_FULL_GROUP_BY'; SELECT COUNT(*) FROM t1; COUNT(*) @@ -1473,12 +1475,19 @@ COUNT(*) SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) FROM t1 outr; ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*) FROM t1 outr, (SELECT b, count(*) FROM t2) as t3; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*) FROM t1 outr where (1,1) in (SELECT a, count(*) FROM t2); +COUNT(*) +0 SELECT COUNT(*) FROM t1 a JOIN t1 outr ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a); COUNT(*) 0 +SELECT * FROM (SELECT a FROM t1 GROUP BY a) sq JOIN t2 ON a = b; +a b SET SQL_MODE=default; -DROP TABLE t1; +DROP TABLE t1,t2; End of 5.0 tests # # BUG#47280 - strange results from count(*) with order by multiple diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index a033c91bc05..1d1300a96db 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -264,6 +264,13 @@ INET_NTOA(0) SELECT '1' IN ('1', INET_NTOA(0)); '1' IN ('1', INET_NTOA(0)) 1 +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1 AND 2)) AND 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1)) OR 1; +NAME_CONST('a', -(1)) OR 1 +1 # # Bug #52165: Assertion failed: file .\dtoa.c, line 465 # @@ -561,6 +568,3 @@ ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa # # End of 5.5 tests # -# -# End of tests -# diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index 78b8e5d908a..f405a2297cb 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -52,7 +52,7 @@ explain extended select * from t1 where xxx regexp('is a test of some long text id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from dual where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to') +Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from dual where 1 select * from t1 where xxx regexp('is a test of some long text to '); xxx this is a test of some long text to see what happens diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index a5280f13ada..c9d7b6636c4 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1153,7 +1153,6 @@ str num notnumber 0 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'notnumber' -Warning 1292 Truncated incorrect DOUBLE value: 'notnumber' SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6); str num notnumber 0 diff --git a/mysql-test/r/gis-precise.result b/mysql-test/r/gis-precise.result index 3b07be3930a..71eed65b2ea 100644 --- a/mysql-test/r/gis-precise.result +++ b/mysql-test/r/gis-precise.result @@ -452,3 +452,6 @@ ST_NUMPOINTS(ST_EXTERIORRING(ST_BUFFER( POLYGONFROMTEXT( 'POLYGON( ( 0.0 -3.0, 0.0 -3.0 ))' ), 136 +select astext(buffer(st_linestringfromwkb(linestring(point(-1,1), point(-1,-2))),-1)); +astext(buffer(st_linestringfromwkb(linestring(point(-1,1), point(-1,-2))),-1)) +GEOMETRYCOLLECTION EMPTY diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index fa7b93092d9..9acdb1a87c2 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1574,6 +1574,27 @@ SELECT 1 FROM g1 WHERE a >= ANY 1 DROP TABLE g1; # +# Bug#16451878 GEOMETRY QUERY CRASHES SERVER +# +# should not crash +SELECT ASTEXT(0x0100000000030000000100000000000010); +ASTEXT(0x0100000000030000000100000000000010) +NULL +#should not crash +SELECT ENVELOPE(0x0100000000030000000100000000000010); +ENVELOPE(0x0100000000030000000100000000000010) +NULL +#should not crash +SELECT +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1); +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1) +NULL +#should not crash +SELECT +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1); +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1) +NULL +# # MDEV-3819 missing constraints for spatial column types # create table t1 (pt point); diff --git a/mysql-test/r/index_intersect_innodb.result b/mysql-test/r/index_intersect_innodb.result index d28b3a0bf92..33f2247e5d1 100644 --- a/mysql-test/r/index_intersect_innodb.result +++ b/mysql-test/r/index_intersect_innodb.result @@ -463,29 +463,29 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,3 NULL # Using sort_intersect(PRIMARY,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country 4,7 NULL # Using sort_intersect(PRIMARY,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City USE INDEX () WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population @@ -739,13 +739,13 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Population,Country 4,4,7 NULL # Using sort_intersect(PRIMARY,Population,Country); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country PRIMARY,Country,Population 4,7,4 NULL # Using sort_intersect(PRIMARY,Country,Population); Using where SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -1033,7 +1033,7 @@ EXPLAIN SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,35 NULL # Using sort_intersect(PRIMARY,f4); Using where +1 SIMPLE t1 index_merge PRIMARY,f4 PRIMARY,f4 4,39 NULL # Using sort_intersect(PRIMARY,f4); Using where SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; f1 f4 f5 diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index b93d15f7bef..5202c79f3c7 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -580,9 +580,12 @@ pk1 pk2 explain select * from t1 where badkey=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1 key1 4 const 100 Using where +set @tmp_index_merge_ror_cpk=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; explain select * from t1 where pk1 < 7500 and key1 = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where +set optimizer_switch=@tmp_index_merge_ror_cpk; explain select * from t1 where pktail1ok=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 2fa2d5bcce1..2c0dc77399f 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1415,9 +1415,12 @@ pk1 pk2 explain select * from t1 where badkey=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1 key1 4 const 91 Using where +set @tmp_index_merge_ror_cpk=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; explain select * from t1 where pk1 < 7500 and key1 = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where +set optimizer_switch=@tmp_index_merge_ror_cpk; explain select * from t1 where pktail1ok=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 4a806e0831c..e303c288552 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1311,7 +1311,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce(1,NULL),3) in ((1,3),(2,2))) +Note 1003 select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where 1 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); f1 f2 f3 f1 f2 diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 6a543f920e4..88f2fd7c630 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1322,7 +1322,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce(1,NULL),3) in ((1,3),(2,2))) +Note 1003 select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where 1 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); f1 f2 f3 f1 f2 diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 53048939fa9..9b2b6e12a14 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1183,7 +1183,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on optimizer-use-condition-selectivity 1 performance-schema TRUE performance-schema-accounts-size 10 @@ -1234,7 +1234,7 @@ port 3306 port-open-timeout 0 preload-buffer-size 32768 profiling-history-size 15 -progress-report-time 56 +progress-report-time 5 protocol-version 10 query-alloc-block-size 8192 query-cache-limit 1048576 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index a473279ce40..c67a7a00425 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5265,6 +5265,20 @@ slow_log CREATE TABLE `slow_log` ( SET @@global.log_output= @old_log_output_state; SET @@global.slow_query_log= @old_slow_query_log_state; SET @@global.general_log= @old_general_log_state; +# MDEV-5481 mysqldump fails to dump geometry types properly +create table t1 (g GEOMETRY) CHARSET koi8r; +create table t2 (g GEOMETRY) CHARSET koi8r; +insert into t1 values (point(1,1)), (point(2,2)); +################################################## +\0\0\0\0\0\0\0\0\0\0\0\0\0ð?\0\0\0\0\0\0ð? +\0\0\0\0\0\0\0\0\0\0\0\0\0\0@\0\0\0\0\0\0\0@ +################################################## +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET koi8r; +select astext(g) from t2; +astext(g) +POINT(1 1) +POINT(2 2) +drop table t1, t2; # # End of 5.1 tests # diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index e57715d1eb6..5ea037df759 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2534,6 +2534,73 @@ i 3 4 DROP TABLE t1; +# +# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703) +# +create table t1 ( +a int not null, +b int not null, +pk int not null, +primary key (pk), +key(a), +key(b) +) partition by hash(pk) partitions 10; +insert into t1 values (1,2,4); +insert into t1 values (1,0,17); +insert into t1 values (1,2,25); +insert into t1 values (10,20,122); +insert into t1 values (10,20,123); +create table t2 (a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C; +insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, +10+A.a + 10*B.a + 100*C.a + 1000*D.a, +2000 + A.a + 10*B.a + 100*C.a + 1000*D.a +from t2 A, t2 B, t2 C ,t2 D; +explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY,a,b b 4 const 982 Using where +create temporary table t3 as +select * from t1 where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +create temporary table t3 as +select * from t1 ignore index(a,b) where a=1 and b=2 and pk between 1 and 999 ; +select count(*) from t3; +count(*) +802 +drop table t3; +drop table t1,t2; +# +# MDEV-5555: Incorrect index_merge on BTREE indices +# +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL, +id2 bigint(20) unsigned NOT NULL, +dob date DEFAULT NULL, +address char(100) DEFAULT NULL, +city char(35) DEFAULT NULL, +hours_worked_per_week smallint(5) unsigned DEFAULT NULL, +weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL, +KEY dob (dob), +KEY address (address), +KEY city (city), +KEY hours_worked_per_week (hours_worked_per_week), +KEY weeks_worked_last_year (weeks_worked_last_year) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY KEY (id) PARTITIONS 5; +# Insert some rows +select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; +id id2 dob address city hours_worked_per_week weeks_worked_last_year +16 16 1949-11-07 address16 city16 40 52 +50 50 1923-09-08 address50 city50 40 52 +select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21'; +id id2 dob address city hours_worked_per_week weeks_worked_last_year +16 16 1949-11-07 address16 city16 40 52 +50 50 1923-09-08 address50 city50 40 52 +drop table t1; CREATE TABLE t1 ( d DATE NOT NULL) PARTITION BY RANGE( YEAR(d) ) ( PARTITION p0 VALUES LESS THAN (1960), diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result index 78ff7cd3121..06c1b63a382 100644 --- a/mysql-test/r/partition_order.result +++ b/mysql-test/r/partition_order.result @@ -752,8 +752,8 @@ select * from t1 force index (b) where b < 10 ORDER BY b DESC; a b 6 6 4 5 -2 4 30 4 +2 4 3 3 35 2 7 1 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 4ac47bb8554..ec680b112a3 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4041,3 +4041,14 @@ DEALLOCATE PREPARE stmt; DROP VIEW v2; DROP TABLE t1, t2; # End of 5.3 tests +# +# MDEV-5505: Assertion `! is_set()' fails on PREPARE SELECT +# with out of range in GROUP BY +# +CREATE TABLE t1 (a INT); +PREPARE stmt FROM "SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1"; +ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)' +SELECT 1 FROM t1 GROUP BY 0 OR 18446744073709551615+1; +ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)' +drop table t1; +# End of 5.3 tests diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 424b1009a97..1b12a9f5512 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -797,6 +797,8 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 41 Using index condition +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -985,6 +987,7 @@ ID Name Country Population 3798 Phoenix USA 1321045 DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; EXPLAIN SELECT * FROM City WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 67e341192da..8428936d25f 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -65,7 +65,7 @@ Country IN ('CAN', 'ARG') AND ID < 3800 OR Country < 'U' AND Name LIKE 'Zhu%' OR ID BETWEEN 3800 AND 3810; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 125 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,7,4 NULL 123 Using sort_union(Name,Country,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); @@ -362,7 +362,7 @@ WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 900 AND 1500) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 681 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 39,3,4 NULL 680 Using sort_union(Name,Country,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -798,6 +798,8 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'Pa%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 71 Using index condition +set @tmp_range_vs_index_merge=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR @@ -986,6 +988,7 @@ ID Name Country Population 3798 Phoenix USA 1321045 DROP INDEX Population ON City; DROP INDEX Name ON City; +set optimizer_switch=@tmp_range_vs_index_merge; EXPLAIN SELECT * FROM City WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR diff --git a/mysql-test/r/rpl_mysqldump_slave.result b/mysql-test/r/rpl_mysqldump_slave.result index e7ea0a308a9..4b29ff99f61 100644 --- a/mysql-test/r/rpl_mysqldump_slave.result +++ b/mysql-test/r/rpl_mysqldump_slave.result @@ -11,4 +11,11 @@ START ALL SLAVES; STOP ALL SLAVES; CHANGE MASTER '' TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_MYPORT, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START; START ALL SLAVES; +start slave; +Warnings: +Note 1254 Slave is already running +CHANGE MASTER '' TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=BINLOG_START; +start slave; +Warnings: +Note 1254 Slave is already running include/rpl_end.inc diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 005dd52c449..9bcd0a90ecf 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -5321,7 +5321,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0) +Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0 DROP TABLE t1; SELECT * FROM mysql.time_zone WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) @@ -5344,7 +5344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system idx NULL NULL NULL 1 100.00 1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index Warnings: -Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1) +Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8) SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) WHERE 1 IS NULL OR b < 33 AND b = c; a b c @@ -5468,7 +5468,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6)) INSERT INTO t1 VALUES (3,1,6); SELECT * FROM t1, t2 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 0113791b799..36babd0f390 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5332,7 +5332,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0) +Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0 DROP TABLE t1; SELECT * FROM mysql.time_zone WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) @@ -5355,7 +5355,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system idx NULL NULL NULL 1 100.00 1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index Warnings: -Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1) +Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8) SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) WHERE 1 IS NULL OR b < 33 AND b = c; a b c @@ -5479,7 +5479,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6)) INSERT INTO t1 VALUES (3,1,6); SELECT * FROM t1, t2 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 005dd52c449..9bcd0a90ecf 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -5321,7 +5321,7 @@ SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where (0 <> 0) +Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0 DROP TABLE t1; SELECT * FROM mysql.time_zone WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) @@ -5344,7 +5344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system idx NULL NULL NULL 1 100.00 1 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index Warnings: -Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` = 8) and 1) +Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`c` = 8) SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) WHERE 1 IS NULL OR b < 33 AND b = c; a b c @@ -5468,7 +5468,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0)) +Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and (`test`.`t1`.`b1` = 6)) INSERT INTO t1 VALUES (3,1,6); SELECT * FROM t1, t2 WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3704812eb04..c9fda1e0d0e 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1166,6 +1166,23 @@ SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; # +# Bug mdev-5630: always true conjunctive condition +# when optimizer_use_condition_selectivity=3 +# +set use_stat_tables = 'preferably'; +set optimizer_use_condition_selectivity = 3; +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10); +CREATE TABLE t2 (id int, flag char(1), INDEX(id)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (100,'0'),(101,'1'); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +SELECT * FROM t1, t2 WHERE id = a AND ( a = 16 OR flag AND a != 6 ); +a id flag +DROP TABLE t1,t2; +# # Bug mdev-4429: join with range condition whose selectivity == 0 # when optimizer_use_condition_selectivity=3 # diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index a3b6049c4d0..e6f88bf0dc2 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1174,6 +1174,23 @@ SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; # +# Bug mdev-5630: always true conjunctive condition +# when optimizer_use_condition_selectivity=3 +# +set use_stat_tables = 'preferably'; +set optimizer_use_condition_selectivity = 3; +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10); +CREATE TABLE t2 (id int, flag char(1), INDEX(id)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (100,'0'),(101,'1'); +ANALYZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +SELECT * FROM t1, t2 WHERE id = a AND ( a = 16 OR flag AND a != 6 ); +a id flag +DROP TABLE t1,t2; +# # Bug mdev-4429: join with range condition whose selectivity == 0 # when optimizer_use_condition_selectivity=3 # diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 88d81196dcf..868efcbdf31 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -3,10 +3,10 @@ Variable_name Value Ssl_cipher DHE-RSA-AES256-SHA SHOW STATUS LIKE 'Ssl_server_not_before'; Variable_name Value -Ssl_server_not_before Jan 29 11:56:49 2010 GMT +Ssl_server_not_before Feb 20 02:55:06 2010 GMT SHOW STATUS LIKE 'Ssl_server_not_after'; Variable_name Value -Ssl_server_not_after Jan 28 11:56:49 2015 GMT +Ssl_server_not_after Sep 3 02:55:06 2030 GMT drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, diff --git a/mysql-test/r/stat_tables_par.result b/mysql-test/r/stat_tables_par.result index a98f934fa96..f9b2a7921cb 100644 --- a/mysql-test/r/stat_tables_par.result +++ b/mysql-test/r/stat_tables_par.result @@ -148,7 +148,7 @@ use dbt3_s001; set use_stat_tables='preferably'; analyze table lineitem persistent for columns() indexes (i_l_receiptdate); set debug_sync='RESET'; -select * from mysql.index_stats where table_name='lineitem' order by index_name; +select * from mysql.index_stats where table_name='lineitem' order by index_name, prefix_arity; db_name table_name index_name prefix_arity avg_frequency dbt3_s001 lineitem PRIMARY 1 4.0033 dbt3_s001 lineitem PRIMARY 2 1.0000 @@ -212,7 +212,7 @@ analyze table lineitem persistent for all; set debug_sync='open_and_process_table WAIT_FOR parker'; set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2'; use dbt3_s001; -select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68; +select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity;; db_name table_name index_name prefix_arity avg_frequency l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment dbt3_s001 lineitem i_l_shipdate 1 2.6500 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s set debug_sync='RESET'; diff --git a/mysql-test/r/stat_tables_par_innodb.result b/mysql-test/r/stat_tables_par_innodb.result index e0c00c482a0..d3cb0d54d39 100644 --- a/mysql-test/r/stat_tables_par_innodb.result +++ b/mysql-test/r/stat_tables_par_innodb.result @@ -151,7 +151,7 @@ use dbt3_s001; set use_stat_tables='preferably'; analyze table lineitem persistent for columns() indexes (i_l_receiptdate); set debug_sync='RESET'; -select * from mysql.index_stats where table_name='lineitem' order by index_name; +select * from mysql.index_stats where table_name='lineitem' order by index_name, prefix_arity; db_name table_name index_name prefix_arity avg_frequency dbt3_s001 lineitem PRIMARY 1 4.0033 dbt3_s001 lineitem PRIMARY 2 1.0000 @@ -161,7 +161,11 @@ dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 dbt3_s001 lineitem i_l_partkey 1 30.0250 dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.5000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 @@ -176,7 +180,11 @@ dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 dbt3_s001 lineitem i_l_partkey 1 30.0250 dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.5000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 @@ -202,15 +210,29 @@ db_name table_name index_name prefix_arity avg_frequency dbt3_s001 lineitem PRIMARY 1 4.0027 dbt3_s001 lineitem PRIMARY 2 1.0000 dbt3_s001 lineitem i_l_commitdate 1 2.7155 +dbt3_s001 lineitem i_l_commitdate 2 1.0364 +dbt3_s001 lineitem i_l_commitdate 3 1.0000 dbt3_s001 lineitem i_l_orderkey 1 4.0027 +dbt3_s001 lineitem i_l_orderkey 2 1.0000 dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0027 dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_orderkey_quantity 3 1.0000 dbt3_s001 lineitem i_l_partkey 1 30.0200 +dbt3_s001 lineitem i_l_partkey 2 1.0089 +dbt3_s001 lineitem i_l_partkey 3 1.0000 dbt3_s001 lineitem i_l_receiptdate 1 2.6473 +dbt3_s001 lineitem i_l_receiptdate 2 1.0152 +dbt3_s001 lineitem i_l_receiptdate 3 1.0000 dbt3_s001 lineitem i_l_shipdate 1 2.6496 +dbt3_s001 lineitem i_l_shipdate 2 1.0149 +dbt3_s001 lineitem i_l_shipdate 3 1.0000 dbt3_s001 lineitem i_l_suppkey 1 600.4000 +dbt3_s001 lineitem i_l_suppkey 2 1.2073 +dbt3_s001 lineitem i_l_suppkey 3 1.0000 dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0200 dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5771 +dbt3_s001 lineitem i_l_suppkey_partkey 3 1.0030 +dbt3_s001 lineitem i_l_suppkey_partkey 4 1.0000 set @save_global_use_stat_tables=@@global.use_stat_tables; set global use_stat_tables='preferably'; set debug_sync='RESET'; @@ -221,9 +243,11 @@ analyze table lineitem persistent for all; set debug_sync='open_and_process_table WAIT_FOR parker'; set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2'; use dbt3_s001; -select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68; +select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity;; db_name table_name index_name prefix_arity avg_frequency l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice l_discount l_tax l_returnflag l_linestatus l_shipDATE l_commitDATE l_receiptDATE l_shipinstruct l_shipmode l_comment dbt3_s001 lineitem i_l_shipdate 1 2.6496 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +dbt3_s001 lineitem i_l_shipdate 2 1.0149 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s +dbt3_s001 lineitem i_l_shipdate 3 1.0000 1 68 9 2 36 34850.16 0.09 0.06 N O 1996-04-12 1996-02-28 1996-04-20 TAKE BACK RETURN MAIL slyly bold pinto beans detect s set debug_sync='RESET'; set global use_stat_tables=@save_global_use_stat_tables; DROP DATABASE dbt3_s001; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index effa77a5eb4..b7909f7028c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -561,7 +561,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6098,7 +6098,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 426e1ba39f9..034cba58c8f 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -163,7 +163,7 @@ SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index 1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index -2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where +2 SUBQUERY t1 const PRIMARY,a PRIMARY 4 const 1 Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; pk a b 0 4 4 @@ -172,7 +172,7 @@ SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 2 Using where; Using index 1 PRIMARY t3 ref b b 5 test.t2.a 2 Using index -2 SUBQUERY t1 index_subquery PRIMARY,a a 5 const 0 Using index; Using where +2 SUBQUERY t1 const PRIMARY,a PRIMARY 4 const 1 Using where SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; pk a b 0 4 4 diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 2c826cc9172..66d3fa6214a 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2304,6 +2304,33 @@ WHERE a='r' AND (c,b) NOT IN (SELECT c,b FROM t2 WHERE (c,b)=(t.c,t.b)); a b c d DROP TABLE t1, t2; # +# MDEV-5468: assertion failure with a simplified condition in subselect +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1); +CREATE TABLE t2 ( pk int PRIMARY KEY, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,4), (2,6); +SELECT ( SELECT MAX(b) FROM t1, t2 WHERE pk = a AND b < from_sq.c ) AS select_sq, +COUNT( DISTINCT from_sq.c ) +FROM ( SELECT DISTINCT t2_1.* FROM t2 AS t2_1, t2 AS t2_2 ) AS from_sq +GROUP BY select_sq ; +select_sq COUNT( DISTINCT from_sq.c ) +1 2 +DROP TABLE t1,t2; +CREATE TABLE t1 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'BE','BEL'); +CREATE TABLE t2 (id int, a2 char(2), a3 char(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'BE','BEL'), (2,'MX','MEX'); +CREATE VIEW v2 AS SELECT DISTINCT * FROM t2; +SELECT * FROM t1 AS outer_t1, v2 +WHERE v2.a3 = outer_t1.a3 +AND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id ) +AND outer_t1.a3 < 'J' +ORDER BY v2.id; +id a2 a3 id a2 a3 +DROP VIEW v2; +DROP TABLE t1,t2; +# # MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT # CREATE TABLE t1 (a INT) ENGINE=MyISAM; diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result index bc9db36c8ce..0e01f585cd4 100644 --- a/mysql-test/r/subselect_exists_to_in.result +++ b/mysql-test/r/subselect_exists_to_in.result @@ -565,7 +565,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6106,7 +6106,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result index 5849ccda631..47cb8bca734 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/subselect_extra.result @@ -46,7 +46,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where ('2007-04-25 18:30:22' = 0) +Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where 0 select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -57,7 +57,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where ('2007-04-25' = 0) +Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where 0 select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 975d90b8358..7bfa940bda2 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -568,7 +568,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6097,7 +6097,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index f9ea3c45ee3..5083e23d461 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -564,7 +564,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6093,7 +6093,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 2bc182ac6e9..6b7dbb7baf1 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -567,7 +567,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6104,7 +6104,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index a41c7636038..1fa3eb2c723 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -564,7 +564,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where (1 = '1') +Note 1003 select 3 AS `numreponse` from `test`.`t1` where 1 drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -6093,7 +6093,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE sq4_alias1 system NULL NULL NULL NULL 0 const row not found SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index b5263163cb1..c76d40d1a5f 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2793,6 +2793,22 @@ mysql information_schema DROP TABLE t1; # +# MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (2),(3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (c INT, INDEX(c)); +INSERT INTO t2 VALUES (5),(6); +PREPARE stmt FROM +"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )"; +EXECUTE stmt; +a +EXECUTE stmt; +a +DROP TABLE t1,t2,t3; +# # MySQL Bug#13340270: assertion table->sort.record_pointers == __null # CREATE TABLE t1 ( diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index d87514923c4..2975176c64a 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1109,6 +1109,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) ); i1 DROP TABLE t1,t2,t3; +# +# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin +# +CREATE TABLE t1 (a INT) engine=innodb; +INSERT INTO t1 VALUES (8),(9); +CREATE TABLE t2 (b INT) engine=innodb; +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; +INSERT INTO t2 VALUES (4),(5); +explain +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +a +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 00e393635a2..ff97882c469 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1124,6 +1124,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) ); i1 DROP TABLE t1,t2,t3; +# +# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin +# +CREATE TABLE t1 (a INT) engine=innodb; +INSERT INTO t1 VALUES (8),(9); +CREATE TABLE t2 (b INT) engine=innodb; +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; +INSERT INTO t2 VALUES (4),(5); +explain +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +a +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 2306f19606c..f4d7d986a9d 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1111,6 +1111,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) ); i1 DROP TABLE t1,t2,t3; +# +# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin +# +CREATE TABLE t1 (a INT) engine=innodb; +INSERT INTO t1 VALUES (8),(9); +CREATE TABLE t2 (b INT) engine=innodb; +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; +INSERT INTO t2 VALUES (4),(5); +explain +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); +a +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 60e309cdf4a..92ad951325b 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2807,6 +2807,22 @@ information_schema mysql DROP TABLE t1; # +# MDEV-5581: Server crashes in in JOIN::prepare on 2nd execution of PS with materialization+semijoin +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (2),(3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (c INT, INDEX(c)); +INSERT INTO t2 VALUES (5),(6); +PREPARE stmt FROM +"SELECT * FROM t1 WHERE ( 9, 5 ) IN ( SELECT b, COUNT(*) FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) )"; +EXECUTE stmt; +a +EXECUTE stmt; +a +DROP TABLE t1,t2,t3; +# # MySQL Bug#13340270: assertion table->sort.record_pointers == __null # CREATE TABLE t1 ( diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 1c8039f6b53..c508bc310e1 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -519,7 +519,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where ('2007-04-25 18:30:22' = 0) +Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from (dual) where 0 select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id cur_date @@ -530,7 +530,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 -Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where ('2007-04-25' = 0) +Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from (dual) where 0 select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date diff --git a/mysql-test/r/udf_debug_sync.result b/mysql-test/r/udf_debug_sync.result new file mode 100644 index 00000000000..2db75f9aa76 --- /dev/null +++ b/mysql-test/r/udf_debug_sync.result @@ -0,0 +1,20 @@ +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; +CREATE VIEW v1 AS SELECT myfunc_int(1); +SET debug_sync='mysql_create_function_after_lock SIGNAL locked WAIT_FOR go'; +CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +SET debug_sync='now WAIT_FOR locked'; +SET debug_sync='find_udf_before_lock SIGNAL go'; +SELECT * FROM v1; +myfunc_int(1) +1 +FLUSH TABLES; +SET debug_sync='mysql_drop_function_after_lock SIGNAL locked WAIT_FOR go'; +DROP FUNCTION myfunc_double; +SET debug_sync='now WAIT_FOR locked'; +SET debug_sync='find_udf_before_lock SIGNAL go'; +SELECT * FROM v1; +myfunc_int(1) +1 +SET debug_sync='RESET'; +DROP VIEW v1; +DROP FUNCTION myfunc_int; diff --git a/mysql-test/r/union_crash-714.result b/mysql-test/r/union_crash-714.result new file mode 100644 index 00000000000..4a51f88b76f --- /dev/null +++ b/mysql-test/r/union_crash-714.result @@ -0,0 +1,5 @@ +create table t1 (i tinyint); +set debug_dbug='+d,bug11747970_raise_error'; +insert into t1 (i) select i from t1 union select i from t1; +ERROR 70100: Query execution was interrupted +drop table t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 5b5410f4bd6..7aadf6f75f1 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4415,7 +4415,7 @@ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select 'r' AS `f4` from dual where (20 <> 0) +Note 1003 select 'r' AS `f4` from dual where 1 DROP VIEW v1; DROP TABLE t1; # diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 95dad6d5510..6a4f5ace7d6 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -1161,6 +1161,9 @@ UPDATEXML('<a><c><a>x</a></c></a>','(a)//a','<b />') SELECT ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b'); ExtractValue('<a><a>aa</a><b>bb</b></a>','(a)/a|(a)/b') aa bb +SELECT ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)'); +ExtractValue('<a><b>abc</b><c>2</c><d>1</d></a>','substring(/a/b,..)') + # # Bug#62429 XML: ExtractValue, UpdateXML max arg length 127 chars # |