From 48aee4595708bebc04a6c8e640bbf6f55ce4e33c Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 7 Feb 2013 21:46:02 -0800 Subject: Fixed bug mdev-3995. This bug happened because the executor tried to use a wrong TABLE REF object when building access keys. It constructed keys from fields of a materialized table from a ref object created to construct keys from the fields of the underlying base table. This could happen only when materialized table was created for a non-correlated IN subquery and only when the materialized table used for lookups. In this case we are guaranteed to be able to construct the keys from the fields of tables that would be outer tables for the tables of the IN subquery. The patch makes sure that no ref objects constructed from fields of materialized lookup tables are to be used. --- mysql-test/r/subselect_sj2_mat.result | 102 ++++++++++++++++++++++++++++++++++ 1 file changed, 102 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 4bce431a771..f4cf874934d 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1127,3 +1127,105 @@ AND ( 6 ) IN ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay DROP TABLE t1, t2; set max_join_size= @tmp_906385; +# +# mdev-3995: Wrong result for semijoin with materialization +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 ( +cat_id int(10) unsigned NOT NULL, +PRIMARY KEY (cat_id) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(709411),(709412),(709413),(709414),(709416),(709417),(709418),(709419),(709421),(709422), +(709424),(709425),(709427),(709428),(709429),(709431),(709432),(709433),(709434),(709435), +(709438),(709439),(709441),(709442),(709443),(709444),(709445),(709446),(709447),(709450), +(709451),(709454),(709455),(709456),(709457),(709459),(709460),(709461),(709462),(709463), +(709464),(709465),(709467),(709469),(709470),(709471),(709472),(709473),(709474),(709475), +(709476),(709477),(709478),(709479),(709480),(709481),(709483),(709484),(709485),(709487), +(709490),(709491),(709492),(709493),(709494),(709495),(709496),(709497),(709498),(709499), +(709500),(709501),(709502),(709503),(709504),(709505),(709506),(709507),(709509),(709510), +(709511),(709512),(709513),(709514),(709515),(709516),(709517),(709518),(709519),(709520), +(709521),(709522),(709523),(709524),(709525),(709526),(709527),(709528),(709529),(709530), +(709531),(709532),(709533),(709534),(709535),(709536),(709537),(709538),(709539),(709540), +(709541),(709542),(709543),(709544),(709545),(709546),(709548),(709549),(709551),(709552), +(709553),(709555),(709556),(709557),(709558),(709559),(709560),(709561),(709562),(709563), +(709564),(709565),(709566),(709567),(709568),(709569),(709570),(709571),(709572),(709573), +(709574),(709575),(709576),(709577),(709578),(709579),(709580),(709581),(709582),(709583), +(709584),(709585),(709586),(709587),(709588),(709590),(709591),(709592),(709593),(709594), +(709595),(709596),(709597),(709598),(709600),(709601),(709602),(709603),(709604),(709605), +(709606),(709608),(709609),(709610),(709611),(709612),(709613),(709614),(709615),(709616), +(709617),(709618),(709619),(709620),(709621),(709622),(709623),(709624),(709625),(709626), +(709627),(709628),(709629),(709630),(709631),(709632),(709633),(709634),(709635),(709637), +(709638),(709639),(709640),(709641),(709642),(709643),(709644),(709645),(709646),(709649), +(709650),(709651),(709652),(709653),(709654),(709655),(709656),(709657),(709658),(709659); +CREATE TABLE t2 ( +cat_id int(10) NOT NULL, +KEY cat_id (cat_id) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(708742),(708755),(708759),(708761),(708766),(708769),(708796),(708798),(708824),(708825), +(708838),(708844),(708861),(708882),(708887),(708889),(708890),(709586),(709626); +CREATE TABLE t3 ( +sack_id int(10) unsigned NOT NULL, +kit_id tinyint(3) unsigned NOT NULL DEFAULT '0', +cat_id int(10) unsigned NOT NULL, +PRIMARY KEY (sack_id,kit_id,cat_id) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(33479,6,708523),(33479,6,708632),(33479,6,709085),(33479,6,709586),(33479,6,709626); +CREATE TABLE t4 ( +cat_id int(10) unsigned NOT NULL, +KEY cat_id (cat_id) +) ENGINE=MyISAM; +INSERT INTO t4 (cat_id) SELECT cat_id from t2; +set optimizer_switch='materialization=off'; +EXPLAIN +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t2) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index +1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t3) +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.cat_id 1 Using where; Using index +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t2) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +count(*) +2 +set optimizer_switch='materialization=on'; +EXPLAIN +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t4) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index +2 MATERIALIZED t4 index cat_id cat_id 4 NULL 19 Using index +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t4) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +count(*) +2 +EXPLAIN +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t2) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index +2 MATERIALIZED t2 index cat_id cat_id 4 NULL 19 Using index +SELECT count(*) FROM t1, t3 +WHERE t1.cat_id = t3.cat_id AND +t3.cat_id IN (SELECT cat_id FROM t2) AND +t3.sack_id = 33479 AND t3.kit_id = 6; +count(*) +2 +DROP TABLE t1,t2,t3,t4; +set optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1