Hi Jørgen,
thanks for looking at this.
Patch is approved.
Roy
On 03.08.10 11.20, Jorgen Loland wrote:
> #At file:///localhome/jl208045/mysql/mysql-next-mr-opt-backporting-49453/ based on
> revid:roy.lyseng@stripped
>
> 3222 Jorgen Loland 2010-08-03
> Bug#49453: "re-execution of prepared statement with
> view and semijoin crashes"
>
> When semijoin was applied on a view, the replaced items were
> pulled out instead of Item_field since the items referring
> to view columns had already been fixed. However, the semijoin
> nest still stored the item list in sj_subq_pred, and on next
> execution of the PS, this item list was reset during parsing.
> Since this list is not attached to the query, Item_fields
> referring to view columns were not replaced with
> Item_direct_view_ref as would happen in normal execution.
>
> The fix for this is to use the sj_inner_exprs list of the
> semijoin nest instead of the item list. The sj_inner_exprs list
> contains the resolved items in the subquery.
> @ mysql-test/include/subquery_sj.inc
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_all.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_all_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_all_jcl7.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_dupsweed.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_firstmatch.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_loosescan.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_loosescan_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_loosescan_jcl7.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_mat.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_mat_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_mat_jcl7.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_mat_nosj.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_none.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_none_jcl6.result
> Added test for BUG#49453
> @ mysql-test/r/subquery_sj_none_jcl7.result
> Added test for BUG#49453
> @ sql/sql_select.cc
> Use semijoin's sj_inner_exprs list instead of the subquery's
> item_list in optimize_semijoin_nest() since the item_list is
> not re-resolved on second execution of PS.
>
> modified:
> mysql-test/include/subquery_sj.inc
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> sql/sql_select.cc
> === modified file 'mysql-test/include/subquery_sj.inc'
> --- a/mysql-test/include/subquery_sj.inc 2010-06-20 07:49:07 +0000
> +++ b/mysql-test/include/subquery_sj.inc 2010-08-03 09:19:56 +0000
> @@ -2744,3 +2744,34 @@ explain extended SELECT * FROM t1 WHERE
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> drop table t1,t2,t3;
> +
> +--echo #
> +--echo # BUG#49453: re-execution of prepared statement with view
> +--echo # and semijoin crashes
> +--echo #
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +
> +INSERT INTO t1 VALUES
> + ('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +
> +CREATE VIEW v1 AS
> + SELECT country_id as vf_country_id
> + FROM t2
> + WHERE LEFT(country,1) = "A";
> +
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +--echo
> +EXECUTE stmt;
> +EXECUTE stmt;
> +
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> +
> +
>
> === modified file 'mysql-test/r/subquery_sj_all.result'
> --- a/mysql-test/r/subquery_sj_all.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_all.result 2010-08-03 09:19:56 +0000
> @@ -4454,4 +4454,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
> --- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4457,6 +4457,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
> --- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4457,6 +4457,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed.result'
> --- a/mysql-test/r/subquery_sj_dupsweed.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-08-03 09:19:56 +0000
> @@ -4453,4 +4453,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4457,6 +4457,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4457,6 +4457,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch.result'
> --- a/mysql-test/r/subquery_sj_firstmatch.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-08-03 09:19:56 +0000
> @@ -4454,4 +4454,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4458,6 +4458,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4458,6 +4458,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_loosescan.result'
> --- a/mysql-test/r/subquery_sj_loosescan.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan.result 2010-08-03 09:19:56 +0000
> @@ -4458,4 +4458,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4462,6 +4462,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4462,6 +4462,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat.result'
> --- a/mysql-test/r/subquery_sj_mat.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_mat.result 2010-08-03 09:19:56 +0000
> @@ -4454,4 +4454,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4458,6 +4458,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4458,6 +4458,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
> --- a/mysql-test/r/subquery_sj_mat_nosj.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-08-03 09:19:56 +0000
> @@ -4676,4 +4676,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none.result'
> --- a/mysql-test/r/subquery_sj_none.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_none.result 2010-08-03 09:19:56 +0000
> @@ -4602,4 +4602,37 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
> --- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-08-03 09:19:56 +0000
> @@ -4606,6 +4606,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
> --- a/mysql-test/r/subquery_sj_none_jcl7.result 2010-06-23 09:00:02 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-08-03 09:19:56 +0000
> @@ -4606,6 +4606,39 @@ i
> 1
> 2
> drop table t1,t2,t3;
> +#
> +# BUG#49453: re-execution of prepared statement with view
> +# and semijoin crashes
> +#
> +CREATE TABLE t1 (city VARCHAR(50), country_id INT);
> +CREATE TABLE t2 (country_id INT, country VARCHAR(50));
> +INSERT INTO t1 VALUES
> +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
> +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
> +CREATE VIEW v1 AS
> +SELECT country_id as vf_country_id
> +FROM t2
> +WHERE LEFT(country,1) = "A";
> +PREPARE stmt FROM "
> +SELECT city, country_id
> +FROM t1
> +WHERE country_id IN (SELECT vf_country_id FROM v1);
> +";
> +
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +EXECUTE stmt;
> +city country_id
> +Batna 2
> +Bchar 2
> +Skikda 2
> +Algeria 2
> +DROP TABLE t1,t2;
> +DROP VIEW v1;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-07-30 07:32:20 +0000
> +++ b/sql/sql_select.cc 2010-08-03 09:19:56 +0000
> @@ -4921,8 +4921,7 @@ static bool optimize_semijoin_nests(JOIN
> sjm->materialization_cost.convert_from_cost(subjoin_read_time);
> sjm->rows= subjoin_out_rows;
>
> - List<Item> &right_expr_list=
> - sj_nest->sj_subq_pred->unit->first_select()->item_list;
> + List<Item> &inner_expr_list=
> sj_nest->nested_join->sj_inner_exprs;
> /*
> Adjust output cardinality estimates. If the subquery has form
>
> @@ -4944,7 +4943,7 @@ static bool optimize_semijoin_nests(JOIN
> JOIN_TAB *tab= join->best_positions[i].table;
> join->map2table[tab->table->tablenr]= tab;
> }
> - List_iterator<Item> it(right_expr_list);
> + List_iterator<Item> it(inner_expr_list);
> Item *item;
> table_map map= 0;
> while ((item= it++))
> @@ -4963,7 +4962,7 @@ static bool optimize_semijoin_nests(JOIN
> /*
> Calculate temporary table parameters and usage costs
> */
> - uint rowlen= get_tmp_table_rec_length(right_expr_list);
> + uint rowlen= get_tmp_table_rec_length(inner_expr_list);
> double lookup_cost;
> if (rowlen * subjoin_out_rows<
> join->thd->variables.max_heap_table_size)
> lookup_cost= HEAP_TEMPTABLE_LOOKUP_COST;
>
>
>
>
>
