[Commits] Rev 3150: Fixed LP bug #823189. in file:///home/igor/maria/maria-5.3-bug823189/

Igor Babaev igor at askmonty.org
Fri Aug 12 06:24:33 EEST 2011


At file:///home/igor/maria/maria-5.3-bug823189/

------------------------------------------------------------
revno: 3150
revision-id: igor at askmonty.org-20110812032432-1e617vqk61iud7xf
parent: igor at askmonty.org-20110811191104-gfn2nvlde8d7h96q
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug823189
timestamp: Thu 2011-08-11 20:24:32 -0700
message:
  Fixed LP bug #823189.
  The method Item_ref::not_null_tables() returned incorrect bitmap
  for outer references to view columns. This could cause an invalid
  conversion of an outer join into an inner join that could lead
  to a wrong result set for a query with a correlated subquery over
  an outer join whose where condition had an outer reference to a view.
-------------- next part --------------
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/view.result	2011-08-12 03:24:32 +0000
@@ -4280,11 +4280,11 @@
 EXECUTE stmt1;
 f1	f2	f3	f4
 DEALLOCATE PREPARE stmt1;
+DROP VIEW v1;
+DROP TABLE t1;
 #
 # LP BUG#806071 (2 views with ORDER BY)
 #
-DROP VIEW v1;
-DROP TABLE t1;
 CREATE TABLE t1 (f1 int);
 INSERT INTO t1 VALUES (1),(1);
 CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT f1 FROM t1;
@@ -4304,5 +4304,51 @@
 Warnings:
 Note	1926	View 'test'.'v2' ORDER BY clause ignored because there is other ORDER BY clause already.
 Note	1003	select `v1`.`f1` AS `f1`,`v1`.`f1` AS `f1` from `test`.`v1` join `test`.`v1` order by `v1`.`f1`
-drop view v1, v2;
-drop table t1;
+DROP VIEW v1, v2;
+DROP TABLE t1;
+#
+# LP bug #823189: dependent subquery with RIGHT JOIN
+#                 referencing view in WHERE
+#
+CREATE TABLE t1 (a varchar(32));
+INSERT INTO t1 VALUES ('y'), ('w');
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (a varchar(32), b int);
+CREATE TABLE t4 (a varchar(32));
+INSERT INTO t4 VALUES ('y'), ('w');
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+WHERE t4.a >= t1.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` join `test`.`t2` where (not(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` left join `test`.`t3` on(multiple equal(NULL, `test`.`t4`.`a`)) where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or isnull(NULL)))) having trigcond(<is_not_null_test>(NULL))))))
+SELECT * FROM t1, t2
+WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+WHERE t4.a >= t1.a);
+a	a
+EXPLAIN EXTENDED
+SELECT * FROM v1, t2
+WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+WHERE t4.a >= v1.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'v1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` join `test`.`t2` where (not(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` left join `test`.`t3` on(multiple equal(NULL, `test`.`t4`.`a`)) where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or isnull(NULL)))) having trigcond(<is_not_null_test>(NULL))))))
+SELECT * FROM v1, t2
+WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+WHERE t4.a >= v1.a);
+a	a
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2011-07-21 22:55:08 +0000
+++ b/mysql-test/t/view.test	2011-08-12 03:24:32 +0000
@@ -4231,13 +4231,13 @@
 
 DEALLOCATE PREPARE stmt1;
 
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo #
 --echo # LP BUG#806071 (2 views with ORDER BY)
 --echo #
 
-DROP VIEW v1;
-DROP TABLE t1;
-
 CREATE TABLE t1 (f1 int);
 INSERT INTO t1 VALUES (1),(1);
 
@@ -4247,5 +4247,42 @@
 SELECT * FROM v2 AS a1, v2 AS a2;
 EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2;
 
-drop view v1, v2;
-drop table t1;
+DROP VIEW v1, v2;
+DROP TABLE t1;
+
+--echo #
+--echo # LP bug #823189: dependent subquery with RIGHT JOIN
+--echo #                 referencing view in WHERE
+--echo #
+
+CREATE TABLE t1 (a varchar(32));
+INSERT INTO t1 VALUES ('y'), ('w');
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (10);
+
+CREATE TABLE t3 (a varchar(32), b int);
+
+CREATE TABLE t4 (a varchar(32));
+INSERT INTO t4 VALUES ('y'), ('w');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+  WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+                       WHERE t4.a >= t1.a);
+SELECT * FROM t1, t2
+  WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+                       WHERE t4.a >= t1.a);
+
+EXPLAIN EXTENDED
+SELECT * FROM v1, t2
+  WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+                       WHERE t4.a >= v1.a);
+SELECT * FROM v1, t2
+  WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a)
+                       WHERE t4.a >= v1.a);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4;

=== modified file 'sql/item.h'
--- a/sql/item.h	2011-07-21 04:55:55 +0000
+++ b/sql/item.h	2011-08-12 03:24:32 +0000
@@ -2606,7 +2606,10 @@
   {
     return (*ref)->const_item();
   }
-  table_map not_null_tables() const { return (*ref)->not_null_tables(); }
+  table_map not_null_tables() const 
+  { 
+    return depended_from ? 0 : (*ref)->not_null_tables();
+  }
   void set_result_field(Field *field)	{ result_field= field; }
   bool is_result_field() { return 1; }
   void save_in_result_field(bool no_conversions)



More information about the commits mailing list