[Commits] Rev 3149: Fixed LP bug #823826. in file:///home/igor/maria/maria-5.3-bug823826/

Igor Babaev igor at askmonty.org
Thu Aug 11 22:11:05 EEST 2011


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

------------------------------------------------------------
revno: 3149
revision-id: igor at askmonty.org-20110811191104-gfn2nvlde8d7h96q
parent: timour at askmonty.org-20110809153426-jxdwowvzgbo8ri2o
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug823826
timestamp: Thu 2011-08-11 12:11:04 -0700
message:
  Fixed LP bug #823826.
  The method Item_func_isnull::update_used_tables() erroneously did not
  update cached values stored in the fields used_tables_cache and
  const_item_cache of the Item_func_isnull objects. As a result the
  Item_func_isnull::used_tables() returned wrong bitmaps and, as a
  consequence, push-down predicates could be attached to wrong tables.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result	2011-08-09 05:02:10 +0000
+++ b/mysql-test/r/derived_view.result	2011-08-11 19:11:04 +0000
@@ -1214,5 +1214,33 @@
 SELECT * FROM (SELECT * FROM (SELECT @b) AS t) AS s;
 @b
 NULL
-set optimizer_switch=@exit_optimizer_switch;
+set optimizer_switch='derived_merge=on';
+#
+# LP bug #823826: view over join + IS NULL in WHERE
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (1), (1);
+CREATE TABLE t2 (b int) ;
+INSERT INTO t2 VALUES (9), (NULL), (7);
+CREATE VIEW v1 AS SELECT * FROM t1,t2;
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+a	b
+1	NULL
+1	NULL
+EXPLAIN
+SELECT * FROM v1 WHERE b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM v1 WHERE b IS NULL;
+a	b
+1	NULL
+1	NULL
+DROP VIEW v1;
+DROP TABLE t1,t2;
 set optimizer_switch=@exit_optimizer_switch;

=== modified file 'mysql-test/t/derived_view.test'
--- a/mysql-test/t/derived_view.test	2011-08-09 05:02:10 +0000
+++ b/mysql-test/t/derived_view.test	2011-08-11 19:11:04 +0000
@@ -735,7 +735,30 @@
 EXPLAIN
 SELECT * FROM (SELECT * FROM (SELECT @b) AS t) AS s;
 SELECT * FROM (SELECT * FROM (SELECT @b) AS t) AS s;
-set optimizer_switch=@exit_optimizer_switch;
+set optimizer_switch='derived_merge=on';
+
+--echo #
+--echo # LP bug #823826: view over join + IS NULL in WHERE
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (1), (1);
+
+CREATE TABLE t2 (b int) ;
+INSERT INTO t2 VALUES (9), (NULL), (7);
+
+CREATE VIEW v1 AS SELECT * FROM t1,t2;
+
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+
+EXPLAIN
+SELECT * FROM v1 WHERE b IS NULL;
+SELECT * FROM v1 WHERE b IS NULL;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
 
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2011-07-20 18:48:41 +0000
+++ b/sql/item_cmpfunc.h	2011-08-11 19:11:04 +0000
@@ -1365,7 +1365,11 @@
       const_item_cache= 1;
     }
     else
+    {
       args[0]->update_used_tables();
+      used_tables_cache= args[0]->used_tables();
+      const_item_cache= args[0]->const_item();
+    }
   }
   table_map not_null_tables() const { return 0; }
   optimize_type select_optimize() const { return OPTIMIZE_NULL; }



More information about the commits mailing list