diff options
Diffstat (limited to 'mysql-test/r')
22 files changed, 792 insertions, 33 deletions
diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result index b3ef9c354f7..54df4e0c96c 100644 --- a/mysql-test/r/alter_table_online.result +++ b/mysql-test/r/alter_table_online.result @@ -184,6 +184,35 @@ CREATE TABLE t1 (a LONGTEXT COLLATE latin1_general_ci); ALTER TABLE t1 MODIFY a LONGTEXT COLLATE latin1_swedish_ci, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY DROP TABLE t1; -# -# End of MDEV-8948 ALTER ... INPLACE does work for BINARY, BLOB -# +select @@global.delay_key_write; +@@global.delay_key_write +ON +create table t1 (a int, b int, key(b)); +flush tables; +flush status; +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 0 +insert t1 values (1,2),(2,3),(3,4); +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 0 +alter online table t1 delay_key_write=1; +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 1 +flush tables; +insert t1 values (1,2),(2,3),(3,4); +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 2 +alter online table t1 delay_key_write=0; +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 2 +flush tables; +insert t1 values (1,2),(2,3),(3,4); +show status like 'Feature_delay_key_write'; +Variable_name Value +Feature_delay_key_write 2 +drop table t1; diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index e5edc1e344b..e077f919aa0 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -614,22 +614,24 @@ ANALYZE }, "block-nl-join": { "table": { - "table_name": "<subquery2>", + "table_name": "t2", "access_type": "ALL", - "possible_keys": ["distinct_key"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": "REPLACED", "filtered": 100, - "r_filtered": 100 + "r_filtered": 0, + "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))" }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "r_filtered": 100, - "materialized": { - "unique": 1, + "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))", + "r_filtered": null + }, + "subqueries": [ + { "query_block": { "select_id": 2, "r_loops": 1, @@ -646,24 +648,7 @@ ANALYZE } } } - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "r_loops": 1, - "rows": 2, - "r_rows": 2, - "r_total_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "buffer_type": "incremental", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t2.b = `<subquery2>`.a", - "r_filtered": 0 - } + ] } } drop table t1,t2; diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 317d8bd05df..262b49e9a60 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -507,6 +507,7 @@ select t.a, count(*) from t1,t where t1.a=t.a group by t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 35 func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) explain @@ -522,6 +523,7 @@ where t1.a=t.a group by t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 35 func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) # with clause in the specification of a derived table diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index e986b486bf2..deba9b1f09a 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2916,5 +2916,64 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 27 deallocate prepare stmt1; drop table t1,t2; +# +# Bug mdev-12670: mergeable derived / view with subqueries +# subject to semi-join optimizations +# (actually this is a 5.3 bug.) +# +create table t1 (a int) engine=myisam; +insert into t1 values (5),(3),(2),(7),(2),(5),(1); +create table t2 (b int, index idx(b)) engine=myisam; +insert into t2 values (2),(3),(2),(1),(3),(4); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +explain select a from t1 where a in (select b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1) +explain select * from (select a from t1 where a in (select b from t2)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1) +create view v1 as select a from t1 where a in (select b from t2); +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1) +drop view v1; +drop table t1,t2; +# +# Bug mdev-12812: mergeable derived / view with subqueries +# NOT subject to semi-join optimizations +# +CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('foo'); +CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar'),('qux'),('foo'); +SELECT STRAIGHT_JOIN * +FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; +c1 +foo +foo +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * +FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#3 */ select `test`.`t2`.`c2` from `test`.`t2` where <cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`)) +DROP TABLE t1, t2; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result index 18aa7ed8379..266ea6c5eef 100644 --- a/mysql-test/r/func_regexp_pcre.result +++ b/mysql-test/r/func_regexp_pcre.result @@ -880,3 +880,17 @@ SET @regCheck= '\\xE0\\x01'; SELECT CAST(0xE001 AS BINARY) REGEXP @regCheck; CAST(0xE001 AS BINARY) REGEXP @regCheck 1 +# MDEV-12420: Testing recursion overflow +SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral,Golf,Hotel,India,Juliet,Kilo,Lima,Mike,StrataL3,November,Oscar,StrataL2,Sand,P3,P4SwitchTest,Arsys,Poppa,ExtensionMgr,Arp,Quebec,Romeo,StrataApiV2,PtReyes,Sierra,SandAcl,Arrow,Artools,BridgeTest,Tango,SandT,PAlaska,Namespace,Agent,Qos,PatchPanel,ProjectReport,Ark,Gimp,Agent,SliceAgent,Arnet,Bgp,Ale,Tommy,Central,AsicPktTestLib,Hsc,SandL3,Abuild,Pca9555,Standby,ControllerDut,CalSys,SandLib,Sb820,PointV2,BfnLib,Evpn,BfnSdk,Sflow,ManagementActive,AutoTest,GatedTest,Bgp,Sand,xinetd,BfnAgentLib,bf-utils,Hello,BfnState,Eos,Artest,Qos,Scd,ThermoMgr,Uniform,EosUtils,Eb,FanController,Central,BfnL3,BfnL2,tcp_wrappers,Victor,Environment,Route,Failover,Whiskey,Xray,Gimp,BfnFixed,Strata,SoCal,XApi,Msrp,XpProfile,tcpdump,PatchPanel,ArosTest,FhTest,Arbus,XpAcl,MacConc,XpApi,telnet,QosTest,Alpha2,BfnVlan,Stp,VxlanControllerTest,MplsAgent,Bravo2,Lanz,BfnMbb,Intf,XCtrl,Unicast,SandTunnel,L3Unicast,Ipsec,MplsTest,Rsvp,EthIntf,StageMgr,Sol,MplsUtils,Nat,Ira,P4NamespaceDut,Counters,Charlie2,Aqlc,Mlag,Power,OpenFlow,Lag,RestApi,BfdTest,strongs,Sfa,CEosUtils,Adt746,MaintenanceMode,MlagDut,EosImage,IpEth,MultiProtocol,Launcher,Max3179,Snmp,Acl,IpEthTest,PhyEee,bf-syslibs,tacc,XpL2,p4-ar-switch,p4-bf-switch,LdpTest,BfnPhy,Mirroring,Phy6,Ptp' REGEXP '^((?!\b(Strata|StrataApi|StrataApiV2)\b).)*$'); +1 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp +SELECT REGEXP_INSTR('a_kollision', 'oll'); +REGEXP_INSTR('a_kollision', 'oll') +4 +SELECT REGEXP_INSTR('a_kollision', '(oll)'); +REGEXP_INSTR('a_kollision', '(oll)') +4 +SELECT REGEXP_INSTR('a_kollision', 'o([lm])\\1'); +REGEXP_INSTR('a_kollision', 'o([lm])\\1') +4 diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index 1305be86e5a..c55e8d138f8 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -1133,5 +1133,78 @@ where index_date_updated= 10 and index_id < 800; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition drop table t0,t1,t2; -set optimizer_switch=@save_ext_key_optimizer_switch; -SET SESSION STORAGE_ENGINE=DEFAULT; +# +# MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' +# was corrupted, server crashes in opt_sum_query +CREATE TABLE t1 ( +pk INT, +f1 VARCHAR(3), +f2 VARCHAR(1024), +PRIMARY KEY (pk), +KEY(f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; +INSERT INTO t1 VALUES (1,'foo','abc'),(2,'bar','def'); +SELECT MAX(t2.pk) FROM t1 t2 INNER JOIN t1 t3 ON t2.f1 = t3.f1 WHERE t2.pk <= 4; +MAX(t2.pk) +2 +drop table t1; +CREATE TABLE t1 ( +pk1 INT, +pk2 INT, +f1 VARCHAR(3), +f2 VARCHAR(1021), +PRIMARY KEY (pk1,pk2), +KEY(f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; +INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); +explain format= json +select * from t1 force index(f2) where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["f2"], + "key": "f2", + "key_length": "3070", + "used_key_parts": ["f2", "pk1"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", + "attached_condition": "t1.f1 <= '3'" + } + } +} +drop table t1; +CREATE TABLE t1 ( +f2 INT, +pk2 INT, +f1 VARCHAR(3), +pk1 VARCHAR(1000), +PRIMARY KEY (pk1,pk2), +KEY k1(pk1,f2) +) ENGINE=InnoDB CHARSET utf8 ROW_FORMAT= DYNAMIC; +INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); +explain format= json +select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["k1"], + "key": "k1", + "key_length": "3011", + "used_key_parts": ["pk1", "f2", "pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", + "attached_condition": "t1.f1 <= '3'" + } + } +} +drop table t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index d7b4e107a8d..46e542910a1 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2337,4 +2337,99 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`v3` = 4 and `test`.`t1`.`i1` = `test`.`t3`.`i3` and `test`.`t2`.`i2` = `test`.`t3`.`i3` drop table t1,t2,t3; +# +# MDEV-11958: LEFT JOIN with stored routine produces incorrect result +# +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where ifnull(`test`.`t2`.`x`,0) = 0 +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where `f`(`test`.`t2`.`x`,0) = 0 +drop function f; +drop table t; +CREATE TABLE t1 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL +); +CREATE TABLE t2 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL, +col3 DECIMAL(33,5) NULL DEFAULT NULL +); +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN +IF p_num IS NULL THEN +RETURN p_return; +ELSE +RETURN p_num; +END IF; +END | +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where ifnull(`test`.`t2`.`col3`,0) = 0 +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0 +DROP FUNCTION f1; +DROP TABLE t1,t2; +# end of 5.5 tests 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 cab4c78eb3a..65e2dde409e 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2348,6 +2348,101 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`v3` = 4 and `test`.`t1`.`i1` = `test`.`t3`.`i3` and `test`.`t2`.`i2` = `test`.`t3`.`i3` drop table t1,t2,t3; +# +# MDEV-11958: LEFT JOIN with stored routine produces incorrect result +# +CREATE TABLE t (x INT); +INSERT INTO t VALUES(1),(NULL); +CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE IFNULL(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where ifnull(`test`.`t2`.`x`,0) = 0 +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +x x IFNULL(t2.x,0) f(t2.x,0) +NULL NULL 0 0 +explain extended +SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) +FROM t t1 LEFT JOIN t t2 +ON t1.x = t2.x +WHERE f(t2.x,0)=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where `f`(`test`.`t2`.`x`,0) = 0 +drop function f; +drop table t; +CREATE TABLE t1 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL +); +CREATE TABLE t2 ( +col1 DECIMAL(33,5) NULL DEFAULT NULL, +col2 DECIMAL(33,5) NULL DEFAULT NULL, +col3 DECIMAL(33,5) NULL DEFAULT NULL +); +INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); +INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); +CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) +RETURNS decimal(33,5) +LANGUAGE SQL +DETERMINISTIC +CONTAINS SQL +SQL SECURITY INVOKER +BEGIN +IF p_num IS NULL THEN +RETURN p_return; +ELSE +RETURN p_num; +END IF; +END | +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE IFNULL(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where ifnull(`test`.`t2`.`col3`,0) = 0 +SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +col1 col1 col3 +2.00000 NULL NULL +2.00000 NULL NULL +EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 +FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 +WHERE f1(t2.col3,0) = 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0 +DROP FUNCTION f1; +DROP TABLE t1,t2; +# end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 3bc97859303..c94599235b1 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -425,7 +425,7 @@ c1 bb cc Warnings: -Warning 1931 Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete +Warning 1931 Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete select * from v1 LIMIT ROWS EXAMINED 11; c1 bb @@ -438,7 +438,8 @@ from (select * from t1 where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp LIMIT ROWS EXAMINED 11; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 2 func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where select * from (select * from t1 diff --git a/mysql-test/r/log_tables-big.result b/mysql-test/r/log_tables-big.result index 1e189a7726f..0a33510fe77 100644 --- a/mysql-test/r/log_tables-big.result +++ b/mysql-test/r/log_tables-big.result @@ -1,8 +1,12 @@ set @@global.log_output = 'TABLE'; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; set session long_query_time=10; select get_lock('bug27638', 1); get_lock('bug27638', 1) 1 +connection con2; set session long_query_time=1; select get_lock('bug27638', 2); get_lock('bug27638', 2) @@ -25,7 +29,11 @@ select if (query_time >= '00:01:40', 'OK', 'WRONG') as qt, sql_text from mysql.s where sql_text = 'select get_lock(\'bug27638\', 101)'; qt sql_text OK select get_lock('bug27638', 101) +connection con1; select release_lock('bug27638'); release_lock('bug27638') 1 +connection default; +disconnect con1; +disconnect con2; set @@global.log_output=default; diff --git a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result index b282be15a56..34764466d2a 100644 --- a/mysql-test/r/mix2_myisam.result +++ b/mysql-test/r/mix2_myisam.result @@ -1549,6 +1549,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where; Using index alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' +show warnings; +Level Code Message +Error 1062 Duplicate entry 'a' for key 'v_2' alter table t1 add key(v); Warnings: Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result index 5706bf21895..95788b393dd 100644 --- a/mysql-test/r/mrr_icp_extra.result +++ b/mysql-test/r/mrr_icp_extra.result @@ -350,6 +350,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where; Using index alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' +show warnings; +Level Code Message +Error 1062 Duplicate entry 'a' for key 'v_2' alter table t1 add key(v); Warnings: Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index f7eb5db6468..5b41b35c1ba 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1255,6 +1255,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 13 const # Using where; Using index alter table t1 add unique(v); ERROR 23000: Duplicate entry '{ ' for key 'v_2' +show warnings; +Level Code Message +Error 1062 Duplicate entry 'a' for key 'v_2' alter table t1 add key(v); Warnings: Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release diff --git a/mysql-test/r/myisam_debug.result b/mysql-test/r/myisam_debug.result index 39cbd69cdb0..6232e3eac0e 100644 --- a/mysql-test/r/myisam_debug.result +++ b/mysql-test/r/myisam_debug.result @@ -29,3 +29,15 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1,t2; disconnect insertConn; +call mtr.add_suppression("Index for table '.*test.t1\\.MYI' is corrupt; try to repair it"); +create table t1 (a int, index(a)); +lock tables t1 write; +insert t1 values (1),(2),(1); +set @old_dbug=@@debug_dbug; +set debug_dbug='+d,mi_lock_database_failure'; +unlock tables; +Warnings: +Error 126 Index for table './test/t1.MYI' is corrupt; try to repair it +Error 1030 Got error 22 "Invalid argument" from storage engine MyISAM +set debug_dbug=@old_dbug; +drop table t1; diff --git a/mysql-test/r/partition_alter.result b/mysql-test/r/partition_alter.result index cbd90b5ba7c..76b55cefb07 100644 --- a/mysql-test/r/partition_alter.result +++ b/mysql-test/r/partition_alter.result @@ -51,3 +51,50 @@ execute stmt; execute stmt; deallocate prepare stmt; drop table test_data; +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +engine=innodb +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +alter table t1 add check (b in (0, 1)); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`#sql-temporary` +alter table t1 add check (b in (0, 10)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `d` date NOT NULL, + `b` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`,`d`), + CONSTRAINT `CONSTRAINT_1` CHECK (`b` in (0,10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(d) +(PARTITION p1 VALUES LESS THAN ('2016-10-18') ENGINE = InnoDB, + PARTITION p2 VALUES LESS THAN ('2020-10-19') ENGINE = InnoDB) +insert t1 values (2, '2020-01-03', 20); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +alter table t1 add check (b in (0, 1)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `d` date NOT NULL, + `b` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`,`d`), + CONSTRAINT `CONSTRAINT_1` CHECK (`b` in (0,1)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(d) +(PARTITION p1 VALUES LESS THAN ('2016-10-18') ENGINE = MyISAM, + PARTITION p2 VALUES LESS THAN ('2020-10-19') ENGINE = MyISAM) +insert t1 values (2, '2020-01-03', 20); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 01257c33361..ec7f2c0a3d5 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -576,3 +576,42 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using where; Using filesort drop table t1,t2; +# +# mdev-12931: semi-join in ON expression of STRAIGHT_JOIN +# joining a base table and a mergeable derived table +# +CREATE TABLE t1 (f1 int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (3),(2); +CREATE TABLE t2 (f2 int) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1),(4); +CREATE TABLE t3 (f3 int) ENGINE=InnoDB; +INSERT INTO t3 VALUES (5),(6); +CREATE TABLE t4 (f4 int) ENGINE=InnoDB; +INSERT INTO t4 VALUES (1),(8); +SELECT * +FROM t1 +INNER JOIN +( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq +ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) +ON ( f1 >= f2 ); +f1 f2 f3 +3 1 5 +2 1 5 +3 1 6 +2 1 6 +EXPLAIN EXTENDED +SELECT * +FROM t1 +INNER JOIN +( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq +ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) +ON ( f1 >= f2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) +3 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` semi join (`test`.`t4`) join `test`.`t3` where `test`.`t4`.`f4` = 1 and `test`.`t1`.`f1` >= `test`.`t2`.`f2` +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index b4ddd5e5849..f5d5be8f726 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -379,6 +379,7 @@ drop table t3, t4, t5; # # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch # +set @optimizer_switch_save= @@optimizer_switch; create table t1 (c1 char(2) not null, c2 char(2)); create table t2 (c3 char(2), c4 char(2)); insert into t1 values ('a1', 'b1'); @@ -400,6 +401,7 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 drop table t1, t2; +set optimizer_switch= @optimizer_switch_save; # # MDEV-12673: cost-based choice between materialization and in-to-exists # @@ -442,3 +444,44 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index DROP TABLE t1,t2,t3; +# +# MDEV-7599: in-to-exists chosen after min/max optimization +# +set @optimizer_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,6),(2,4), (8,9); +SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +b c +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 MATERIALIZED t1 index NULL a 5 NULL 2 100.00 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where `test`.`t2`.`b` = `<subquery2>`.`MIN(a)`)))) +set optimizer_switch= 'materialization=off'; +SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +b c +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t1 index NULL a 5 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` having trigcond(<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))) +set optimizer_switch= @optimizer_switch_save; +DROP TABLE t1,t2; +CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('baz'),('qux'); +CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('abc'),('def'); +SELECT * FROM t1 +WHERE f1 = ALL( SELECT MAX(t2a.f2) +FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 +ON (f3 = t2b.f2) ); +f1 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 861360ddf9a..a601dac5337 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1652,9 +1652,9 @@ CREATE VIEW v1 AS SELECT 1; EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived3> system NULL NULL NULL NULL 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); a a @@ -3072,4 +3072,97 @@ project_number aaa drop table t1, t2, t3; set optimizer_switch= @tmp_mdev6859; +# +# MDEV-12675: subquery subject to semi-join optimizations +# in ON expression of INNER JOIN +# +set @tmp_mdev12675=@@optimizer_switch; +set optimizer_switch=default; +create table t1 (a int) engine=myisam; +insert into t1 values (5),(3),(2),(7),(2),(5),(1); +create table t2 (b int, index idx(b)) engine=myisam; +insert into t2 values (2),(3),(2),(1),(3),(4); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +explain +select a from t1, t2 where b between 1 and 2 and a in (select b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +explain +select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +drop table t1,t2; +set optimizer_switch= @tmp_mdev12675; +# +# MDEV-12817: subquery NOT subject to semi-join optimizations +# in ON expression of INNER JOIN +# +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +c1 +1 +2 +EXPLAIN EXTENDED SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1 +# mdev-12820 +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +c1 c2 c4 +1 NULL NULL +2 NULL NULL +EXPLAIN EXTENDED SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(`test`.`t2`.`c2` = `test`.`t1`.`c1` and <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#3 */ select `test`.`t3`.`c3` from `test`.`t3` where <cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))) where 1 +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 07c9e74b4d7..8466f9fba4e 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1625,3 +1625,26 @@ i1 DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; +# +# mdev-7791: materialization of a semi-join subquery + +# RAND() in WHERE +# (materialized table is accessed last) +# +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='materialization=on'; +create table t1(i int); +insert into t1 values (1), (2), (3), (7), (9), (10); +create table t2(i int); +insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +select * from t1 where (rand() < 0) and i in (select i from t2); +i +explain extended +select * from t1 where (rand() < 0) and i in (select i from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where rand() < 0 +drop table t1,t2; +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index bcfa1b14be9..77a073ea2d3 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1665,9 +1665,9 @@ CREATE VIEW v1 AS SELECT 1; EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived3> system NULL NULL NULL NULL 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1); a a @@ -3086,6 +3086,99 @@ project_number aaa drop table t1, t2, t3; set optimizer_switch= @tmp_mdev6859; +# +# MDEV-12675: subquery subject to semi-join optimizations +# in ON expression of INNER JOIN +# +set @tmp_mdev12675=@@optimizer_switch; +set optimizer_switch=default; +create table t1 (a int) engine=myisam; +insert into t1 values (5),(3),(2),(7),(2),(5),(1); +create table t2 (b int, index idx(b)) engine=myisam; +insert into t2 values (2),(3),(2),(1),(3),(4); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +explain +select a from t1, t2 where b between 1 and 2 and a in (select b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +explain +select a from t1 join t2 on b between 1 and 2 and a in (select b from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1) +1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +drop table t1,t2; +set optimizer_switch= @tmp_mdev12675; +# +# MDEV-12817: subquery NOT subject to semi-join optimizations +# in ON expression of INNER JOIN +# +CREATE TABLE t1 (c1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (c2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c3 int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); +CREATE TABLE t4 (c4 int) ENGINE=MyISAM; +INSERT INTO t4 VALUES (7),(8); +SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +c1 +1 +2 +EXPLAIN EXTENDED SELECT c1 +FROM t1 +LEFT JOIN +( t2 INNER JOIN t3 ON ( 1 IN ( SELECT c4 FROM t4 ) ) ) +ON (c1 = c3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1 +# mdev-12820 +SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +c1 c2 c4 +1 NULL NULL +2 NULL NULL +EXPLAIN EXTENDED SELECT * +FROM t1 +LEFT JOIN +( ( SELECT * FROM t2 WHERE c2 IN ( SELECT c3 FROM t3 ) ) AS sq INNER JOIN t4 ) +ON (c1 = c2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) +3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t4`.`c4` AS `c4` from `test`.`t1` left join (`test`.`t2` join `test`.`t4`) on(`test`.`t2`.`c2` = `test`.`t1`.`c1` and <in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#3 */ select `test`.`t3`.`c3` from `test`.`t3` where <cache>(`test`.`t2`.`c2`) = `test`.`t3`.`c3`))) where 1 +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 407f8534672..b751632307a 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2368,6 +2368,16 @@ tr1 1 2016-01-01 10:10:10.33 tr2 2 2016-01-01 10:10:10.99 drop table t1; set time_zone= @@global.time_zone; +# MDEV-12992: Increasing memory consumption +with each invocation of trigger +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT); +CREATE TRIGGER tr +AFTER UPDATE ON t1 FOR EACH ROW SELECT (SELECT b FROM t2) INTO @x; +# Running 20000 queries +DROP TABLE t1,t2; # # Start of 10.3 tests # diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index e0aa93d5c97..956703237a1 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -2178,6 +2178,35 @@ WHERE t1_2.b NOT IN ( SELECT 4 UNION ALL SELECT 5 ); a b a b 1 1 1 1 DROP TABLE t1; +# Bug mdev-12788: UNION ALL + impossible having for derived +# with IN subquery in WHERE +# +CREATE TABLE t1 (i int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (pk int PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +SELECT 1, 2 +UNION ALL +SELECT i, COUNT(*) FROM ( +SELECT * FROM t1 WHERE i IN ( SELECT pk FROM t2 ) +) AS sq +GROUP BY i +HAVING i = 10; +1 2 +1 2 +EXPLAIN EXTENDED SELECT 1, 2 +UNION ALL +SELECT i, COUNT(*) FROM ( +SELECT * FROM t1 WHERE i IN ( SELECT pk FROM t2 ) +) AS sq +GROUP BY i +HAVING i = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +Warnings: +Note 1003 /* select#1 */ select 1 AS `1`,2 AS `2` union all /* select#2 */ select 1 AS `i`,count(0) AS `COUNT(*)` from `test`.`t2` where 1 group by 1 having 0 +DROP TABLE t1,t2; # # Start of 10.3 tests # |