On Fri, Jun 21, 2019 at 3:50 AM IgorBabaev <igor@mariadb.com> wrote:

revision-id: 28a2ba77430690a4889eba26203c4c4bc0e4a1bf (mariadb-5.5.64-21-g28a2ba7)
parent(s): 167da05f554dbe27d16373f6f0b02408ee76dc94
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-20 18:50:20 -0700
message:

MDEV-19778 Wrong Result on Left Outer Join with Subquery right on true
and WHERE filter afterwards

This patch complements the patch fixing the bug MDEV-6892. The latter
properly handled queries that used mergeable views returning constant
columns as inner tables of outer joins and whose where clause contained
predicates referring to these columns if the predicates of happened not
to be equality predicates. Otherwise the server still could return wrong
result sets for such queries. Besides the fix for MDEV-6892 prevented
some possible conversions of outer joins to inner joins for such queries.

This patch corrected the function check_simple_equality() to handle
properly conjunctive equalities of the where clause that refer to the
constant columns of mergeable views used as inner tables of an outer join.
The patch also changed the code of Item_direct_view_ref::not_null_tables().
This change allowed to take into account predicates containing references
to constant columns of mergeable views when converting outer joins into
inner joins.

---
mysql-test/r/derived.result | 9 ++++++
mysql-test/r/derived_view.result | 57 +++++++++++++++++++++++++++++++++++++
mysql-test/r/func_group.result | 2 ++
mysql-test/r/subselect_cache.result | 2 ++
mysql-test/t/derived.test | 4 +++
mysql-test/t/derived_view.test | 34 ++++++++++++++++++++++
sql/item.cc | 13 +++++----
sql/item.h | 1 +
sql/item_func.cc | 1 +
sql/sql_select.cc | 9 +++++-
10 files changed, 126 insertions(+), 6 deletions(-)

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 2c0d009..9714963 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -578,6 +578,15 @@ select x.id, message from (select id from t1) x left join
(select id, 1 as message from t2) y on x.id=y.id
where coalesce(message,0) <> 0;
id message
+explain extended
+select x.id, message from (select id from t1) x left join
+(select id, 1 as message from t2) y on x.id=y.id
+where message <> 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,1 AS `message` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`id` = `test`.`t1`.`id`)
drop table t1,t2;
#
# MDEV-7827: Assertion `!table || (!table->read_set ||
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index f8cf919..412786c 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -3041,3 +3041,60 @@ id select_type table type possible_keys key key_len ref rows Extra
7 DERIVED p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
7 DERIVED p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join)
DROP TABLE t1, t2;
+#
+# MDEV-19778: equality condition for mergeable view returning constants
+# in its columns and used as inner table of outer join
+#
+create table t1 (pk int, a int);
+insert into t1 values (1,7), (2,3), (3,2), (4,3);
+create table t2 (b int);
+insert into t2 values (5), (1), (NULL), (3);
+create table t3 (c int);
+insert into t3 values (1), (8);
+create view v1 as
+select 3 as d, t2.b from t2;
+select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;
+pk a d b
+2 3 3 5
+2 3 3 1
+2 3 3 NULL
+2 3 3 3
+explain extended select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = 3) and (`test`.`t1`.`pk` <= 2))
+select * from t1 left join (select 3 as d, t2.b from t2) dt on t1.pk <= 2
+where t1.a=dt.d;
+pk a d b
+2 3 3 5
+2 3 3 1
+2 3 3 NULL
+2 3 3 3
+explain extended select * from t1 left join (select 3 as d, t2.b from t2) dt on t1.pk <= 2
+where t1.a=dt.d;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = 3) and (`test`.`t1`.`pk` <= 2))
+select * from t1 left join (v1,t3) on t1.pk <= 2 where t1.a=v1.d;
+pk a d b c
+2 3 3 5 1
+2 3 3 5 8
+2 3 3 1 1
+2 3 3 1 8
+2 3 3 NULL 1
+2 3 3 NULL 8
+2 3 3 3 1
+2 3 3 3 8
+explain extended select * from t1 left join (v1,t3) on t1.pk <= 2 where t1.a=v1.d;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (incremental, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t1`.`a` = 3) and (`test`.`t1`.`pk` <= 2))
+drop view v1;
+drop table t1,t2,t3;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 38fae2f0..c1de5d9 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1981,6 +1981,7 @@ NULL
Warnings:
Warning 1292 Truncated incorrect INTEGER value: 'j'
Warning 1292 Truncated incorrect INTEGER value: 'j'
+Warning 1292 Truncated incorrect INTEGER value: 'j'

EXPLAIN
SELECT MIN(t2.pk)
@@ -1995,6 +1996,7 @@ id select_type table type possible_keys key key_len ref rows Extra
Warnings:
Warning 1292 Truncated incorrect INTEGER value: 'j'
Warning 1292 Truncated incorrect INTEGER value: 'j'
+Warning 1292 Truncated incorrect INTEGER value: 'j'

#
# 2) Test that subquery materialization is setup for query with
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index 95f9359..0bdef7a 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -3130,6 +3130,7 @@ WHERE table1 .`col_varchar_key` ) field10
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'f'
Warning 1292 Truncated incorrect DOUBLE value: 'f'
+Warning 1292 Truncated incorrect DOUBLE value: 'f'
SET @@optimizer_switch = 'subquery_cache=on';
/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (
SELECT SUBQUERY2_t1 .`col_int_key`
@@ -3146,6 +3147,7 @@ WHERE table1 .`col_varchar_key` ) field10
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'f'
Warning 1292 Truncated incorrect DOUBLE value: 'f'
+Warning 1292 Truncated incorrect DOUBLE value: 'f'
drop table t1,t2,t3,t4;
set @@optimizer_switch= default;
#launchpad BUG#611625
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 62b2c43..38ab28f 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -502,6 +502,10 @@ insert into t2 values(4),(5),(6);
select x.id, message from (select id from t1) x left join
(select id, 1 as message from t2) y on x.id=y.id
where coalesce(message,0) <> 0;
+explain extended
+select x.id, message from (select id from t1) x left join
+(select id, 1 as message from t2) y on x.id=y.id
+where message <> 0;
drop table t1,t2;

--echo #
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 61c4278..34aaa27 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -2194,3 +2194,37 @@ JOIN
) gp_20 ON gp_20.id=t2.id ;

DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-19778: equality condition for mergeable view returning constants
+--echo # in its columns and used as inner table of outer join
+--echo #
+
+create table t1 (pk int, a int);
+insert into t1 values (1,7), (2,3), (3,2), (4,3);
+create table t2 (b int);
+insert into t2 values (5), (1), (NULL), (3);
+create table t3 (c int);
+insert into t3 values (1), (8);
+
+create view v1 as
+select 3 as d, t2.b from t2;
+
+let $q=
+select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select * from t1 left join (select 3 as d, t2.b from t2) dt on t1.pk <= 2
+ where t1.a=dt.d;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select * from t1 left join (v1,t3) on t1.pk <= 2 where t1.a=v1.d;
+eval $q;
+eval explain extended $q;
+
+drop view v1;
+drop table t1,t2,t3;
diff --git a/sql/item.cc b/sql/item.cc
index 719bdcf..ffd899e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9814,11 +9814,14 @@ table_map Item_direct_view_ref::used_tables() const

table_map Item_direct_view_ref::not_null_tables() const
{
- return get_depended_from() ?
- 0 :
- ((view->is_merged_derived() || view->merged || !view->table) ?
- (*ref)->not_null_tables() :
- view->table->map);
+ if (get_depended_from())
+ return 0;
+ if (!( view->merged || !view->table))
+ return view->table->map;
+ TABLE *tab= get_null_ref_table();
+ if (tab == NO_NULL_TABLE || (*ref)->used_tables())
+ return (*ref)->not_null_tables();
+ return get_null_ref_table()->map;
}

/*
diff --git a/sql/item.h b/sql/item.h
index f6c3c20..6dc9997 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -3382,6 +3382,7 @@ class Item_direct_view_ref :public Item_direct_ref
void update_used_tables();
table_map not_null_tables() const;
bool const_item() const { return used_tables() == 0; }
+ TABLE *get_null_ref_table() const { return null_ref_table; }
bool walk(Item_processor processor, bool walk_subquery, uchar *arg)
{
return (*ref)->walk(processor, walk_subquery, arg) ||
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 22d7e8c..dad4b89 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -226,6 +226,7 @@ Item_func::fix_fields(THD *thd, Item **ref)
with_field= with_field || item->with_field;
used_tables_cache|= item->used_tables();
const_item_cache&= item->const_item();
+ not_null_tables_cache|= item->not_null_tables();
with_subselect|= item->has_subquery();
}
}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0d94ba2..3d78000 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1030,7 +1030,6 @@ JOIN::optimize()
DBUG_RETURN(1); /* purecov: inspected */
/* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
select_lex->update_used_tables();
-
}

eval_select_list_used_tables();
@@ -1092,6 +1091,8 @@ JOIN::optimize()

sel->where= conds;

+ select_lex->update_used_tables();
+
if (arena)
thd->restore_active_arena(arena, &backup);
}
@@ -11763,6 +11764,9 @@ static bool check_simple_equality(Item *left_item, Item *right_item,
{
if (((Item_ref*)left_item)->get_depended_from())
return FALSE;
+ if (((Item_direct_view_ref*)left_item)->get_null_ref_table() !=
+ NO_NULL_TABLE && !left_item->real_item()->used_tables())
+ return FALSE;
left_item= left_item->real_item();
}
if (right_item->type() == Item::REF_ITEM &&
@@ -11770,6 +11774,9 @@ static bool check_simple_equality(Item *left_item, Item *right_item,
{
if (((Item_ref*)right_item)->get_depended_from())
return FALSE;
+ if (((Item_direct_view_ref*)right_item)->get_null_ref_table() !=
+ NO_NULL_TABLE && !right_item->real_item()->used_tables())
+ return FALSE;
right_item= right_item->real_item();
}
if (left_item->type() == Item::FIELD_ITEM &&
_______________________________________________
commits mailing list
commits@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits