MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:December 8 2009 2:33pm
Subject:bzr commit into mysql-6.0-codebase-bugfixing branch (tor.didriksen:3763)
Bug#49097
View as plain text  
#At file:///export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-sj-view/ based on revid:roy.lyseng@stripped

 3763 Tor Didriksen	2009-12-08
      Bug#49097 subquery with view generates wrong result with non-prepared statement.
      
      The problem was that flatten_subqueries() would only flatten the
      original where clause of the outer query, thus losing the
      where clause of the view.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        In flatten_subqueries():
        Use the augmented 'conds' which is produced by setup_conds() when preparing views.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-12-02 07:08:26 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-12-08 10:05:48 +0000
@@ -611,3 +611,73 @@ DROP VIEW v1,v2;
 DROP PROCEDURE p1;
 set SESSION optimizer_switch='default';
 # End of BUG#48834
+
+Bug#49097 subquery with view generates wrong result with
+non-prepared statement
+
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'XAmerican Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+;
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM v1
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+PREPARE stmt FROM
+"
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+";
+execute stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+deallocate prepare stmt;
+drop table t1, t2;
+drop view v1;
+# End of Bug#49097

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-12-02 07:08:26 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-12-08 10:05:48 +0000
@@ -615,6 +615,76 @@ DROP VIEW v1,v2;
 DROP PROCEDURE p1;
 set SESSION optimizer_switch='default';
 # End of BUG#48834
+
+Bug#49097 subquery with view generates wrong result with
+non-prepared statement
+
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'XAmerican Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+;
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+SELECT country_id 
+FROM v1
+);
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+PREPARE stmt FROM
+"
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+";
+execute stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+deallocate prepare stmt;
+drop table t1, t2;
+drop view v1;
+# End of Bug#49097
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-12-02 07:08:26 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-12-08 10:05:48 +0000
@@ -533,3 +533,73 @@ DROP PROCEDURE p1;
 set SESSION optimizer_switch='default';
 
 --echo # End of BUG#48834
+
+--echo
+--echo Bug#49097 subquery with view generates wrong result with
+--echo           non-prepared statement
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+CREATE TABLE t1 (
+  city VARCHAR(50) NOT NULL,
+  country_id SMALLINT UNSIGNED NOT NULL
+);
+
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+
+CREATE TABLE t2 (
+  country_id SMALLINT UNSIGNED NOT NULL,
+  country VARCHAR(50) NOT NULL
+);
+
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(3,'XAmerican Samoa') ;
+
+CREATE VIEW v1 AS 
+SELECT country_id, country 
+FROM t2
+WHERE LEFT(country,1) = "A" 
+; 
+
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM t2
+  WHERE LEFT(country,1) = "A" 
+);
+
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+
+PREPARE stmt FROM
+"
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (
+  SELECT country_id 
+  FROM v1
+);
+";
+
+execute stmt;
+
+deallocate prepare stmt;
+drop table t1, t2;
+drop view v1;
+
+--echo # End of Bug#49097

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-12-08 12:50:53 +0000
+++ b/sql/sql_select.cc	2009-12-08 10:05:48 +0000
@@ -656,8 +656,9 @@ JOIN::prepare(Item ***rref_pointer_array
 
       in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
 
-      /* Register the subquery for further processing */
-      select_lex->outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
+      /* Register the subquery for further processing in flatten_subqueries() */
+      select_lex->
+        outer_select()->join->sj_subselects.append(thd->mem_root, in_subs);
       in_subs->expr_join_nest= thd->thd_marker.emb_on_expr_nest;
     }
     else
@@ -3574,8 +3575,17 @@ bool JOIN::flatten_subqueries()
   for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back(); 
        in_subq != in_subq_end; in_subq++)
   {
-    JOIN *child_join= (*in_subq)->unit->first_select()->join;
+    st_select_lex *child_select= (*in_subq)->get_select_lex();
+    JOIN *child_join= child_select->join;
     child_join->outer_tables = child_join->tables;
+
+    /*
+      In setup_conds() we do prepare_where() for all views,
+      but fix_prepare_information() will save the result only for prepared
+      statements. Pick up the augmented conds here, before flattening the query.
+    */
+    child_select->where= child_join->conds;
+
     if (child_join->flatten_subqueries())
       DBUG_RETURN(TRUE);
     (*in_subq)->sj_convert_priority= 


Attachment: [text/bzr-bundle] bzr/tor.didriksen@sun.com-20091208100548-kg6c79wz5xo8hynu.bundle
Thread
bzr commit into mysql-6.0-codebase-bugfixing branch (tor.didriksen:3763)Bug#49097Tor Didriksen8 Dec