#At file:///home/igor/dev-bzr/mysql-6.0-bug35835/
2648 Igor Babaev 2008-10-08
Fixed several bugs reported in the bug entry of bug #35835.
All these bugs concerned null complemented rows in the
result sets of queries with nested outer joins and are
closely interconnected.
Added test cases for bug #35835.
Fixed a wrong result set for one of the previous tests.
modified:
mysql-test/r/join_nested_jcl6.result
mysql-test/t/join_nested_jcl6.test
sql/sql_select.cc
per-file messages:
mysql-test/r/join_nested_jcl6.result
Added test cases for bug #35835.
Fixed a wrong result set for one of the previous tests.
mysql-test/t/join_nested_jcl6.test
Added test cases for bug #35835.
sql/sql_select.cc
Fixed several bugs reported in the bug entry of bug #35835.
All these bugs concerned null complemented rows in the
result sets of queries with nested outer joins and are
closely interconnected.
The bugs caused:
- generation of superfluous null complemented rows
- missing some null complemented rows
- not null values for some columns of null complements.
The erroneous code was fixed in the functions JOIN_CACHE::join_records,
JOIN_CACHE::check_match and JOIN_CACHE::join_null_complements.
The code of the function check_join_cache_usage was modified to
prohibit usage of a join buffer to join an inner table of an outer
join or a semi-join unless each of the inner tables is joined
using a join buffer. In other words either any of the inner tables of
an outer join of a semi-join must be joined using a join buffer or
none of them is joined with a join buffer.
Also now a join buffer can be used to join tables with the JT_CONST
and JT_SYSTEM access type.
=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result 2008-04-06 07:20:43 +0000
+++ b/mysql-test/r/join_nested_jcl6.result 2008-10-09 00:31:34 +0000
@@ -1120,8 +1120,6 @@ a b a b a b a b a b a b a b a b a b a b
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
-1 2 2 2 NULL NULL 1 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
-1 2 2 2 NULL NULL 1 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1
1 2 2 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 2
SELECT t2.a,t2.b
@@ -1750,6 +1748,108 @@ COUNT(*)
6
DROP TABLE t1,t2,t3,t4,t5;
End of 5.0 tests
+CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b AND
+(t8.a > 0 OR t8.c IS NULL);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t5 ALL NULL NULL NULL NULL 3
+1 SIMPLE t7 ref b_i b_i 5 test.t5.b 2 Using join buffer
+1 SIMPLE t6 ALL b_i NULL NULL NULL 3 Using where; Using join buffer
+1 SIMPLE t8 ref b_i b_i 5 test.t7.b 2 Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5
+LEFT JOIN
+(
+(t6, t7)
+LEFT JOIN
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b AND
+(t8.a > 0 OR t8.c IS NULL);
+a b a b a b a b
+2 2 1 2 2 2 1 2
+2 2 3 2 2 2 1 2
+1 1 1 2 1 1 NULL NULL
+1 1 3 2 1 1 NULL NULL
+3 3 NULL NULL NULL NULL NULL NULL
+DELETE FROM t5;
+DELETE FROM t6;
+DELETE FROM t7;
+DELETE FROM t8;
+INSERT INTO t5 VALUES (1,3,0), (3,2,0);
+INSERT INTO t6 VALUES (3,3,0);
+INSERT INTO t7 VALUES (1,2,0);
+INSERT INTO t8 VALUES (1,1,0);
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t6 LEFT JOIN t7 ON t7.a=1, t8)
+ON (t5.b=t8.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t5 ALL NULL NULL NULL NULL 2
+1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer
+1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer
+1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t6 LEFT JOIN t7 ON t7.a=1, t8)
+ON (t5.b=t8.b);
+a b a b a b a b
+1 3 NULL NULL NULL NULL NULL NULL
+3 2 NULL NULL NULL NULL NULL NULL
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t6 LEFT JOIN t7 ON t7.b=2, t8)
+ON (t5.b=t8.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t5 ALL NULL NULL NULL NULL 2
+1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer
+1 SIMPLE t7 ref b_i b_i 5 const 0 Using join buffer
+1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t6 LEFT JOIN t7 ON t7.b=2, t8)
+ON (t5.b=t8.b);
+a b a b a b a b
+1 3 NULL NULL NULL NULL NULL NULL
+3 2 NULL NULL NULL NULL NULL NULL
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t8, t6 LEFT JOIN t7 ON t7.a=1)
+ON (t5.b=t8.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t5 ALL NULL NULL NULL NULL 2
+1 SIMPLE t8 ALL b_i NULL NULL NULL 1 Using where; Using join buffer
+1 SIMPLE t6 ALL NULL NULL NULL NULL 1 Using join buffer
+1 SIMPLE t7 const PRIMARY PRIMARY 4 const 1 Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN
+(t8, t6 LEFT JOIN t7 ON t7.a=1)
+ON (t5.b=t8.b);
+a b a b a b a b
+1 3 NULL NULL NULL NULL NULL NULL
+3 2 NULL NULL NULL NULL NULL NULL
+DROP TABLE t5,t6,t7,t8;
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/join_nested_jcl6.test'
--- a/mysql-test/t/join_nested_jcl6.test 2008-04-06 07:20:43 +0000
+++ b/mysql-test/t/join_nested_jcl6.test 2008-10-09 00:31:34 +0000
@@ -7,5 +7,89 @@ show variables like 'join_cache_level';
--source t/join_nested.test
+#
+# BUG#35835: queries with nested outer joins with BKA enabled
+#
+
+CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+
+INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5
+ LEFT JOIN
+ (
+ (t6, t7)
+ LEFT JOIN
+ t8
+ ON t7.b=t8.b AND t6.b < 10
+ )
+ ON t6.b >= 2 AND t5.b=t7.b AND
+ (t8.a > 0 OR t8.c IS NULL);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5
+ LEFT JOIN
+ (
+ (t6, t7)
+ LEFT JOIN
+ t8
+ ON t7.b=t8.b AND t6.b < 10
+ )
+ ON t6.b >= 2 AND t5.b=t7.b AND
+ (t8.a > 0 OR t8.c IS NULL);
+
+DELETE FROM t5;
+DELETE FROM t6;
+DELETE FROM t7;
+DELETE FROM t8;
+
+INSERT INTO t5 VALUES (1,3,0), (3,2,0);
+INSERT INTO t6 VALUES (3,3,0);
+INSERT INTO t7 VALUES (1,2,0);
+INSERT INTO t8 VALUES (1,1,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t6 LEFT JOIN t7 ON t7.a=1, t8)
+ ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t6 LEFT JOIN t7 ON t7.a=1, t8)
+ ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t6 LEFT JOIN t7 ON t7.b=2, t8)
+ ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t6 LEFT JOIN t7 ON t7.b=2, t8)
+ ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t8, t6 LEFT JOIN t7 ON t7.a=1)
+ ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+ FROM t5 LEFT JOIN
+ (t8, t6 LEFT JOIN t7 ON t7.a=1)
+ ON (t5.b=t8.b);
+
+DROP TABLE t5,t6,t7,t8;
+
set join_cache_level=default;
show variables like 'join_cache_level';
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-09-04 18:41:27 +0000
+++ b/sql/sql_select.cc 2008-10-09 00:31:34 +0000
@@ -8336,16 +8336,12 @@ void revise_cache_usage(JOIN_TAB *join_t
if (join_tab->first_inner)
{
JOIN_TAB *end_tab= join_tab;
- JOIN_TAB *last_inner= join_tab->first_inner->last_inner;
for (first_inner= join_tab->first_inner;
- first_inner && first_inner->last_inner == last_inner;
+ first_inner;
first_inner= first_inner->first_upper)
{
for (tab= end_tab-1; tab >= first_inner; tab--)
- {
- if (tab->first_inner->last_inner == last_inner)
- set_join_cache_denial(tab);
- }
+ set_join_cache_denial(tab);
end_tab= first_inner;
}
}
@@ -8368,12 +8364,12 @@ bool check_join_cache_usage(JOIN_TAB *ta
{
uint flags;
COST_VECT cost;
+ ha_rows rows;
uint bufsz= 4096;
JOIN_CACHE *prev_cache=0;
uint cache_level= join->thd->variables.join_cache_level;
bool force_unlinked_cache= test(cache_level & 1);
uint i= tab-join->join_tab;
- ha_rows rows;
if (cache_level == 0)
return FALSE;
@@ -8393,10 +8389,32 @@ bool check_join_cache_usage(JOIN_TAB *ta
goto no_join_cache;
for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
first_inner= first_inner->first_upper)
- {
+ {
if (first_inner != tab && !first_inner->use_join_cache)
goto no_join_cache;
}
+ if (tab->first_sj_inner_tab && !tab->first_sj_inner_tab->use_join_cache)
+ goto no_join_cache;
+ if (!tab[-1].use_join_cache)
+ {
+ /*
+ Check whether table tab and the previous one belong to the same nest of
+ inner tables and if so do not use join buffer when joining table tab.
+ */
+ if (tab->first_inner)
+ {
+ for (JOIN_TAB *first_inner= tab[-1].first_inner;
+ first_inner;
+ first_inner= first_inner->first_upper)
+ {
+ if (first_inner == tab->first_inner)
+ goto no_join_cache;
+ }
+ }
+ else if (tab->first_sj_inner_tab &&
+ tab->first_sj_inner_tab == tab[-1].first_sj_inner_tab)
+ goto no_join_cache;
+ }
if (!force_unlinked_cache)
prev_cache= tab[-1].cache;
@@ -8411,6 +8429,8 @@ bool check_join_cache_usage(JOIN_TAB *ta
!tab->cache->init())
return TRUE;
goto no_join_cache;
+ case JT_SYSTEM:
+ case JT_CONST:
case JT_REF:
case JT_EQ_REF:
if (cache_level <= 4)
@@ -8492,21 +8512,28 @@ make_join_readinfo(JOIN *join, ulonglong
return TRUE;
}
switch (tab->type) {
- case JT_SYSTEM: // Only happens with left join
+ case JT_SYSTEM:
+ case JT_CONST:
+ /* Only happens with outer joins */
table->status=STATUS_NO_RECORD;
- tab->read_first_record= join_read_system;
- tab->read_record.read_record= join_no_more_records;
- break;
- case JT_CONST: // Only happens with left join
- table->status=STATUS_NO_RECORD;
- tab->read_first_record= join_read_const;
+ tab->read_first_record= tab->type == JT_SYSTEM ?
+ join_read_system :join_read_const;
tab->read_record.read_record= join_no_more_records;
+ using_join_cache= FALSE;
+ if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+ {
+ using_join_cache= TRUE;
+ tab[-1].next_select=sub_select_cache;
+ }
+ tab->use_join_cache= using_join_cache;
if (table->covering_keys.is_set(tab->ref.key) &&
- !table->no_keyread)
+ !table->no_keyread)
{
- table->key_read=1;
- table->file->extra(HA_EXTRA_KEYREAD);
+ table->key_read=1;
+ table->file->extra(HA_EXTRA_KEYREAD);
}
+ else
+ push_index_cond(tab, tab->ref.key, !using_join_cache);
break;
case JT_EQ_REF:
table->status=STATUS_NO_RECORD;
@@ -18398,10 +18425,10 @@ enum_nested_loop_state JOIN_CACHE::join_
enum_nested_loop_state rc= NESTED_LOOP_OK;
bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
- if (outer_join_first_inner)
- join_tab->not_null_compl= TRUE;
+ if (outer_join_first_inner && !join_tab->first_unmatched)
+ join_tab->not_null_compl= TRUE;
- if (!join_tab->first_inner || join_tab->first_inner->not_null_compl)
+ if (!join_tab->first_unmatched)
{
/* Find all records from join_tab that match records from join buffer */
rc= join_matching_records(skip_last);
@@ -18428,7 +18455,7 @@ enum_nested_loop_state JOIN_CACHE::join_
tab->first_unmatched= join_tab->first_inner;
}
}
- if (join_tab->first_unmatched && !join_tab->first_unmatched->not_null_compl)
+ if (join_tab->first_unmatched)
{
/*
Generate all null complementing extensions for the records from
@@ -18534,7 +18561,7 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
{
/* A dynamic range access was used last. Clean up after it */
delete join_tab->select->quick;
- join_tab->select->quick=0;
+ join_tab->select->quick= 0;
}
for (tab= join->join_tab; tab != join_tab ; tab++)
@@ -18544,7 +18571,7 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
}
/* Start retrieving all records of the joined table */
- if ((error=join_init_read_record(join_tab)))
+ if ((error= join_init_read_record(join_tab)))
{
rc= error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;
goto finish;
@@ -18745,7 +18772,7 @@ inline bool JOIN_CACHE::check_match(ucha
do
{
if (!set_match_flag_if_none(first_inner, rec_ptr))
- return TRUE;
+ continue;
if (first_inner->check_only_first_match() &&
!first_inner->first_upper)
return TRUE;
@@ -18766,7 +18793,8 @@ inline bool JOIN_CACHE::check_match(ucha
return FALSE;
}
}
- while ((first_inner= first_inner->first_upper));
+ while ((first_inner= first_inner->first_upper) &&
+ first_inner->last_inner == join_tab);
return TRUE;
}
@@ -18832,7 +18860,7 @@ enum_nested_loop_state JOIN_CACHE::join_
if (is_last_inner)
{
JOIN_TAB *first_upper= join_tab->first_unmatched->first_upper;
- while (first_upper)
+ while (first_upper && first_upper->last_inner == join_tab)
{
if (!set_match_flag_if_none(first_upper, get_curr_rec()))
break;
| Thread |
|---|
| • bzr commit into mysql-6.0-bka-preview branch (igor:2648) Bug#35835 | Igor Babaev | 9 Oct |
