summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj2.result27
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result27
-rw-r--r--mysql-test/r/subselect_sj2_mat.result28
-rw-r--r--mysql-test/t/subselect_sj2.test26
-rw-r--r--sql/sql_join_cache.cc19
5 files changed, 126 insertions, 1 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index c3d9ef0cf92..db99d77f50c 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -848,5 +848,32 @@ NULL x x
DROP TABLE t1, t2, t3, t4;
set join_cache_level= @tmp869001_jcl;
set optimizer_switch= @tmp869001_os;
+#
+# Bug #881318: join cache + duplicate elimination + left join
+# with empty materialized derived inner table
+#
+CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB;
+CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('a');
+CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('c','c');
+CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES ('c'), ('b');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+3 DERIVED t1 ALL NULL NULL NULL NULL 1
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+a b b a
+c c NULL NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 5b22a2fa3f0..05da22fc10d 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -859,6 +859,33 @@ NULL x x
DROP TABLE t1, t2, t3, t4;
set join_cache_level= @tmp869001_jcl;
set optimizer_switch= @tmp869001_os;
+#
+# Bug #881318: join cache + duplicate elimination + left join
+# with empty materialized derived inner table
+#
+CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB;
+CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('a');
+CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('c','c');
+CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES ('c'), ('b');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t3.a 1 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join)
+1 PRIMARY t4 hash_ALL NULL #hash#$hj 5 test.t3.b 2 Using where; End temporary; Using join buffer (incremental, BNLH join)
+3 DERIVED t1 ALL NULL NULL NULL NULL 1
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+a b b a
+c c NULL NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
set join_cache_level=default;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index f358bcfc7d4..75d61d42c31 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -860,6 +860,34 @@ NULL x x
DROP TABLE t1, t2, t3, t4;
set join_cache_level= @tmp869001_jcl;
set optimizer_switch= @tmp869001_os;
+#
+# Bug #881318: join cache + duplicate elimination + left join
+# with empty materialized derived inner table
+#
+CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB;
+CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('a');
+CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('c','c');
+CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES ('c'), ('b');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1
+2 SUBQUERY t4 ALL NULL NULL NULL NULL 2
+3 DERIVED t1 ALL NULL NULL NULL NULL 1
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+WHERE t3.b IN (SELECT b FROM t4);
+a b b a
+c c NULL NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 62fb4e27682..b2721574deb 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1039,6 +1039,32 @@ DROP TABLE t1, t2, t3, t4;
set join_cache_level= @tmp869001_jcl;
set optimizer_switch= @tmp869001_os;
+--echo #
+--echo # Bug #881318: join cache + duplicate elimination + left join
+--echo # with empty materialized derived inner table
+--echo #
+
+CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB;
+
+CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('a');
+
+CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('c','c');
+
+CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES ('c'), ('b');
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+ WHERE t3.b IN (SELECT b FROM t4);
+SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
+ WHERE t3.b IN (SELECT b FROM t4);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;
--echo # This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index c8933fe69ee..f53bf738b86 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -1413,12 +1413,22 @@ uint JOIN_CACHE::write_record_data(uchar * link, bool *is_full)
TABLE *table= (TABLE *) copy->str;
copy->str= table->file->ref;
copy->length= table->file->ref_length;
+ if (!copy->str)
+ {
+ /*
+ If table is an empty inner table of an outer join and it is
+ a materialized derived table then table->file->ref == NULL.
+ */
+ cp+= copy->length;
+ break;
+ }
}
/* fall through */
default:
/* Copy the entire image of the field from the record buffer */
DBUG_ASSERT(cp + copy->length <= buff + buff_size);
- memcpy(cp, copy->str, copy->length);
+ if (copy->str)
+ memcpy(cp, copy->str, copy->length);
cp+= copy->length;
}
}
@@ -1811,6 +1821,13 @@ uint JOIN_CACHE::read_record_field(CACHE_FIELD *copy, bool blob_in_rec_buff)
memset(copy->str+len, ' ', copy->length-len);
len+= 2;
break;
+ case CACHE_ROWID:
+ if (!copy->str)
+ {
+ len= copy->length;
+ break;
+ }
+ /* fall through */
default:
/* Copy the entire image of the field from the record buffer */
len= copy->length;
View Lorry Controller Status