summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/myisam.h6
-rw-r--r--mysql-test/r/derived_view.result88
-rw-r--r--mysql-test/r/join_cache.result149
-rw-r--r--mysql-test/r/join_nested_jcl6.result6
-rw-r--r--mysql-test/r/join_outer_jcl6.result2
-rw-r--r--mysql-test/t/derived_view.test93
-rw-r--r--mysql-test/t/join_cache.test129
-rw-r--r--sql/sql_join_cache.cc2
-rw-r--r--sql/sql_select.cc47
-rw-r--r--sql/table.cc47
-rw-r--r--sql/table.h2
11 files changed, 560 insertions, 11 deletions
diff --git a/include/myisam.h b/include/myisam.h
index 4ea40210cd3..d50b1dc360e 100644
--- a/include/myisam.h
+++ b/include/myisam.h
@@ -40,6 +40,12 @@ extern "C" {
#endif
#define MI_MAX_POSSIBLE_KEY_BUFF HA_MAX_POSSIBLE_KEY_BUFF
+/*
+ The following defines can be increased if necessary.
+ But beware the dependency of MI_MAX_POSSIBLE_KEY_BUFF and MI_MAX_KEY_LENGTH.
+*/
+#define MI_MAX_KEY_LENGTH 1000 /* Max length in bytes */
+#define MI_MAX_KEY_SEG 16 /* Max segments for key */
#define MI_NAME_IEXT ".MYI"
#define MI_NAME_DEXT ".MYD"
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 9b88cd214b3..ec9357eb4cb 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2406,6 +2406,94 @@ deallocate prepare stmt;
drop table t1,t2;
set optimizer_switch=@save_optimizer_switch5740;
#
+# Bug mdev-5721: possible long key access to a materialized derived table
+# (see also the test case for Bug#13261277 that is actually the same bug)
+#
+CREATE TABLE t1 (
+id varchar(255) NOT NULL DEFAULT '',
+familyid int(11) DEFAULT NULL,
+withdrawndate date DEFAULT NULL,
+KEY index_td_familyid_id (familyid,id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+CREATE TABLE t2 (
+id int(11) NOT NULL AUTO_INCREMENT,
+activefromts datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+shortdescription text,
+useraccessfamily varchar(512) DEFAULT NULL,
+serialized longtext,
+PRIMARY KEY (id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+insert into t1 values ('picture/89/1369722032695.pmd',89,NULL);
+insert into t1 values ('picture/90/1369832057370.pmd',90,NULL);
+insert into t2 values (38,'2013-03-04 07:49:22','desc','CODE','string');
+EXPLAIN
+SELECT * FROM t2 x,
+(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*)
+FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y
+WHERE x.useraccessfamily = y.useraccessfamily;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY x system NULL NULL NULL NULL 1
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
+2 DERIVED t2 system NULL NULL NULL NULL 1
+2 DERIVED t1 index NULL index_td_familyid_id 772 NULL 2 Using index
+SELECT * FROM t2 x,
+(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*)
+FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y
+WHERE x.useraccessfamily = y.useraccessfamily;
+id activefromts shortdescription useraccessfamily serialized useraccessfamily picturesubuser COUNT(*)
+38 2013-03-04 07:49:22 desc CODE string CODE string 2
+DROP TABLE t1,t2;
+#
+# Bug#13261277: Unchecked key length caused missing records.
+#
+CREATE TABLE t1 (
+col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
+);
+INSERT INTO t1 VALUES
+('d','d','l','ther'),
+(NULL,'s','NJBIQ','trzetuchv'),
+(-715390976,'coul','MYWFB','cfhtrzetu'),
+(1696792576,'f','i\'s','c'),
+ (1,'i','ltpemcfhtr','gsltpemcf'),
+ (-663027712,'mgsltpemcf','sa','amgsltpem'),
+ (-1686700032,'JPRVK','i','vamgsltpe'),
+ (NULL,'STUNB','UNVJV','u'),
+ (5,'oka','qyihvamgsl','AXSMD'),
+ (NULL,'tqwmqyihva','h','yntqwmqyi'),
+ (3,'EGMJN','e','e');
+CREATE TABLE t2 (
+col_varchar varchar(10) DEFAULT NULL,
+col_int INT DEFAULT NULL
+);
+INSERT INTO t2 VALUES ('d',9);
+set optimizer_switch='derived_merge=off,derived_with_keys=on';
+SET @save_heap_size= @@max_heap_table_size;
+SET @@max_heap_table_size= 16384;
+SELECT t2.col_int
+FROM t2
+RIGHT JOIN ( SELECT * FROM t1 ) AS dt
+ON t2.col_varchar = dt.col_varchar
+WHERE t2.col_int IS NOT NULL ;
+col_int
+9
+# Shouldn't use auto_key0 for derived table
+EXPLAIN
+SELECT t2.col_int
+FROM t2
+RIGHT JOIN ( SELECT * FROM t1 ) AS dt
+ON t2.col_varchar = dt.col_varchar
+WHERE t2.col_int IS NOT NULL ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 Using where
+2 DERIVED t1 ALL NULL NULL NULL NULL 11
+SET @@max_heap_table_size= @save_heap_size;
+SET optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
+#
# end of 5.3 tests
#
set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index b3ca6dc0df2..ab7d5a2cdb0 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5656,4 +5656,153 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
drop table t0,t1,t2;
+#
+# mdev-6292: huge performance degradation for a sequence
+# of LEFT JOIN operations when using join buffer
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL AUTO_INCREMENT,
+col1 varchar(255) NOT NULL DEFAULT '',
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+CREATE TABLE t2 (
+id int(11) NOT NULL AUTO_INCREMENT,
+parent_id smallint(3) NOT NULL DEFAULT '0',
+col2 varchar(25) NOT NULL DEFAULT '',
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+set join_buffer_size=8192;
+set join_cache_level=0;
+set @init_time:=now();
+SELECT t.*
+FROM
+t1 t
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+col1;
+id col1
+select timestampdiff(second, @init_time, now()) <= 1;
+timestampdiff(second, @init_time, now()) <= 1
+1
+set join_cache_level=2;
+set @init_time:=now();
+SELECT t.*
+FROM
+t1 t
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+col1;
+id col1
+select timestampdiff(second, @init_time, now()) <= 1;
+timestampdiff(second, @init_time, now()) <= 1
+1
+EXPLAIN
+SELECT t.*
+FROM
+t1 t
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+col1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t ALL NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 SIMPLE c1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE c2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c6 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c7 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c8 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c9 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c10 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c11 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c12 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c13 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c14 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c15 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c16 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c17 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c18 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c19 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c20 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c21 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c22 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c23 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c24 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE c25 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+set join_buffer_size=default;
+set join_cache_level = default;
+DROP TABLE t1,t2;
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index 6b5a50ba978..3b47645ca79 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -705,18 +705,18 @@ t0.b=t1.b AND
(t8.b=t9.b OR t8.c IS NULL) AND
(t9.a=1);
a b a b a b a b a b a b a b a b a b a b
-1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
-1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 1
1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 4 2 1 2 3 2 2 2 6 2 2 2 0 2 1 2
+1 2 3 2 4 2 1 2 4 2 2 2 6 2 2 2 0 2 1 2
1 2 3 2 4 2 1 2 3 2 3 1 6 2 1 1 NULL NULL 1 2
1 2 3 2 4 2 1 2 4 2 3 1 6 2 1 1 NULL NULL 1 2
1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 1
1 2 3 2 4 2 1 2 3 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
1 2 3 2 4 2 1 2 4 2 3 3 NULL NULL NULL NULL NULL NULL 1 2
-1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 1
+1 2 3 2 5 3 NULL NULL NULL NULL 2 2 6 2 2 2 0 2 1 2
1 2 3 2 5 3 NULL NULL NULL NULL 3 1 6 2 1 1 NULL NULL 1 2
1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 1
1 2 3 2 5 3 NULL NULL NULL NULL 3 3 NULL NULL NULL NULL NULL NULL 1 2
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 88f2fd7c630..beea0daa1fa 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -696,9 +696,9 @@ insert into t2 values (1,3), (2,3);
insert into t3 values (2,4), (3,4);
select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
a1 a2 b1 b2 c1 c2
+3 2 NULL NULL 3 4
1 2 1 3 NULL NULL
2 2 2 3 NULL NULL
-3 2 NULL NULL 3 4
explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index de08b1c4d0d..67899837bb2 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1731,6 +1731,99 @@ drop table t1,t2;
set optimizer_switch=@save_optimizer_switch5740;
--echo #
+--echo # Bug mdev-5721: possible long key access to a materialized derived table
+--echo # (see also the test case for Bug#13261277 that is actually the same bug)
+--echo #
+
+CREATE TABLE t1 (
+ id varchar(255) NOT NULL DEFAULT '',
+ familyid int(11) DEFAULT NULL,
+ withdrawndate date DEFAULT NULL,
+ KEY index_td_familyid_id (familyid,id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+CREATE TABLE t2 (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ activefromts datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ shortdescription text,
+ useraccessfamily varchar(512) DEFAULT NULL,
+ serialized longtext,
+ PRIMARY KEY (id)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+insert into t1 values ('picture/89/1369722032695.pmd',89,NULL);
+insert into t1 values ('picture/90/1369832057370.pmd',90,NULL);
+insert into t2 values (38,'2013-03-04 07:49:22','desc','CODE','string');
+
+EXPLAIN
+SELECT * FROM t2 x,
+(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*)
+ FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y
+WHERE x.useraccessfamily = y.useraccessfamily;
+
+SELECT * FROM t2 x,
+(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*)
+ FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y
+WHERE x.useraccessfamily = y.useraccessfamily;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#13261277: Unchecked key length caused missing records.
+--echo #
+
+CREATE TABLE t1 (
+ col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+ stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+ stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+ stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
+);
+
+INSERT INTO t1 VALUES
+ ('d','d','l','ther'),
+ (NULL,'s','NJBIQ','trzetuchv'),
+ (-715390976,'coul','MYWFB','cfhtrzetu'),
+ (1696792576,'f','i\'s','c'),
+ (1,'i','ltpemcfhtr','gsltpemcf'),
+ (-663027712,'mgsltpemcf','sa','amgsltpem'),
+ (-1686700032,'JPRVK','i','vamgsltpe'),
+ (NULL,'STUNB','UNVJV','u'),
+ (5,'oka','qyihvamgsl','AXSMD'),
+ (NULL,'tqwmqyihva','h','yntqwmqyi'),
+ (3,'EGMJN','e','e');
+
+CREATE TABLE t2 (
+ col_varchar varchar(10) DEFAULT NULL,
+ col_int INT DEFAULT NULL
+);
+
+INSERT INTO t2 VALUES ('d',9);
+
+set optimizer_switch='derived_merge=off,derived_with_keys=on';
+
+SET @save_heap_size= @@max_heap_table_size;
+SET @@max_heap_table_size= 16384;
+
+SELECT t2.col_int
+FROM t2
+ RIGHT JOIN ( SELECT * FROM t1 ) AS dt
+ ON t2.col_varchar = dt.col_varchar
+WHERE t2.col_int IS NOT NULL ;
+
+--echo # Shouldn't use auto_key0 for derived table
+EXPLAIN
+SELECT t2.col_int
+FROM t2
+ RIGHT JOIN ( SELECT * FROM t1 ) AS dt
+ ON t2.col_varchar = dt.col_varchar
+WHERE t2.col_int IS NOT NULL ;
+
+SET @@max_heap_table_size= @save_heap_size;
+SET optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
+--echo #
--echo # end of 5.3 tests
--echo #
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 585ef677492..1d22de86799 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3656,5 +3656,134 @@ explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
drop table t0,t1,t2;
+--echo #
+--echo # mdev-6292: huge performance degradation for a sequence
+--echo # of LEFT JOIN operations when using join buffer
+--echo #
+
+--source include/have_innodb.inc
+
+CREATE TABLE t1 (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ col1 varchar(255) NOT NULL DEFAULT '',
+ PRIMARY KEY (id)
+) ENGINE=INNODB;
+
+CREATE TABLE t2 (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ parent_id smallint(3) NOT NULL DEFAULT '0',
+ col2 varchar(25) NOT NULL DEFAULT '',
+ PRIMARY KEY (id)
+) ENGINE=INNODB;
+
+set join_buffer_size=8192;
+
+set join_cache_level=0;
+
+set @init_time:=now();
+SELECT t.*
+FROM
+ t1 t
+ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+ col1;
+select timestampdiff(second, @init_time, now()) <= 1;
+
+set join_cache_level=2;
+
+set @init_time:=now();
+SELECT t.*
+FROM
+ t1 t
+ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+ col1;
+select timestampdiff(second, @init_time, now()) <= 1;
+
+EXPLAIN
+SELECT t.*
+FROM
+ t1 t
+ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+ LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+ LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+ LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+ LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+ LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+ LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+ LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+ LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+ LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+ LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+ LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+ LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+ LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+ LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+ LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+ LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+ LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+ LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+ LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+ LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+ LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+ LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+ LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+ LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY
+ col1;
+
+set join_buffer_size=default;
+set join_cache_level = default;
+
+DROP TABLE t1,t2;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index e97f0e185c6..9411b3a92c8 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2088,7 +2088,7 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
goto finish;
if (outer_join_first_inner)
{
- if (next_cache)
+ if (next_cache && join_tab != join_tab->last_inner)
{
/*
Ensure that all matches for outer records from join buffer are to be
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 084cbb4645d..1b57cb24308 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9202,6 +9202,25 @@ uint get_next_field_for_derived_key(uchar *arg)
}
+static
+uint get_next_field_for_derived_key_simple(uchar *arg)
+{
+ KEYUSE *keyuse= *(KEYUSE **) arg;
+ if (!keyuse)
+ return (uint) (-1);
+ TABLE *table= keyuse->table;
+ uint key= keyuse->key;
+ uint fldno= keyuse->keypart;
+ for ( ;
+ keyuse->table == table && keyuse->key == key && keyuse->keypart == fldno;
+ keyuse++)
+ ;
+ if (keyuse->key != key)
+ keyuse= 0;
+ *((KEYUSE **) arg)= keyuse;
+ return fldno;
+}
+
static
bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
{
@@ -9232,12 +9251,28 @@ bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
}
else
{
- if (table->add_tmp_key(table->s->keys, parts,
- get_next_field_for_derived_key,
- (uchar *) &first_keyuse,
- FALSE))
- return TRUE;
- table->reginfo.join_tab->keys.set_bit(table->s->keys);
+ KEYUSE *save_first_keyuse= first_keyuse;
+ if (table->check_tmp_key(table->s->keys, parts,
+ get_next_field_for_derived_key_simple,
+ (uchar *) &first_keyuse))
+
+ {
+ first_keyuse= save_first_keyuse;
+ if (table->add_tmp_key(table->s->keys, parts,
+ get_next_field_for_derived_key,
+ (uchar *) &first_keyuse,
+ FALSE))
+ return TRUE;
+ table->reginfo.join_tab->keys.set_bit(table->s->keys);
+ }
+ else
+ {
+ /* Mark keyuses for this key to be excluded */
+ for (KEYUSE *curr=save_first_keyuse; curr < first_keyuse; curr++)
+ {
+ curr->key= MAX_KEY;
+ }
+ }
first_keyuse= keyuse;
key_count++;
parts= 0;
diff --git a/sql/table.cc b/sql/table.cc
index 501e5e21982..c23f0c2bcf1 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -5996,6 +5996,52 @@ void TABLE::create_key_part_by_field(KEY *keyinfo,
/**
@brief
+ Check validity of a possible key for the derived table
+
+ @param key the number of the key
+ @param key_parts number of components of the key
+ @param next_field_no the call-back function that returns the number of
+ the field used as the next component of the key
+ @param arg the argument for the above function
+
+ @details
+ The function checks whether a possible key satisfies the constraints
+ imposed on the keys of any temporary table.
+
+ @return TRUE if the key is valid
+ @return FALSE otherwise
+*/
+
+bool TABLE::check_tmp_key(uint key, uint key_parts,
+ uint (*next_field_no) (uchar *), uchar *arg)
+{
+ Field **reg_field;
+ uint i;
+ uint key_len= 0;
+
+ for (i= 0; i < key_parts; i++)
+ {
+ uint fld_idx= next_field_no(arg);
+ reg_field= field + fld_idx;
+ uint fld_store_len= (uint16) (*reg_field)->key_length();
+ if ((*reg_field)->real_maybe_null())
+ fld_store_len+= HA_KEY_NULL_LENGTH;
+ if ((*reg_field)->type() == MYSQL_TYPE_BLOB ||
+ (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR ||
+ (*reg_field)->type() == MYSQL_TYPE_GEOMETRY)
+ fld_store_len+= HA_KEY_BLOB_LENGTH;
+ key_len+= fld_store_len;
+ }
+ /*
+ We use MI_MAX_KEY_LENGTH (myisam's default) below because it is
+ smaller than MAX_KEY_LENGTH (heap's default) and it's unknown whether
+ myisam or heap will be used for the temporary table.
+ */
+ return key_len <= MI_MAX_KEY_LENGTH;
+}
+
+/**
+ @brief
Add one key to a temporary table
@param key the number of the key
@@ -6025,6 +6071,7 @@ bool TABLE::add_tmp_key(uint key, uint key_parts,
KEY* keyinfo;
Field **reg_field;
uint i;
+
bool key_start= TRUE;
KEY_PART_INFO* key_part_info=
(KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts);
diff --git a/sql/table.h b/sql/table.h
index b771b41a3b7..ac15e389f75 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1264,6 +1264,8 @@ public:
{ return !db_stat || m_needs_reopen; }
bool alloc_keys(uint key_count);
+ bool check_tmp_key(uint key, uint key_parts,
+ uint (*next_field_no) (uchar *), uchar *arg);
bool add_tmp_key(uint key, uint key_parts,
uint (*next_field_no) (uchar *), uchar *arg,
bool unique);
View Lorry Controller Status