3844 Norvald H. Ryeng 2012-02-06
Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
WITHOUT UNION ALL
Problem: Some outer join queries using views as inner tables do not
evaluate conditions correctly.
Inner tables of outer joins are tagged by setting the outer_join flag
in the TABLE_LIST structure for the table. However, if the inner table
is a view, the tables of the view are not tagged unless they are inner
tables of a view defined as an outer join query. This means that
tables referenced by views should some times be treated as inner
tables of an outer join even if they are not tagged as such.
In JOIN::optimize(), calls to list_contains_unique_index(), are used
to check if GROUP BY or DISTINCT can be skipped. The function checks
that enough columns to make it a unique key are mentioned and that
none of the columns can return NULL. The check for NULL fails since it
only checks the outer_join flag, which may not be set because of view
usage. The result is the erroneous conclusion that GROUP BY or
DISTINCT can be skipped, which in the end causes the query to return
too many or too few rows.
Fix: In list_contains_unique_index(), check if the table in question
is in the inner part of an outer join (directly or nested) and
therefore may return NULL values.
This patch also adds checks for nested outer joins in
Item_field::is_outer_field() and internal_remove_eq_conds().
@ mysql-test/r/group_by.result
Fix erroneous result.
@ mysql-test/r/join_outer.result
Add test case for bug #13464334.
@ mysql-test/r/join_outer_bka.result
Add test case for bug #13464334.
@ mysql-test/r/join_outer_bka_nixbnl.result
Add test case for bug #13464334.
@ mysql-test/t/join_outer.test
Add test case for bug #13464334.
@ sql/item.h
Include nested outer joins in check for outer join.
@ sql/sql_optimizer.cc
Include nested outer joins in checks for outer join.
modified:
mysql-test/r/group_by.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_bka.result
mysql-test/r/join_outer_bka_nixbnl.result
mysql-test/t/join_outer.test
sql/item.h
sql/sql_optimizer.cc
3843 Vasil Dimov 2012-02-06 [merge]
Merge mysql-5.5 -> mysql-trunk
added:
mysql-test/suite/innodb/r/innodb_bug11754376.result
mysql-test/suite/innodb/t/innodb_bug11754376.test
modified:
storage/innobase/handler/ha_innodb.cc
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2012-01-30 13:57:08 +0000
+++ b/mysql-test/r/group_by.result 2012-02-06 12:47:30 +0000
@@ -2110,6 +2110,7 @@ SELECT v1.pk
FROM t1 LEFT JOIN v1 ON t1.i = v1.pk
GROUP BY v1.pk;
pk
+NULL
DROP VIEW v1;
DROP TABLE t1,t2;
# End of Bug#12798270
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/join_outer.result 2012-02-06 12:47:30 +0000
@@ -1996,3 +1996,19 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
drop table t1,t2,t3,t4,t5,t6;
+#
+# Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
+# WITHOUT UNION ALL
+#
+CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));
+CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)
+UNION (SELECT NULL LIMIT 0);
+p1
+NULL
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
=== modified file 'mysql-test/r/join_outer_bka.result'
--- a/mysql-test/r/join_outer_bka.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/join_outer_bka.result 2012-02-06 12:47:30 +0000
@@ -1997,4 +1997,20 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
drop table t1,t2,t3,t4,t5,t6;
+#
+# Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
+# WITHOUT UNION ALL
+#
+CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));
+CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)
+UNION (SELECT NULL LIMIT 0);
+p1
+NULL
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/r/join_outer_bka_nixbnl.result'
--- a/mysql-test/r/join_outer_bka_nixbnl.result 2012-01-03 11:04:14 +0000
+++ b/mysql-test/r/join_outer_bka_nixbnl.result 2012-02-06 12:47:30 +0000
@@ -1997,4 +1997,20 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where
drop table t1,t2,t3,t4,t5,t6;
+#
+# Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
+# WITHOUT UNION ALL
+#
+CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));
+CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)
+UNION (SELECT NULL LIMIT 0);
+p1
+NULL
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
set optimizer_switch=default;
=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test 2012-01-03 11:04:14 +0000
+++ b/mysql-test/t/join_outer.test 2012-02-06 12:47:30 +0000
@@ -1489,3 +1489,19 @@ eval SELECT $rest_of_query;
eval EXPLAIN SELECT $rest_of_query;
drop table t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo # Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
+--echo # WITHOUT UNION ALL
+--echo #
+
+CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));
+CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)
+UNION (SELECT NULL LIMIT 0);
+DROP VIEW v1, v2;
+DROP TABLE t1, t2;
=== modified file 'sql/item.h'
--- a/sql/item.h 2012-01-31 15:16:16 +0000
+++ b/sql/item.h 2012-02-06 12:47:30 +0000
@@ -2034,7 +2034,8 @@ public:
bool is_outer_field() const
{
DBUG_ASSERT(fixed);
- return field->table->pos_in_table_list->outer_join;
+ return field->table->pos_in_table_list->outer_join ||
+ field->table->pos_in_table_list->in_outer_join_nest();
}
Field::geometry_type get_geometry_type() const
{
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-01-27 14:22:35 +0000
+++ b/sql/sql_optimizer.cc 2012-02-06 12:47:30 +0000
@@ -55,7 +55,7 @@ static Item *optimize_cond(JOIN *join, I
List<TABLE_LIST> *join_list,
bool build_equalities,
Item::cond_result *cond_value);
-static bool list_contains_unique_index(TABLE *table,
+static bool list_contains_unique_index(JOIN_TAB *tab,
bool (*find_func) (Field *, void *), void *data);
static bool find_field_in_item_list (Field *field, void *data);
static bool find_field_in_order_list (Field *field, void *data);
@@ -522,7 +522,7 @@ JOIN::optimize()
QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX))
{
if (group_list && rollup.state == ROLLUP::STATE_NONE &&
- list_contains_unique_index(join_tab[const_tables].table,
+ list_contains_unique_index(&join_tab[const_tables],
find_field_in_order_list,
(void *) group_list))
{
@@ -560,7 +560,7 @@ JOIN::optimize()
group= 0;
}
if (select_distinct &&
- list_contains_unique_index(join_tab[const_tables].table,
+ list_contains_unique_index(&join_tab[const_tables],
find_field_in_item_list,
(void *) &fields_list))
{
@@ -8069,7 +8069,7 @@ internal_remove_eq_conds(THD *thd, Item
if (!eq_cond)
return cond;
- if (field->table->pos_in_table_list->outer_join)
+ if (args[0]->is_outer_field())
{
// outer join: transform "col IS NULL" to "col IS NULL or col=0"
Item *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond);
@@ -8223,7 +8223,7 @@ remove_eq_conds(THD *thd, Item *cond, It
can safely remove the GROUP BY/DISTINCT,
as no result set can be more distinct than an unique key.
- @param table The table to operate on.
+ @param tab The join table to operate on.
@param find_func function to iterate over the list and search
for a field
@@ -8231,13 +8231,19 @@ remove_eq_conds(THD *thd, Item *cond, It
1 found
@retval
0 not found.
+
+ @note
+ The function assumes that make_outerjoin_info() has been called in
+ order for the check for outer tables to work.
*/
static bool
-list_contains_unique_index(TABLE *table,
+list_contains_unique_index(JOIN_TAB *tab,
bool (*find_func) (Field *, void *), void *data)
{
- if (table->pos_in_table_list->outer_join)
+ TABLE *table= tab->table;
+
+ if (tab->is_inner_table_of_outer_join())
return 0;
for (uint keynr= 0; keynr < table->s->keys; keynr++)
{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (norvald.ryeng:3843 to 3844) Bug#13464334 | Norvald H. Ryeng | 6 Feb |
