diff options
31 files changed, 664 insertions, 38 deletions
diff --git a/include/my_base.h b/include/my_base.h index 361c0aa0b00..16f8803d2d5 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -54,6 +54,7 @@ /* Internal temp table, used for temporary results */ #define HA_OPEN_INTERNAL_TABLE 512 #define HA_OPEN_MERGE_TABLE 1024 +#define HA_OPEN_FOR_STATUS 2048 /* The following is parameter to ha_rkey() how to use key */ diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index ee95812c03e..f096620daf0 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -267,3 +267,19 @@ drop table t1; create table t1(a char character set latin1 default _cp1251 0xFF); ERROR 42000: Invalid default value for 'a' End of 4.1 tests +SET CHARACTER SET DEFAULT; +# +# LP BUG#944504 Item_func_conv_charset tries to execute subquery constant +# +SET optimizer_switch = 'in_to_exists=on'; +SET character_set_connection = utf8; +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('m'),('n'); +CREATE VIEW v1 AS SELECT 'w' ; +SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 ); +ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +drop view v1; +drop table t1; +SET character_set_connection = default; +SET optimizer_switch= default; +#End of 5.3 tests diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 2f0e0bd371b..49e1422c3d4 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -420,7 +420,7 @@ join (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL key0 NULL NULL NULL 11 100.00 Using where +1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where 1 SIMPLE <derived5> ref key0 key0 5 tt.f1 2 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort @@ -478,7 +478,7 @@ join (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <derived2> ALL key0 NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 Using where 1 PRIMARY <derived4> ref key0 key0 5 x.f1 2 100.00 4 DERIVED <derived5> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort @@ -1585,7 +1585,7 @@ a EXPLAIN SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 Using where; Using filesort +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where; Using filesort 1 PRIMARY <derived3> ref key0 key0 5 v1.b 2 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort @@ -1929,5 +1929,37 @@ x y drop table t1,t2,t3; set SESSION optimizer_switch= @save_optimizer_switch; +# +# LP BUG#944782: derived table from an information schema table +# +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; +CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5)); +EXPLAIN +SELECT COUNT(*) > 0 +FROM INFORMATION_SCHEMA.COLUMNS +INNER JOIN +(SELECT TABLE_SCHEMA, +GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES +FROM INFORMATION_SCHEMA.STATISTICS +GROUP BY TABLE_SCHEMA) AS UNIQUES +ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY COLUMNS ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 DERIVED STATISTICS ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort +SELECT COUNT(*) > 0 +FROM INFORMATION_SCHEMA.COLUMNS +INNER JOIN +(SELECT TABLE_SCHEMA, +GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES +FROM INFORMATION_SCHEMA.STATISTICS +GROUP BY TABLE_SCHEMA) AS UNIQUES +ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); +COUNT(*) > 0 +1 +DROP TABLE t1; +set SESSION optimizer_switch= @save_optimizer_switch; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 9918ad2380d..f8db2c8cf72 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5151,7 +5151,7 @@ EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 60defea291b..5624d9467af 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1656,4 +1656,88 @@ b b a b DEALLOCATE PREPARE stmt; SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +# +# LP bug #943543: LEFT JOIN converted to JOIN with +# ORed IS NULL(primary key) in WHERE clause +# +CREATE TABLE t1 ( +a int, b int NOT NULL, pk int NOT NULL, +PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES +(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), +(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +DROP TABLE t2; +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES +(3,30), (8,88), (5,50), (8,81), +(4,40), (9,90), (7,70), (9,90), +(13,130), (18,188), (15,150), (18,181), +(14,140), (19,190), (17,170), (19,190); +INSERT INTO t1 VALUES (8,5,9); +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 767d0544f1b..d80b838e4e7 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1667,6 +1667,90 @@ b b a b DEALLOCATE PREPARE stmt; SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +# +# LP bug #943543: LEFT JOIN converted to JOIN with +# ORed IS NULL(primary key) in WHERE clause +# +CREATE TABLE t1 ( +a int, b int NOT NULL, pk int NOT NULL, +PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES +(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), +(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +DROP TABLE t2; +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES +(3,30), (8,88), (5,50), (8,81), +(4,40), (9,90), (7,70), (9,90), +(13,130), (18,188), (15,150), (18,181), +(14,140), (19,190), (17,170), (19,190); +INSERT INTO t1 VALUES (8,5,9); +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/myisam-big.result b/mysql-test/r/myisam-big.result new file mode 100644 index 00000000000..95a6e91d766 --- /dev/null +++ b/mysql-test/r/myisam-big.result @@ -0,0 +1,40 @@ +drop table if exists t1,t2; +create table t1 (id int, sometext varchar(100)) engine=myisam; +insert into t1 values (1, "hello"),(2, "hello2"),(4, "hello3"),(4, "hello4"); +create table t2 like t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +count(*) +131072 +alter table t1 add index (id), add index(sometext), add index(sometext,id); +alter table t1 disable keys; +alter table t1 enable keys; +drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 327cb5b2563..4eec1729fe3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5994,6 +5994,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; 1 NULL drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; # # LP BUG#905353 Wrong non-empty result with a constant table, # aggregate function in subquery, MyISAM or Aria diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 9a49f74ec42..9c928fd81f6 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1892,7 +1892,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: @@ -1900,11 +1900,12 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materializ SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b +7 7 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 @@ -1912,6 +1913,57 @@ WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); a b SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# +# BUG#946055: Crash with semijoin IN subquery when hash join is used +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (7); +CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); +INSERT INTO t2 VALUES +(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), +(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); +SET @save_optimizer_switch=@@optimizer_switch; +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=2; +EXPLAIN +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index +2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +a c +7 1 +7 1 +7 1 +SET optimizer_switch='join_cache_hashed=on'; +SET join_cache_level=4; +EXPLAIN +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 index NULL c 5 NULL 8 Using where; Using index +2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s1 hash_ALL NULL #hash#$hj 5 test.s2.d 8 Using where; Using join buffer (flat, BNLH join) +3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +a c +7 1 +7 1 +7 1 +SET optimizer_switch=@save_optimizer_switch; +SET join_cache_level=@save_join_cache_level; +DROP TABLE t1,t2; # # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index bcd08b70517..0962894bc19 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5993,6 +5993,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; 1 NULL drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; # # LP BUG#905353 Wrong non-empty result with a constant table, # aggregate function in subquery, MyISAM or Aria diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index a493722fabe..6bd5e0e4ddd 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -5989,6 +5989,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; 1 NULL drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; # # LP BUG#905353 Wrong non-empty result with a constant table, # aggregate function in subquery, MyISAM or Aria diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1167e9483c9..d97b95ad314 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6000,6 +6000,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; 1 NULL drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; # # LP BUG#905353 Wrong non-empty result with a constant table, # aggregate function in subquery, MyISAM or Aria diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 782aba08d26..5c8ccfdbda0 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5989,6 +5989,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; 1 NULL drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; # # LP BUG#905353 Wrong non-empty result with a constant table, # aggregate function in subquery, MyISAM or Aria diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index d737cbd71d1..e60851775c0 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1930,7 +1930,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: @@ -1938,11 +1938,12 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materializ SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b +7 7 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 @@ -1950,6 +1951,59 @@ WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); a b SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# +# BUG#946055: Crash with semijoin IN subquery when hash join is used +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (7); +CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); +INSERT INTO t2 VALUES +(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), +(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); +SET @save_optimizer_switch=@@optimizer_switch; +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=2; +EXPLAIN +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 index c c 5 NULL 8 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 +2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s1 ALL c NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +a c +7 1 +7 1 +7 1 +SET optimizer_switch='join_cache_hashed=on'; +SET join_cache_level=4; +EXPLAIN +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 index c c 5 NULL 8 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 +2 MATERIALIZED s2 ref d d 4 const 1 Using where; Using index +2 MATERIALIZED s1 hash_ALL c #hash#$hj 10 const,test.s2.d 8 Using where; Using join buffer (flat, BNLH join) +3 SUBQUERY t2 ALL NULL NULL NULL NULL 8 +SELECT a, c FROM t1, t2 +WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 +WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +a c +7 1 +7 1 +7 1 +SET optimizer_switch=@save_optimizer_switch; +SET join_cache_level=@save_join_cache_level; +DROP TABLE t1,t2; # # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization # diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index 1f55aea414a..ee07ef24def 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -210,3 +210,21 @@ drop table t1; create table t1(a char character set latin1 default _cp1251 0xFF); --echo End of 4.1 tests + +SET CHARACTER SET DEFAULT; +--echo # +--echo # LP BUG#944504 Item_func_conv_charset tries to execute subquery constant +--echo # +SET optimizer_switch = 'in_to_exists=on'; +SET character_set_connection = utf8; +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('m'),('n'); +CREATE VIEW v1 AS SELECT 'w' ; +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 ); +drop view v1; +drop table t1; +SET character_set_connection = default; +SET optimizer_switch= default; + +--echo #End of 5.3 tests diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 95412426aa0..9660dd1e5f5 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1313,6 +1313,40 @@ drop table t1,t2,t3; set SESSION optimizer_switch= @save_optimizer_switch; +--echo # +--echo # LP BUG#944782: derived table from an information schema table +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; + +CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5)); + +EXPLAIN +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +# this query crashed in mariadb-5.5.20 +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +DROP TABLE t1; + +set SESSION optimizer_switch= @save_optimizer_switch; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 04816402205..3fae21ac738 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1221,4 +1221,65 @@ SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +--echo # +--echo # LP bug #943543: LEFT JOIN converted to JOIN with +--echo # ORed IS NULL(primary key) in WHERE clause +--echo # + +CREATE TABLE t1 ( + a int, b int NOT NULL, pk int NOT NULL, + PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES + (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), + (1,9,6), (8,5,7), (NULL,8,8), (8,1,5); + +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +DROP TABLE t2; + +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES + (3,30), (8,88), (5,50), (8,81), + (4,40), (9,90), (7,70), (9,90), + (13,130), (18,188), (15,150), (18,181), + (14,140), (19,190), (17,170), (19,190); + +INSERT INTO t1 VALUES (8,5,9); + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +DROP TABLE t1,t2; + SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/myisam-big.test b/mysql-test/t/myisam-big.test new file mode 100644 index 00000000000..2fec2450ecd --- /dev/null +++ b/mysql-test/t/myisam-big.test @@ -0,0 +1,64 @@ +# +# Test bugs in the MyISAM code that require more space/time +--source include/big_test.inc + +# Initialise +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# BUG#925377: +# Querying myisam table metadata while 'alter table..enable keys' is +# running may corrupt the table +# +create table t1 (id int, sometext varchar(100)) engine=myisam; +insert into t1 values (1, "hello"),(2, "hello2"),(4, "hello3"),(4, "hello4"); +create table t2 like t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +insert into t2 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +connect (con2,localhost,root,,); +connection con2; +alter table t1 add index (id), add index(sometext), add index(sometext,id); +alter table t1 disable keys; +send alter table t1 enable keys; +connection default; +--sleep 1 +--disable_query_log +--disable_result_log +show table status like 't1'; +--enable_query_log +--enable_result_log +connection con2; +reap; +disconnect con2; +connection default; +drop table t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e9735dae79f..6e2b2ef86ae 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5075,8 +5075,8 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1; # example with "random" SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; - drop table t1,t2,t3; +set optimizer_switch=@subselect_tmp; --echo # --echo # LP BUG#905353 Wrong non-empty result with a constant table, @@ -5090,5 +5090,6 @@ SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 ); drop table t1; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 78dad76738c..a077e9b5af5 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1602,6 +1602,46 @@ SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +--echo # +--echo # BUG#946055: Crash with semijoin IN subquery when hash join is used +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (7); + +CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c)); + +INSERT INTO t2 VALUES + (4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'), + (7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j'); + +SET @save_optimizer_switch=@@optimizer_switch; +SET @save_join_cache_level=@@join_cache_level; + +SET join_cache_level=2; +EXPLAIN +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); + +SET optimizer_switch='join_cache_hashed=on'; +SET join_cache_level=4; +EXPLAIN +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); +SELECT a, c FROM t1, t2 + WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2 + WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2)); + +SET optimizer_switch=@save_optimizer_switch; +SET join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + --echo # --echo # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization --echo # diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index c4ce09112bc..fc4e888e6ea 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -747,7 +747,7 @@ public: { DBUG_ASSERT(args[0]->fixed); conv_charset= cs; - if (cache_if_const && args[0]->const_item()) + if (cache_if_const && args[0]->const_item() && !args[0]->with_subselect) { uint errors= 0; String tmp, *str= args[0]->val_str(&tmp); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 80828b3d554..19fabd27d72 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -2985,7 +2985,9 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root, } error= open_unireg_entry(thd, table, table_list, alias, key, key_length, - mem_root, (flags & OPEN_VIEW_NO_PARSE)); + mem_root, + (flags & (OPEN_VIEW_NO_PARSE | + MYSQL_LOCK_IGNORE_FLUSH))); if (error > 0) { my_free((uchar*)table, MYF(0)); @@ -4074,8 +4076,11 @@ retry: HA_GET_INDEX | HA_TRY_READ_ONLY), READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD | (flags & OPEN_VIEW_NO_PARSE), - thd->open_options, entry, table_list, - mem_root); + thd->open_options | + (thd->version == 0 && + (flags & MYSQL_LOCK_IGNORE_FLUSH) ? + HA_OPEN_FOR_STATUS : 0), + entry, table_list, mem_root); if (error) goto err; /* TODO: Don't free this */ @@ -4113,7 +4118,11 @@ retry: HA_TRY_READ_ONLY), (READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD), - thd->open_options, entry, FALSE))) + thd->open_options | + (thd->version == 0 && + (flags & MYSQL_LOCK_IGNORE_FLUSH) ? + HA_OPEN_FOR_STATUS : 0), + entry, FALSE))) { if (error == 7) // Table def changed { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fcbde9e27d3..437833eb90c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -60,6 +60,7 @@ static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse, bool skip_unprefixed_keyparts); static int sort_keyuse(KEYUSE *a,KEYUSE *b); +static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, bool allow_full_scan, table_map used_tables); void best_access_path(JOIN *join, JOIN_TAB *s, @@ -3781,15 +3782,15 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, if (old->field->maybe_null()) { old->optimize= KEY_OPTIMIZE_REF_OR_NULL; - /* - Remember the NOT NULL value unless the value does not depend - on other tables. - */ - if (!old->val->used_tables() && old->val->is_null()) - old->val= new_fields->val; /* The referred expression can be NULL: */ old->null_rejecting= 0; } + /* + Remember the NOT NULL value unless the value does not depend + on other tables. + */ + if (!old->val->used_tables() && old->val->is_null()) + old->val= new_fields->val; } else { @@ -7262,7 +7263,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, do { - if (!(~used_tables & keyuse->used_tables)) + if (!(~used_tables & keyuse->used_tables) && + are_tables_local(join_tab, keyuse->used_tables)) { if (first_keyuse) { @@ -7275,7 +7277,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, for( ; curr < keyuse; curr++) { if (curr->keypart == keyuse->keypart && - !(~used_tables & curr->used_tables)) + !(~used_tables & curr->used_tables) && + are_tables_local(join_tab, curr->used_tables)) break; } if (curr == keyuse) @@ -7306,7 +7309,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, keyuse= org_keyuse; do { - if (!(~used_tables & keyuse->used_tables)) + if (!(~used_tables & keyuse->used_tables) && + are_tables_local(join_tab, keyuse->used_tables)) { bool add_key_part= TRUE; if (!first_keyuse) @@ -7314,7 +7318,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, for(KEYUSE *curr= org_keyuse; curr < keyuse; curr++) { if (curr->keypart == keyuse->keypart && - !(~used_tables & curr->used_tables)) + !(~used_tables & curr->used_tables) && + are_tables_local(join_tab, curr->used_tables)) { keyuse->keypart= NO_KEYPART; add_key_part= FALSE; @@ -8712,8 +8717,13 @@ void JOIN::drop_unused_derived_keys() continue; if (table->max_keys > 1) table->use_index(tab->ref.key); - if (table->s->keys && tab->ref.key >= 0) - tab->ref.key= 0; + if (table->s->keys) + { + if (tab->ref.key >= 0) + tab->ref.key= 0; + else + table->s->keys= 0; + } tab->keys= (key_map) (table->s->keys ? 1 : 0); } } diff --git a/storage/maria/ma_bitmap.c b/storage/maria/ma_bitmap.c index 25ab5c6531e..f5cfa1ce23f 100644 --- a/storage/maria/ma_bitmap.c +++ b/storage/maria/ma_bitmap.c @@ -368,7 +368,7 @@ static inline void _ma_bitmap_mark_file_changed(MARIA_SHARE *share, if (flush_translog && share->now_transactional) (void) translog_flush(share->state.logrec_file_id); - _ma_mark_file_changed(share); + _ma_mark_file_changed_now(share); pthread_mutex_lock(&share->bitmap.bitmap_lock); /* purecov: end */ } diff --git a/storage/maria/ma_dbug.c b/storage/maria/ma_dbug.c index af90a108e2a..8391f1a9d9d 100644 --- a/storage/maria/ma_dbug.c +++ b/storage/maria/ma_dbug.c @@ -186,7 +186,7 @@ my_bool _ma_check_table_is_closed(const char *name, const char *where) MARIA_SHARE *share= info->s; if (!strcmp(share->unique_file_name.str, filename)) { - if (share->last_version) + if (share->last_version > 1) { fprintf(stderr,"Warning: Table: %s is open on %s\n", name,where); DBUG_PRINT("warning",("Table: %s is open on %s", name,where)); diff --git a/storage/maria/ma_open.c b/storage/maria/ma_open.c index 678f74bdda2..84da4f12f65 100644 --- a/storage/maria/ma_open.c +++ b/storage/maria/ma_open.c @@ -62,7 +62,8 @@ MARIA_HA *_ma_test_if_reopen(const char *filename) { MARIA_HA *info=(MARIA_HA*) pos->data; MARIA_SHARE *share= info->s; - if (!strcmp(share->unique_file_name.str,filename) && share->last_version) + if (!strcmp(share->unique_file_name.str,filename) && + share->last_version > 1) return info; } return 0; @@ -840,7 +841,12 @@ MARIA_HA *maria_open(const char *name, int mode, uint open_flags) share->base.key_parts=key_parts; share->base.all_key_parts=key_parts+unique_key_parts; if (!(share->last_version=share->state.version)) - share->last_version=1; /* Safety */ + share->last_version= 2; /* Safety */ + if (open_flags & HA_OPEN_FOR_STATUS) + { + share->last_version= 1; /* Not reusable version */ + share->options|= HA_OPTION_READ_ONLY_DATA; + } share->rec_reflength=share->base.rec_reflength; /* May be changed */ share->base.margin_key_file_length=(share->base.max_key_file_length - (keys ? MARIA_INDEX_BLOCK_MARGIN * diff --git a/storage/maria/ma_recovery.c b/storage/maria/ma_recovery.c index 37def17c4c3..77091c4747b 100644 --- a/storage/maria/ma_recovery.c +++ b/storage/maria/ma_recovery.c @@ -665,11 +665,13 @@ prototype_redo_exec_hook_dummy(INCOMPLETE_GROUP) prototype_redo_exec_hook(INCOMPLETE_LOG) { MARIA_HA *info; + if (skip_DDLs) { tprint(tracef, "we skip DDLs\n"); return 0; } + if ((info= get_MARIA_HA_from_REDO_record(rec)) == NULL) { /* no such table, don't need to warn */ @@ -1479,7 +1481,13 @@ end: if (error) { if (info != NULL) + { + /* let maria_close() mark the table properly closed */ + info->s->state.open_count= 1; + info->s->global_changed= 1; + info->s->changed= 1; maria_close(info); + } if (error == -1) error= 0; } diff --git a/storage/myisam/mi_create.c b/storage/myisam/mi_create.c index 21b683d0d4d..919f0f3e040 100644 --- a/storage/myisam/mi_create.c +++ b/storage/myisam/mi_create.c @@ -643,7 +643,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs, NOTE: The filename is compared against unique_file_name of every open table. Hence we need a real path here. */ - if (test_if_reopen(filename)) + if (test_if_reopen(filename, 1)) { my_printf_error(0, "MyISAM table '%s' is in use " "(most likely by a MERGE table). Try FLUSH TABLES.", diff --git a/storage/myisam/mi_dbug.c b/storage/myisam/mi_dbug.c index 61c52b454a0..559fa8710a0 100644 --- a/storage/myisam/mi_dbug.c +++ b/storage/myisam/mi_dbug.c @@ -183,7 +183,7 @@ my_bool check_table_is_closed(const char *name, const char *where) MYISAM_SHARE *share=info->s; if (!strcmp(share->unique_file_name,filename)) { - if (share->last_version) + if (share->last_version > 1) { fprintf(stderr,"Warning: Table: %s is open on %s\n", name,where); DBUG_PRINT("warning",("Table: %s is open on %s", name,where)); diff --git a/storage/myisam/mi_open.c b/storage/myisam/mi_open.c index 744059d941c..46ec10c064e 100644 --- a/storage/myisam/mi_open.c +++ b/storage/myisam/mi_open.c @@ -52,7 +52,8 @@ if (pos > end_pos) \ ** In MySQL the server will handle version issues. ******************************************************************************/ -MI_INFO *test_if_reopen(char *filename) +MI_INFO *test_if_reopen(char *filename, + my_bool ignore_last_version __attribute__((unused))) { LIST *pos; @@ -61,8 +62,8 @@ MI_INFO *test_if_reopen(char *filename) MI_INFO *info=(MI_INFO*) pos->data; MYISAM_SHARE *share=info->s; DBUG_ASSERT(strcmp(share->unique_file_name,filename) || - share->last_version); - if (!strcmp(share->unique_file_name,filename) && share->last_version) + share->last_version > 1 || ignore_last_version); + if (!strcmp(share->unique_file_name,filename) && share->last_version > 1) return info; } return 0; @@ -109,7 +110,8 @@ MI_INFO *mi_open(const char *name, int mode, uint open_flags) } pthread_mutex_lock(&THR_LOCK_myisam); - if (!(old_info=test_if_reopen(name_buff))) + if (!(old_info=test_if_reopen(name_buff, + test(open_flags & HA_OPEN_FOR_STATUS)))) { share= &share_buff; bzero((uchar*) &share_buff,sizeof(share_buff)); @@ -511,7 +513,12 @@ MI_INFO *mi_open(const char *name, int mode, uint open_flags) share->base.key_parts=key_parts; share->base.all_key_parts=key_parts+unique_key_parts; if (!(share->last_version=share->state.version)) - share->last_version=1; /* Safety */ + share->last_version= 2; /* Safety */ + if (open_flags & HA_OPEN_FOR_STATUS) + { + share->last_version= 1; /* Not reusable version */ + share->options|= HA_OPTION_READ_ONLY_DATA; + } share->rec_reflength=share->base.rec_reflength; /* May be changed */ share->base.margin_key_file_length=(share->base.max_key_file_length - (keys ? MI_INDEX_BLOCK_MARGIN * diff --git a/storage/myisam/myisamdef.h b/storage/myisam/myisamdef.h index 617ad5e8382..58d69afbea8 100644 --- a/storage/myisam/myisamdef.h +++ b/storage/myisam/myisamdef.h @@ -726,7 +726,7 @@ my_bool mi_check_status(void *param); void mi_fix_status(MI_INFO *org_table, MI_INFO *new_table); void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows); -extern MI_INFO *test_if_reopen(char *filename); +extern MI_INFO *test_if_reopen(char *filename, my_bool ignore_last_version); my_bool check_table_is_closed(const char *name, const char *where); int mi_open_datafile(MI_INFO *info, MYISAM_SHARE *share, const char *orn_name, File file_to_dup); |