List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:June 9 2011 6:24am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3168 to 3169) Bug#12355958
View as plain text  
 3169 Olav Sandstaa	2011-06-09
      Fix for bug Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
      
      The assert that crashes in InnoDB is caused by the ICP code pushing
      down an index condition that contains a subquery. When InnoDB
      evaluates this index condition the subquery is executed and this results in
      a second access to data in InnoDB. InnoDB does not allow a transaction
      to have more than one concurrent executing table operation and this 
      causes the assert to trigger.
      
      The ICP code should not have pushed down this index condition to InnoDB
      since it contains a subquery. The existing ICP code checks for subqueries in
      the condition by checking the value of the condition's with_subselect
      member. In this case the value for the Item_func condition's with_subselect
      is false even though one of the arguments for the Item_func is a subquery.
      This situation can occur when we initially create the item tree
      for a condition and then later substitutes non-subquery parts of it
      with a subquery. In the test case for this bug this occurs when
      optimize_conds() replaces a field reference with a subquery due to the
      subquery will be const when it is evaluated. This replacement is done
      without updating the Item_func node's with_subselect to true.
      
      The fix for this would be easy if it was possible to update the 
      with_subselect member locally when doing changes. What makes this
      more complex is that this information needs to be propagated 
      upwards in the item tree. Since we have no "parent reference" in
      the item tree this can not be done locally. In order to be able
      to propagate this information upwards in the item tree this patch
      extends Item::update_used_tables() to also update
      the value of with_subselect when traversing the item tree.
     @ mysql-test/include/icp_tests.inc
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/innodb_icp.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/innodb_icp_all.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/innodb_icp_none.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/myisam_icp.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/myisam_icp_all.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ mysql-test/r/myisam_icp_none.result
        Test for Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1".
     @ sql/item.h
        Add Item_ref::has_subquery() that calls has_subquery() on the
        item it has a ref pointer to.
     @ sql/item_cmpfunc.cc
        Extend Item_cond::update_used_tables(), 
        Item_is_not_null_test::update_used_tables(), and 
        Item_equal::update_used_tables() to update its value
        for with_subselect based on the value in its arguments.
     @ sql/item_cmpfunc.h
        Extend Item_func_isnull::update_used_tables() to propagate
        the value of with_subselect in the item tree.
     @ sql/item_func.cc
        Extend Item_func::update_used_tables() to update its value
        for with_subselect based on the value in its arguments.
     @ sql/item_row.cc
        Extend Item_row::update_used_tables() to update its value
        for with_subselect based on the value in its arguments.
     @ sql/item_strfunc.cc
        Extend Item_func_make_set::update_used_tables() to update its 
        value for with_subselect based on the value in its item object.
     @ sql/item_sum.cc
        Extend Item_sum::update_used_tables() to update its value
        for with_subselect based on the value in its arguments.

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_icp_none.result
      sql/item.h
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
      sql/item_func.cc
      sql/item_row.cc
      sql/item_strfunc.cc
      sql/item_sum.cc
 3168 Ramil Kalimullin	2011-06-08
      BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD 
      BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
      
      Problem: performing spatial operation on "wrong"
      polygons may lead to server crash.
      
      Fix: take into account some corner cases:
      polygons with 0-point linerings,
      polygons with touching linerings.
     @ mysql-test/r/gis-precise.result
        BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD 
        BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
          - test result.
     @ mysql-test/t/gis-precise.test
        BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD 
        BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
          - test case.
     @ sql/gcalc_tools.cc
        BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD 
        BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
          - calculating a shape of the resulting spatial object fixed 
        to handle polygons with touching linerings.
     @ sql/spatial.cc
        BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD 
        BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
          - check if there're points in each poligon's line ring to avoid
        OOM error.

    modified:
      mysql-test/r/gis-precise.result
      mysql-test/t/gis-precise.test
      sql/gcalc_tools.cc
      sql/spatial.cc
=== modified file 'mysql-test/include/icp_tests.inc'

=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-03-24 13:16:36 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-06-09 06:22:39 +0000
@@ -924,3 +924,43 @@
 eval $query;
 
 DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+--echo #
+
+CREATE TABLE t1 (
+  pk INTEGER PRIMARY KEY, 
+  a INTEGER NOT NULL, 
+  b CHAR(1), 
+  KEY(b)
+);
+
+INSERT INTO t1 VALUES (23,5,'d');
+
+let query=
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+      AND (a1.a != a2.a OR a1.b IS NULL);
+
+eval EXPLAIN $query;
+eval $query;
+
+# Re-run the same query using a view. This will test the code with
+# an Item_ref object in the condition tree (note: in order for this
+# to trigger the bug the patch for Bug#59696 needs to be applied first).
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+let query=
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+      AND (a1.a != a2.a OR a1.b IS NULL);
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP VIEW v1;
+DROP TABLE t1;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-03-24 13:16:36 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-06-09 06:22:39 +0000
@@ -871,5 +871,46 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	a1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result	2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/innodb_icp_all.result	2011-06-09 06:22:39 +0000
@@ -871,5 +871,46 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	a1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-03-24 13:16:36 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2011-06-09 06:22:39 +0000
@@ -870,5 +870,46 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	a1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ref	b	b	2	const	1	Using where
+3	SUBQUERY	t1	index	NULL	b	2	NULL	1	Using index
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-03-24 13:16:36 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-06-09 06:22:39 +0000
@@ -868,4 +868,43 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result	2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/myisam_icp_all.result	2011-06-09 06:22:39 +0000
@@ -868,4 +868,43 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-03-24 13:16:36 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2011-06-09 06:22:39 +0000
@@ -867,4 +867,43 @@
 1
 2
 DROP TABLE t1, t2, t3;
+#
+# Bug#12355958 "FAILING ASSERTION: TRX->LOCK.N_ACTIVE_THRS == 1"
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY, 
+a INTEGER NOT NULL, 
+b CHAR(1), 
+KEY(b)
+);
+INSERT INTO t1 VALUES (23,5,'d');
+EXPLAIN SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM t1 AS a1 JOIN (SELECT * FROM t1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM t1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+SELECT a1.pk 
+FROM v1 AS a1 JOIN (SELECT * FROM v1 LIMIT 1) AS a2 ON a2.b = a1.b
+WHERE a1.a = (SELECT pk FROM v1 LIMIT 1) 
+AND (a1.a != a2.a OR a1.b IS NULL);
+pk
+DROP VIEW v1;
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'sql/item.h'
--- a/sql/item.h	2011-06-06 12:49:55 +0000
+++ b/sql/item.h	2011-06-09 06:22:39 +0000
@@ -571,7 +571,8 @@
  protected:
   my_bool with_subselect;               /* If this item is a subselect or some
                                            of its arguments is or contains a
-                                           subselect. Computed by fix_fields. */
+                                           subselect. Computed by fix_fields
+                                           and updated by update_used_tables. */
 
  public:
   // alloc & destruct is done as start of select using sql_alloc
@@ -2701,6 +2702,14 @@
     return (*ref)->is_outer_field();
   }
 
+  /**
+    Checks if the item tree that ref points to contains a subquery.
+  */
+  virtual bool has_subquery() const 
+  { 
+    DBUG_ASSERT(ref);
+    return (*ref)->has_subquery();
+  }
 };
 
 

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-06-06 12:49:55 +0000
+++ b/sql/item_cmpfunc.cc	2011-06-09 06:22:39 +0000
@@ -4615,11 +4615,13 @@
 
   used_tables_cache=0;
   const_item_cache=1;
+  with_subselect= false;
   while ((item=li++))
   {
     item->update_used_tables();
     used_tables_cache|= item->used_tables();
     const_item_cache&= item->const_item();
+    with_subselect|= item->has_subquery();
   }
 }
 
@@ -4803,6 +4805,8 @@
   else
   {
     args[0]->update_used_tables();
+    with_subselect= args[0]->has_subquery();
+
     if (!(used_tables_cache=args[0]->used_tables()) && !with_subselect)
     {
       /* Remember if the value is always NULL or never NULL */
@@ -5827,12 +5831,14 @@
   not_null_tables_cache= used_tables_cache= 0;
   if ((const_item_cache= cond_false))
     return;
+  with_subselect= false;
   while ((item=li++))
   {
     item->update_used_tables();
     used_tables_cache|= item->used_tables();
     /* see commentary at Item_equal::update_const() */
     const_item_cache&= item->const_item() && !item->is_outer_field();
+    with_subselect|= item->has_subquery();
   }
 }
 

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2011-05-26 15:20:09 +0000
+++ b/sql/item_cmpfunc.h	2011-06-09 06:22:39 +0000
@@ -1363,6 +1363,8 @@
     else
     {
       args[0]->update_used_tables();
+      with_subselect= args[0]->has_subquery();
+
       if ((const_item_cache= !(used_tables_cache= args[0]->used_tables()) &&
           !with_subselect))
       {

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2011-06-06 12:49:55 +0000
+++ b/sql/item_func.cc	2011-06-09 06:22:39 +0000
@@ -405,11 +405,13 @@
 {
   used_tables_cache=0;
   const_item_cache=1;
+  with_subselect= false;
   for (uint i=0 ; i < arg_count ; i++)
   {
     args[i]->update_used_tables();
     used_tables_cache|=args[i]->used_tables();
     const_item_cache&=args[i]->const_item();
+    with_subselect|= args[i]->has_subquery();
   }
 }
 

=== modified file 'sql/item_row.cc'
--- a/sql/item_row.cc	2011-05-06 13:32:53 +0000
+++ b/sql/item_row.cc	2011-06-09 06:22:39 +0000
@@ -126,11 +126,13 @@
 {
   used_tables_cache= 0;
   const_item_cache= 1;
+  with_subselect= false;
   for (uint i= 0; i < arg_count; i++)
   {
     items[i]->update_used_tables();
     used_tables_cache|= items[i]->used_tables();
     const_item_cache&= items[i]->const_item();
+    with_subselect|= items[i]->has_subquery();
   }
 }
 

=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc	2011-05-26 15:20:09 +0000
+++ b/sql/item_strfunc.cc	2011-06-09 06:22:39 +0000
@@ -2571,6 +2571,7 @@
   item->update_used_tables();
   used_tables_cache|=item->used_tables();
   const_item_cache&=item->const_item();
+  with_subselect= item->has_subquery();
 }
 
 

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2011-05-26 15:20:09 +0000
+++ b/sql/item_sum.cc	2011-06-09 06:22:39 +0000
@@ -529,10 +529,12 @@
   if (!forced_const)
   {
     used_tables_cache= 0;
+    with_subselect= false;
     for (uint i=0 ; i < arg_count ; i++)
     {
       args[i]->update_used_tables();
       used_tables_cache|= args[i]->used_tables();
+      with_subselect|= args[i]->has_subquery();
     }
 
     used_tables_cache&= PSEUDO_TABLE_BITS;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3168 to 3169) Bug#12355958Olav Sandstaa9 Jun