From: Jorgen Loland
Date: November 15 2010 2:28pm
Subject: Re: bzr commit into mysql-next-mr-bugfixing branch (olav.sandstaa:3281)
 Bug#56529
List-Archive: http://lists.mysql.com/commits/123917
Message-Id: <4CE1438A.4040700@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Olav,

The patch looks good. See one comment inline.

On 11/15/2010 01:42 PM, Olav Sandstaa wrote:
> #At file:///export/home/tmp/mysql/opt-bug56529-v2/ based on revid:olav.sandstaa@stripped
>
>   3281 Olav Sandstaa	2010-11-15
>        Fix for Bug#56529 Crash due to long semaphore wait in InnoDB with ICP and subqueries
>
>        This crash was caused by the server pushing down an index condition to
>        InnoDB that contained a subselect. When InnoDB executed this index
>        condition the subselect would open a new handler object and access
>        InnoDB a second time. This caused the same transaction to have two
>        active data accesses to InnoDB which triggered the assert in InnoDB.
>
>        The fix for this is to not push down index conditions to the handler
>        that contain a subselect.
>       @ mysql-test/include/icp_tests.inc
>          Test case for Bug#56529 Crash due to long semaphore wait in InnoDB
>          with ICP and subqueries.
>       @ mysql-test/r/innodb_icp.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ mysql-test/r/innodb_icp_all.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ mysql-test/r/innodb_icp_none.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ mysql-test/r/myisam_icp.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ mysql-test/r/myisam_icp_all.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ mysql-test/r/myisam_icp_none.result
>          Result file for Test case for Bug#56529 Crash due to long semaphore
>          wait in InnoDB with ICP and subqueries.
>       @ sql/sql_select.cc
>          We should not push down index conditions to the handler that contains
>          a subselect. This was already handled by the default case of the
>          switch in uses_index_fields_only() but due to an earlier check in
>          uses_index_fields() the code for checking that the condition only
>          used "other tables" could return ok early.
>
>          Fix this by adding an explicite check for if the condition
>          contains a subselect and if so return false.
>
>      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/sql_select.cc
> === modified file 'mysql-test/include/icp_tests.inc'
>
> === modified file 'mysql-test/include/icp_tests.inc'
> --- a/mysql-test/include/icp_tests.inc	2010-11-08 08:49:16 +0000
> +++ b/mysql-test/include/icp_tests.inc	2010-11-15 12:42:32 +0000
> @@ -515,3 +515,61 @@
>   EXPLAIN SELECT * FROM t1 WHERE (c1<= '6566-06-15' AND c2<>  3);
>
>   DROP TABLE t1;
> +
> +--echo #
> +--echo # Bug#56529 - "Crash due to long semaphore wait in InnoDB
> +--echo #              with ICP and subqueries"
> +--echo #
> +
> +CREATE TABLE t1 (
> +  col_int_nokey INTEGER,
> +  col_int_key INTEGER,
> +  col_varchar_key VARCHAR(1),
> +
> +  KEY (col_int_key),
> +  KEY (col_varchar_key, col_int_key)
> +);
> +
> +INSERT INTO t1 VALUES (NULL,2,'w');
> +INSERT INTO t1 VALUES (7,9,'m');
> +INSERT INTO t1 VALUES (9,3,'m');
> +INSERT INTO t1 VALUES (7,9,'k');
> +INSERT INTO t1 VALUES (4,NULL,'r');
> +INSERT INTO t1 VALUES (2,9,'t');
> +INSERT INTO t1 VALUES (6,3,'j');
> +INSERT INTO t1 VALUES (8,8,'u');
> +INSERT INTO t1 VALUES (NULL,8,'h');
> +INSERT INTO t1 VALUES (5,53,'o');
> +INSERT INTO t1 VALUES (NULL,0,NULL);
> +INSERT INTO t1 VALUES (6,5,'k');
> +INSERT INTO t1 VALUES (188,166,'e');
> +INSERT INTO t1 VALUES (2,3,'n');
> +INSERT INTO t1 VALUES (1,0,'t');
> +INSERT INTO t1 VALUES (1,1,'c');
> +INSERT INTO t1 VALUES (0,9,'m');
> +INSERT INTO t1 VALUES (9,5,'y');
> +INSERT INTO t1 VALUES (NULL,6,'f');
> +
> +CREATE TABLE t2 (
> +  c1 INTEGER NOT NULL
> +);
> +
> +let query=
> +SELECT table1.col_int_nokey
> +FROM
> +  (
> +    t1 AS table1 STRAIGHT_JOIN
> +      (
> +        (
> +          t1 AS table2 INNER JOIN t1 AS table3
> +          ON (table3.col_varchar_key = table2.col_varchar_key)
> +        )
> +      )
> +    ON (table3.col_int_nokey = table1.col_int_key)
> +  )
> +WHERE table3.col_int_key != SOME ( SELECT c1 FROM t2 );

This is a quite a bit more verbose than we normally do in our test files. I 
suggest removing as many newlines as possible while maintaining fair readability.

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway