3749 Norvald H. Ryeng 2012-04-27
Bug#13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
VARIABLES
Outer join queries with ALL may return incorrect results because the
optimizer incorrectly rewrites them to use inner join. E.g.:
SELECT *
FROM t2 RIGHT JOIN t3 ON(t3.c = t2.b)
WHERE t2.b < ALL(SELECT t1.a FROM t1 WHERE t1.a <= 7);
is first rewritten by Item_in_subselect::single_value_transformer()
into:
SELECT *
FROM t2 RIGHT JOIN t3 ON(t3.c = t2.b)
WHERE <not>(t2.b >= (SELECT MIN(t1.a) FROM t1 WHERE t1.a <= 7));
When simplify_joins() checks not_null_tables() on the <not> condition
to find out if the outer join can be transformed into an inner join,
Item_func::not_null_tables() returns its arguments'
not_null_tables(). This means that simplify_joins() is told that the
condition will be false if t2.b is NULL. But the condition is actually
true if t1 has no rows where t1.a <= 7. This leads to the optimizer
incorrectly rewriting the query to use inner join.
Fix: Let Item_func_not_all::not_null_tables() return a zero table map.
@ mysql-test/include/subquery.inc
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_all.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_all_bka.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_all_bka_nixbnl.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_nomat_nosj.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_nomat_nosj_bka.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_none.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_none_bka.result
Add test cases for bugs #13735712.
@ mysql-test/r/subquery_none_bka_nixbnl.result
Add test cases for bugs #13735712.
@ sql/item_cmpfunc.h
Return 0 from Item_func_not_all::not_null_tables().
modified:
mysql-test/include/subquery.inc
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
sql/item_cmpfunc.h
3748 Sunny Bains 2012-04-27
Bug#14004341 - REDUCE MEMCPY() OVERHEAD IN ROW_SEARCH_FOR_MYSQL()
InnoDB uses a read ahead cache for rows that can potentially be accessed in a
batch. This is to avoid the overhead of mini-transactions. Currently, all
records are copied to this fetch cache and when we exit from the function
we copy the first record from this queue to the MySQL buffer. Avoid this copy
to the fetch cache queue and back on normal exit from the function by doing the
changes directly in the MySQL recod buffer for the first record.
This eliminates two memcpy() calls but is only done for non-ICP code paths.
rb://1046 Approved by Jimmy.
modified:
storage/innobase/row/row0sel.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2012-03-27 12:58:14 +0000
+++ b/mysql-test/include/subquery.inc 2012-04-27 06:33:39 +0000
@@ -6067,3 +6067,25 @@ eval SELECT *
);
DROP TABLE t2,t1;
+
+--echo #
+--echo # Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+--echo # VARIABLES
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+
+DROP TABLE t1, t2, t3;
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-03-27 12:58:14 +0000
+++ b/mysql-test/r/subquery_all.result 2012-04-27 06:33:39 +0000
@@ -7375,4 +7375,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2012-04-27 06:33:39 +0000
@@ -7375,4 +7375,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result 2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-04-27 06:33:39 +0000
@@ -7376,5 +7376,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none.result 2012-04-27 06:33:39 +0000
@@ -7374,4 +7374,26 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none_bka.result 2012-04-27 06:33:39 +0000
@@ -7375,5 +7375,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result 2012-03-22 08:13:35 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result 2012-04-27 06:33:39 +0000
@@ -7375,5 +7375,27 @@ AND ( table1.col_varchar_key LIKE '%a%'
);
field1 field2 field3 field4 field5 field6
DROP TABLE t2,t1;
+#
+# Bug #13735712 SELECT W/ SUBQUERY PRODUCES MORE ROWS WHEN USING
+# VARIABLES
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(9);
+CREATE TABLE t2(b INT);
+INSERT INTO t2 VALUES(8);
+CREATE TABLE t3(c INT);
+INSERT INTO t3 VALUES(3);
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7);
+b c
+NULL 3
+SET @var = 7;
+SELECT *
+FROM t2 RIGHT JOIN t3 ON(c = b)
+WHERE b < ALL(SELECT a FROM t1 WHERE a <= @var);
+b c
+NULL 3
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2012-03-06 14:29:42 +0000
+++ b/sql/item_cmpfunc.h 2012-04-27 06:33:39 +0000
@@ -546,6 +546,30 @@ public:
void set_sum_test(Item_sum_hybrid *item) { test_sum_item= item; };
void set_sub_test(Item_maxmin_subselect *item) { test_sub_item= item; };
void set_subselect(Item_subselect *item) { subselect= item; }
+ table_map not_null_tables() const
+ {
+ /*
+ See handling of not_null_tables_cache in
+ Item_in_optimizer::fix_fields().
+
+ This item is the result of a transformation from an ALL clause
+ such as
+ left-expr < ALL(subquery)
+ into
+ <not>(left-expr >= (subquery)
+
+ An inequality usually rejects NULLs from both operands, so the
+ not_null_tables() of the inequality is the union of the
+ null-rejecting tables of both operands. However, since this is a
+ transformed ALL clause that should return true if the subquery
+ is empty (even if left-expr is NULL), it is not null rejecting
+ for left-expr. The not null tables mask for left-expr should be
+ removed, leaving only the null-rejecting tables of the
+ subquery. Item_subselect::not_null_tables() always returns 0 (no
+ null-rejecting tables). Therefore, always return 0.
+ */
+ return 0;
+ }
bool empty_underlying_subquery();
Item *neg_transformer(THD *thd);
};
@@ -558,6 +582,7 @@ public:
Item_func_nop_all(Item *a) :Item_func_not_all(a) {}
longlong val_int();
const char *func_name() const { return "<nop>"; }
+ table_map not_null_tables() const { return not_null_tables_cache; }
Item *neg_transformer(THD *thd);
};
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (norvald.ryeng:3748 to 3749) Bug#13735712 | Norvald H. Ryeng | 27 Apr |
