bzr push into mysql-trunk branch (tor.didriksen:3710 to 3711) Bug#13783777

[Tor Didriksen]

 3711 Tor Didriksen	2012-03-01
      Bug#13783777 CONSTANT PROPAGATION IS WRONG FOR DISJUNCTIVE PREDICATES IN VIEWS
      
      This predicate: pk > 8 OR ((pk BETWEEN 9 AND 13) AND pk = 90)
      was optimized into : (90 > 8)
      
      The reason is sharing of the underlying Item_field.
      Solution: disable equality propagation for views.
     @ mysql-test/r/join.result
        One execution plan modification.
     @ mysql-test/r/view.result
        New test case.
     @ mysql-test/t/view.test
        New test case.
     @ sql/item.h
        Implement Item_direct_view_ref::subst_argument_checker()

    modified:
      mysql-test/r/join.result
      mysql-test/r/view.result
      mysql-test/t/view.test
      sql/item.h
 3710 Marc Alff	2012-03-01
      Fixed robustness of tests hostcache_ipv4_max_con, hostcache_ipv6_max_con

    modified:
      mysql-test/suite/perfschema/r/hostcache_ipv4_max_con.result
      mysql-test/suite/perfschema/r/hostcache_ipv6_max_con.result
      mysql-test/suite/perfschema/t/hostcache_ipv4_max_con.test
      mysql-test/suite/perfschema/t/hostcache_ipv6_max_con.test
      sql/mysqld.cc
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/join.result	2012-03-01 11:38:57 +0000
@@ -1250,8 +1250,8 @@ CREATE VIEW v_t2 AS SELECT * FROM t2;
 EXPLAIN SELECT v_t2.pk, v_t2.c1 FROM v_t2, t1 
 WHERE v_t2.pk = t1.c1 AND v_t2.pk >= 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.c1	1	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.c1	1	Using index condition
 SELECT v_t2.pk, v_t2.c1 FROM v_t2, t1 
 WHERE v_t2.pk = t1.c1 AND v_t2.pk >= 2;
 pk	c1

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/view.result	2012-03-01 11:38:57 +0000
@@ -4185,3 +4185,29 @@ ORDER BY f1 ;
 f1
 DROP TABLE t1, t2;
 DROP VIEW v_t1, v_t2;
+#
+# Bug#13783777 CONSTANT PROPAGATION IS WRONG FOR
+#              DISJUNCTIVE PREDICATES IN VIEWS
+#
+CREATE TABLE t1 (
+pk INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1), (2);
+CREATE VIEW v_t1 AS SELECT * FROM t1;
+SELECT pk
+FROM t1
+WHERE
+pk > 8
+OR ((pk BETWEEN 9 AND 13) AND pk = 90)
+;
+pk
+SELECT pk
+FROM v_t1
+WHERE
+pk > 8
+OR ((pk BETWEEN 9 AND 13) AND pk = 90)
+;
+pk
+DROP VIEW v_t1;
+DROP TABLE t1;

=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test	2012-02-27 10:00:05 +0000
+++ b/mysql-test/t/view.test	2012-03-01 11:38:57 +0000
@@ -4284,6 +4284,39 @@ ORDER BY f1 ;
 DROP TABLE t1, t2;
 DROP VIEW v_t1, v_t2;
 
+--echo #
+--echo # Bug#13783777 CONSTANT PROPAGATION IS WRONG FOR
+--echo #              DISJUNCTIVE PREDICATES IN VIEWS
+--echo #
+
+CREATE TABLE t1 (
+  pk INTEGER,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE VIEW v_t1 AS SELECT * FROM t1;
+
+# Query directly against the table (correct result)
+SELECT pk
+FROM t1
+WHERE
+  pk > 8
+  OR ((pk BETWEEN 9 AND 13) AND pk = 90)
+;
+
+# Query against a view (wrong result)
+SELECT pk
+FROM v_t1
+WHERE
+  pk > 8
+  OR ((pk BETWEEN 9 AND 13) AND pk = 90)
+;
+
+DROP VIEW v_t1;
+DROP TABLE t1;
+
 
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.

=== modified file 'sql/item.h'
--- a/sql/item.h	2012-02-29 15:28:26 +0000
+++ b/sql/item.h	2012-03-01 11:38:57 +0000
@@ -3089,6 +3089,16 @@ public:
   Item_direct_view_ref(THD *thd, Item_direct_ref *item)
     :Item_direct_ref(thd, item) {}
 
+  /*
+    We share one underlying Item_field, so we have to disable
+    build_equal_items_for_cond().
+    TODO: Implement multiple equality optimization for views.
+  */
+  virtual bool subst_argument_checker(uchar **arg)
+  {
+    return false;
+  }
+
   bool fix_fields(THD *, Item **);
   bool eq(const Item *item, bool binary_cmp) const;
   Item *get_tmp_table_item(THD *thd)

No bundle (reason: useless for push emails).