[Commits] Rev 3604: MDEV-3873 & MDEV-3876 : Wrong result (extra rows) with ALL subquery from a MERGE view in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-3873/

sanja at montyprogram.com sanja at montyprogram.com
Thu Nov 22 23:48:41 EET 2012


At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-3873/

------------------------------------------------------------
revno: 3604
revision-id: sanja at montyprogram.com-20121122214839-c1ekbee7se35wyda
parent: sergii at pisem.net-20121117155015-4ab41ncach4iavao
committer: sanja at montyprogram.com
branch nick: work-maria-5.3-MDEV-3873
timestamp: Thu 2012-11-22 23:48:39 +0200
message:
  MDEV-3873 & MDEV-3876 : Wrong result (extra rows) with ALL subquery from a MERGE view
  
  Problem was in lost ability to be null for table of left jopin if it is view.
  
  Fixed algorithm detection null-tables in left joins which takes into account views...
-------------- next part --------------
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2012-10-10 19:42:50 +0000
+++ b/mysql-test/r/view.result	2012-11-22 21:48:39 +0000
@@ -4610,6 +4610,84 @@ f2	f1
 7	NULL
 8	NULL
 drop tables t1,t2;
+#
+# MDEV-3873 Wrong result (extra rows) with NOT IN and a subquery
+# from a MERGE view
+#
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(7),(0);
+CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (4),(6),(3);
+CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (4),(5),(3);
+CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO tv VALUES (1),(3);
+CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv;
+CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv;
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	5	test.t3.c	2	100.00	Using where
+3	DERIVED	tv	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((not(<expr_cache><`test`.`t1`.`a`,`test`.`t2`.`b`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `v_temptable`.`e` from `test`.`t3` left join `test`.`v_temptable` on((`v_temptable`.`e` = `test`.`t3`.`c`)) where ((`test`.`t3`.`c` <> `test`.`t2`.`b`) and ((<cache>(`test`.`t1`.`a`) = `v_temptable`.`e`) or isnull(`v_temptable`.`e`))) having <is_not_null_test>(`v_temptable`.`e`)))))) and (`test`.`t1`.`a` < `test`.`t2`.`b`))
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	tv	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((not(<expr_cache><`test`.`t1`.`a`,`test`.`t2`.`b`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`tv`.`e` from `test`.`t3` left join (`test`.`tv`) on((`test`.`tv`.`e` = `test`.`t3`.`c`)) where ((`test`.`t3`.`c` <> `test`.`t2`.`b`) and ((<cache>(`test`.`t1`.`a`) = `test`.`tv`.`e`) or isnull(`test`.`tv`.`e`))) having <is_not_null_test>(`test`.`tv`.`e`)))))) and (`test`.`t1`.`a` < `test`.`t2`.`b`))
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN tv ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN tv ON (c = e) WHERE c <> b ) AND a < b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	tv	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t2.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((not(<expr_cache><`test`.`t1`.`a`,`test`.`t2`.`b`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`tv`.`e` from `test`.`t3` left join `test`.`tv` on((`test`.`tv`.`e` = `test`.`t3`.`c`)) where ((`test`.`t3`.`c` <> `test`.`t2`.`b`) and ((<cache>(`test`.`t1`.`a`) = `test`.`tv`.`e`) or isnull(`test`.`tv`.`e`))) having <is_not_null_test>(`test`.`tv`.`e`)))))) and (`test`.`t1`.`a` < `test`.`t2`.`b`))
+drop view v_temptable, v_merge;
+drop table t1,t2,t3,t4,tv;
+#
+# MDEV-3876 Wrong result (extra rows) with ALL subquery
+# from a MERGE view
+#
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(3);
+CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2;
+SELECT a FROM t1 AS alias
+WHERE a >= ALL ( 
+SELECT b FROM t1 LEFT JOIN v1 ON (a = b) 
+WHERE a = alias.a );
+a
+1
+drop view v1;
+drop table t1,t2;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2012-10-10 19:42:50 +0000
+++ b/mysql-test/t/view.test	2012-11-22 21:48:39 +0000
@@ -4548,6 +4548,74 @@ SELECT * FROM (
 
 drop tables t1,t2;
 
+--echo #
+--echo # MDEV-3873 Wrong result (extra rows) with NOT IN and a subquery
+--echo # from a MERGE view
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(7),(0);
+
+CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+
+CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (4),(6),(3);
+
+CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (4),(5),(3);
+
+CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO tv VALUES (1),(3);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv;
+CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv;
+
+
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b;
+
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b;
+
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b;
+
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b;
+
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN tv ON (c = e) WHERE c <> b ) AND a < b;
+
+explain extended
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN tv ON (c = e) WHERE c <> b ) AND a < b;
+
+drop view v_temptable, v_merge;
+drop table t1,t2,t3,t4,tv;
+
+--echo #
+--echo # MDEV-3876 Wrong result (extra rows) with ALL subquery
+--echo # from a MERGE view
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(3);
+
+CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2;
+
+SELECT a FROM t1 AS alias
+WHERE a >= ALL ( 
+SELECT b FROM t1 LEFT JOIN v1 ON (a = b) 
+WHERE a = alias.a );
+
+drop view v1;
+drop table t1,t2;
 
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.3 tests.

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2012-11-20 12:57:49 +0000
+++ b/sql/mysql_priv.h	2012-11-22 21:48:39 +0000
@@ -2696,11 +2696,15 @@ inline void setup_table_map(TABLE *table
   table->null_row= 0;
   table->status= STATUS_NO_RECORD;
   table->maybe_null= table_list->outer_join;
-  TABLE_LIST *embedding= table_list->embedding;
+  TABLE_LIST *embedding= (table_list->embedding ?
+                          table_list->embedding :
+                          table_list->referencing_view);
   while (!table->maybe_null && embedding)
   {
     table->maybe_null= embedding->outer_join;
-    embedding= embedding->embedding;
+    embedding= (embedding->embedding ?
+                embedding->embedding :
+                embedding->referencing_view);
   }
   table->tablenr= tablenr;
   table->map= (table_map) 1 << tablenr;



More information about the commits mailing list